Using Mysql Dump to Create Fixtures with Where Conditions

When running unit tests it is generally advisable to have a test database that has a limited but known set of data.

The idea is that before each test run, the test database is recreated. So that this process is not too slow we then tend to use a small subset of the real live database - perhaps 100 records or entities rather than however many are on the live site.

In order to build this test database, we then use something call fixtures. This can be all kinds of things - some times the fixtures are created using PHP code. For absolute speed though, I prefer to use fixtures that are raw SQL files that can be very quickly loaded into a database.

To create the fixtures, the very simplest way is to use mysqldump

However, the issue with mysql dump in the way we normally use it is that it will dump an entire database - the opposite of what we are trying to achieve here.

The trick here is to use the --where option to mysqldump to limit the data that is actually dumped.

Lets take a simple database with an orders, orders_products and an orders_status_history table:

First we will dump the orders into an SQL file:

mysqldump database_name  orders --where="1 order by orders_id desc limit 0,100" --no-create-info > /tmp/orders.sql

Then we need to calculate what the lowest order ID would be:

lowestOrderId=$(mysql -N database_name -e "select min(t.orders_id) from (select orders_id from orders order by orders_id DESC limit 0,100) as t")

Then we can run mysqldump on the other tables and dump their data:

mysqldump database_name  orders_products --where="orders_id > $lowestOrderId" --no-create-info >> /tmp/orders.sql


mysqldump database_name  orders_status_history --where="orders_id > $lowestOrderId" --no-create-info >> /tmp/orders.sql

And thats it, you now have an orders.sql file with the data for your 100 most recent orders in a format that can easily be imported into a test database simple with

mysql test_db_name < orders.sql