Free SQLite to MySql Converter - Super Easy!

Posted at April 28, 2009

Heres how to copy my a development SQLite Database into a MySQL Production Database in 5 Easy Steps. Here’s the easiest way to do it. Seriously:

1. Install the SQLite Manager Firefox Addon

2. After the Addon is installed, in Firefox, go up to Tools > SQLite Manager (Opens the SQLite Manager Addon Window)

3. Click on the “Connect Database” Icon, looks like the Folder Open icon in any other program. Locate your SQLite Database file.

In a Ruby on Rails project you can find it in your rails_project > db > development.sqlite3

4. Back in the SQLite Manager Firefox Addon Window, go up to Database > Export Database (It will automatically export it as a SQL file just about ready to import into MySQL after the next step

5. Open that .sql file that was just exported in any basic text or code editor (ex: Notepad, TextEdit, TextMate, etc…). All you have to do now is do some find and replace SQLite specific syntax to MySQL syntax (easy):

find: AUTOINCREMENT   -   replace: AUTO_INCREMENT

find: ”   -   replace: [blank space - nothing - delete them all!!!]

Delete any line with this in it: “sqlite_sequence”, like the whole row. Example, you would delete both these rows:

CREATE TABLE sqlite_sequence(name,seq);
INSERT INTO sqlite_sequence VALUES(’teams’,4);

Now it should be ready to import into your MySQL database. I used phpmyadmin by just going to yourhostingdomain.com/phpmyadmin, then clicked on the import link on the phpmyadmin front page, and browsed for our file we just finished modifying. You should be ready to rock.

Extra Reading

Here’s another article that explains only what is going on, but a good read nonetheless: Quick Easy Way to Migrate SQLite 3 to MySQL?

MySQL, Ruby on Rails, SQLite

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments

8 Responses to “Free SQLite to MySql Converter - Super Easy!”

Leave Comment

(required)

(required)