Convert MySQL to SQlite

Question

Is it possible to convert from MySQL to SQLite with a free tool on windows?

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

Sqlite has officially list of converting tools.

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

Answer 7

I found the perfect solution

First, you need this script (put it into a file called 'mysql-to-sqlite.sh'):

#!/bin/bash
if [ "x$1" == "x" ]; then
  echo "Usage: $0 <dumpname>"
  exit
fi

cat $1 |
grep -v ' KEY "' |
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |
sed 's/ unsigned / /g' |
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' |
sed 's/ tinyint([0-9]*) / integer /g' |
sed 's/ int([0-9]*) / integer /g' |
sed 's/ character set [^ ]* / /g' |
sed 's/ enum([^)]*) / varchar(255) /g' |
sed 's/ on update [^,]*//g' |
sed 's/\\r\\n/\\n/g' |
sed 's/\\"/"/g' |
perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
perl -pe '
if (/^(INSERT.+?)\(/) {
  $a=$1;
  s/\\'\''/'\'\''/g;
  s/\\n/\n/g;
  s/\),\(/\);\n$a\(/g;
}
' > $1.sql
cat $1.sql | sqlite3 $1.db > $1.err
ERRORS=`cat $1.err | wc -l`
if [ $ERRORS == 0 ]; then
  echo "Conversion completed without error. Output file: $1.db"
  rm $1.sql
  rm $1.err
else
  echo "There were errors during conversion.  Please review $1.err and $1.sql for details."
fi

Then, dump a copy of your database:

you@prompt:~$ mysqldump -u root -p --compatible=ansi --skip-opt generator > dumpfile

And now, run the conversion:

you@prompt:~$ mysql-to-sqlite.sh dumpfile

And if all goes well, you should now have a dumpfile.db which can be used via sqlite3.

you@prompt:~$ sqlite3 dumpfile.db 
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
dg_cities                 dg_forms                  dg_surnames             
dg_counties               dg_provinces              dg_user_accounts        
dg_countries              dg_provinces_netherlands
dg_first_names            dg_states

Answer 8

Simplest way to Convert MySql DB to Sqlite:

1) Generate sql dump file for you MySql database.

2) Upload the file to RebaseData online converter here

3) A download button will appear on page to download database in Sqlite format

Answer 9

There are different ways to do this. I also had this problem and I searched a lot and then I got a simple way to convert MySQL to SQLite.

Follow these steps:

1. First You Need to Install SQLite DB Browser (very small and fast to view Tables and Data)

2. Open your MySQL File in Notepad or it would be great if you open in Notepad++

3. Remove First extra Lines Contains Information or Queries and Save it.

4. Open SQLite DB Browser, Create Database, then Tables, and Same Types as it is in MySQL Database.

5. In Menu Bar of SQLite DB Browser Select File-> then Import data MySQL File which you saved.

It will easily Convert into SQLite After Warning Dialog.

If error then remove more extra lines if your MySQL file have.

You can also Install MySQL to SQLite Converter Software on trial Basis, but the information I am giving for conversion is life time.

Answer 10

My solution to this issue running a Mac was to

1. Install Ruby and sequel similar to Macario's answer. I followed this link to help setup Ruby, mysql and sqlite3 Ruby on Rails development setup for Mac OSX

2. Install sequel

   $ gem install sequel
   

   If still required

   % gem install mysql sqlite3
   

   then used the following based of the Sequel doc bin_sequel.rdoc (see Copy Database)

   sequel -C mysql://myUserName:myPassword@host/databaseName sqlite://myConvertedDatabaseName.sqlite
   

A windows user could install Ruby and Sequel for a windows solution.

Answer 11

I tried a number of methods on this thread, but nothing worked for me. So here is a new solution, which I also found to be very simple:

1. Install RazorSQL. Works for Mac, Windows and Linux.
2. In RazorSQL connect to your database, on localhost for example. Conversion doesn't work with sql dump files.
3. Right click on your database -> Database Conversion -> select SQLite. This will save a txt file with all the sqlite queries necessary to create this database.
4. Install a SQLite database manager, like DB Browser for SQLite. It works on any OS.
5. Create an empty database, go to Execute SQL tab and paste the content from step 3.

That's it, now you have your SQLite database.

Answer 12

I like the SQLite2009 Pro Enterprise Manager suggested by Jfly. However:

• The MySQL datatype INT is not converted to SQlite datatype INTEGER (works with DBeaver )

• It does not import foreign key constaints from MySQL (I could not find any tool that supports the transfer of foreign key constraints from MySQL to SQlite.)

Answer 13

From list of converter tools I found Kexi. It is a UI tool to import from various DB servers (including MySQL) into SQLite. When importing some database (say from MySQL) it stores it in Kexi format. The Kexi format is the 'native' SQLite format. So simply copy the kexi file and have your data in sqlite format

Answer 14

If you have experience write simple scripts by Perl\Python\etc, and convert MySQL to SQLite. Read data from Mysql and write it on SQLite.

Answer 15

If you have been given a database file and have not installed the correct server (either SQLite or MySQL), try this tool: https://dbconvert.com/sqlite/mysql/ The trial version allows converting the first 50 records of each table, the rest of the data is watermarked. This is a Windows program, and can either dump into a running database server, or can dump output to a .sql file