MySQL to SQLite Migration

What follows is a simple way to convert a MySQL database to SQLite. It will not work universally, and will likely require modification in most cases, but hopefull it will be useful as a guide.

First you need to create an empty database containing the tables in your MySQL database. If you have a copy of your database schema in SQLite format, say schema.sql you can create empty database as follows:

sqlite3 database.sqlite3 < schema.sql

If you don’t have the schema file or can’t write it then you have at least two options.

• You can have MySQL dump the schema by removing the -t from the mysqldump command. This will require some extra cleanup work such as removing auto_increment and Engine InnoDB statements and reformatting the PRIMARY KEY statement.

• If the database is for a Rails application, you can always create an empty SQLite table by using the sqlite3 adapter in config/database.yml and running rake db:migrate. In this case, you can skip loading the schema altogether.

Now, assuming you have an empty database with the appropriate schema in place, run

mysqldump --complete-insert=TRUE --extended-insert=FALSE \
    --protocol=TCP -t -h [HOSTNAME] -P [PORT] \
    -u [USERNAME] -p [DATABASE] > dump.sql

to dump the MySQL database to a flat file. Unfortunately, the MySQL dump is not immediately compatible with SQLite. You will need to write a short Perl script (I called it mysql2sqlite.pl) to convert the statements in the dump file so to the format that SQLite wants:

#!/usr/bin/perl
while (<>){
    s/\\'/''/g;                # Use '' instead of \'
    s/\\"/"/g;                 # Use " instead of \"
    s/\\r\\n/\r\n/g;           # Convert escaped \r\n to literal
    s/\\\\/\\/g;               # Convert escaped \ to literal
    s/ auto_increment//g;      # Remove auto_increment
    s/^[UN]*?LOCK TABLES.*//g; # Remove locking statements
    print;
}

You can now populate the database using the data in the dump file by piping it through the conversion script:

cat dump.sql | perl mysql2sqlite.pl | sqlite3 database.sqlite3

Important note: While the above substitutions were sufficient for the particular database I needed to convert, they are not necessarily comprehensive. If SQLite reports errors when populating the database, you may need to include some additional conversion rules. Most importantly, before you do anything backup your database first!