Migrate From SQLite3 To MySQL

I created a large SQLite database to store information on every game on www.boardgamegeek.com.  SQLite worked fine for this.  However, I was querying this database often and decided I needed a way to store these queries so that I could run them more easily.  Unfortunately, SQLite does not support this.  In addition, this is my first large database.  I think more people use MySQL for large databases than SQLite, so it will be good to have experience working with a large database in MySQL.  Finally, it will be good practice to migrate from one RDMBS to another.

dump is a file that has all of the SQL commands to completely restore your database.  It is a way to backup your database in such a way that you could literally rebuild it.  For us, we will be taking this dump and translating it into the SQL commands that MySQL recognizes and then rebuilding the database with those commands.

Crate and Translate A Dump To MySQL Syntax

If your database is small, this would be a pretty easy thing to do.  However, if your database is large, like mine was, you will want a script to automate this for you.  There are several out there, but the one I used can be found here.  To make it easier for you, the code is also below:

Save this script as dump_for_mysql.py into the folder location where your SQLite database is.  Open the command prompt here and type:

This will create a file that has all of the CREATE and INSERT commands to create your database and insert all of your data.

Import Into MySQL

First, open MySQL and create a database with whatever name you want it to be called.  Then, we can import our data into this database.

To import this into MySQL, in the command prompt run:

For example, mine looked like this:

For me this errored out a few times for a few different reasons.  The reasons were:

  • With SQLite, the CHAR limit is not mandatory.  Therefore, for many tables I had a CHAR limit of 50 but there were items with more than 50 characters.  This is okay with SQLite but illegal with MySQL.  For those tables, I had to open the dump.sql file in a text editor and change the SQL command in the CREATE TABLE statement.
  • The first INSERT of every table had the character instead of '.  To fix this, I opened the dump.sql file in a text editor and changed the to ‘.
  • The tables that are set to AUTO_INCREMENT already had values in those columns.  To fix this, I again opened the dump file and removed the AUTO_INCREMENT requirement.  After the database was successfully built, I added this requirement to shoe tables by using the command:

That’s it.  The entire process didn’t take too long.  It would have gone faster had the Python script worked properly.  If I have time, I may try to edit it so that at least the issue with the first INSERT command doesn’t happen any more.  Another thing to keep in mind – if you have a large SQLite database, it will be even larger in MySQL.  My board game database was 17 MB in SQLite and 57 MB in MySQL.  I haven’t played around with it too much to know which is faster but I intend on doing some performance testing with both of these.

Have questions or suggestions?  Please feel free to comment below or contact me.

One thought on “Migrate From SQLite3 To MySQL

Leave a Reply

Your email address will not be published. Required fields are marked *