How to organize MySQL hot backup?

Question

MySQL

There is a server on which a medium-sized database is spinning (about 1GB), during deployment, before migrations, a backup of the database (mysqldump) is made, which seriously slows down the deployment of a new version of the site.

Does it make sense to deploy Percona Xtrabackup for a quick database backup and in which case a quick restore to a previous state? What are the results in terms of backup time, restore compared to mysqldump?

Answer 1

In my opinion, the easiest way in this case is to organize master-slave replication. Before the release, stop replication, make a backup from the slave. If something went wrong on the master during the release, switch all requests to the slave.

Answer 2

we use mysql with a database on a zfs partition. if a downntime of ten seconds is not critical, in such situations it is faster and more reliable to do the following:
1. stop mysql
2. take a zfs snapshot of the partition with the database (atomic operation, performed instantly)
3. start mysql
recovery is the same, instead of step 2 - rollback , also an atomic operation

Answer 3

Use the snapshots and go to percona server.