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/bash

mysql_host=localhost
mysql_user=george
mysql_dbname=database
sqlite3_dbname=database.sqlite3

# dump the mysql database to a txt file
mysqldump \
  --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.3

if [ -e $sqlite3_dbname ]; then
    mv $sqlite3_dbname $sqlite3_dbname.bak
fi
sqlite3 $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.

I manualy created the table structure in sqlite database.

Than I uploaded the data with teh following command:

mysqldump -u root {database} {table} --no-create-info --skip-extended-insert  --complete-insert --skip-add-locks  --compatible=ansi | sed "s/\\\'/''/g" |sqlite3 flora.db

I had to use sed to fix a different apex encoding in the two databases

Personally I like the simple usage of mysqldump, yet some adjustments are need (depending on your art with Unix and what you want to do).

Ex. for just one table (prods) with PK:

$ mysqldump mysql prods -u ME -pPASS  --compatible ansi --compact |grep -v "^\/\*" |sqlite3 testme2.db
$ mysqldump mysql prods -u ME -pPASS  --compatible ansi --compact |grep -v "^\/\*" |sqlite3 testme2.db
    Error: near line 1: table "prods" already exists
    Error: near line 7: UNIQUE constraint failed: prods.id, prods.ts
$ sqlite3 testme2.db '.schema'
    CREATE TABLE "prods" (
      "id" varchar(30) NOT NULL DEFAULT '',
      "ts" int(11) NOT NULL DEFAULT '0',
      "val" double DEFAULT NULL,
      PRIMARY KEY ("id","ts")
    );

For more complex things, probably better to write a wrapper, or then, use the already mentioned fantastic awk Linux shell script on Gist .

The following Website has an online tool to convert Convert MySQL to SQLite online https://www.rebasedata.com/convert-mysql-to-sqlite-online

There is an alternative method: use the python script in the link convert the mysql dump to csv, and then use tools like DB Browser for SQLite or your own script to import the csv to a SQLite database.

There is a fantastic, lightweight tool called SQLite Database Browser that allows you to create and edit sqlite databases. I used it to craete databases for Android apps. You can run SQL statements against it to load up the data so if you export the data from a mySQL database you can just import it using this tool. Here's a link: http://sqlitebrowser.sourceforge.net/