# Fast backup & restore of MySQL database to revert recent changes

I have a decently big MySQL DB (InnoDB) with size about 400 MB. Thus dumping the whole DB with mysqldump and restoring it back takes forever (more than 15 minutes, which is unacceptable).

For debugging purposes I need to run my code against a certain "version" of the DB and then revert all changes back (if any) and try again (possibly multiple times). In order to reproduce a bug, I need to always start from the same version of DB.

Is there any way to manually instruct the DB to create a point-in-time snapshot and then restore it back on demand? This should ideally take just a couple of seconds (I imagine some copy-on-write method would be used).

I'm looking for a solution that is independent from the end application. Imagine a web application that you need to run your robot tests on (which includes multiple request/response cycles). Moreover, if we treat the application as possibly buggy we can't trust it to reliably manage transaction rollbacks either.

I just tried to backup /var/lib/mysql with rsync. Cannot even start MySQL after restore, probably there's some file left somewhere that breaks the integrity. Anyway, I was looking for a more higher level solution, resorting to FS-level backups only as a last hope.

Copying the data directory, as you mentioned, is one way to accomplish what you need. Here are the steps, and if you face any error, please post it.

1. Shut MySQL server down; or in a terminal flush tables with lock FLUSH TABLES WITH READ LOCK [Important: Keep the terminal open]
2. Copy the data directory: cp, or rsync
3. Restart the server if you shut it down; or go back to the terminal and unlock the tables: UNLOCK TABLES
5. Chnage the owner of the newly created directories to mysql: chown mysql:mysql dir_name (I think it is chown -r, so it runs recursively)
• Change the value of datadir in my.cfg (or my.ini) to a location of one of the data copies, and restart the server; OR
• This worked like charm at last, I must have forgotten to set proper ownership on restored files before. Anyway, this did the work finally: rsync -aP /var/lib/mysql/ mysql.backup/; and then restore with rsync -aP mysql.backup/ /var/lib/mysql/ – Jozef Mar 22 '17 at 17:02