It is extremely straightforward to shift from an SQLite database to a MySQL database. SQLite itself does not store any procedures or advanced operations, which is why all that is required is a simple transfer of data from one database to the other. In this case, from SQLite to MySQL.

However, there are a few hiccups that you could encounter. Firstly, both handle escaping strings within INSERT INTO clauses differently. Secondly, for booleans both use different identifiers. SQLite uses ‘t’ for true and ‘f’ for false while MySQL uses 1 and 0 respectively.

To go through with the actual migration, the following steps are required:

1. Assuming you have installed sqlite3, create a dump of the database. Sample command:

$ echo “.dumpextable” | sqlite3 dbtest.sdb>dbtest.sql

1. Create the database in MySQL

$ echo “CREATE DATABASE dbtest ” | mysql -u root -p

1. Copy the rows form the dump to the new MySQL database

$ sed -e ‘/PRAGMA/d’ -e’s/BEGIN/START/’ -e ‘s/”extable”/extable/’ <dbtest.sql | mysql -u root -p –database=dbtest

You can use the above commands by substituting the names of the database with your own database identifiers. For our case, it would create a new database in MySQL named “dbtest” with a new table “extable” which will have the data from the SQLite database.

If you prefer not to do this manually, instead prefer an automated script that can convert from SQLite to MySQL. You can use a Perl script to do so. The below script not only takes care of migrating but also handles the differences between SQLite and MySQL effectively.

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

    }

}

If you are still not comfortable with doing it manually or running a script, you may choose to do it via third-party software. These tools offer various options and customizations when migrating, such as changing the types and names of columns. One such tool is SQLite-to-MySQL by Intelligent Converters, it has key features as follows:

• Support for all versions of MySQL including forks (MariaDB, Percona) and SAAS platforms
• Schemas, data, indexes and relationships between tables are converted
• Conversion settings are stored into profile for next reuse
• MySQL charset and engine type can be customized
• option to export SQLite database into MySQL script file for those cases when direct connection is not available
• Support for command line to automate the migration