script to convert mysql dump sql file into format that can be imported into sqlite3 db

Question

hi,

i have an export sql file containing tables and data from mysql and i want to import it into a sqlite 3 db.

please can you tell me the best way?

i get error reading file in via sqlite3 binary.

Answer 1

This shell script help you

#!/bin/sh
if [ "x$1" == "x" ]; then
   echo "Usage: $0 <dumpname>"
   exit
fi
cat $1 |
grep -v ' KEY "' |
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |
sed 's/ unsigned / /g' |
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' |
sed 's/ tinyint([0-9]*) / integer /g' |
sed 's/ int([0-9]*) / integer /g' |
sed 's/ character set [^ ]* / /g' |
sed 's/ enum([^)]*) / varchar(255) /g' |
sed 's/ on update [^,]*//g' |
perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
perl -pe '
  if (/^(INSERT.+?)\(/) {
     $a=$1;
     s/\\'\''/'\'\''/g;
     s/\\n/\n/g;
     s/\),\(/\);\n$a\(/g;
  }
  ' > $1.sql
cat $1.sql | sqlite3 $1.db > $1.err
ERRORS=`cat $1.err | wc -l`
if [ $ERRORS == 0 ]; then
  echo "Conversion completed without error. Output file: $1.db"
  rm $1.sql
  rm $1.err
    rm tmp
else
   echo "There were errors during conversion.  Please review $1.err and $1.sql for details."
fi

Answer 2

To get the above script to work, I made the following changes:

1. run it with #!/bin/bash
2. add two seds to the list of pipelined seds:
   • sed 's/\\r\\n/\\n/g'
   • sed 's/\\"/"/g'
3. the 'rm tmp' line is a no-op (unless you have a file named 'tmp' lying around :O )

4. my mysqldump command looked like this:

   $ mysqldump -u usernmae -h host --compatible=ansi --skip-opt -p database_name > dump_file

Then it worked nicely... thanks for the script.

Answer 3

This script throws the following errors for me on OSX.

Substitution loop at -e line 1, <> chunk 1.

Answer 4

works fine on Centos 5.3 64bit. once you have the output file load it like so:

shell> sqlite3 file_name.db SQLite version 3.3.6 Enter ".help" for instructions sqlite> .databases seq name file

---

0 main /current_directory/file_name.db
sqlite> select * from table; . . . . . results... sqlite>.quit

Answer 5

When the sqlite3 database is going to be used with ruby you may want to change:

tinyint([0-9]*) 

to:

sed 's/ tinyint(1*) / boolean/g ' |
sed 's/ tinyint([0|2-9]*) / integer /g' |

alas, this only half works because even though you are inserting 1's and 0's into a field marked boolean, sqlite3 stores them as 1's and 0's so you have to go through and do something like:

Table.find(:all, :conditions => {:column => 1 }).each { |t| t.column = true }.each(&:save)
Table.find(:all, :conditions => {:column => 0 }).each { |t| t.column = false}.each(&:save)

but it was helpful to have the sql file to look at to find all the booleans.

Answer 6

sorry for the late questioning. In Mike Fogel's comment above, he issued the mysqldump command as: $ mysqldump -u usernmae -h host --compatible=ansi --skip-opt -p database_name > dump_file. Where is the reference to the #!/bin/bash file?

I am trying to convert a dump file by mysql into an sqlite3 database. My database has several tables and the sqlite3 DBM is not on the same machine that have MYSQL server. Is there a way to write MYSQL database to a file that sqlite3 can read with not problems?

Thanks

Answer 7

Thanks Mike for the quick response. I followed your instruction above, and the one you sent via e-mail. The script is failing to create the table, and thus i receive an error saying "Error: near line xxxx: No such table: "

Ali

Answer 8

I had an issue with the mysql db being ISO-8859-1 (Latin-1). When did the conversion to sqlite3 assumed the data was UTF-8 resulting in decoding errors.

It was easy to fix with this:

iconv -f ISO-8859-1 -t UTF-8 mysql_dump_file > mysql_dump_file_utf8

Incase this helps someone.