How to export a mySql database to a embedded database (for example H2)?

We have been working on a project. In the beginning we had some database issues, so we used a mySQL-server database to work around this.

Now we really should get back to an embedded database, access is out of the question (has to be cross-platform)

Our mentor suggested using an H2 embedded database, but we our sql-dump is g开发者_如何学Pythonetting syntax errors if we try to run it in the console of H2.

Any thoughts? Thanks in advance!

To generate suitable for H2 SQL script on unix system you may try the following:

mysqldump -u root -c --p --skip-opt db_name | sed -e "/^--/d" -e 's/`//g' -e 's/bigint(20)/numeric(20)/g' -e 's/double\(([^(]*)\)/double/' -e 's/int(11)/integer/g' -e '/^\/\*.*\*\//d' -e '/^LOCK TABLES/d' -e '/^\/\*/,/\*\//c\;' -e '/^CREATE TABLE/,/);/{/^  KEY/d; /^  PRIMARY KEY/ s/,$//}' > db.sql

Currently it is not supporting conversion of all mysql specific statements, feel free to edit and add additional conversions.

The SQL script generated by MySQL is made to run against MySQL. It contains options and features that other databases don't support.

As described in a related question, you could try creating the dump using the compatibility option. But you may still need to fix problems manually.