Preliminary knowledge of mariadb 4--xtrabackup backup and restore

first, the basic knowledge

Mysqldump is a single-threaded logical backup tool that comes with MySQL, and when the amount of data is large, the time it takes to use mysqldump backup is very long and cannot be incremental, xtrabackup is a backup tool based on underlying block replication, open source, and can be backed up online with a database. Xtrabackup supports full, incremental, and differential backups of the INNODB and XTRADB storage engines, and can only be fully backed up for MyISAM storage engines that do not support things.

Second, backup tools

The commands used by the Xtrabackup tool are Xtrabackup and Innobackupex, and Innobackupex is a two-time version of the xtrabackup using the Perl scripting language for the purpose of ease of use

This article focuses on Innobackupex as an example, introducing full, incremental, and differential backups

Third, the experimental explanation

This experiment uses CentOS7.4 system, the database version is MARIADB 5.5.56,innobackupex version is 2.3.6.

1. Full backup

(1) Create a backup account

MariaDB [hellodb]> Grant all privileges on * * to ' backup ' @ ' localhost ' identified by ' Backuppassword ';

MariaDB [hellodb]> flush Privileges;

(2) Create a backup directory

[Email protected] ~]# Mkdir-pv/data/back

[Email protected] ~]# chown-r mysql.mysql/data/back

(3) Perform a backup

Innobackupex--defaults-file=/etc/my.cnf--user=backup--password=backuppassword/data/back

When you see the completed ok! prompt, the backup succeeds, the above command can also use--databases= "DB1 DB2" to back up the established library, or use--no-timestamp to not automatically generate timestamps

(4) Close database service, delete data, simulate database corruption

[Email protected] back]# Systemctl stop Mariadb.service

(5) Pre-recovery preparation (synchronization of committed objects to disk, uncommitted transactions rollback)

Innobackupex--defaults-file=/etc/my.cnf--user=backup--password=backuppassword--apply-log/data/back/2018-04-24_ 07-38-47/

(6) Recover data (if the root account is used, you need to change the data directory of the group and the owner of the recovery)

Innobackupex--defaults-file=/etc/my.cnf--user=backup--password=backuppassword--copy-back/data/back/2018-04-24_ 07-38-47/

(7) Restart the database service to complete the operation

2. Incremental backup

The implementation of an incremental backup is based on a log sequence number (LSN) for each InnoDB page, and when the data changes, the LSN of the InnoDB page on which it is located automatically grows, and the incremental backup is the backup based on the range sequence of the LSN. The LSN number of each backup can be viewed in xtrabackup_checkpoints. The way it is backed up is slightly different from the full provisioning, and the incremental backup needs to be given a full or previous increment, in the following steps:

(1) Create a full backup

[Email protected] ~]# Innobackupex--defaults-file=/etc/my.cnf--user=backup--password=backuppassword/data/back

(2) Make incremental backups based on a full backup that has been created

[Email protected] ~]# Innobackupex--defaults-file=/etc/my.cnf--user=backup--password=backuppassword--incremental /data/incremental/--incremental-basedir=/data/back/2018-04-25_06-32-18/

(3) Make a second incremental backup based on the incremental backup that has been created for the first time (the--incremental-basedir points to the backup, which is a differential backup)

[Email protected] ~]# Innobackupex--defaults-file=/etc/my.cnf--user=backup--password=backuppassword--incremental /data/incremental/--incremental-basedir=/data/incremental/2018-04-25_06-34-15/

(4) Delete the database file, simulate the failure (save the binary file in advance, restore between backup to the point of failure also need to pass the binary file)

[Email protected] ~]# Cat/data/incremental/2018-04-25_06-39-57/xtrabackup_binlog_info

[Email protected] ~]# mysqlbinlog--start-position=1721 mysql-binlog.000002 >/data/incremental/binlog.sql

[Email protected] ~]# Systemctl stop Mariadb.service

[Email protected] ~]# rm-rf/var/lib/mysql*

(5) Pre-recovery preparation (except for the last increment, the--redo-only option is required, and the effect is not to roll back, because uncommitted transactions in Delta 1 may be committed in increment 2, so they are not rolled back.) The last 1 increments can also be used with the--redo-only option, even if he does not roll back, according to MARIADB's crash-recover mechanism, will also roll back uncommitted transactions at startup)

[Email protected] ~]# Innobackupex--apply-log--redo-only/data/back/2018-04-25_06-32-18/

[Email protected] ~]# Innobackupex--apply-log--redo-only/data/back/2018-04-25_06-32-18/--incremental-dir=/data/ incremental/2018-04-25_06-34-15/

[Email protected] ~]# Innobackupex--apply-log--redo-only/data/back/2018-04-25_06-32-18/--incremental-dir=/data/ incremental/2018-04-25_06-39-57/

[Email protected] ~]# chown-r mysql.mysql/var/lib/mysql

(6) Recovering a database

[Email protected] ~]# Innobackupex--copy-back/data/back/2018-04-25_06-32-18/

[[email protected] ~]# MySQL </data/incremental/binlog.sql

Additional notes:

1.xtrabackup is by reading the LSN thereby directing the data changes of each INNODB page size by default is 16K, the actual test found that if the data volume changes very small, even if the position in Binlog changed, The LSN number does not change when using an incremental backup, but the recovered data is consistent

2. You cannot use a relative path after the--incremental-dir option when recovering the preparation phase of an incremental backup, otherwise you will be prompted not to find the xtrabackup_logfile to recover the incremental backup

Preliminary knowledge of mariadb 4--xtrabackup backup and restore