Submitted by boydjd on Tue, 09/15/2009 - 13:59
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
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
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
Von MySQL nach SQLite ...
from Dirks Logbuch on Sun, 07/11/2010 - 23:46Wie 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 ...
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/