Export a MySQL Database to SQLite Database
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.
The answer by @user2111698 edited by @quassy works as promised. Since I do this frequently I put their instructions into a bash script:
#!/bin/bashmysql_host=localhostmysql_user=georgemysql_dbname=databasesqlite3_dbname=database.sqlite3# dump the mysql database to a txt filemysqldump \ --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.3if [ -e $sqlite3_dbname ]; then mv $sqlite3_dbname $sqlite3_dbname.bakfisqlite3 $sqlite3_dbname < /tmp/localdb.txt.3
A gist with detailed comments can be found at https://gist.github.com/grfiv/b79ace3656113bcfbd9b7c7da8e9ae8d
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.