While there are other ways to export and import database dumps, for this article I chose steps which are easy to automate.

Requirements

I am assuming that you are using MySQL in prouction and SQLite for your local development.

Besides, you have to make absolutely sure that both environments use the same database schema. This is crucial to enable a smooth import.

You need a user account on your production server (which can access the machine via SSH) and the MySQL client tools installed on your development system (if you use Homebrew on a Mac or a CentOS/RedHat Linux, the package is named mysql, Debian/Ubuntu users need to install mysql-client).

Dump your production database

First use SSH to forward a local port to your remote server:

ssh -N -L 3307:localhost:3306 someuser@your.server

This command will forward all connections to port 3307 on your local machine to port 3006 on your remote server (replace user and server name accordingly). By default it will stay in the foreground, blocking your terminal (so turning forwarding off just requires pressing Ctrl-C). If you do not want to open a new terminal session, you can run the command in the background using the -f flag.

Now you can now use your local mysqldump command to get a dump of the MySQL database on your server:

mysqldump --host=127.0.0.1 --port=3307 --user=mydbuser --password --ignore-table=mydb.schema_migrations mydb | grep -i ^insert > ~/dump.sql

This command will do the following things:

• Connect to your database as user mydbuser (asking for a password)
• Dump all tables of the database mydb, skipping the schema_migrations table
• Filter the dump to just leave lines starting with INSERT INTO
• Write the final output to the file dump.sql in your home directory

Again, change the database user and database name to suit your environment before running the command.

Import the dump into your development database

cd /path/to/your/app/db
for table in $(awk '{ print $3 }' ~/dump.sql | tr -d '`'); do sqlite3 development.sqlite3 'DELETE FROM $table;'; done
cat ~/dump.sql | sqlite3 development.sqlite3

These commands first step into your Rails application's db subdirectory and then use a small awk-script to get all table names from your SQL dump (stripping them from surrounding backticks). It then iterates over them and removes all rows from each table.

When this is done, the dump is imported into your development database.

Caveats

The small issue is that MySQL and SQLite represent boolean values differently (MySQL uses 0 and 1 whereas SQLite uses f and t). This is more of a cosmetic one since Rails interprets them correctly, but if you want to clean things up you can run the following queries in the SQLite console after the import:

UPDATE table_name SET boolean_field='t' WHERE boolean_field=1;
UPDATE table_name SET boolean_field='f' WHERE boolean_field=0;

The bigger problem is that the steps outlined here do not work when you dump a table whose values include text (varchar or text fields) with escaped quotes (like blog posts or comments).