Skip to main content

mysql

How-to: Convert mysql to sqlite

Posted in

I had a need today to convert a mysql database into a sqlite3 database.

First, you need this script:

#!/bin/bash
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' |
sed 's/\\r\\n/\\n/g' |
sed 's/\\"/"/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
else
  echo "There were errors during conversion.  Please review $1.err and $1.sql for details."
fi

Then, dump a copy of your database:

josh@josh-laptop-work:~/tmp$ mysqldump -u root -p --compatible=ansi --skip-opt generator > dumpfile

And now, run the conversion:

josh@josh-laptop-work:~/tmp$ mysql-to-sqlite.sh dumpfile

And if all goes well, you should now have a dumpfile.db which can be used via sqlite3.

josh@josh-laptop-work:~/tmp$ sqlite3 dumpfile.db
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
dg_cities                 dg_forms                  dg_surnames            
dg_counties               dg_provinces              dg_user_accounts        
dg_countries              dg_provinces_netherlands
dg_first_names            dg_states

How-to: Drop all tables from a mysql database

Posted in

Much easier than have to drop a database, recreate it, and then grant privileges again.

mysqldump -u <username> -p <password> --add-drop-table --no-data <database> | grep ^DROP | mysql -u <username> -p <password <database>

Got error 28 from storage engine (1030)

Apparently rather than throwing a “disk full” error message, mysql likes to tell me ERROR 28. Oh how I love mysql error messages.

Also, I really enjoy having to do work on a “production server” that is a virtual machine, with 6GB of disk space, in a test lab.

Good idea guys. Maybe some server monitoring should be in place for a PRODUCTION SERVER, no?

Syndicate content