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.
Best Answer
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:
This combination of incremental allows you to restore at different points in time instead of single full raw copy.