Xtrabackup is a necessary tool for creating hot MySQL backups. The tool creates a backup of the server (i.e. all databases at once) without locks and stops. Can be installed from packages:

apt-get install percona-xtrabackup

If doesn’t exist by default.

Backup

First, copy the files of the current server using the command:

innobackupex --user = root --password = pwd / var / backups

A subfolder with the current date (for example, 20201010132431) will be created in the / var / backups folder. After that, you need to put the change log to the data file:

innobackupex --user = root --password = pwd --apply-log / var / backups / 20201010132431

Now this folder can be saved to the backup server and wait for the reason to use.

Recovery

For recovering, just copy the backup to the MySQL data folder (datadir parameter in my.cnf):

/etc/init.d/mysql stop

mv / var / lib / mysql /var/lib/mysql.old

mv / var / backups / db / 20201010132431 / var / lib / mysql

chown -R mysql: mysql / var / lib / mysql

/etc/init.d/mysql start

Periodic Copy

It is better to add a backup to cron and run it once an hour (a day, if the database takes up more than 10 GB):

rm -rf /var/backups/db.prev

mv / var / backups / db /var/backups/db.prev

innobackupex --user = root --password = pwd / var / backups

d = "/ var / backups / db /` ls / var / backups / db` "

innobackupex --user = root --password = pwd --apply-log $ d