Michael Trojanek (relativkreativ) — Bootstrapper and creator of things

This article was published on October 20th 2014 and received its last update on February 5th 2019. It takes about 3 minutes to read.

Develop your application with meaningful data

Importing your production database into your development environment is a great way to work with meaningful data and will help you to identify edge cases before pushing a new feature to production.

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:

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).

Get in the loop!

Like this article? There's more where this one's coming from — join my email list to get new articles delivered straight into your inbox.

I'll send you a free copy of my book Build Your Own Rails Server as a welcome gift.

No spam — guaranteed. You can leave at any time.