How to convert the H2Database database file to MySQL.sql database file?

Question

I have some data in an H2Database file and I want to convert it to a MySQL .sql database .sql . What methods can I follow?

Answer 1

In response to Thomas Muller, SquirrelSQL did a great job with me. The following is the procedure for Windows to convert the H2 database:

• Go to the "driver list", where by default everything is red.

• Select the "H2" driver and specify the full path to "h2-1.3.173.jar" (for example) in "An instance of an additional class." The H2 driver should display a blue check list.

• Select the target driver (PostgreSQL, MySQL) and do the same, for example, for PostgreSQL, specify the full path to "postgresql-9.4-1201.jdbc41.jar" in the path of the additional class.

• Go to "Aliases", then click "+" for H2: configure the JDBC chain, for example, copy / paste the jdbc chain that you get when H2 starts, and do the same for your target database: click "+", configure and "check."

• If you double-click your nickname, you will see everything inside your database in a new tab. Go to the tables in the source database, make a multi-selection in all of your tables and right-click: "Copy Table".

• Go to your target database from Alias ​​and make the "Insert Table". When all tables are fully copied, links to foreign keys are also generated.

• Check your primary keys: from H2 to PostgreSQL, I have lost the limitations of the main key and the ability to automatically increase. You can also rename columns and tables with a right-click: "refactor". I used it to rename reserved columns of words after a full copy, disabling name verification in the parameters.

  It worked for me.

Answer 2

The SQL script generated by the H2 database is not fully compatible with SQL supported by MySQL. You will have to modify the SQL script manually. This requires you to know both H2 and MySQL pretty well.

To avoid this problem, an alternative, possibly simpler way to copy data from H2 to MySQL is to use a third-party tool such as SQuirreL SQL along with the SQuirreL DB Copy Plugin plugin . (First you need to install SQuirreL SQL and, in addition, SQuirreL DB Copy Plugin.)

Answer 3

I created a Groovy script that does the transition from h2 to mysql. From there you can do mysqldump. This requires tables to exist in the Mysql database. It should work on a DBMS with minor changes.

 @Grapes( [ @Grab(group='mysql', module='mysql-connector-java', version='5.1.26'), @Grab(group='com.h2database', module='h2', version='1.3.166'), @GrabConfig(systemClassLoader = true) ]) import groovy.sql.Sql def h2Url='jdbc:h2:C:\\Users\\xxx\\Desktop\\h2\\sonardata\\sonar' def h2User='sonar' def h2Passwd='sonar' def mysqlUrl='jdbc:mysql://10.56.xxx.xxx:3306/sonar?useunicode=true&characterencoding=utf8&rewritebatchedstatements=true' def mysqlUser='sonar' def mysqlPasswd='xxxxxx' def mysqlDatabase='sonar' sql = Sql.newInstance(h2Url, h2User, h2Passwd, 'org.h2.Driver' ) def tables = [:] sql.eachRow("select * from information_schema.columns where table_schema='PUBLIC'") { if(!it.TABLE_NAME.endsWith("_MY")) { if (tables[it.TABLE_NAME] == null) { tables[it.TABLE_NAME] = [] } tables[it.TABLE_NAME] += it.COLUMN_NAME; } } tables.each{tab, cols -> println("processing $tab") println("droppin $tab"+"_my") sql.execute("DROP TABLE IF EXISTS "+tab+"_my;") sql.execute("create linked table "+tab+"_my ('com.mysql.jdbc.Driver', '"+mysqlUrl+"', '"+mysqlUser+"', '"+mysqlPasswd+"', '"+mysqlDatabase+"."+tab.toLowerCase()+"');") sql.eachRow("select count(*) as c from " + tab + "_my"){println("deleting $it.c entries from mysql table")} result = sql.execute("delete from "+tab+"_my") colString = cols.join(", ") sql.eachRow("select count(*) as c from " + tab){println("starting to copy $it.c entries")} sql.execute("insert into " + tab + "_my ("+colString+") select "+colString+" from " + tab) } 

Answer 4

The H2 database allows you to create an SQL script using the SCRIPT SQL statement or the Script command line tool . You may need to configure the script before you can run it in the MySQL database.