The following blog post contains material either currently found or soon to be incorporated into my new book, "Easy Active Record for Rails Developers". Among many other topics, you'll learn about model generation, migrations, validations, associations, scopes, joins, includes, forms integration, nested forms, and model testing with RSpec and FactoryGirl. The book is now available, head over to this website's home page to learn more.

I recently had the occasion to perform a rather dramatic refactoring of a MySQL database that involved not only the altering and altogether removal of several tables, but also the deletion of several million records. The MySQL database underlies a Rails applications, so I took full advantage of Rails migrations so the changes could easily be replicated in our staging and production environments, however even so these alterations were so severe due to the significant data removal that I wanted to first test the migrations on a copy of the development database.

To do so I logged into the MySQL terminal client and created a new database:

mysql> CREATE DATABASE dev2_example_com;
Query OK, 1 row affected (0.01 sec)

Next, I logged out of the client, and copied the contents of the original development database into the newly created database:

$ mysqldump -u root -p dev_example_com > 032014-dev_example_com.db
Enter password:

After entering your password this operation could take anywhere from a few seconds to several minutes to complete, depending upon the size of your database. When complete, mysqldump will have dumped the contents of the dev_example_com database (including the table structures and all data) into a file named 032014-dev_example_com.db.

Next, run the following command to dump the contents of 032014-dev_example_com.db into dev2_example_com:

$ mysql -u root -p dev2_example_com < 032014-dev_example_com.db
Enter password:

Once completed, the dev_example_com and dev2_example_com databases will be identical in structure and content!

On an aside, online you’ll find variations of this process in which the output of mysqldump is piped to mysql. I’m not a fan of this alternative approach, for two reasons. First, unless you’ve taken care to configure MySQL in a certain fashion, you’ll be required to supply the MySQL account password in plaintext on the command-line when calling the mysql command. Second, you may very well wish to rebuild the contents of the original database within the test database several times before you’ve worked out the refactoring kinks, so why waste time repeatedly dumping the database?

Like what you read? There’s plenty more where this came from in my new book, “Easy Active Record for Rails Developers”!