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.