Menu
I have some data in H2Database file and I want to convert it to MySQL .sql database file. What are the methods I can follow?
687
Select each table that has data and choose the Database Conversion > Table option. Using the multiple tables option makes troubleshooting harder, don't do that. Convert each of the H2 tables to a . sql file (assuming MySQL) and don't use RazorSQL to upload the data to the new db automatically.
Select a table in schema editor. Right/Alternate click the table name. Generate SQL. Show Create.
MySQL is a server - based database - it runs as a separate process from your application, and is commonly used in production deployments. H2 is a lightweight database, which can run entirely in-memory, or with disk storage, either in your application's process (embedded) or in a separate process.
Use the following command for the same. BACKUP TO 'backup. zip'; On executing the above command, you will get the backup.
In answer to Thomas Mueller, SquirrelSQL worked fine for me. Here is the procedure for Windows to convert a H2 database:
Go to "drivers list", where everything is red by default.
Select "H2" driver, and specify the full path to "h2-1.3.173.jar" (for example) in "Extra Class Path". The H2 driver should display a blue check in the list.
Select your target driver (PostgreSQL, MySQL), and do the same, for example for PostgreSQL, specify the full path to "postgresql-9.4-1201.jdbc41.jar" in Extra Class Path.
Go to "Aliases", then click on "+" for H2 : configure your JDBC chain, for example copy/paste the jdbc chain you obtain when you launch H2, and do the same for your target database: click on "+", configure and "test".
When you double click on your alias, you should see everything inside your database in a new Tab. Go to the tables in source database, do a multi-select on all your tables and do a right-click : "Copy Table".
Go to your target database from Alias, and do a "Paste Table". When all tables are copied altogether, the foreign key references are also generated.
Check your primary keys : from H2 to PostgreSQL, I lost the Primary Key constraints, and the auto-increment capability. You could also rename columns and tables by a right click : "refactor". I used it to rename reserved words columns after full copy, by disabling name check in options.
This worked well for me.
132
The SQL script generated by the H2 database is not fully compatible with the SQL supported by MySQL. You would have to change the SQL script manually. This requires that you know both H2 and MySQL quite well.
To avoid this problem, an alternative, probably simpler way to copy the data from H2 to MySQL is to use a 3rd party tool such as the SQuirreL SQL together with the SQuirreL DB Copy Plugin plugin. (First you need to install SQuirreL SQL and on top of that the SQuirreL DB Copy Plugin.)
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
