Database Migration From SQLite To MySQL

SQLite and MySQL are two high-reliable, free and open-source relational database management systems supporting Structured Query Language (SQL) to extract and process the data. SQLite is a compact self-contained engine that can handle simple and small databases. MySQL, in contrast, has been developed for long-term projects handling huge amounts of data.

Both DBMS are widely used by numerous users and both have their strength and weak sides. As the business is constantly growing, there might be a need to migrate the source SQLite database to more powerful database management system. Being designed as an embedded database initially, SQLite is not capable of handling large projects that forces many companies and organizations to migrate from SQLite to MySQL which is more suitable for this purpose.

Also, SQLite doesn’t utilize client-server architecture that prevents from using it in concurrent access environments. Although SQLite database has a light and nature and mostly contains simplest objects, the migration task may require some efforts and skills when running manually.

SQLite and MySQL are distinguished as follows:

Different scope of data types supported
Escaping strings in the INSERT statements
Boolean data type (MySQL utilizes 1 and 0 while SQLite utilizes ‘t’ and ‘f’)

Besides, SQLite is a serverless self-contained DBMS while MySQL runs on server in a multi-user environment. There are different methods of database migration from SQLite to MySQL. Below is the most straightforward one:

First of all, install SQLite3
Dump the SQLite database using the following statement or command

$ echo ".dump archive" | sqlite3 dbtest.sdb > dbtest.sql

Create a database in the MySQL platform using the following statement

$ echo "CREATE DATABASE dbtest " | mysql -u root -p

Now, import the dump file created above to the MySQL database

$ sed -e '/PRAGMA/d' -e's/BEGIN/START/' -e 's/"archive"/archive/' < dbtest.sql | mysql -u root -p --database=dbtest

Since syntax of CREATE and INSERT statements is different in SQLite and MySQL, the method specified above may not work for large and complicated databases.

If the dump method discussed above is not working for you, you can use Python or Perl scripts for partial automation of SQLite to MySQL migration. Such scripts can convert SQLite database with respect to considerable differences in syntax between the source and target database management systems.

This is example of database conversion written on Perl:

#! /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;

    }

}

Considering all the factors, the process of database migration from SQLite to MySQL is not as easy as it may look. All methods considered above include some human factor that carries a risk of minor failures and mistakes in processing leading to data losses or corruption.

Due to this fact, it is reasonable to use special tools fully automating SQLite to MySQL database migration and eliminating risk of human errors. One of such tools has been developed by Intelligent Converters software company. Their converter carefully handles all the data, columns, tables and other database entries. During migration customers can customize name, type and other attributes of every column as well as exclude selected columns from result of migration.

Gogorapid

Database Migration from SQLite to MySQL