Shulou(Shulou.com)06/01 Report--

I. basic knowledge

Mysqldump is a single-threaded logical backup tool provided by mysql. When there is a large amount of data, it takes a long time to use mysqldump backup and cannot do incremental backup. Xtrabackup is a backup tool based on underlying block replication and open source, which can back up the database online. Xtrabackup supports full, incremental, and differential backups of Innodb and Xtradb storage engines, and only full backups for MyISAM storage engines that do not support things.

II. Backup tools

The commands used by the Xtrabackup tool are xtrabackup and innobackupex, and innobackupex is a second version of xtrabackup encapsulated in perl scripting language for ease of use.

This paper focuses on innobackupex as an example, including full backup, incremental backup and differential backup.

Third, the experimental explanation

This experiment uses CentOS7.4 system, the database version is MariaDB 5.5.56 and the 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

[root@node1 ~] # mkdir-pv / data/back

[root@node1] # chown-R mysql.mysql / data/back

(3) perform backup

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

See completed OK! The prompt indicates that the backup is successful. 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 the database service, delete the data, and simulate the database damage.

[root@node1 back] # systemctl stop mariadb.service

(5) preparation before recovery (synchronize committed things to disk and roll back uncommitted transactions)

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

(6) restore data (if you operate with a root account, you need to change the group and owner of the recovered data directory)

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

(7) restart the database service, and the operation is complete.

two。 Incremental backup

Incremental backup is based on the fact that each Innodb page has a log sequence number (LSN). When the data changes, the LSN of the Innodb page will automatically grow. Incremental backup is based on the range sequence of LSN. The LSN number of each backup can be checked in xtrabackup_checkpoints. The backup method is slightly different from that of full backup. Incremental backup needs to be performed with a full or previous increment. The specific steps are as follows:

(1) create a full backup

[root@node1] # innobackupex-- defaults-file=/etc/my.cnf-- user=backup-- password=backuppassword / data/back

(2) make an incremental backup based on the created full backup

[root@node1] # innobackupex-- defaults-file=/etc/my.cnf-- user=backup-- password=backuppassword-- incremental/ data/incremental/-- incremental-basedir=/data/back/2018-04-25mm 06-32-18 /

(3) make a second incremental backup based on the incremental backup created for the first time (if you point-- incremental-basedir to the finished backup, you will make a differential backup)

[root@node1] # innobackupex-- defaults-file=/etc/my.cnf-- user=backup-- password=backuppassword-- incremental/ data/incremental/-- incremental-basedir=/data/incremental/2018-04-25mm 06-34-15 /

(4) delete the database file and simulate the fault (save the binary file in advance, and restore the backup to the point where the failure occurs through the binary file)

[root@node1] # cat / data/incremental/2018-04-250639-57/xtrabackup_binlog_info

[root@node1 ~] # mysqlbinlog-- start-position=1721 mysql-binlog.000002 > / data/incremental/binlog.sql

[root@node1 ~] # systemctl stop mariadb.service

[root@node1 ~] # rm-rf / var/lib/mysql*

(5) preparation before recovery (you need to use the-- redo-only option except for the last increment, and its purpose is not to roll back, because uncommitted transactions in increment 1 may be committed in increment 2, so they are not rolled back. The last increment can also be used-- redo-only option, even if it does not roll back, according to mariadb's crash-recover mechanism, uncommitted transactions will be rolled back at startup)

[root@node1] # innobackupex-- apply-log-- redo-only / data/back/2018-04-250632-18 /

[root@node1] # innobackupex-- apply-log-- redo-only / data/back/2018-04-250632-18 /-incremental-dir=/data/incremental/2018-04-250006-34-15 /

[root@node1] # innobackupex-- apply-log-- redo-only / data/back/2018-04-250632-18 /-- incremental-dir=/data/incremental/2018-04-250006-39-57 /

[root@node1] # chown-R mysql.mysql / var/lib/mysql

(6) restore the database

[root@node1] # innobackupex-- copy-back / data/back/2018-04-250632-18 /

[root@node1 ~] # mysql < / data/incremental/binlog.sql

Supplementary note:

1.xtrabackup guides the change of data by reading LSN. The default size of each innodb page page is 16K. In the actual test, it is found that if the amount of data changes very little, even if the position in binlog has changed, the LSN number will not change when using incremental backup, but the recovered data is consistent.

two。 The relative path cannot be used after the incremental-dir option when preparing to restore an incremental backup, otherwise you will be prompted that the xtrabackup_logfile to restore the incremental backup cannot be found

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.