CODEWITHSUNDEEP
mysqldatabasedatabase-backupsdatabase-restore
Alisson Vieira
Alisson Vieira

Reputation: 197

How to restore MySQL database to a point in time

A MySQL database is used for test purpose, so I want to run backend API tests on this (multiple transactions), simulating a real database in production.

When tests execution are finished, I want to restore the database in the point before that execution, to be able to run more tests with that database snapshot.

I don't have any MySQL version restriction, because it'll be a fresh database server.

What's the most performant way to restore this database?

Upvotes: 0

Views: 2046

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562478

I'd use Percona XtraBackup to create a physical backup. This can be restored a lot faster than a dump created by mysqldump.

If the database is large enough (over 100GB), even a physical backup won't be fast enough. I'd use LVM snapshots of the filesystem. See https://www.lullabot.com/articles/mysql-backups-using-lvm-snapshots for example.

Upvotes: 1

tadman
tadman

Reputation: 211610

You have a lot of options:

  • Create a snapshot with a tool like mysqldump and restore from this snapshot later to reset the database.
  • Create a volume-level snapshot on a filesystem that supports it like ZFS and restore it later.
  • Create a virtual machine and snapshot that, later restoring it to the snapshot. Most VM environments like VirtualBox support this feature.
  • Use a Docker container with an attached volume you can snapshot and restore.

Upvotes: 1

Related Questions