Export a MySQL Database to SQLite Database

Question

Please help me with exporting a MySQL database into a SQLite database.

Illmannered

The SQLite project also has a page on conversion utilities: sqlite.org/cvstrac/wiki?p=ConverterTools – Twodimensional

@Twodimensional there is a warning on the page you've linked to that the information is obsolete. – Vivisectionist

@Twodimensional link returns "Not Found". It looks like those converter tools are no longer available/supported – Thorlie

@Thorlie - www2.sqlite.org/cvstrac/wiki?p=ConverterTools – Keldon

Use DBeaver: dbeaver.com/docs/wiki/Data-migration – Auger

Answer 1

There's a fantastic Linux shell script on Github that converts Mysql to an Sqlite3 file. You need both mysqldump and sqlite3 installed on your server. Works great.

Mandell

works best so far! only needed to remove some "COLLATE xy" statements with encodings that sqlite doesnt understand. note that you wont need sqlite3 on your server if you want a clone of your mysql database for local development. – Margartmargate

Unfortunately didn't work for me. Apart from problems with "PRIMARY KEY" statements mentioned below, many other errors were reported, related to "INSERT" statements and missing tables ("objects"). – Callery

Author anandoned the script, but work continued in this fork: github.com/dumblob/mysql2sqlite – Marietta

github.com/dumblob/mysql2sqlite is now the official version. 2016-05-11 17:32 GMT+2 @esperlu declared MIT as a fitting license (also retrospectively) and the original gist as deprecated. – Denote

thanks for the headsup ilyaigpetrov and @Li-aungYip, I've updated the link – Mandell

Answer 2

The answer by @user2111698 edited by @quassy works as promised. Since I do this frequently I put their instructions into a bash script:

#!/bin/bash

mysql_host=localhost
mysql_user=george
mysql_dbname=database
sqlite3_dbname=database.sqlite3

# dump the mysql database to a txt file
mysqldump \
  --skip-create-options \
  --compatible=ansi \
  --skip-extended-insert \
  --compact \
  --single-transaction \
  -h$mysql_host \
  -u$mysql_user \
  -p $mysql_dbname \
  > /tmp/localdb.txt

# remove lines mentioning "PRIMARY KEY" or "KEY"
cat /tmp/localdb.txt \
  | grep -v "PRIMARY KEY" \
  | grep -v KEY \
  > /tmp/localdb.txt.1

# mysqldump leaves trailing commas before closing parentheses  
perl -0pe 's/,\n\)/\)/g' /tmp/localdb.txt.1 > /tmp/localdb.txt.2

# change all \' to ''
sed -e 's/\\'\''/'\'''\''/g' /tmp/localdb.txt.2 > /tmp/localdb.txt.3

if [ -e $sqlite3_dbname ]; then
    mv $sqlite3_dbname $sqlite3_dbname.bak
fi
sqlite3 $sqlite3_dbname < /tmp/localdb.txt.3

A gist with detailed comments can be found at https://gist.github.com/grfiv/b79ace3656113bcfbd9b7c7da8e9ae8d

Cryogenics

If you format your code to eliminate scrolling, you make it easier for others to read. – Webworm

Happy to; don't quickly see how – Cryogenics

If you remove the comments, you are done with the vertical scroll (add any comments you think are important into the text of your answer). Vertical scrolling can be handled with backslash newline. – Webworm

if your sql file is bigger than 1GB, split it first. otherwise perl script gives substitution loop error. – Loup

Answer 3

mysql2sqlite.sh mentioned in the top post doesn't cope well with PRIMARY KEY lines, it doesn't write the trailing ) to complete the CREATE statement.

This is what I did. I ran the mysql dump as:

mysqldump --skip-create-options --compatible=ansi --skip-extended-insert --compact --single-transaction -h<host> -u<user> -p<passwd> <database name> > localdb.txt

I then used grep to remove PRIMARY KEY and KEY:

cat localdb.txt | grep -v "PRIMARY KEY' | grep -v KEY > localdb.txt.1

I then used an editor to fix the file. When the keys are removed you end up with a CREATE statement that looks like:

CREATE ...
  ...,
)

That trailing , has to be removed. In vi this expression matches them, ,$\n)

Then you need to change all \' to ''

Then you can do the import:

sqlite3 local.sqlite3 < localdb.txt.1

And that's it. I haven't found a single program that worked for me. I hope this helps someone.

Electioneer

Answer 4

I manualy created the table structure in sqlite database.

Than I uploaded the data with teh following command:

mysqldump -u root {database} {table} --no-create-info --skip-extended-insert  --complete-insert --skip-add-locks  --compatible=ansi | sed "s/\\\'/''/g" |sqlite3 flora.db

I had to use sed to fix a different apex encoding in the two databases

Keely

Thanks for this, it was helpful since the original question didn't specify whether or not the create schema needed to be included. Simpler when the schema has already been setup – Vivisectionist

Answer 5

Personally I like the simple usage of mysqldump, yet some adjustments are need (depending on your art with Unix and what you want to do).

Ex. for just one table (prods) with PK:

$ mysqldump mysql prods -u ME -pPASS  --compatible ansi --compact |grep -v "^\/\*" |sqlite3 testme2.db
$ mysqldump mysql prods -u ME -pPASS  --compatible ansi --compact |grep -v "^\/\*" |sqlite3 testme2.db
    Error: near line 1: table "prods" already exists
    Error: near line 7: UNIQUE constraint failed: prods.id, prods.ts
$ sqlite3 testme2.db '.schema'
    CREATE TABLE "prods" (
      "id" varchar(30) NOT NULL DEFAULT '',
      "ts" int(11) NOT NULL DEFAULT '0',
      "val" double DEFAULT NULL,
      PRIMARY KEY ("id","ts")
    );

For more complex things, probably better to write a wrapper, or then, use the already mentioned fantastic awk Linux shell script on Gist .

Lazuli

Answer 6

The following Website has an online tool to convert Convert MySQL to SQLite online https://www.rebasedata.com/convert-mysql-to-sqlite-online

Oud

Answer 7

There is an alternative method: use the python script in the link convert the mysql dump to csv, and then use tools like DB Browser for SQLite or your own script to import the csv to a SQLite database.

Catron

Answer 8

There is a fantastic, lightweight tool called SQLite Database Browser that allows you to create and edit sqlite databases. I used it to craete databases for Android apps. You can run SQL statements against it to load up the data so if you export the data from a mySQL database you can just import it using this tool. Here's a link: http://sqlitebrowser.sourceforge.net/

Henze

How does this help with the MySQL conversion? – Acetylide

It helps you do exactly what you need to do. In fact, I am literally doing this right now. Export your MySQL using mysqldump or phpMyAdmin and then use the above tool to import it into an sqlite database. He asked for "with exporting a MySQL database into a SQLite database" & the tool above will be the second step in doing exactly what you need. To down vote legitimate help is just so wrong. I'm new here and my answer not only addressed the question but is what I am using right now in a real world situation. Don't be so quick to down vote for no reason and discourage people from participating. – Henze

You left out the actual second (and most important) step, converting all the MySQL-specific parts of the MySQL dump into SQLite-specific or standard syntax. – Acetylide

I'm sorry that the answer wasn't up to your standards but answers the question as asked. If he exports the data from MySQL he can import it with the tool I mentioned. I don't see how anything was left out. This is pointless. I answered the question. It works and I use it all the time. You can disagree if you wish but you are just plain wrong. – Henze

Late, but this tool does not work for this problem. It does not support MySQL-specific syntax (only SQLite syntax). You will have to edit the dump file quite heavily in order for it to be imported successfully. – Arena

You guys are being exceptionally difficult given that this is a very helpful solution that I've used dozens of times in real word scenarios. Not every solutions needs to address every single aspect of a problem. This might have helped the OP and/or someone else reading this. I fail to see what your issues are. The solution works. It's a very strange feeling to have spent time helping someone and have people complain about it. A solution needn't be a one size fits all solution to be helpful. Jeez, relax. I was just sharing my solution for this problem considering I've dealt with it before. – Henze

@AaronRatner No, the solution does not work. The question asks explicitly about converting a MySQL database, and the tool you recommend doesn't handle MySQL syntax. You don't need to take this personally - from what you're saying I'd guess that you are the one that needs to "relax" :-) If someone says that your solution is not helpful to them they are just stating a simple fact. Your response that this is "being exceptionally difficult because the solution is helpful" is absurd. I know it's not helpful for me, because it isn't :-) Cheers! – Callery

Answer 9

export the data with

  mysqldump database > database.sql

and import the data with

  sqlite3 database < database.sql

you may need -u (user) and -p (password) options

Mcglone

Will this actually work with the differences and features available in mysql and not in sqlite? – Assumpsit

This will not work. Indices, table descriptions, binary data escape sequences, locking mechanisms, probably other stuff, are all different between MySQL and SQLite. – Redmund

This is a much better solution: #456106 – Zerlina

this will not work. Please test the solution before posting whatever first comes to your mind... – Krause