Export a MySQL Database to SQLite Database

Question

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

Solution 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.

Solution 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

Solution 3

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

Comments

[rzetterberg]

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

[CR.]

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

[Joseph]

This is a much better solution: stackoverflow.com/questions/455606/…

[Wirsing]

How does this help with the MySQL conversion?

[Aaron Ratner]

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.

[Wirsing]

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.

[Aaron Ratner]

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.

[Maciej Jankowski]

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

[benzkji]

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.

[Sverri M. Olsen]

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.

[Aaron Ratner]

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.

[BartoszKP]

@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!

[BartoszKP]

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").

[ilyaigpetrov]

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

[Li-aung Yip]

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.

[georgiecasey]

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

[zhon]

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

[George Fisher]

Happy to; don't quickly see how

[zhon]

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.

[kjones]

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

[nurp]

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