Michael Trojanek (relativkreativ) — Bootstrapper and creator of things

This article was published on October 20th 2014 and takes about 3 minutes to read.

Use it with caution — it is probably still valid, but it has not been updated for over a year.

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

Outlining how to translate them is neither the goal of this article, nor will I cover this topic here. But I am excited to tell you that I am working on a product which will help you to automate the whole process outlined here, which will take care of problems like this and which will also do some other nifty things (like sanitizing passwords).

If you haven't already, I'd suggest joining my mailing list so I can notify you when I launch it (I will also send you my ebook after you have joined).

Expand your DevOps skills!

Join hundreds of Rails developers and operators on my email list and get my ebook Build Your Own Rails Server as a free welcome gift.

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