The G String

When working in ruby on rails 2.0.2 I keep my development database in the default sqlite3 but use mysql for the production database on a seperate test server. I found that after a while of use from my alpha-testers, the test server database for my web app had been filling up with useful, real-life data, that I wanted to use on my development environment. So I needed a way to transfer the data from mySQL (5.0.32) into sqlite3.

I exported the data from mySQL using the mysqldump utility. The mysqldump tool is normally used for backing up mySQL databases, but with a but of cunning can be used to export data into other formats. Firstly, since my sqlite3 development database already exists and can be recreated from the rake migration scripts, I didn’t need any of the SQL statements that modify or create the schema. You can tell mysqldump that with the –no-create-db and –no-create-info arguements to mysqldump:

mysqldump --no-create-db --no-create-info yourdatabase

Secondly, mysqldump uses its comma-seperated INSERT feature for brevity. However, sqlite doesn’t like this, so I wanted it to use a sepereate INSERT statement for each row. This is done with the –extended-insert=0 option

mysqldump --no-create-db --no-create-info --extended-insert=0 yourdatabase

Lastly, I wanted to ignore any of the comments or locking statements and output only those INSERT statements into a file. I did this using grep and piping the output:

mysqldump --no-create-db --no-create-info --extended-insert=0 yourdatabase | grep 'INSERT'

you can then redirect this output into a file

mysqldump --no-create-db --no-create-info --extended-insert=0 yourdatabase | grep 'INSERT' > yourdatabase.sql

The one problem that I found with this output was that sqlite3 handles escape characters differently from mySQL, so I had to manually replace any instances of \’ with ” using my text editor.

This yourdatabase.sql file then contains a load of INSERT statements. Since I didn’t need the data in my development database anymore, I decided that the easiest thing to do would be to just delete the db/development.sqlite3 database and use rake to rebuild the schema, to leave me a clean, empty database. This avoids problems such as clashing primary key entries.

rm db/development.sqlite3
rake db:create
rake db:migrate

Finally, to import my data into sqlite3 I used the .read command in the interactive command-line tool

.read yourdatabase.sql

Tags: mySQL, Ruby On Rails, sqlite3

This entry was posted on Saturday, September 27th, 2008 at 7:53 pm and is filed under Databases, Ruby On Rails. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.