After working with some sqlite databases for one of my iOS projects lately, I have now decided to also add an online service for the project. But how can I migrate from sqlite to MySQL?

Here are the basic three steps, I figured out that are needed to extract and modify the DDL to be ready for an import to MySQL:

  1. 1. Export the SQLite database with sqlite and command parameter “.dump” – as an example :
sqlite3 mySQLiteDataBase .dump .quit >> myDumpSQLite
  2. 2. Adapt the dump to get it compatible for MySQL
  – Replace “ (double-quotes) with ` (grave accent)
  – Remove “BEGIN TRANSACTION;” “COMMIT;”, and lines related to “sqlite_sequence”
  – Replace “auto increment” with “auto_increment”
  3. 3. The dump is ready to get imported in a MySQL server

Luckily the modifications can be done with a simple python script Axel Steiner wrote. You can find it on his website:

http://www.treibsand.com/2008/02/15/sqlite_mysql/