2

I'm currently working in a testing database and we are not cleaning up after test because of our current configuration and we probably aren't changing that configuration soon, we are currently using a server to apply some UI testing on a staging server.

So I tried mysqldump but the restore time was slow, then I tried mysqldbcopy and the copy time is better, but I read something about incremental backups with innobackupex of percona xtrabackup and I think it could be faster.

I would like to know what is the best option to rollback a database to a certain point in time in the faster way possible.

  • 1
    Consider filesystem snapshots (if available on your operating system). – mustaccio Jun 21 '16 at 19:20
1

If you are trying to rollback to previous state after making logical changes i.e. DDL/DMLs - then xtrabackup can help. However, so is making a raw copy of the data while MySQL is shutdown before making the changes. Once you are done with the changes, stop MySQL then restore the previous copy, start it back up.

If you are looking for a more complex point in time recovery - yes, xtrabackup with incremental can help but not as fast the previous suggestion. You can do it like this for example:

  1. Take full backup
  2. Make changes to data
  3. Take incremental backup
  4. Make changes to data
  5. Restore backup from #1
  6. Make more changes to data
  7. Restore backup from #3 - at this point you have restored to a snapshot before you did #4.

This combination of incremental allows you to restore at different points in time instead of single full raw copy.

  • So I think mysqldbcopy is the faster one, but I should rename afterwards. – Cristopher Chacon Jun 23 '16 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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