Website Design Chester by the Informatics Centre

Convert SQLite version 2 database version 3/MySQL

Sometimes for one reason or another you have to work with old SQLite databases. A common task is migrating from V2 to the newer V3 format. You may find that you want to migrate to another DB provider like MySQL. This blog will show you these scenarios.

Prep work

Download SQLite V2 from http://www.sqlite.org/sqlitedll-2_8_17.zip and place into a folder. My example will be in C:sqlite

Download SQLite V3 from http://www.sqlite.org/2013/sqlite-shell-win32-x86-3080002.zip and place the file sqlite3.exe into the same folder as the earlier one.

Open up a command prompt and change directory to this folder. My command would be cd C:sqlite

Create a temporary SQL dump of V2M

sqlite.exe sqlitev2db.sqlite .dump > temp.sql

Check database is valid

Sometimes there's issues with the ' apostrophes (single quotes) which make the database fail when creating it in another solution like phpMyAdmin. Open up the temp.sql file in your favourite text editor (mine is Notepad++ http://notepad-plus-plus.org/). Its best to replace single quotes like '' (note the 2 characters) or ' and replace with double ones ''. It's also possible to replace using %27 if this is a HTML database like a blog.

I like to use Notepad++ find and replace but any text editor will do this. It's usually found under the Edit or View menu. Alternatively, the keyboard shortcut is almost always Ctrl + H.

Save the new file as db_valid.sql

SQLite V3

To make an SQLite 3 database:

sqlite3 database.sqlite3 < db_valid.sql

This will create a new database called database.sqlite3

That's it. All done!

MySQL

To make a MySQL database you need a server with MySQL and preferably with phpMyAdmin running on it to manage SQL efficiently from a web based interface.

In phpMyAdmin create a new database:

Click on the database and click SQL menu. Open up db_valid.sql in Notepad or your other favourite text editor and copy the contents into the SQL box.

Once this is done you'll have something like this:

The database will now have tables like so:

Clicking a table shows that the entries are in fact there:

Tada. You now have a new MYSQL database.

View all Informatics Centre blog posts.