Quick easy way to migrate SQLite3 to MySQL?

Question

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

Answer 1

I usually use the Export/import tables feature of IntelliJ DataGrip.

You can see the progress in the bottom right corner.

[]

Answer 2

Get a SQL dump

moose@pc08$ sqlite3 mySqliteDatabase.db .dump > myTemporarySQLFile.sql

Import dump to MySQL

For small imports:

moose@pc08$ mysql -u <username> -p
Enter password:
....
mysql> use somedb;
Database changed
mysql> source myTemporarySQLFile.sql;

or

mysql -u root -p somedb < myTemporarySQLFile.sql

This will prompt you for a password. Please note: If you want to enter your password directly, you have to do it WITHOUT space, directly after -p:

mysql -u root -pYOURPASS somedb < myTemporarySQLFile.sql

For larger dumps:

mysqlimport or other import tools like BigDump.

BigDump gives you a progress bar:

Answer 3

I recently had to migrate from MySQL to JavaDB for a project that our team is working on. I found a Java library written by Apache called DdlUtils that made this pretty easy. It provides an API that lets you do the following:

1. Discover a database's schema and export it as an XML file.
2. Modify a DB based upon this schema.
3. Import records from one DB to another, assuming they have the same schema.

The tools that we ended up with weren't completely automated, but they worked pretty well. Even if your application is not in Java, it shouldn't be too difficult to whip up a few small tools to do a one-time migration. I think I was able to pull of our migration with less than 150 lines of code.

Answer 4

There is no need to any script,command,etc...

you have to only export your sqlite database as a .csv file and then import it in Mysql using phpmyadmin.

I used it and it worked amazing...

Answer 5

If you are using Python/Django it's pretty easy:

create two databases in settings.py (like here https://docs.djangoproject.com/en/1.11/topics/db/multi-db/)

then just do like this:

objlist = ModelObject.objects.using('sqlite').all()

for obj in objlist:
    obj.save(using='mysql')

Answer 6

I have carefully checked all the answers in this post, as well as the answers in another related post Translating Perl to Python. Yet none could fully solve my problem.

My scenario is I need to migrate a database of Trac from sqlite to MySQL, and the database contains a lot of tech-based wiki content. Therefore inside the INSERT INTO values, there could be SQL statements like CREATE TABLE and AUTOINCREMENT. But the line-by-line replacement could have wrong replacements there.

Eventually I have written my own tool for this purpose:

https://github.com/motherapp/sqlite_sql_parser

The usage is relatively simple:

python parse_sqlite_sql.py export.sql

Two files would be generated: export.sql.schema.sql and export.sql.data.sql. One for updated DB schema, and the other for updated DB data.

One could do further manual modifications on the DB schema file using any text editor, without worrying about changing the content.

Hope it could helps others in future.