xtrabackup backup and restore MySQL database

Due to some of its own characteristics (locking tables, essentially backing up insert scripts or text, and not supporting differential backup), mysqldump backup is not suitable for situations with high real-time requirements.
Xtrabackup can solve some of the above problems of mysqldump and will be used more in production environments.
This article simply tests Xtrabackup's backup and restore operations on the MySQL database.

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

There are many articles about xtrabackup on the Internet. Due to different environments, some of them require configuration of xtrabackup configuration files.
But I need any configuration file when testing under xtrabackup 2.4.7 version. The details of each version may be different, so pay attention to the version and environment when referring to materials.

innobackupex backup

The versions of xtrabackup and MySQL are as follows

Full backup

illustrate:

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

As shown in the screenshot, the full backup is completed

As shown in the screenshot, a full backup will create a file named with a date (year-month-day-hour-minute-second, yyyy-MM-dd_hh-mm-ss). The memory generated by the full backup is actually a copy of the data files 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 backups.

Differential backup

The reason why differential backup can achieve differences is that it relies on full backup. It is a differential backup after full backup based on full backup.
How to determine where to back up 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, differential backup is completed

If the specified full backup file is incorrect or no full backup file is specified during differential backup, xtrabackup will prompt that the xtrabackup_checkpoints file cannot be found.

innobackupex restore

Preparation

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 backups to full backups separately 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 differential backups to the full backup separately.

Recovery Phase

1. After all the differential backups are applied to the full backup, copy the restored differential backup to the original data directory. By default, if there are files 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 the copy-back

2. Start the MySQL service

Start the mysql service and find that the startup fails

Take a look at the error log (startup error information). The default error log installed by mysql5.7yum is located in /var/log/mysqld.log, and it will not scroll by default, which means that all error messages are recorded in this file.

After the data file is restored, you need to grant read and write permissions to the data file path. Here, directly authorize the data file path 777, chmod -R 777 /var/lib/mysql
Then start the mysql service and it will start normally.

I have just started working on xtrabackupex, so there are still a lot of questions left. I will verify them one by one when I have time.

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

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

3. How to verify the validity of backup files?

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.