Migrate from SQLite to MySQL (then back)

Question

For various reasons, I must use a SQLite database with a local application. However, I wrote a Flask app that enables users to more easily classify items in the database, and it runs very slow on pythonanywhere hosting (which I believe is hosting its services on AWS) because of the SQLite. They support MySQL databases, and I believe it would be a significant performance boost for my users to migrate the data to MySQL. However, I need to then pull the data back out of MySQL and write it back into SQLite to work with the local applications. My concern is that the BLOB data would get messed up by creating a dump script (it's binary pickled data that contains Python data structures the application works with once unpickled).

Currently updating the local copy is as easy as downloading the SQLite database from the server. What is an optimal way to migrate this data to MySQL in such a way that it will be easy to migrate it back to SQLite? Should I?

• jynus: If you like python, this may be useful to you: stackoverflow.com/a/1067151/342196 use that on a sqlite dump and import it to MySQL.
• Dan: @jynus thanks for sharing, are you sure that would handle BLOB data sufficiently?
• jynus: no, that doesn't have into account blobs, you you will need an extra filter to convert SQLlite's 0'CAFEBABE' into MySQL's 0xCAFEBABE. If you need a more professional tool, check the options at stackoverflow.com/a/6000355/342196

Answer 1

It can be done with an initial manual migration from SQLite to a central MySQL database and then using a synchronization solutions like MySQLSync may be OK for this use case. Of course it would be best to start with an initial proof of concept application to verify if this is a feasible solution for this use case.

Disclosure: I work for DidiSoft (the company behind MySQLSync)