xtrabackup backup and restore MySQL database

Mysqldump backup has some of its own characteristics (locking tables, essentially backing up insert scripts or text, and does not support differential backup), so it is not suitable for situations that require high real-time performance
Xtrabackup can solve some of the above problems with mysqldump, and will be used more in production environments.
This article briefly tests the backup and restore operation of Xtrabackup on the MySQL database.

Based on the principle of introducing the functions first and then going into the details, we roughly implemented a backup and restore without going into the details.

There are many xtrabackup articles on the Internet. Because the environment is different, some need to configure xtrabackup configuration files.
But when I tested under xtrabackup version 2.4.7, I needed any configuration file. The details may be different in each version, so pay attention to the version and environment when referring to reference materials.

innobackupex backup

The versions of xtrabackup and MySQL are as follows

Full backup

innobackupex --defaults-file=/etc/my.cnf --user=root --password=root --socket=/var/lib/mysql/mysql.sock /data/backup

Instructions:
1.--defaults-file=/etc/my.cnf file must be at the front
2.--user=root --password=root, --use=*** There must be a space between --password=***,

As shown in the screenshot, the full backup is completed

As shown in the screenshot, the full backup will create a date (year, month, day, hour, minute, second, yyyy-MM-dd_hh-mm-ss) The file named
is a complete backup of the memory. It is actually a copy of the data file of the backed-up database plus some information generated during the backup. ,
For example, xtrabackup_checkpoints is some information about the current full backup. This information is very important for differential backup.

Differential backup

The reason why differential backup can achieve the difference is that it relies on the full backup. After the full backup is performed on the basis of the full backup, Differential backup.
How to determine where to backup after a complete backup depends on the xtrabackup_checkpoints file after the complete backup.
innobackupex --defaults-file=/etc/my.cnf --user=root --password=root --socket=/var/lib/mysql/mysql.sock --incremental /data/backup --incremental -basedir=/data/backup/2017-06-22_13-40-29
As shown in the screenshot, the differential backup is completed

　

If in progress During differential backup, if the specified full backup file is wrong or the full backup file is not specified, you will find that xtrabackup prompts that the xtrabackup_checkpoints file cannot be found.

　

innobackupex restore

　

　Preparation phase

　1 , restore the full backup, that is, the full backup application (--apply-log) log
innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only --socket=/var /lib/mysql/mysql.sock /data/backup/2017-06-22_13-40-29
　2, apply incremental backup to full backup respectively
　　innobackupex --defaults- file=/etc/my.cnf --apply-log --redo-only --socket=/var/lib/mysql/mysql.sock --incremental /data/backup/2017-06-22_13-40 -29 --incremental-basedir=/data/backup/2017-06-22_13-41-48

If there are multiple differential backups, apply the differences separately Backup to full backup.

Recovery phase

1. After applying all differential backups to the full backup, copy the restored differential backup to the original data directory

By default , if there is a file in the data path, the copy fails and the files in the data file path need to be cleared.

innobackupex --copy-back /data/backup/2017-06-22_13-40-29

As shown in the screenshot, complete copy-back

　2. Start the MySQL service

Start the mysql service and find that the startup failed

　

Look at the error log (startup error message), mysql5.7yum The default errorlog installed is located in /var/log/mysqld.log and does not scroll by default, which means that all error messages are recorded in this file.

After the data file is restored, you need to read the data file path to grant read and write permissions Here you directly authorize the data file path 777, chmod -R 777 /var/lib/mysql

Then Start the mysql service and it can start normally.

　

xtrabackupex has just started, leaving a lot of questions. I will verify them one by one when I have time.

1. How to realize the backup and restoration of a single database (table)? After all, in the actual environment, the frequency and method (backup plan) of backup for each database are different?

　2. How to use full backup + differential backup and then combine it with binary logs to perform point-in-time restore?

　3. How to verify the validity of the backup file?

The above is the detailed content of xtrabackup backup and restore MySQL database. For more information, please follow other related articles on the PHP Chinese website!