Convert MySQL to SQlite [closed]

Question

There is a mysql2sqlite.sh script on GitHub

As described in the header, the script can be used like this:

./mysql2sqlite.sh myDbase | sqlite3 database.sqlite

---

alternatives

• an updated version https://github.com/dumblob/mysql2sqlite
• A simpler script was posted at the the MySQL Forums

Here is a list of converters. (snapshot at archive.today)

---

An alternative method that would work even on windows but is rarely mentioned is: use an ORM class that abstracts specific database differences away for you. e.g. you get these in PHP (RedBean), Python (Django's ORM layer, Storm, SqlAlchemy), Ruby on Rails (ActiveRecord), Cocoa (CoreData) etc.

i.e. you could do this:

1. Load data from source database using the ORM class.
2. Store data in memory or serialize to disk.
3. Store data into destination database using the ORM class.

Sequel (Ruby ORM) has a command line tool for dealing with databases, you must have ruby installed, then:

  $ gem install sequel mysql2 sqlite3 
  $ sequel mysql2://user:password@host/database -C sqlite://db.sqlite

Not every DB schema can be converted. MySQL is more complex and feature-rich than SQLite. However, if your schema is simple enough, you could dump it into an SQL file and try to import it / load it into an SQLite DB.

I faced the same problem about 2 days ago when I had to convert a 20GB+ MySQL database to SQLite. It was by no means an easy task and I ended up writing this Python package that does the job.

The upside of it being written in Python is that it's cross platform (unlike a shell/bash script) and can all be easily installed using pip install (even on Windows). It uses generators and chunking of the data being processed and is therefore very memory efficient.

I also put in some effort to correctly translate most of the datatypes from MySQL to SQLite.

The tool is also thoroughly tested and works on Python 2.7 and 3.5+.

It is invokable via command line but can also be used as a standard Python class which you can include in some larger Python orchestration.

Here's how you use it:

Usage: mysql2sqlite [OPTIONS]

Options:
  -f, --sqlite-file PATH     SQLite3 database file  [required]
  -d, --mysql-database TEXT  MySQL database name  [required]
  -u, --mysql-user TEXT      MySQL user  [required]
  -p, --mysql-password TEXT  MySQL password
  -h, --mysql-host TEXT      MySQL host. Defaults to localhost.
  -P, --mysql-port INTEGER   MySQL port. Defaults to 3306.
  -c, --chunk INTEGER        Chunk reading/writing SQL records
  -l, --log-file PATH        Log file
  -V, --vacuum               Use the VACUUM command to rebuild the SQLite
                             database file, repacking it into a minimal amount
                             of disk space
  --use-buffered-cursors     Use MySQLCursorBuffered for reading the MySQL
                             database. This can be useful in situations where
                             multiple queries, with small result sets, need to
                             be combined or computed with each other.
  --help                     Show this message and exit.

Sqlite has officially list of converting tools.

http://www2.sqlite.org/cvstrac/wiki?p=ConverterTools