Database: Migration from SQLite to MySQL

simple

September 6, 2011

Databases

No Comment

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/

Related Posts

Postgres: Copy a database

simple

November 17, 2012

Databases, Postgres

No Comment

For backup and development reasons a copy of a postgres database on the same system is often used. This is not a big problem and can be done directly as a SQL statement: [crayon-5adbde79778fa105131977/] in your favorite console (psql, pgadmin, etc). There is one thing to remember, if you do a template copy this can […]

Read More

Oracle: Timeout idle or dead sessions

Lately in a project we had a lot of problems with idle database sessions. It turns out that the DBA did not configure any timeout for the oracle database for idle or dead sessions. This is done with the PMON process, and via SQL*Net, by the sqlnet_expire_time parameter. SQL*Net In your sqlnet.ora file add the […]

Read More

Leave a Reply