Skip to main content

How-to: Convert mysql to sqlite

Posted in

I had a need today to convert a mysql database into a sqlite3 database.

First, you need this script:

#!/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:

josh@josh-laptop-work:~/tmp$ mysqldump -u root -p --compatible=ansi --skip-opt generator > dumpfile

And now, run the conversion:

josh@josh-laptop-work:~/tmp$ mysql-to-sqlite.sh dumpfile

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

josh@josh-laptop-work:~/tmp$ 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

Trackback URL for this post:

http://www.jbip.net/trackback/69

How to convert MySQL to sqlite

How to convert from mysql to sqlite? Here is how...

Von MySQL nach SQLite ...

Wie hier schon mehrfach im Blog geschrieben, bin ich ein grosser Fan der Mini-Datenbank SQLite.

Jetzt habe ich ein (verbesserungswürdiges aber funktionierendes) Skript gefunden, dass einen MySQL-Dump für SQLite aufbereitet: How-to: Convert mysql to ...

Reply to comment | JBipNet

Hi, i believe that i noticed you visited my website thus i came to go back the desire?
.I am trying to in finding things to improve my site!I suppose its good enough to use
some of your concepts!!

Reply to comment | JBipNet

Hi there I amm so happy I found your site, I really found you
by accident, while I was researching on Askjeeve ffor
something else, Nonetheless I am here now and would just like tto
say cheers for a remarkable post and a all rounnd enjoyable blog (I also
love the theme/design), I don’t have time to browse it all
at the moment but I have book-marked it and also included your RSS
feeds, so when I have time I will be back too read a lot more,
Please do keep up the fantastic jo.

I followed every step of the

I followed every step of the procedure and when finally i try to run the script, i get the following error : ' mysql-to-sqlite.sh: command not found '
Any help in this regard is welcome.....

Reply to comment | JBipNet

Fantastic beat ! I would like to apprentice while you amend your website, how could
i subscribe for a blog site? The account helped me
a acceptable deal. I had been tiny bit acquainted of this your broadcast provided bright clear idea

Didn't quite work so some edits needed.

In order to make this work I had to run:

mysqldump -p --compatible=ansi  books > books

NB: Without --skip-opt else I lost all the AUTO_INCREMENT directives

Then modified your script slightly by upper casing:
sed 's/ AUTO_INCREMENT/ PRIMARY KEY AUTOINCREMENT/g' |
and inserting:
sed 's/ bigint([0-9]*) / integer /g' |

Thanks to boydjd for posting this online. Very useful script.

Worked like a charm!

Thanks for the hint. I linked your page on my blog.
http://code.byteblues.com/2012/05/09/convert-mysql-to-sqlite/

Great ! At first, I didn't

Great !

At first, I didn't run the dump will specified options and it did not work...
If errors, check if you dumped with --compatible=ANSI and --skip-opt options

Thank you for the script :)

Getting empty db file

I'm Getting empty db file while using the script.

Any help.

Great, worked without a

Great,
worked without a warning; the best script found on the net.
Thank you very much !!

This worked perfectly

Thanks for the very helpful article. I followed the instructions using MAMP on my MacBook Pro and created a shell script. No problem at all converting my rather large MySQL database over to SQLite 3. I would also recommend SQLite Manager as a nice free little SQLite 3 database management tool (a free FireFox plugin). http://code.google.com/p/sqlite-manager/

Reply to comment | JBipNet

Hurrah, that's what I was seeking for, what a information! existing here at this weblog, thanks admin of this web page.

Problems

I am having problems running this script. I keep getting these errors when I run the script on my dump file, please help.

SQL error: near ")": syntax error
SQL error: no such table: game_complexity
SQL error: no such table: game_complexity
SQL error: no such table: game_complexity
SQL error: no such table: game_complexity
SQL error: no such table: game_complexity
SQL error: near ")": syntax error
SQL error: no such table: game_concept
....

SQL error: near ")": syntax error
SQL error: near ")": syntax error
SQL error: near ")": syntax error
SQL error: no such table: game_questiontype
SQL error: no such table: game_questiontype
SQL error: no such table: game_questiontype

Much appreciated.

Thanks

Solution to some of your problems perhaps

I was also getting "no such table" errors on conversion of two out of three tables. The one that was ok had a single primary key, whereas the ones that gave the errors had a primary key composed of two fields. Apparently this is not allowed in SQLite, and as far as I can gather, you don't have to declare a primary key anyway, so if you know all your rows are in fact unique you can go ahead willy nilly.

I also had to remove LOCK UNLOCK and TYPE statements that had been generated by MySQLDump.

I also had 'gremlins' from copying and pasting from the web page (perhaps a Mac thing). Zapping in BBEdit killed them.

Then the script worked and double-clicking loaded the resulting file into a SQLite db manager I had on my machine, so presumably it's ok on the command line - will test tomorrow and then on to iOS!

Thanks for the script and goodnight (here).

Command line the same script works fine But php script is not

Hello All,

Could you any one suggest me how to execute the script in php. Because when I execute the same script in command line its working fine but when I use this script to execute via php system or shell_exec() it is not working? The output file came as a blank.Could you please suggest me to resolve this issue.

Regards,
Sara