Yet another blog...

A little while ago I was tasked with migrating about hundred Trac instances from SQLite to MySQL. Unfortunately SQLites schema definitions is not compatible with MySQL. So I had to write some scripts to handle it. After a little bit of experimenting everything worked perfectly.

The Strategy I employed was like this:

1. Make a dump of a default MySQL Trac db schema.
2. Make a dump of the Trac SQLite database
3. Remove db schema definitions from the SQLite dump
4. Concatenate the MySQL and SQLite dumps
5. Load data into MySQL
6. Edit Trac's database settings to use MySQL
7. Do a trac-admin upgrade

Here is the shell script I made:

MYSQL_USER=username
MYSQL_PSWD=password

SQLITECMD=sqlite3
TRACBASE=/path/to/tracreps


# loop through all trac instances in tracreps
for d in $( ls $TRACBASE )
do
if [ -d "$TRACBASE/$d" ]; then
echo $d
TRACNAME=$d

# create database in mysql
echo "creating database for $TRACNAME..."
mysqladmin --user $MYSQL_USER -p$MYSQL_PSWD create $TRACNAME

# dump sqlite db structure + data
echo "dumping data from sqlite..."
$SQLITECMD $TRACBASE/$TRACNAME/db/trac.db .dump > trac.sqlite.sql

# remove database definitions from dump using a custom python script
echo "cleaning database definition from dump..."
`./cleansql.py < trac.sqlite.sql > trac.sqlite.sql.dataonly`

# concatenate mysql database definitions and sqlite data
cat trac.mysql.sql trac.sqlite.sql.dataonly > trac.sql

echo "loading data into mysql..."
mysql --user $MYSQL_USER -p$MYSQL_PSWD --default_character_set utf8 $TRACNAME < trac.sql

# edit database connection string in trac.ini
sed -i "s?sqlite:db/trac.db?mysql://$MYSQL_USER:$MYSQL_PSWD@localhost:3306/$TRACNAME?" $TRACBASE/$TRACNAME/conf/trac.ini

echo "upgrading mysql database..."
trac-admin $TRACBASE/$TRACNAME upgrade --no-backup

To remove schema definitions from the sqlite dump and fix some incompatibilities I made this python script referenced above as cleansql.py:

#!/usr/bin/env python

import sys
import re

file = sys.stdin.read()
file = re.sub(r'(CREATE (TABLE|INDEX)[^;]*|COMMIT|BEGIN TRANSACTION);', '', file)
file = re.sub(r'INSERT INTO "([^"]+)"', lambda m: 'INSERT INTO `%s`' % m.groups(1), file)
# fix sql for reports
file = re.sub(r'CAST\((.+) AS int\)', lambda m: 'CAST(%s AS signed)' % m.groups(1), file)

sys.stdout.write(file)

And that's it. I hope this will benefit someone tasked with the same job.

Loading mentions Retweet