6

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.

Improve this question
0

2 Answers 2

Reset to default
2

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
  4. Make multiple copies of the copy you already made
  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)

So far, you have multiple copies of the data at the point of time that you need.

In order to "restore" a copy, you can do one of the following:

  • 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
  • Shut down the server and copy the contents of a copy of the data back to the original data directory, and start the server again.
Improve this answer
1
  • 1
    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/
    – knaperek
    Mar 22, 2017 at 17:02
0

Establish LVM on the filesystem. Then a snapshot takes less than a second, regardless of the dataset size. You get a separate copy of the disk, but with unchanged blocks shared. Changed blocks are "copy-on-write", so you need to blow away the snapshot before it grows too big. (Soulds like that is no problem.)

Since there is essentially no copying of data (a la mysqldump or file dumps), it is very fast. You would have two instances running on the same machine, but pointing to different "Logical Volumes", so they can run independently. (Note: use a different port for each instance.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.