Quick easy way to migrate SQLite3 to MySQL?

Question

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

Answer 1

It's messy because dump files are database vendor specific.

If you're using Rails, a great plugin exists for this. Read: http://blog.heroku.com/archives/2007/11/23/yamldb_for_databaseindependent_data_dumps/

Update

Currently maintained fork: https://github.com/ludicast/yaml_db

Answer 2

• http://sqlfairy.sourceforge.net/
• http://search.cpan.org/dist/SQL-Translator/

aptitude install sqlfairy libdbd-sqlite3-perl

sqlt -f DBI --dsn dbi:SQLite:../.open-tran/ten-sq.db -t MySQL --add-drop-table > mysql-ten-sq.sql
sqlt -f DBI --dsn dbi:SQLite:../.open-tran/ten-sq.db -t Dumper --use-same-auth > sqlite2mysql-dumper.pl
chmod +x sqlite2mysql-dumper.pl
./sqlite2mysql-dumper.pl --help
./sqlite2mysql-dumper.pl --add-truncate --mysql-loadfile > mysql-dump.sql
sed -e 's/LOAD DATA INFILE/LOAD DATA LOCAL INFILE/' -i mysql-dump.sql

echo 'drop database `ten-sq`' | mysql -p -u root
echo 'create database `ten-sq` charset utf8' | mysql -p -u root
mysql -p -u root -D ten-sq < mysql-ten-sq.sql
mysql -p -u root -D ten-sq < mysql-dump.sql

Answer 3

Based on Jims's solution: Quick easy way to migrate SQLite3 to MySQL?

sqlite3 your_sql3_database.db .dump | python ./dump.py > your_dump_name.sql
cat your_dump_name.sql | sed '1d' | mysql --user=your_mysql_user --default-character-set=utf8 your_mysql_db -p  

This works for me. I use sed just to throw the first line, which is not mysql-like, but you might as well modify dump.py script to throw this line away.

Answer 4

This script is ok except for this case that of course, I've met :

INSERT INTO "requestcomparison_stopword" VALUES(149,'f');
INSERT INTO "requestcomparison_stopword" VALUES(420,'t');

The script should give this output :

INSERT INTO requestcomparison_stopword VALUES(149,'f');
INSERT INTO requestcomparison_stopword VALUES(420,'t');

But gives instead that output :

INSERT INTO requestcomparison_stopword VALUES(1490;
INSERT INTO requestcomparison_stopword VALUES(4201;

with some strange non-ascii characters around the last 0 and 1.

This didn't show up anymore when I commented the following lines of the code (43-46) but others problems appeared:

    line = re.sub(r"([^'])'t'(.)", "\1THIS_IS_TRUE\2", line)
    line = line.replace('THIS_IS_TRUE', '1')
    line = re.sub(r"([^'])'f'(.)", "\1THIS_IS_FALSE\2", line)
    line = line.replace('THIS_IS_FALSE', '0')

This is just a special case, when we want to add a value being 'f' or 't' but I'm not really comfortable with regular expressions, I just wanted to spot this case to be corrected by someone.

Anyway thanks a lot for that handy script !!!

Answer 5

I use data loader for migrating almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. In my view this is a best Data Migration Tool

Download Free : http://www.dbload.com

Answer 6

The python script worked after a few modifications as follows:

# Remove "PRAGMA foreign_keys=OFF; from beginning of script
# Double quotes were not removed from INSERT INTO "BaselineInfo" table, check if removed from subsequent tables.  Regex needed A-Z added.
# Removed backticks from CREATE TABLE
# Added replace AUTOINCREMENT with AUTO_INCREMENT
# Removed replacement,
#line = line.replace('"', '`').replace("'", '`')

...

useless_es = [
    'BEGIN TRANSACTION',
    'COMMIT',
    'sqlite_sequence',
    'CREATE UNIQUE INDEX',
    'PRAGMA foreign_keys=OFF',
    ]

...

m = re.search('CREATE TABLE "?([A-Za-z_]*)"?(.*)', line)
if m:
    name, sub = m.groups()
    line = "DROP TABLE IF EXISTS %(name)s;\nCREATE TABLE IF NOT EXISTS %(name)s%(sub)s\n"
    line = line % dict(name=name, sub=sub)
    line = line.replace('AUTOINCREMENT','AUTO_INCREMENT')
    line = line.replace('UNIQUE','')
    line = line.replace('"','')
else:
    m = re.search('INSERT INTO "([A-Za-z_]*)"(.*)', line)
    if m:
        line = 'INSERT INTO %s%s\n' % m.groups()
        line = line.replace('"', r'\"')
        line = line.replace('"', "'")

...