Convert a MySQL database to a SQLite3 database
I wanted to convert a MySQL database to a SQLite3 database the other day. I did some searching and found a good script on the SQLite3 site . It didn’t quite work for me, but it was close (left a bunch of random MySQL “set” statements everywhere and used MySQL’s default multiple insert syntax). After some tweaking I got it to create the file without errors. Here’s my version for anyone that needs to do the same thing (requires mysqldump and perl be installed on your system):
["\n#!/bin/sh\n\nif [ \"x$1\" == \"x\" ]; then\n echo \"Usage: $0 <dbname>\" \n exit\nfi\n\nif [ -e \"$1.db\" ]; then\n echo \"$1.db already exists. I will overwrite it in 15 seconds if you do not press CTRL-C.\" \n COUNT=15\n while [ $COUNT -gt 0 ]; do\n echo \"$COUNT\" \n sleep 1\n COUNT=$((COUNT - 1))\n done\n rm $1.db\nfi\n\n/usr/local/mysql/bin/mysqldump -u root --compact --compatible=ansi --default-character-set=binary --extended-insert=false $1 |\ngrep -v ' KEY \"' |\ngrep -v ' UNIQUE KEY \"' |\ngrep -v ' PRIMARY KEY ' |\nsed 's/^SET.*;//g' |\nsed 's/ UNSIGNED / /g' |\nsed 's/ auto_increment/ primary key autoincrement/g' |\nsed 's/ smallint([0-9]*) / integer /g' |\nsed 's/ tinyint([0-9]*) / integer /g' |\nsed 's/ int([0-9]*) / integer /g' |\nsed 's/ enum([^)]*) / varchar(255) /g' |\nsed 's/ on update [^,]*//g' |\nsed \"s/\\\\\\'/''/g\" | # convert MySQL escaped apostrophes to SQLite \\' => ''\nsed 's/\\\\\\\"/\"/g' | # convert escaped double quotes into regular quotes\nsed 's/\\\\\\n/\\n/g' |\nsed 's/\\\\r//g' |\nperl -e 'local $/;$_=<>;s/,\\n\\)/\\n\\)/gs;print \"begin;\\n\";print;print \"commit;\\n\"' |\nperl -pe '\nif (/^(INSERT.+?)\\(/) {\n $a=$1;\n s/\\\\'\\''/'\\'\\''/g;\n s/\\\\n/\\n/g;\n s/\\),\\(/\\);\\n$a\\(/g;\n}\n' > $1.sql\ncat $1.sql | sqlite3 $1.sqlite3 > $1.err\nERRORS=`cat $1.err | wc -l`\nif [ $ERRORS == 0 ]; then\n echo \"Conversion completed without error. Output file: $1.sqlite3\" \n rm $1.sql\n rm $1.err\nelse\n echo \"There were errors during conversion. Please review $1.err and $1.sql for details.\" \nfi\n"]
Update, 11/3/08 Updated the script above. Fixed a couple issues with newlines and lowercasing everything also lowercased the actual values in the tables! For some reason I had convinced myself it was only lowercasing the table and column names… There is still an issue where apostrophes are turned into weird characters, seemingly UTF-8. This might just be a simple matter of telling mysqldump to use latin instead of utf-8 encoding? I haven’t played around with it, but if anyone figures it out please let me know.