Migrate SQLite3 to MySQL easily

I’ve been using a simple Rails application locally with a SQlite 3 database for some time. Now I want to move to another host and use MySQL instead. But guess what? You can’t just migrate your data!

Here are some easy steps on how to migrate your data to MySQL. First of all you need to dump your SQLite3 database. This includes transaction statements and create commands. That’s fine. Since we also migrate the schema information, our RoR app will not know any difference after we change config/database.yml.

The biggest probem I encoutered was that the SQLite3 dump places table names in double quotes, which MySQL won’t accept.

First, make sure you create your MySQL database and create a user to access that database. Then run the following command. (It’s a long one, so where you see a , just continue on the same line.)

sqlite3 db/production.sqlite3 .dump | \
grep -v "BEGIN TRANSACTION;" | \
grep -v "COMMIT;" | \
perl -pe 's/INSERT INTO \"(.*)\" VALUES/INSERT INTO `\1` VALUES/' | \
mysql -u YOURUSERNAME -p YOURPROJECT_production[/source]

This will take the SQLite 3 dump, remote the transaction commands. Next I use perl to replace all INSERT commands containing double quotes with something MySQL will understand.

That’s it. You MySQL database will be populated with your data.

Don’t forget to change your config/database.yml file after this!

Note. You may also migrate your MySQL database using Rails. If you do this I recommend that you dump the SQLite3 database to a file first before you commit it directly to MySQL. You’ll have to remove the CREATE TABLE statements as well as any reference to the schema_info table.