CODEWITHSUNDEEP
mysqldatabasesqlite
Devi
Devi

Reputation: 1619

Export a MySQL Database to SQLite Database

Please help me with exporting a MySQL database into a SQLite database.

Upvotes: 101

Views: 104742

Answers (9)

georgiecasey
georgiecasey

Reputation: 23381

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.

Upvotes: 98

Feng Jiang
Feng Jiang

Reputation: 1945

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.

Upvotes: 0

M.A. Khan
M.A. Khan

Reputation: 21

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

Upvotes: 0

George Fisher
George Fisher

Reputation: 3346

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

Upvotes: 8

caiofior
caiofior

Reputation: 429

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

Upvotes: 4

user2111698
user2111698

Reputation:

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.

Upvotes: 4

Nuno
Nuno

Reputation: 51

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 .

Upvotes: 0

Aaron Ratner
Aaron Ratner

Reputation: 111

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/

Upvotes: -1

Brandon Frohbieter
Brandon Frohbieter

Reputation: 18139

export the data with 

  mysqldump database > database.sql

and import the data with 

  sqlite3 database < database.sql

you may need -u (user) and -p (password) options

Upvotes: -18

Related Questions