MySQL for testing environment. Is there a way to freeze the database?

user_3480193 0
Viewed 1351

I have a website and I need to do some unit tests before I push my development code into production.

I was thinking of coding some unit tests, but instead of sending back fake database data to the unit tests, using an actual MySQL database.

However, I'd want the test MySQL database to stay relatively static. That is, after a page is done loading, I would want the MySQL database to undo any queries that modified data, such as INSERT, UPDATE, etc. That way the database is reset to its old data and ready for the next test.

  • Is there an easy way to implement this idea? Or should I take another approach?
  • What are some of the common industry practices for testing websites that are heavily reliant on MySQL queries?
testing unit-testing mysql database
2 Answers

As @Barmar mentions, you can reload your data after each test run. This works, but depending on the size of your data, it could take too long to run mysqldump and restore the dump file.

You can make an effort to create the smallest possible data set for your tests, to reduce the time.

You can also make an effort to organize your unit tests to minimize the number of times you need to revert the data. I mean group your tests, so they can keep testing the database even after changes have been made to data, until they really need the data to be reverted, and then do that revert, and move on to the next group of tests. By ordering the tests carefully, you don't necessarily have to revert after every test.

There are a few other strategies:

  • Use a physical backup instead of a mysqldump backup. Use Percona XtraBackup to create the backup, then to restore, you shut down mysqld, rsync the backup files over your datadir, chown -R mysql:mysql the files, and start mysqld. Unfortunately, this requires shutting down and restarting mysqld.

  • Use InnoDB transportable tablespaces. If you do a physical backup of the database with a special option to make tablespaces transportable, then you can use a couple of ALTER TABLE commands and replace each file table by table. See the documentation. Unfortunately, I don't know of any community tool to do this for all tables in a schema, you'll have to write a script to do that yourself.

  • Use the Percona feature for "fake changes." I've never tried this for unit test environments, but I suppose it's possible. It's really meant to help prime the buffer pool on a replica. The idea is that any DML like INSERT, UPDATE, DELETE will become a no-op. This requires using Percona Server. And it might not do what you need for your testing, because of course any changes your tests are supposed to write won't make any change.

  • LVM snapshots. This might be the best option for you. Load your test data, and take a filesystem snapshot at that time, before you begin your tests. Then after each test run, revert the filesystem to the snapshot. This requires you shut down your mysqld each time you revert. But it's very simple and quick to perform the snapshot and the restore, regardless of the size of your database. I've seen this method used to restore multi-terabyte databases quickly.

Bill_Karwin_208600

Make a snapshot by dumping the database to a file.

mysqldump yourDB > yourDB.sql

Then run your tests and revert to the snapshot:

mysql < yourDB.sql
user_14918950
Related