public
Last active

MySQL to Sqlite converter

  • Download Gist
mysql2sqlite.sh
Shell
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
#!/bin/sh
 
# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
# CREATE block and create them in separate commands _after_ all the INSERTs.
 
# Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
# The mysqldump file is traversed only once.
 
# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite
 
# Thanks to and @artemyk and @gkuenning for their nice tweaks.
 
mysqldump --compatible=ansi --skip-extended-insert --compact "$@" | \
 
awk '
 
BEGIN {
FS=",$"
print "PRAGMA synchronous = OFF;"
print "PRAGMA journal_mode = MEMORY;"
print "BEGIN TRANSACTION;"
}
 
# CREATE TRIGGER statements have funny commenting. Remember we are in trigger.
/^\/\*.*CREATE.*TRIGGER/ {
gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
print
inTrigger = 1
next
}
 
# The end of CREATE TRIGGER has a stray comment terminator
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }
 
# The rest of triggers just get passed through
inTrigger != 0 { print; next }
 
# Skip other comments
/^\/\*/ { next }
 
# Print all `INSERT` lines. The single quotes are protected by another single quote.
/INSERT/ {
gsub( /\\\047/, "\047\047" )
gsub(/\\n/, "\n")
gsub(/\\r/, "\r")
gsub(/\\"/, "\"")
gsub(/\\\\/, "\\")
gsub(/\\\032/, "\032")
print
next
}
 
# Print the `CREATE` line as is and capture the table name.
/^CREATE/ {
print
if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 )
}
 
# Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
/^ [^"]+KEY/ && !/^ PRIMARY KEY/ { gsub( /.+KEY/, " KEY" ) }
 
# Get rid of field lengths in KEY lines
/ KEY/ { gsub(/\([0-9]+\)/, "") }
 
# Print all fields definition lines except the `KEY` lines.
/^ / && !/^( KEY|\);)/ {
gsub( /AUTO_INCREMENT|auto_increment/, "" )
gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
gsub( /(COLLATE|collate) [^ ]+ /, "" )
gsub(/(ENUM|enum)[^)]+\)/, "text ")
gsub(/(SET|set)\([^)]+\)/, "text ")
gsub(/UNSIGNED|unsigned/, "")
if (prev) print prev ","
prev = $1
}
 
# `KEY` lines are extracted from the `CREATE` block and stored in array for later print
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to
# avoid a sqlite error for duplicate index name.
/^( KEY|\);)/ {
if (prev) print prev
prev=""
if ($0 == ");"){
print
} else {
if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 )
if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 )
key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
}
}
 
# Print all `KEY` creation lines.
END {
for (table in key) printf key[table]
print "END TRANSACTION;"
}
'
exit 0

Generalities

This small shell script should help you to convert a mysqldump file into a Sqlite3 compatible file.

Works with the original awk, gawk and mawk. If you have, like me, laaarge files to convert (millions of lines) you better off using the lightning fast mawk. For these large files you could also consider using a csv export from MySQL and the import Sqlite command.

As usual: your mileage may vary but if you have any issue with this snippet, ping me and I'll see what I can do.

Usage

Usual stuff:

  • copy paste this in a file, say: mysql2sqlite.sh file
  • make it executable: $ chmod +x mysql2sqlite.sh
  • run it $ ./mysql2sqlite.sh -u MyUserName -pMySecretPassWord myDbase | sqlite3 database.sqlite

If you only need to create the table structures without data:
$ ./mysql2sqlite.sh --no-data -u MyUserName -pMySecretPassWord myDbase | sqlite3 database.sqlite

Remarks

  • In the INSERTs, the single quote must be protected as SQLite doesn't like the Ten o\'clock mysqldump format. It is changed to Ten o''clock
  • It isn't necessary to convert the varchar() data typa as SQLite accepts them in version 3. Read more over Data Typa affinity in the Sqlite doc.
  • The AUTO_INCREMENT is not necessary as it is more or less automatically activated for keys declared as PRIMARY. Read more...
  • The COLLATE declaration is removed to let SQLite collate by its default BINARY rule (Read more) . If BINARY is not what you are after, SQLite let you define User Defined Collation
  • enum and set data types are not supported in SQLite. They are therefore converted to text.
  • In a CREATE block, Sqlite only accepts a PRIMARY KEY declaration. Not the single KEY's that have to be removed from the block and added at the end. See example below. They are added after all the INSERT's to speed up the all process.
  • As far as I know, in MySQL, when you create a KEY (INDEX), it is attached in some way to its table. Not in SQLite. Therefore, all KEY declaration are prefixed by its table name to avoid SQLite error for duplicate key name.

Release notes:

https://gist.github.com/943776/afb04cb97a0977fd1422d5dff7cc02b116238cfb

  • Deal with key-length specifications in both primary and secondary keys (@artemyk and @gkuenning)
  • INSERT sanitization (@artemyk)
  • partial trigger support (@gkuenning). Still some issue when MySQL dumps an INSERT statement like INSERT INTO test2 SET a2 = NEW.a1; a MySQL syntax extension not supported by SQLite.
  • substantial speed optimization using transactions for inserts (@gkuenning).

Example

Typical mysqldump output:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE "clients" (
  "CODCLI" varchar(6) NOT NULL DEFAULT '',
  "NAME" varchar(10) NOT NULL DEFAULT '',
  "STAT" char(2) NOT NULL DEFAULT '',
  "VRP" char(2) NOT NULL DEFAULT '',
  "TEL" varchar(15) NOT NULL DEFAULT '',
  "EMAIL" varchar(100) NOT NULL,
  PRIMARY KEY ("CODCLI"),
  FULLTEXT KEY "NAME" ("NAME")
);
/*!40101 SET character_set_client = @saved_cs_client */;

This will be converted as:

CREATE TABLE "clients" (
  "CODCLI" varchar(6) NOT NULL DEFAULT '',
  "NAME" varchar(10) NOT NULL DEFAULT '',
  "STAT" char(2) NOT NULL DEFAULT '',
  "VRP" char(2) NOT NULL DEFAULT '',
  "TEL" varchar(15) NOT NULL DEFAULT '',
  "EMAIL" varchar(100) NOT NULL,
  PRIMARY KEY ("CODCLI")
);
CREATE INDEX "clients_NAME" ON "clients" ("NAME");

Hi!
Thanks for your work, and for the code..
It didnt work for me, because I didn't know that I should use a mysqldump, something that I even don't know what is.
But the major problem is that after trying to run the script, my terminal became weird.. Now I can't type "/" (slash), nether copy it. What happened?? What would I do without typing "/"???

I tried to reinstall terminal, no lucky (didn't find a way to do it);
I tried to reinstall X11, no lucky (didn't find a way to do it);

Can You please help me whit this issue, please?? I'm becoming crazy.
Thanks in advise.

Hi,

Yes, you must use mysqldump to dump your MySQL database. Use it first with the --no-data option to generate a basic output. In my script, the output is piped into the awk snippet. If everything works as expected, remove the option to process all the inserts.

Pretty standard stuff that shoudn't freeze your terminal.

Sqlite3 doesn't like the auto_increment used in this fashion: (this is a segment of the dump that would go into sqlite3 after running through your script)

Note this is an attempt at a migration from a django app running on mysql to sqlite

CREATE TABLE "auth_group" (
"id" int(11) NOT NULL auto_increment,
"name" varchar(80) NOT NULL,
PRIMARY KEY ("id")
);

Any thoughts? Thanks for the script!

EDIT: After actually reading your script :P, I copied line 35 and added:
gsub( / auto_increment/, "" )

which took care of this lower case situation.

Unfortunately this script didn't play well with a utf-8 encoded mysql database. Once the data was in sqlite, Django would fail trying to read fields that had non-ascii characters. Ultimately I did:

Set the django settings to use a new sqlitedb file.
$ python manage.py syncdb (I chose no on creating any users)

$ mysqldump --skip-extended-insert --compact -u ** -p** > mysql_dump.db

Because I am lazy, I manually deleted all lines in the mysql_dump.db except for INSERT lines (cause syncdb created the schema for me).

$ cat mysql_dump.db | sed -e 's/`/"/g' | sed -e "s/\\'/''/g" | sed -e 's/\\"/"/g' | sed -e 's/\r//g' | sed -e 's/\n//g' | sqlite3 evite.db

Im sure there are a lot less stupid ways to do this, but this worked.

This database contained a lot of html and json, and both worked as expected afterwards.

@ warren-runk Fort the auto_increment problem, you could also make the replacement case insensitive by adding IGNORECASE=1 in the BEGIN block:

BEGIN{ FS=",$"; IGNORECASE=1}

But be aware that only GNU awk supports that built-in variable. I have edited the script to make case insensitive replacements.

As for the utf-8 problem, you could also put awk at work to replace all characters sqlite3 doesn't like in:

# Print all `INSERT` lines. The single quotes are protected by another single quote.
/INSERT/ {
    gsub( /\\\047/, "\047\047" )
    gsub( /pattern/, "replacement" )
    print
    next
}

Cool thanks. I am definitely lacking awk fu. The utf-8 issue is tough because sqlite3 is fine with the import, but the django app cannot decode the data. So it is quite tough to debug :(

One example is what appears to be a windows smart single quote [ ’ ] will turn into # (or so sqlite says when viewing rows via the console), and will then cause this error when trying to retrieve via django1.2:

...django/db/backends/sqlite3/base.py", line 200, in execute
return Database.Cursor.execute(self, query, params)
DatabaseError: Could not decode to UTF-8 column...

Dear esperlu,

I believe there is a bug in your script when handling MySQL partial indexes. An example of this could be a varchar column only be indexed on the first 10 characters:

create table test_table (id int not null primary key auto_increment, txtfld varchar(50), index (txtfld(10))  );

(I'm running this code runs on MySQL 5.1.49)

Here's a sample shell script that demonstrates the error:

#!/bin/bash

echo "drop database if exists m2stest; create database m2stest; use m2stest; \
create table test_table (id int not null primary key auto_increment, txtfld varchar(50), index (txtfld(10))  );
" |mysql -u root

rm m2stest.sqlite 
./mysql2sqlite.sh > m2stest.sql
cat m2stest.sql | sqlite3 m2stest.sqlite

echo "drop database m2stest;" | mysql -u root

I get:
Error: near line 6: near "(": syntax error
where Line 6 in m2stest.sql is:

CREATE INDEX "test_table_txtfld" ON "test_table" ("txtfld"(10);

You can see its parsing the index definition wrong. I was able to fix it by changing the line:

           if ( match( $0, /\([^\)]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 

to:

           if ( match( $0, /\(([^\(\)]+(\([^\)]+\))?)+[^\)]*/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
            gsub(/\([^\)]+\)/, "", indexKey);

That is, it will now find indexKey by matching to a closing parenthesis, while omitting (one-level deep) nested parenthesis along the way (such as those used to specify index length). The next gsub line should cut those nested parenthesis blocks out (as a result, partial indexes are converted to full column indexes by this script).

Another thing I changed was added extra de-escaping (Mysql backslash-escapes double quotes, CRs, newlines, etc.). Apparently Sqlite3 doesn't:

/INSERT/ { gsub( /\\\047/, "\047\047" ); print; next }

to:

/INSERT/ { 
gsub( /\\\047/, "\047\047" ); 
gsub(/\\n/, "\n");
gsub(/\\r/, "\r");
gsub(/\\"/, "\"");
gsub(/\\\\/, "\\");
gsub(/\\\032/, "\032");
print; 
next }

This actually doesn't handle escaping that could occur in default values of table declarations, but its a start...

@ artemyk

You are right for the index creation. I just checked and SQlite doesn't seem to support indexing on a column subset. Just changed these two lines to get rid of that:

    if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
    if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 

This should return an index creation line on the full key:

 CREATE INDEX "test_table_txtfld" ON "test_table" ("txtfld");

Hi Esperlu,

That was my initial attempt also, but I changed it to the code I posted
earlier. The reason is if you have a key name like:

(name(10), id, city)

then your code will truncate it at

name

The gsub stuff I have will eliminate nested parens.

Best,
-Artemy

On Tue, Jun 7, 2011 at 3:50 PM, esperlu <
reply@reply.github.com>wrote:

@ artemyk

You are right for the index creation. I just checked and SQlite doesn't
seem to support indexing on a column subset. Just changed these two lines to
get rid of that:

           if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0,

RSTART+1, RLENGTH-1 )
if ( match( $0, /([^()]+/ ) ) indexKey = substr( $0,
RSTART+1, RLENGTH-1 )

This should return an index creation line on the full key:

CREATE INDEX "test_table_txtfld" ON "test_table" ("txtfld");

Reply to this email directly or view it on GitHub:
https://gist.github.com/943776

Right again. Wouldn't this be enough?

    if ( match( $0, /\(.+\)/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-2 ); gsub(/\([^)]+\)/,"", indexKey)

Can you also tell me more about that escape thing above so that I can do some testing and change the script accordingly? Thanks for your input anyway.

Hi esperlu,

Right again. Wouldn't this be enough?

           if ( match( $0, /\(.+\)/ ) ) indexKey = substr( $0,

RSTART+1, RLENGTH-2 ); gsub(/([^)]+)/,"", indexKey)

If I'm not mistaken, for an index on something like (blahfield(20),
idfield), using your regex, indexKey will be "blahfield(20".

Do you see something wrong with the regular expression that I posted in my
comment to the github page? I think it should accomplish what you need it
to.

Best,
-Artemy

Can you also tell me more about that escape thing above so that I can do
some testing and change the script accordingly? Thanks for your input
anyway.

Reply to this email directly or view it on GitHub:
https://gist.github.com/943776

Here's a patch that does several things:

1) mysqldump user/password and db name aren't hardwired; instead the script takes them as arguments.
2) Add pragmas and begin/end transactions to speed up insertion a LOT (over 1000X in my case).
3) Handle mysqldump's very goofy formatting of CREATE TRIGGER.
4) Deal with key-length specifications in both primary and secondary keys. The code here is much simpler than that proposed by artemyk; I just zap any parenthesized string of digits. It works perfectly on my DB; I can't make promises for anybody else but I think it's the right approach.

I have also tried hard to match the coding style of the original, since I'm a huge believer in consistency over my own prejudices. ;-)

--- mysql2sqlite        2011-06-08 07:44:33.000000000 +1200
+++ /usr/local/bin/mysql2sqlite 2011-06-20 19:57:20.000000000 +1200
@@ -6,22 +6,49 @@
 # Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
 # CREATE block and create them in separate commands _after_ all the INSERTs.

-# Usage: $ ./mysql2sqlite | sqlite3 database.sqlite
+# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite

-mysqldump  --compatible=ansi --skip-extended-insert --compact -u **** -p******  datbase_name | \
+echo "PRAGMA synchronous = OFF;"
+echo "PRAGMA journal_mode = MEMORY;"
+echo "BEGIN TRANSACTION;"
+
+mysqldump  --compatible=ansi --skip-extended-insert --compact "$@" | \


 awk -F ",$" '

 BEGIN{ FS=",$" }

-# Skip comments
+# CREATE TRIGGER statements have funny commenting.  Remember we are in trigger.
+/^\/\*.*CREATE.*TRIGGER/ {
+       gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
+       print
+       inTrigger = 1
+       next
+}
+
+# The end of CREATE TRIGGER has a stray comment terminator
+/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }
+
+# The rest of triggers just get passed through
+inTrigger != 0 { print; next }
+
+# Skip other comments
 /^\/\*/ { next }

 # Print all `INSERT` lines. The single quotes are protected by another single quote.
-/INSERT/ { gsub( /\\\047/, "\047\047" ); print; next }
+/INSERT/ {
+       gsub( /\\\047/, "\047\047" )
+       gsub(/\\n/, "\n")
+       gsub(/\\r/, "\r")
+       gsub(/\\"/, "\"")
+       gsub(/\\\\/, "\\")
+       gsub(/\\\032/, "\032")
+       print
+       next
+}

-# Print the ´CREATE´ line as is and capture the table name.
+# Print the `CREATE` line as is and capture the table name.
 /^CREATE/ {
        print
        if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 ) 
@@ -30,6 +57,9 @@
 # Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
 /^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }

+# Get rid of field lengths in KEY lines
+/ KEY/ { gsub(/\([0-9]+\)/, "") }
+
 # Print all fields definition lines except the `KEY` lines.
 /^  / && !/^(  KEY|\);)/ {
        gsub( /AUTO_INCREMENT|auto_increment/, "" )
@@ -63,3 +93,5 @@
        for (table in key) printf key[table]
 }
 ' 
+
+echo "END TRANSACTION;"

edited by @esperlu for block formatting.

Hi,

A couple of things:

  • I tried your new code. I could not get patch to apply the diff perfectly (but that might be my fault, I've never used patch/diff before). So I had to apply some changes by hand.
  • The new patch seems to work really well. First of all, its superfast. And it now gives me zero errors on the database I was previously trying to convert (the database is a mediawiki install with some data in it)
  • A small bug: I think / KEY/ { gsub(/\([0-9]+\)/, "") } should be: /^ KEY/ { gsub(/\([0-9]+\)/, "") } (that way you don't match things like default values for varchar fields and such (right now, if a varchar is defined like: textfield" varchar(50) default 'MY DEFAULT KEY(10) your code would catch it)

Best, -A

Edited by @esperlu for formating

@gkuenning: got a Hunk #1 FAILED at 6 with your patch. I'll see what the problem is.

@artemyk: sorry for the late reply. I will test your pattern above and incorporate it.

Thanks to both.

Jean-Luc.

@gkuenning: got it sorted out.

Mod pushed. Thanks.

This is exactly what I was looking for, thank you for your contribution esperlu & above!

1) mysqldump user/password and db name aren't hardwired; instead the script takes them as arguments.

Can you update this part of your mini-doc:

- mysqldump --compatible=ansi --skip-extended-insert --compact -u **** -p******  datbase_name
+ mysqldump --compatible=ansi --no-data --compact -u **** -p******  datbase_name

to

- mysqldump --compatible=ansi --skip-extended-insert --compact  "$@" | \
+ mysqldump --compatible=ansi --no-data --compact  "$@" | \

to reflect change 1) you made?

Thanks again, this script rocks!

@d-rome: done. Thanks for mentioning it. And BTW, it's @gkuenning who suggested this change. Credits should go to him.

@artemyk, sorry for the slow response.

I used the pattern / KEY/ in the gsub because you need to match PRIMARY KEY declarations. If the pattern causes errors as-is, I guess we need to change it to /^ KEY|^ PRIMARY KEY/. (I just tested that pattern and it works for my situation.)

For @artemyk's remark I suggest the following:

--- /home/jeanluc/scripts/mysql2sqlite/sh/gist-943776/mysql2sqlite  2011-06-20 21:12:45.000000000 +0200
+++ mysql2sqlite.sh 2011-06-22 10:38:00.000000000 +0200
@@ -63,3 +63,3 @@
 # Get rid of field lengths in KEY lines
-/ KEY/ { gsub(/\([0-9]+\)/, "") }
+/^  (PRIMARY )?KEY/{ gsub(/\([0-9]+\)/, "") }

Seems to work. Waiting for your testing before I push it.

Jean-Luc.

That works and is cleaner than my regex. I like it!

HI guys I am not very experienced with these tools so I have tried your script and it is great work! but I have some encoding problem with sqlite in Android. All the latin-1 type caracters such as é è, ç are not read correctly and are transformed inot what seems like chinese characters. What could I do? I saw there was some answer above about utf-8 but I don't understand it. In the original DB (php admin, sql) everything is fine My sqlite Db is in utf-8 encoding by default so what is wrong? Thanks for your help

From http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql, sqlite doesn't support table and column comments, If mysqldump output contains COMMENT(like in my Drupal7 case), there will cause error importing to sqlite. So the COMMENT stuffs should be cut off.

I also count some UTF-8 problem, there's many non-English characters in MySQL, it all become question mark '?'. In order to solve this problem, just add --default-character-set=utf8 to mysqldump.

And I suggest to run sqlite3 with -echo, you can see what's going on there if there's some error.

Below is the patch for cutting COMMENT:

--- mysql2sqlite.sh.orig        2011-07-28 11:50:20.000000000 +0800
+++ mysql2sqlite.sh     2011-07-28 17:33:24.000000000 +0800
@@ -57,6 +57,9 @@
        if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 )
 }

+# Replace table and column COMMENT
+/ COMMENT '.*'/ { gsub( / COMMENT '.*'/, "" ) }
+
 # Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
 /^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }

Hi,

i am rather new to shell scripting and do not understand the parameters in your "usage-example" when calling the script for execution!? Is "myDbase" the MySQL dump file? And for what purpose do i need username and password? Will the script write a file to my current directory which is named "database.sqlite" and contains the sqlite dump?

is there any more detailed example of usage?

cyberix

Hi, cyberix,

myDbase is the name of a MySQL database. There's no need for the name of a dump file, because the script runs mysqldump and captures its output directly.

The script itself (mysql2sqlite.sh) writes sqlite3 commands to its standard output. You can capture that into a file with the ">" symbol and later feed it into sqlite3's standard input with the "<" symbol:

mysql2sqlite.sh [parameters] > saved-commands
[do other stuff]
sqlite3 some-database-name.sqlite3 < saved-commands

However, most people just want the Sqlite3 database, so they avoid creating the scratch file (here, I've called it "saved-commands"). Instead, they use the single greatest invention in Unix, the pipe symbol (vertical bar), which directly connects the standard output from the first command to the standard input of the second. In this case:

mysql2sqlite.sh [parameters] | sqlite3 some-database-name.sqlite3

That will run the conversion script with the given parameters and feed the result directly into sqlite3, which will then create the database you want (here, "some-database-name.sqlite3" -- a pretty bad name!).

You need the username and password only if you would need them when running the "mysql" command. For example, my username defaults to my login name and I have a passwordless mysql account (that's safe, since it's on a private server). So I can talk to my photos database with simply:

mysql photos

and that means I can convert it to sqlite3 with:

mysql2sqlite.sh photos | sqlite3 photos.sqlite3

In my situation, I need to convert Drupal7's database from MySQL 5.0 to SQLite 3.7.

As in Drupal7 MySQL database, all column with auto_increment must be the primary key. So I add some code to replace PRIMARY KEY and auto_increment, this patch works for me.
The raw file is here: http://525183.serverbox.org/drupal7/mysql2sqlite.sh

--- mysql2sqlite.sh.orig        2011-07-28 11:50:20.000000000 +0800
+++ mysql2sqlite.sh     2011-08-03 09:36:45.000000000 +0800
@@ -13,6 +13,18 @@

 mysqldump  --compatible=ansi --skip-extended-insert --compact  "$@" | \

+
+awk '
+# Replace PRIMARY KEY if theres auto_increment
+BEGIN { RS="/CREATE TABLE /" }
+/NOT NULL auto_increment/ {
+        gsub( /,\n  PRIMARY KEY  \(\"[a-z_]+\"\)/, "" )
+        print
+        next
+}
+' | \
+
+
 awk '

 BEGIN {
@@ -57,6 +69,12 @@
        if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 )
 }

+#Replace auto_increment with AUTOINCREMENT
+/int\([0-9]+\) NOT NULL auto_increment/ { gsub( /int\([0-9]+\) NOT NULL auto_increment/, "INTEGER PRIMARY KEY AUTOINCREMENT" ) }
+
+#Replace table and column COMMENT
+/ COMMENT '.*'/ { gsub( / COMMENT '.*'/, "" ) }
+
 # Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
 /^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }

@@ -97,4 +115,5 @@
        print "END TRANSACTION;"
 }
 '
+
 exit 0

For Example
mysqldump --no-data --default-character-set=utf8 --compatible=ansi --skip-extended-insert --compact -uusername -p drupal7
Output(The last table):

CREATE TABLE "watchdog" (
  "wid" int(11) NOT NULL auto_increment,
  "uid" int(11) NOT NULL default '0',
  "type" varchar(64) NOT NULL default '' COMMENT 'Type of log message, for example "user" or "page not found."',
  "message" longtext NOT NULL,
  "variables" longblob NOT NULL COMMENT 'Serialized array of variables that match the message string and that is passed into the t() function.',
  "severity" tinyint(3) unsigned NOT NULL default '0',
  "link" varchar(255) default '' COMMENT 'Link to view the result of the event.',
  "location" text NOT NULL,
  "referer" text,
  "hostname" varchar(128) NOT NULL default '',
  "timestamp" int(11) NOT NULL default '0',
  PRIMARY KEY  ("wid"),
  KEY "type" ("type"),
  KEY "uid" ("uid")
);

With my patch it will be converted to:

CREATE TABLE "watchdog" (
  "wid" INTEGER PRIMARY KEY AUTOINCREMENT,
  "uid" int(11) NOT NULL default '0',
  "type" varchar(64) NOT NULL default '',
  "message" longtext NOT NULL,
  "variables" longblob NOT NULL,
  "severity" tinyint(3)  NOT NULL default '0',
  "link" varchar(255) default '',
  "location" text NOT NULL,
  "referer" text,
  "hostname" varchar(128) NOT NULL default '',
  "timestamp" int(11) NOT NULL default '0'
);
CREATE INDEX "watchdog_type" ON "watchdog" ("type");
CREATE INDEX "watchdog_uid" ON "watchdog" ("uid");

This shell code working for my database. It's very helpful. Thanks. :D

When trying to run the file, I'm getting the following error:

./mysql2sqlite.sh: line 14: mysqldump: command not found
memory

Hello, great script! SQLite's auto-incrementing only works if a column is declared as integer, even if the declared datatype is one of the ones that SQLite maps to integer (bigint, etc.). So, if you have primary key auto-increment columns that are something else and use the output of the script, they will not auto-increment. The workaround is to replace these datatype declarations with integer, since that's what SQLite considers them, anyway (SQLite type affinities).

I pushed a fix in my fork:

diff --git a/mysql2sqlite.sh b/mysql2sqlite.sh
index dd87f40..1e6e856 100644
--- a/mysql2sqlite.sh
+++ b/mysql2sqlite.sh
@@ -3,7 +3,7 @@
 # Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts t
 # CREATE block and create them in separate commands _after_ all the INSERTs.

-# Awk is choosen because it's fast and portable. You can use gawk, original awk
+# Awk is chosen because it's fast and portable. You can use gawk, original awk 
 # The mysqldump file is traversed only once.

 # Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
@@ -72,6 +72,7 @@ inTrigger != 0 { print; next }
        gsub(/(ENUM|enum)[^)]+\)/, "text ")
        gsub(/(SET|set)\([^)]+\)/, "text ")
        gsub(/UNSIGNED|unsigned/, "")
+       gsub(/" [^ ]*(INT|int)[^ ]*/, "\" integer")
        if (prev) print prev ","
        prev = $1
 }

Also, technically the behaviour of SQLite's INTEGER PRIMARY KEY auto-incrementing is not equivalent to its AUTOINCREMENT, because the latter ensures that the key is unique, whereas the former doesn't (SQLite FAQ). Doesn't bother me enough to find a solution, but just FYI!

@martinbean You need to have the MySQL client package installed in order to access MySQL. In Ubuntu, for example, mysql-client provides mysqldump and the other client executables.

@mrtorrent I installed MySQL from source I think. I installed it on my Mac OS X machine to compliment the built-in versions of Apache and PHP.

Is this the same thing? Or do I need to install something supplementary to surpress the above error and get the script working?

@martinbean You must not have installed the programs to somewhere in your shell's $PATH (i.e. the directories it looks in to find an application when you type a command). You'll need to find out where you installed it and replace mysqldump in the script with the full path to your mysqldump, e.g. /usr/bin/mysqldump.

@mrtorrent I see. OK, I'll try that. Thanks for the tips!

The script fails to properly convert an insert with a double backslash like, for example, INSERT INTO "abc" VALUES (1,'\\\\');
It will convert it to INSERT INTO "abc" VALUES (1,'\\\''); and subsequent statements will fail of course.

Hi!
Thanks for your work, and for the code..
It didnt work for me, because I didn't know that I should use a mysqldump, something that I even don't know what is.
But the major problem is that after trying to run the script, my terminal became weird.. Now I can't type "/" (slash), nether copy it. What happened?? What would I do without typing "/"???

I tried to reinstall terminal, no lucky (didn't find a way to do it);
I tried to reinstall X11, no lucky (didn't find a way to do it);

Can You please help me whit this issue, please?? I'm becoming crazy.
Thanks in advise.

I was having big problems with the conversion as the non-english characters (especially letters with accents) were getting getting destroyed in the process.

Fixed with addition of "--default-character-set=utf8" to Line 14: "mysqldump --default-character-set=utf8 --compatible=ansi --skip-extended-insert --compact "$@" | \"

Otherwise, thanks!

https://gist.github.com/1683507.js?file=mysql2sqlite.sh
contains simple int(11) integer swap for primary keys with auto_increment, since they aren't automatically handled by sqlite.

Hi,
I have a 50mb db and i am trying to convert it..
I always get the error
Error: near line 4: near "SET": syntax error
Error: near line 8: no such table: main.Definition

The db has one single table called Definition.

the sql file is this:
http://burstsoft.info/temp/dex-evita.sql.zip

Any idea why it gives an error?

@alxd: the file you linked has 10 tables in it. Definition as well as several prefixed with pma, so you may want to begin there. It would appear that the script also didn't generate the table definition exactly right for Definition either. It wouldn't hurt to apply @stefanacin's --default-character-set=utf8 patch as well.

@nebulous: I deleted the pma* tables, I changed the table column encoding to utf8, I applied the patch:
mysqldump --default-character-set=utf8 --compatible=ansi --skip-extended-insert --compact "$@" | \
and I still get the
Error: near line 4: near "SET": syntax error
Any idea why the Definition table is not generated?

What does it generate? It might help to add --no-data to the mysqldump command so you can see just the table definitions involved. If nothing else you can just tweak that output after which the inserts should all work, barring any additional complications like triggers.

i used --no-data and nothing is generated - the file is empty
the script just prints the error:
./mysql2sqlite.sh --no-data -u root -pxxx dex | sqlite3 dex.sqlite
memory
Error: near line 4: near "SET": syntax error
Error: near line 8: no such table: main.Definition
Error: near line 27: no such table: main.Definition
Error: near line 45: no such table: main.Definition

Pipe the output of the script into a file instead of SQLite, so you can see what it's doing.
./mysql2sqlite.sh --no-data -u root -pxx dex > dex.sql

well, it generates this:
CREATE TABLE "Definition" (
"lexicon" varchar(100) DEFAULT NULL,
"internalRep" mediumtext CHARACTER SET utf8
);
I don't see anywhere on the sqlite documentation
http://www.sqlite.org/lang_createtable.html
CHARACTER SET, so I guess this is a bug. How can we fix it?

you can either remove it manually for the import, or add another search/replace to the awk to remove it.

Hello, very nice script.
Is it supposed to manager mysql views ?

Very impressed with your awk-script. A little further developed, taking into account sqlite>3.6 features like foreign keys, autoincrement... Maybe someone will find it useful.

#!/bin/sh

# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
# CREATE block and create them in separate commands _after_ all the INSERTs.

# Awk is chosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
# The mysqldump file is traversed only once.

# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite

# Thanks to and @artemyk and @gkuenning for their nice tweaks.

mysqldump --skip-extended-insert --compact  "$@" | \
#cat "$@" | \
awk '

BEGIN {
    FS=",$"
    print "PRAGMA synchronous = OFF;"
    print "PRAGMA journal_mode = MEMORY;"
    print "BEGIN TRANSACTION;"
}

# CREATE TRIGGER statements have funny commenting.  Remember we are in trigger.
/^\/\*.*CREATE.*TRIGGER/ {
    gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
    print
    inTrigger = 1
    next
}
# The end of CREATE TRIGGER has a stray comment terminator
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }
# The rest of triggers just get passed through
inTrigger != 0 { print; next }

# CREATE VIEW looks like a TABLE in comments
/^\/\*.*CREATE.*TABLE/ {
    inView = 1
    next
}
# The end of CREATE VIEW
/^(\).*ENGINE.*\*\/;)/ {
    inView = 0;
    next
}
# The rest of view just get passed through
inView != 0 { next }

# Skip other comments
/^\/\*/ { next }

# Print all `INSERT` lines. The single quotes are protected by another single quote.
/^INSERT/ {
    prev = "";
    gsub( /\\\047/, "\047\047" )
    gsub( /\\\047\047,/, "\\\047," )
    gsub(/\\n/, "\n")
    gsub(/\\r/, "\r")
    gsub(/\\"/, "\"")
    gsub(/\\\\/, "\\")
    gsub(/\\\032/, "\032")
    print
    next
}

# Print the `CREATE` line as is and capture the table name.
/^CREATE/ {
    print
    aInc = 0;
    if ( match( $0, /\`[^\`]+/ ) ) 
    {
    tableName = substr( $0, RSTART+1, RLENGTH-1 ) 
    }
    prev = "";
    firstInTable = 1;
    next
}

# Replace `FULLTEXT KEY` (probably other `XXXXX KEY`)
/^  FULLTEXT KEY/ { gsub( /.+KEY/, "  KEY" ) }

# Get rid of field lengths in KEY lines
/ (PRIMARY )?KEY/ { gsub(/\([0-9]+\)/, "") }

aInc == 1 && /PRIMARY KEY/ {
    next
}

# Print all fields definition lines except the `KEY` lines.
/^  / && !/^(  KEY|\);)/ {
    if ( match( $0, /AUTO_INCREMENT|auto_increment/))
    {
    aInc = 1;
    gsub( /AUTO_INCREMENT|auto_increment/, "PRIMARY KEY AUTOINCREMENT" )
    }
    gsub( /(UNIQUE KEY|unique key) `.*` /, "UNIQUE " )
    gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
    gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
    gsub( /(COLLATE|collate) [^ ]+ /, "" )
    gsub(/(ENUM|enum)[^)]+\)/, "text ")
    gsub(/(SET|set)\([^)]+\)/, "text ")
    gsub(/UNSIGNED|unsigned/, "")
    gsub(/` [^ ]*(INT|int)[^ ]*/, "` integer")
    if (prev)
    {
    if (firstInTable)
    {
        print prev
        firstInTable = 0
    }
    else print "," prev
    }
    prev = $1
}

/ ENGINE/ {
    if (prev)
    {
    if (firstInTable)
    {
        print prev
        firstInTable = 0
    }
    else print "," prev
    }
    prev=""
    print ");"
    next
}
# `KEY` lines are extracted from the `CREATE` block and stored in array for later print 
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to 
# avoid a sqlite error for duplicate index name.
/^(  KEY|\);)/ {
    if (prev)
    {
    if (firstInTable)
    {
        print prev
        firstInTable = 0
    }
    else print "," prev
    }
    prev = ""
    if ($0 == ");"){
        print
    } else {
        if ( match( $0, /\`[^`]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
    if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
    key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
    }
}

# Print all `KEY` creation lines.
END {
    for (table in key) printf key[table]
    print "END TRANSACTION;"
}
'
exit 0

Hello. Newbie help please. I can't get "$ chmod +x mysql2sqlite.sh" to work, it says that command +x isn't found.

The dollar sign is intended to represent your shell (command line) prompt. So you should just type this:

chmod +x mysql2sqlite.sh

I've got the following being generated: "CREATE INDEX "REATE TABLE _ KEY " ON "REATE TABLE " (theme);"

That's obviously grabbing the wrong part of the statement. Attempting to apply various fixes from this thread, I've got the same result with the following code segment:

else {
if ( match( $0, /`[^`]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 )
if ( match( $0, /(([^()]+(([^)]+))?)+[^)]*/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 )
gsub(/([^)]+)/, "", indexKey);

key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
}

What am I doing wrong?

how to convert mysql to sqlite3 please do something in windows xp.....

@amarraj virtualbox + ubuntu will help ;)

I tweaked @alx77 version to fix the problem @yamikuronue was having (I was having too), specifically for django created and managed mysql databases

#!/bin/sh

# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
# CREATE block and create them in separate commands _after_ all the INSERTs.

# Awk is chosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
# The mysqldump file is traversed only once.

# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite

# Thanks to and @artemyk and @gkuenning for their nice tweaks.

mysqldump --skip-extended-insert --compact  "$@" | \
#cat "$@" | \
awk '

BEGIN {
    FS=",$"
    print "PRAGMA synchronous = OFF;"
    print "PRAGMA journal_mode = MEMORY;"
    print "BEGIN TRANSACTION;"
}

# CREATE TRIGGER statements have funny commenting.  Remember we are in trigger.
/^\/\*.*CREATE.*TRIGGER/ {
    gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
    print
    inTrigger = 1
    next
}
# The end of CREATE TRIGGER has a stray comment terminator
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }
# The rest of triggers just get passed through
inTrigger != 0 { print; next }

# CREATE VIEW looks like a TABLE in comments
/^\/\*.*CREATE.*TABLE/ {
    inView = 1
    next
}
# The end of CREATE VIEW
/^(\).*ENGINE.*\*\/;)/ {
    inView = 0;
    next
}
# The rest of view just get passed through
inView != 0 { next }

# Skip other comments
/^\/\*/ { next }

# Print all `INSERT` lines. The single quotes are protected by another single quote.
/^INSERT/ {
    prev = "";
    gsub( /\\\047/, "\047\047" )
    gsub( /\\\047\047,/, "\\\047," )
    gsub(/\\n/, "\n")
    gsub(/\\r/, "\r")
    gsub(/\\"/, "\"")
    gsub(/\\\\/, "\\")
    gsub(/\\\032/, "\032")
    print
    next
}

# Print the `CREATE` line as is and capture the table name.
/^CREATE/ {
    print
    aInc = 0;
    if ( match( $0, /`[^`]+/ ) ) 
    {
        tableName = substr( $0, RSTART+1, RLENGTH-1 ) 
    }
    prev = "";
    firstInTable = 1;
    next
}

# Replace `FULLTEXT KEY` (probably other `XXXXX KEY`)
/^  FULLTEXT KEY/ { gsub( /.+KEY/, "  KEY" ) }

# Get rid of field lengths in KEY lines
/ (PRIMARY )?KEY/ { gsub(/\([0-9]+\)/, "") }

aInc == 1 && /PRIMARY KEY/ {
    next
}

# Print all fields definition lines except the `KEY` lines.
/^  / && !/^(  KEY|\);)/ {
    if ( match( $0, /AUTO_INCREMENT|auto_increment/))
    {
    aInc = 1;
    gsub( /AUTO_INCREMENT|auto_increment/, "PRIMARY KEY AUTOINCREMENT" )
    }
    gsub( /(UNIQUE KEY|unique key) `.*` /, "UNIQUE " )
    gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
    gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
    gsub( /(COLLATE|collate) [^ ]+ /, "" )
    gsub(/(ENUM|enum)[^)]+\)/, "text ")
    gsub(/(SET|set)\([^)]+\)/, "text ")
    gsub(/UNSIGNED|unsigned/, "")
    gsub(/` [^ ]*(INT|int)[^ ]*/, "` integer")
    if (prev)
    {
    if (firstInTable)
    {
        print prev
        firstInTable = 0
    }
    else print "," prev
    }
    prev = $1
}

/ ENGINE/ {
    if (prev)
    {
    if (firstInTable)
    {
        print prev
        firstInTable = 0
    }
    else print "," prev
    }
    prev=""
    print ");"
    next
}
# `KEY` lines are extracted from the `CREATE` block and stored in array for later print 
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to 
# avoid a sqlite error for duplicate index name.
/^(  KEY|\);)/ {
    if (prev)
    {
    if (firstInTable)
    {
        print prev
        firstInTable = 0
    }
    else print "," prev
    }
    prev = ""
    if ($0 == ");"){
        print
    } else {
        if (  match( $0, /`[^`]+/ ) )
        {
            indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
        }
        if ( match( $0, /\([^()]+/ ) ) 
        {
            indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
        }
        key[tableName]=key[tableName] "CREATE INDEX \"" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
    }
}

# Print all `KEY` creation lines.
END {
    for (table in key) printf key[table]
    print "END TRANSACTION;"
}
'
exit 0

On a windows machine had the following error with cygwin:
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysql.sock' (2) when trying to connect

To fix it add -h 127.0.0.1

./mysql2sqlite.sh -u root -p -h 127.0.0.1 myDatabase | sqlite3 sqliteDb.sqlite

use sequel , one line :

sequel mysql://user:password@host/database -C sqlite://db.sqlite

I found @hplc 's and @multigl 's tweaks very useful for my mysql to sqlite export! Thanks :)

Well I answered my question before anybody answered, so I'll just edit it with the answer :-) :

If you want to ACTUALLY translate a Mysql dump into a sqlite dump (i.e. a text file created by Myphp into a text file that sqlite can read), you have to change the first line :

mysqldump --compatible=ansi --skip-extended-insert --compact "$@" | \

into:

cat "$@" | \

and use it like that :

me@mymachine$ ./mysql2sqlite.sh mydumpfile.myphpdump > mydumpfile.sqlitedump

Hope it helps ! (and is true...)

Antoine

I am running a mysql database with autoincrement fields in it and tried the tool. It throws no such table many times

The sql code of the dump is like this

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE "answers_control_question" (
"control_questionID" int(11) NOT NULL,
"inspectionformID" int(11) NOT NULL,
"status" tinyint(4) NOT NULL COMMENT '1 = goed, 2 = matig, 3 = slecht',
"questiontext" varchar(100) DEFAULT NULL,
PRIMARY KEY ("control_questionID","inspectionformID")
CONSTRAINT "fk_answers_control_question_control_question1" FOREIGN KEY ("control_questionID") REFERENCES "control_question" ("control_questionID") ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT "fk_answers_control_question_inspection_form1" FOREIGN KEY ("inspectionformID") REFERENCES "inspection_form" ("inspectionformID") ON DELETE NO ACTION ON UPDATE NO ACTION
);
INSERT INTO "answers_control_question" VALUES (1,10,1,NULL);

Can I tell the script to skip tables that start with a fixed prefix like skip all tables that start with dataface.

Can the script run using a dump.sql file. If this is true I can delete the dataface tables in it.

John

Hi.
I am developing application in android, my data(write by mysql) on service I want import all it into sqlite(local database) on android. I don't work, can't help me.....
thanks

Hello
I tried to convert my MySQL 5 db with the first script. It works fine but I see then that no Auto_increment columns in SQLite and that the type is int instead of INTEGER. Then I tried to use the patch from multigl or alx77 but then I got the same errors like user "alxd" has reported;
Error: near line 4: near "SET": syntax error
Error: near line 8: no such table: main.Definition
..
no such table

no output will be produced

Any idea why it gives an error?

kind regards
hawk

Worked a treat, thanks for sharing

sqlite kept complaining with Error: incomplete SQL: so I couldn't get the script to work.

But Kudos to @sevk because the following worked for me.

sudo gem install sequel
sudo gem install sqlite3
sudo gem install mysql
rbenv rehash
sequel mysql://user:password@host/database -C sqlite://db.sqlite

excellent work!

I received a message simply saying

memory

is this output from SQLite, mysqldump or the shell script?

it might be a good idea to echo progress eg:

starting mysqldump
...
completed mysqldump

Hi folks, I want to report that in sqlite 3.5.9 ( which is the sqlite in android 2.1 and below ) ON UPDATE NO ACTION is not supported thus if you want your database to be compatible with android 2.1 and below you should add :
/ ON UPDATE NO ACTION/ { gsub( /ON UPDATE NO ACTION/,"") } before # Print all fields definition lines except the KEY lines.
Note: I haven't written in awk before so if you belive that you can create a better fix please feel free to share

Thanks for this script, I just migrated my wiki with it: www.staerk.de/thorsten. But I had a hard time figuring out that this script did not take over the autoincrement settings: http://www.linuxintro.org/wiki/Migrating_mediawiki_from_mysql_to_sqlite#Revision::insertOn.2Fsingle-row

any chance to get this right as well? Then the script would be perfect :)

UTF-8 characters in INSERT command are translated to "?????" . How can I get rid of this?

How can I encrypt the generated sqlite database file on the fly. I have seen 'ATTACH DATABASE' sqlite command doing this. I think this script would be nice place to do so. But where should I add that command ??

Hello
I tried the script from "multigl" to convert my MySQL DB to SQLite
I do the conversion on Windows and I receive a lot of errors like
" AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY"

I do not know why? My DB has 4 tables with BIGINT instead of INT, Is this the problem?
there are also 3 error "table xyz already exists"
and at the end
"No such table main"

can anybody help me?

thanks a lot
regards
hawk

Is it also possible to create a UNIQUE index? As I understand the script makes always "CREATE INDEX" for all keys also if there was a Unique index in MySQL?

helpful, thanks.

I meet a syntax error if MySQL columns have comment definition.
e.g.
create table t_test(
id int comment 'id,primary key'
);

it seems that sqlite3 dose not support comment.
add this line :
gsub(/ COMMENT/, ", --COMMENT")
after
gsub(/UNSIGNED|unsigned/, "")

thanks, regards.

Error in MySQL: insert

'CA\'

to SQLITE 'CA\''

No run.

thanks

Error in MySQL : Insert statement 'CA\' to SQLite 'CA\'' .

No run.

Thanks.

MySQL son 'CA\\'

Awesome script, like hawkmaster, I could also use support for UNIQUE indexes. Looks like it is an easy addition, I just dont have the awk skills to implement.

This command ./mysql2sqlite.sh --default-character-set=utf8 -hHost -uUser -pPass db | sqlite3 db.sqlite support utf8,There should not have comment.Thanks hplc for the comment solution.

+# Replace table and column COMMENT
+/ COMMENT '.*'/ { gsub( / COMMENT '.*'/, "" ) }

@esperlu, thanks for sharing, this is great!

First, would it be possible for you to add one of the Open Source Licenses to your script please? We would like to use this script to automatically export some MySQL tables to SQLite on our test server every time we add a new application or application version. I have created a fork of this gist and have added an example license so it's easy for you to merge in if you are willing: https://gist.github.com/nshenry03/8960534

Second, SQLite does not support BIT fields... I was able to work around this with the following sed command (which switches to integers which are supported):

./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | \
  sed -e "s/bit(1)/INTEGER/g" -e "s/DEFAULT b'\([01]\)',/DEFAULT \1,/g" | \
  sqlite3 database.sqlite

Hopefully this helps others with similar needs.

Thanks again!

Great script! No more worries :)

The original script was stripping the final semicolon for table definitions with partitions. I'm producing those again by adding one line

#!/bin/sh

# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
# CREATE block and create them in separate commands _after_ all the INSERTs.

# Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
# The mysqldump file is traversed only once.

# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite

# Thanks to and @artemyk and @gkuenning for their nice tweaks.

mysqldump  --compatible=ansi --skip-extended-insert --compact  "$@" | \

awk '

BEGIN {
    FS=",$"
    print "PRAGMA synchronous = OFF;"
    print "PRAGMA journal_mode = MEMORY;"
    print "BEGIN TRANSACTION;"
}

# CREATE TRIGGER statements have funny commenting.  Remember we are in trigger.
/^\/\*.*CREATE.*TRIGGER/ {
    gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
    print
    inTrigger = 1
    next
}

# The end of CREATE TRIGGER has a stray comment terminator
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }

# The rest of triggers just get passed through
inTrigger != 0 { print; next }

# Print ); after removed partitions
/^\/\*.*PARTITION.*/ { print ");" }

# Skip other comments
/^\/\*/ { next }

# Print all `INSERT` lines. The single quotes are protected by another single quote.
/INSERT/ {
    gsub( /\\\047/, "\047\047" )
    gsub(/\\n/, "\n")
    gsub(/\\r/, "\r")
    gsub(/\\"/, "\"")
    gsub(/\\\\/, "\\")
    gsub(/\\\032/, "\032")
    print
    next
}

# Print the `CREATE` line as is and capture the table name.
/^CREATE/ {
    print
    if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 ) 
}

# Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
/^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }

# Get rid of field lengths in KEY lines
/ KEY/ { gsub(/\([0-9]+\)/, "") }

# Print all fields definition lines except the `KEY` lines.
/^  / && !/^(  KEY|\);)/ {
    gsub( /AUTO_INCREMENT|auto_increment/, "" )
    gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
    gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
    gsub( /(COLLATE|collate) [^ ]+ /, "" )
    gsub(/(ENUM|enum)[^)]+\)/, "text ")
    gsub(/(SET|set)\([^)]+\)/, "text ")
    gsub(/UNSIGNED|unsigned/, "")
    if (prev) print prev ","
    prev = $1
}

# `KEY` lines are extracted from the `CREATE` block and stored in array for later print 
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to 
# avoid a sqlite error for duplicate index name.
/^(  KEY|\);)/ {
    if (prev) print prev
    prev=""
    if ($0 == ");"){
        print
    } else {
        if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
        if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
        key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
    }
}

# Print all `KEY` creation lines.
END {
    for (table in key) printf key[table]
    print "END TRANSACTION;"
}
'
exit 0

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.