Xtrabackup backup restore MySQL Database

Original: xtrabackup backup restore MySQL Database

Mysqldump Backup In view of some of its own characteristics (lock table, essentially back up the insert script or text, does not support differential backup), is not suitable for high-real-time requirements of the situation
Xtrabackup can solve some of the above problems in mysqldump, and the production environment will have more applications.
This article simply tests the backup and restore operations of Xtrabackup to the MySQL database.

In line with the first function of the first and then deep details of the principle of a rough implementation of a backup restore, not in-depth details.

Online There are many xtrabackup articles, because the environment is different, some need to configure the Xtrabackup configuration file,
But I'm going to need any configuration file for testing under Xtrabackup 2.4.7. The details of each version may be different, so be aware of the version and environment when referencing.

Innobackupex Backup

The xtrabackup and MySQL versions are as follows

Full backup

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

Description
The 1.--defaults-file=/etc/my.cnf file must be in the front
2.--user=root--password=root,--use=*** and--password=*** must have a space in the middle,

For example, complete backup complete

For example, a full backup creates a file named after a date (month, day, minutes, minutes, seconds, YYYY-MM-DD_HH-MM-SS)
The fully backed up memory is actually a copy of the data file of the backed up database plus some information that is generated when the backup
For example, xtrabackup_checkpoints is some information about the current full backup, which is important for a differential backup.

Differential backup

Differential backups can be differentiated by a backup that relies on a full backup, based on a full backup, after a full backup.
And how to determine where to back up after a full backup depends on the xtrabackup_checkpoints of this file after the full 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
For example, differential backup complete

　　

If the specified full backup file is wrong or if the full backup file is not specified during a differential backup, you will find that the Xtrabackup prompt cannot find the xtrabackup_checkpoints file.

Innobackupex Restore

　　

Preparation phase

1, restore full backup, also 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, 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 you have multiple differential backups, apply differential backups to full backups, respectively.

Recovery phase

1. After all the differential backups have been applied to the full backup, copy the recovered differential backup to the original data directory
By default, if a file exists under the data path, copy fails and the file under the path to the data file needs to be emptied.
Innobackupex--copy-back/data/backup/2017-06-22_13-40-29
For example, complete copy-back

　　

　　

2. Start the MySQL service

Start the MySQL service and discover that the boot failed

　　

Looking at the error log (startup error message), the Mysql5.7yum installation default errorlog 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, the read data file path is required to grant both read and Write permissions
Here direct authorization data file path 777,chmod-r 777/var/lib/mysql
Then start the MySQL service and it will start normally.

　　

Xtrabackupex has just begun, leaving a lot of questions and having time to verify it again.

1, how to achieve a single library (table) backup and restore, after all, because of the actual environment, because each library backup frequency and mode (backup scheme) is not the same?

2, how to use the full plus differential backup and then combine the binary log to do a point-in-time restore?

3, how to verify the validity of the backup file?

Xtrabackup backup restore MySQL Database