Migration of SQLite databases to MySQL servers is relatively easy compared to migrating between other DBMSs, mainly due to the fact that SQLite does not have complex database objects like stored procedures. Therefore, SQLite databases are primarily used as storage, while the data handling logic is placed in external applications. However, there are still challenges involved in migrating from SQLite to MySQL, such as differences in handling strings and boolean values.
There are various methods for SQLite to MySQL migration, including using the sqlite3 and mysql commands, as well as applying conversion scripts on Perl or Python. One popular and straightforward method is to dump the SQLite database using sqlite3 and create the MySQL database, then restore the items from the file with some modifications using mysql. However, this process may not work with large and complex SQLite databases due to essential alterations in formats of DDL and INSERT statements:
$ echo “.dump archive” | sqlite3 mydb.sdb > mydb.sql
$ echo “CREATE DATABASE mydb ” | mysql -u root -p
$ sed -e ‘/PRAGMA/d’ -e’s/BEGIN/START/’ -e ‘s/”archive”/archive/’ < mydb.sql | mysql -u root -p –database=mydb
Another method is to apply conversion scripts on Perl or Python, like the Perl script provided, which can automate the conversion of SQLite script files into MySQL format:
#! /usr/bin/perl
while ($line = <>){
if (($line !~ /BEGIN TRANSACTION/) && ($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) && ($line !~ /CREATE UNIQUE INDEX/)){
if ($line =~ /CREATE TABLE \”([a-z_]*)\”(.*)/){
$name = $1;
$sub = $2;
$sub =~ s/\”//g;
$line = “DROP TABLE IF EXISTS $name;\nCREATE TABLE IF NOT EXISTS $name$sub\n”;
}
elsif ($line =~ /INSERT INTO \”([a-z_]*)\”(.*)/){
$line = “INSERT INTO $1$2\n”;
$line =~ s/\”/\\\”/g;
$line =~ s/\”/\’/g;
}else{
$line =~ s/\’\’/\\\’/g;
}
$line =~ s/([^\\’])\’t\'(.)/$1THIS_IS_TRUE$2/g;
$line =~ s/THIS_IS_TRUE/1/g;
$line =~ s/([^\\’])\’f\'(.)/$1THIS_IS_FALSE$2/g;
$line =~ s/THIS_IS_FALSE/0/g;
$line =~ s/AUTOINCREMENT/AUTO_INCREMENT/g;
print $line;
}
}
Lastly, commercial software such as SQLite to MySQL converter from Intelligent Converters can simplify and automate the database migration process and allow customization of every available parameter.
Key features of SQLite to MySQL converter:
- Direct reading the source data right from database files
- All versions of Linux/Unix and Windows MySQL servers are supported including DBaaS variations
- Table structures, data, indexes and relationships between tables are migrated
- Option to merge and synchronize existing MySQL database with SQLite data
- SQLite data can be converted into MySQL script. This option is useful if the target MySQL server does not accept remote connections.
- The tool works on Windows XP(SP2 or SP3)/2003/Vista/Server 2008/7/8/10 and Linux with WINE
SQLite to MySQL converter does not convert views and triggers. Also, free demo version of the tool migrates only 50 records per table and does not convert foreign keys.