Quick easy way to migrate SQLite3 to MySQL?

Question

Anyone know a quick easy way to migrate a SQLite3 database to MySQL?

Answer 1

This simple solution worked for me:

<?php
$sq = new SQLite3( 'sqlite3.db' );

$tables = $sq->query( 'SELECT name FROM sqlite_master WHERE type="table"' );

while ( $table = $tables->fetchArray() ) {
    $table = current( $table );
    $result = $sq->query( sprintf( 'SELECT * FROM %s', $table ) );

    if ( strpos( $table, 'sqlite' ) !== false )
        continue;

    printf( "-- %s\n", $table );
    while ( $row = $result->fetchArray( SQLITE3_ASSOC ) ) {
        $values = array_map( function( $value ) {
            return sprintf( "'%s'", mysql_real_escape_string( $value ) );
        }, array_values( $row ) );
        printf( "INSERT INTO `%s` VALUES( %s );\n", $table, implode( ', ', $values ) );
    }
}

Answer 2

this software out of the box - works for me. try it and let other know.

https://dbconvert.com/sqlite/mysql/

In addition:

I had to make one small change: somehow the auto_increment of one field (a field found from error message) was not enabled. So in phpmyadmin i check property A_I of this field and it works completely. Hope it helps.

Dunn.

Answer 3

echo ".dump" | sqlite3 /tmp/db.sqlite > db.sql

watch out for CREATE statements

Answer 4

Surprised no one's mentioned this by now, but there's actually a tool explicitly for this. It's in perl, SQL:Translator: http://sqlfairy.sourceforge.net/

Converts between most any form of tabular data (Different SQL formats, Excel spreadsheet), and even makes diagrams of your SQL schema.

Answer 5

I have taken the Python script from https://stackoverflow.com/a/32243979/746459 (above) and fixed it to cope with our own sqlite schemas. There were a few issues to deal with.

You may find it in source control here: https://bitbucket.org/mjogltd/sqlite3mysql

Also available is the same thing wrapped as a Docker image, here: https://hub.docker.com/r/mjog/sqlite3mysql/ - it is fully usable even under a Windows desktop.

Answer 6

Ha... I wish I had found this first! My response was to this post... script to convert mysql dump sql file into format that can be imported into sqlite3 db

Combining the two would be exactly what I needed:

---

When the sqlite3 database is going to be used with ruby you may want to change:

tinyint([0-9]*) 

to:

sed 's/ tinyint(1*) / boolean/g ' |
sed 's/ tinyint([0|2-9]*) / integer /g' |

alas, this only half works because even though you are inserting 1's and 0's into a field marked boolean, sqlite3 stores them as 1's and 0's so you have to go through and do something like:

Table.find(:all, :conditions => {:column => 1 }).each { |t| t.column = true }.each(&:save)
Table.find(:all, :conditions => {:column => 0 }).each { |t| t.column = false}.each(&:save)

but it was helpful to have the sql file to look at to find all the booleans.