Migrated from MySQL to SQLite

I bought a new VPS from HostMist a few weeks back and installed Ubuntu with 128M RAM. After running Apache and MySQL for a while, I realised that 128M is simply not enough. So I decided to give Lighttpd and SQLite a try. Installation is very smooth in Ubuntu for both and configure Lighttpd is easy enough. However, it does take me sometime to figure out how to migrate the data from MySQL to SQLite. I will explain what I did in this post. Firstly you will need to install a PDO plugin for SQLite to run under WordPress. It should be straightforword enough and no explanations would be needed. Secondly you will need to migrate your existing data in MySQL into SQlite, unless you just started your new blog. I have found a little shell script to convert exported MySQL schema to SQLite syntax at JBipNet, it is very useful:

#!/bin/bash
if [ "x$1" == "x" ]; then
  echo "Usage: $0 "
  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

I simply copy the code to my local directory named “mysql-sqlite-converter.sh”, dump the MySQL database with:

mysqldump -u {user} -p --compatible=ansi --skip-opt {database} {tablelist} > MyBlog

You will need to make sure that your SQLite database file has the same name as the new dumped MySQL file, in my case is MyBlog.sqlite. Now simply run the script saved before:

sh mysql-sqlite-converter.sh MyBlog

The script will create and overwrite the original MyBlog.sqlite file and you are ready. Enjoy!!