Xtrabackup backup to restore MySQL database, xtrabackupmysql

Xtrabackup backup to restore MySQL database, xtrabackupmysql

 

Mysqldump backup is not suitable for scenarios with high real-time requirements due to some of its own features (locking tables, essentially backing up insert scripts or text, and differential backup is not supported)
Xtrabackup can solve some of the above problems in mysqldump, and there will be more applications in the production environment.
This article briefly tests how Xtrabackup backs up and restores MySQL databases.

In this document, the function is first rolled up and then detailed, and a backup is roughly restored without in-depth details.

There are a lot of articles about xtrabackup on the Internet, because the environment is different, some configuration files that need to be configured xtrabackup,
However, any configuration file is required for testing in xtrabackup 2.4.7.

 

 

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

Note:
1. -- defaults-file =/etc/my. cnf file must be at the beginning
2. -- user = root -- password = root, -- use = *** and -- password = *** must contain spaces,

For example, the complete backup is completed.

For example, a full backup creates a file named after the date (year, month, day, minute, second, yyyy-MM-dd_hh-mm-ss)
The memory backed up completely is actually the information generated when the backup is added to the copy of the data file of the backed up database,
For example, xtrabackup_checkpoints is the information of the current full backup, which is very important for differential backup.

Differential backup

Differential backup relies on full backup for differential backup after full backup.
How to determine the backup location after the 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. sock -- incremental/data/backup -- incremental-basedir =/data/backup/2017-06-22_13-40-29
For example, differential backup is completed.

　　

If the specified full backup file is incorrect or the full backup file is not specified during differential backup, The xtrabackup prompt cannot find the xtrabackup_checkpoints file.

 

Innobackupex Restoration

　　

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. 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. sock -- incremental/data/backup/2017-06-22_13-40-29 -- incremental-basedir =/data/backup/2017-06-22_13-41-48

If multiple differential backups exist, apply the differential backup to the full backup respectively.

 

Recovery Phase

After applying all the differential backups to the full backup, copy the recovered differential backup to the original data directory.
By default, if a file exists in the data path, the copy operation fails. You need to clear the file in the data file path.
Innobackupex -- copy-back/data/backup/2017-06-22_13-40-29
For example, copy-back is completed.

　　

　　

Start MySQL Service

　　

Start mysql service and find startup failure

　　

Check the error log (startup error message). After mysql5.7, the default error is/var/log/mysqld. log, and it does not scroll by default, which means all error messages are recorded in this file.

　　

After the data file is restored, you must grant the read/write permission to the data file path.
The data file path 777, chmod-R 777/var/lib/mysql
Start the mysql service.

　　

 

Xtrabackupex was just started, leaving a lot of problems, and there is time to verify it one by one.

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

2. How can I use full backup + differential backup and then combine binary logs for Restoration Based on Time points?

3. How can I verify the validity of the backup file?