Use xtrabackup to perform Incremental backup and recovery for the mariadb Database

Due to recent commonplace issues, the data volume in the zabbix database is getting bigger and bigger. When the conventional mysqldump tool backs up the database, the zabbix service becomes abnormal and generates a large number of alarms. As a result, we have the article about zabbix database backup and Table Partitioning. Because zabbix is an innodb Storage engine

Due to recent commonplace issues, the data volume in the zabbix database is getting bigger and bigger. When the conventional mysqldump tool backs up the database, the zabbix service becomes abnormal and generates a large number of alarms. As a result, we have the article about zabbix database backup and Table Partitioning. Because zabbix is an innodb Storage engine

Due to recent commonplace issues, the data volume in the zabbix database is getting bigger and bigger. When the conventional mysqldump tool backs up the database, the zabbix service becomes abnormal and generates a large number of alarms. As a result, we have the article about zabbix database backup and Table Partitioning.

Because zabbix is the innodb Storage engine used, the xtradbbackup database backup software developed by percona is much more efficient than mysqldump in innodb database backup, and can implement Incremental Backup without locking table backup. So it is very suitable for my needs. I will study xtradbbackup as a database backup tool today.

First, you need to download xtradbbackup on percona official website, link in this: https://www.percona.com/downloads/XtraBackup/LATEST/

You can download the source package installation, you can also download rpm package installation, I download here is centos 7 rpm package installation, package name is: percona-xtrabackup-2.2.12-1.el7.x86_64.rpm

Install the rpm package

yum -y install perl-DBD-MySQL

Two more errors are reported during subsequent installation. You need to install two packages:

yum -y install libaioyum -y install perl-Digest-MD5

Then install xtradbbackup:

rpm -ivh percona-xtrabackup-2.2.12-1.el7.x86_64.rpm

After installation, you can use innobackupex -- help to view the command parameters.

Syntax: innobackupex -- user = DBUSER -- password = DBUSERPASS/path/to/BACKUP-DIR/

/Path/to/BACKUP-DIR/file is xtrabackup backup file, while backing up, innobackupex will also create the following file in the backup directory:

Xtrabackup_checkpoints-Backup Type (such as full or incremental), backup status (such as whether it is already in prepared status), And LSN (log serial number) range information; each InnoDB page (usually 16 K size) it will contain a log serial number (LSN. The LSN is the system version number of the entire database system. The LSN related to each page can indicate how the page has changed recently.

Xtrabackup_binlog_info -- the binary log file currently in use by the mysql server and the location of the binary log event until the moment of backup.

Xtrabackup_binlog_pos_innodb -- the current position of the binary log file used for InnoDB or XtraDB tables.

Xtrabackup_binary -- The xtrabackup executable file used in backup;

Backup-my.cnf -- configuration option information used by the BACKUP command;

Note: When Using innobackupex for backup, you can also use the -- no-timestamp Option to prevent the command from automatically creating a time-based directory, the innobackupex command creates a BACKUP-DIR directory to store backup data.

Common options:

-- Defaults-file # path of the configuration file of the database. It feels that the local backup is not written. It has not been tested remotely.

-- Apply-log # prepare to start the mysql service on a backup.

-- Copy-back # copy data, indexes, and logs from the backup directory to the specified initial location in the my. cnf file.

-- No-timestamp # The time directory is not automatically generated when the backup is created. You can customize the backup directory name, for example,/backups/mysql/base.

-- Databases # used to specify the database to be backed up. How to use multiple database files: "database1 database2 ″

-- Incremental # perform incremental backup on the basis of full backup, followed by the path to the storage directory of the incremental Backup

-- Incremental-basedir = DIRECTORY # DIRECTORY of the full backup path required for incremental backup or DIRECTORY of the last incremental Backup

-- Incremental-dir = DIRECTORY # DIRECTORY path of incremental backup storage

-- Redo-only # used to prepare incremental Backup content and merge the data to the full backup directory, which is used in combination with the-incremental-dir incremental Backup Directory.

-- Force-non-empty-directories # If a specific database is used for backup and restoration, you do not need to delete the entire mysql directory, but only the specific database and related files, if this parameter is added during restoration, no error is reported.

Now, we need to test the process of using xtradbbackup to perform full backup and recovery for the database.

First, create the backup directory:

mkdir -p /opt/backup

Note that when innobackupex is used for backup, you can also use the -- no-timestamp Option to prevent the command from automatically creating a directory named after time. In this way, the innobackupex command creates a BACKUP-DIR directory to store backup data.

Innobackupex -- user = root -- password = xxxxxx/opt/backup (PS: in this case, a full-backup directory named after time is automatically created under/opt/backup) innobackupex -- user = root -- password = xxxxxx -- no-timestamp/opt/backup1 (PS: The full backup file and directory will be generated under the/opt/backup1 directory)

The following is the directory file backed up:

ls 2015-10-12_16-09-18/backup-my.cnf  CrazyEyes  ibdata1  mysql  performance_schema  test  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile  zabbix

Backup File description:

(1) xtrabackup_checkpoints -- Backup Type (such as full or incremental), backup status (such as whether it is already in prepared status), And LSN (log serial number) range information;

Each InnoDB page (usually 16 KB) contains a log serial number, that is, the LSN. The LSN is the system version number of the entire database system. The LSN related to each page can indicate how the page has changed recently.

Cat xtrabackup_checkpoints backup_type = full-backuped # indicate this is full backup from_lsn = 0 # full backup lsn start number to_lsn = 2026561686 # maximum serial number record from full backup last_lsn = 2026562643 # current log after full backup serial number compact = 0 # indicates not to be packaged

(2) xtrabackup_binlog_info -- the binary log file currently in use by the mysql server and the location of the binary log event until the moment of backup.

cat xtrabackup_binlog_info mysql-bin.0000011371180-1-175

(3) backup-my.cnf -- the configuration option information used by the BACKUP command and backup-independent will not be recorded, backup configuration files need to be separately backed up.

cat backup-my.cnf # This MySQL options file was generated by innobackupex.# The MySQL server[mysqld]innodb_checksum_algorithm=innodbinnodb_log_checksum_algorithm=innodbinnodb_data_file_path=ibdata1:12M:autoextendinnodb_log_files_in_group=2innodb_log_file_size=50331648innodb_page_size=16384innodb_log_block_size=512innodb_undo_directory=.innodb_undo_tablespaces=0

(4) xtrabackup_info -- records the mariadb version information and some attribute information. Restoration is used to detect version matching.

cat xtrabackup_info uuid = a2443721-70b8-11e5-ae53-000c29bc81b7name = tool_name = innobackupextool_command = --user=root --password=... /backup/tool_version = 1.5.1-xtrabackupibbackup_version = xtrabackup version 2.2.12 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 8726828)server_version = 10.0.17-MariaDB-logstart_time = 2015-10-12 16:09:18end_time = 2015-10-12 16:10:00lock_time = 3binlog_pos = filename 'mysql-bin.000001', position 137118, GTID of the last change '0-1-175'innodb_from_lsn = 0innodb_to_lsn = 2026561686partial = Nincremental = Nformat = filecompact = Ncompressed = N

After the backup file is created, the data cannot be used for restoration, because the backup data may contain uncommitted transactions or transactions that have been committed but not synchronized to the data file. Therefore, the data files are still inconsistent. The main function of "Preparation" is to roll back uncommitted transactions and synchronize committed transactions to data files, so that the data files are in a consistent state.

Use the -- apply-log option of the innobakupex command to synchronize data files.

Innobackupex -- apply-log 2015-10-12_16-09-18 directory structure after synchronization: ls implements ibdata1 ib_logfile1 performance_schema implements xtrabackup_checkpoints implements ib_logfile0 mysql test implements xtrabackup_info zabbix

During the "Preparation" process, innobackupex can also use the -- use-memory option to specify the memory size that can be used. The default value is usually 100 MB. If enough memory is available, you can allocate more memory to the prepare process to speed up its completion.

The command is as follows:

innobackupex --user=root --password=xxxxx --no-timestamp --use-memory=500MB /backup2

Set the memory used for data preparation to 500 mb, and do not create a folder named after time.

Simulate database corruption and recover data:

First, delete all data in the data directory of the mysql database:

rm -fr /data/mysql/*ll /data/mysql/

Total usage 0

In this case, you cannot directly close the database:

service mysqld stop ERROR! MySQL server PID file could not be found!

You can use pkill to kill the master database process:

pkill mysqlps aux|grep mysqlroot     27381  0.0  0.0 112656   976 pts/1    R+   18:10   0:00 grep --color=auto mysql

Then, check whether the datadir path is set in [mysqld] in the database configuration file (PS: If no path is set here, an error will be reported during data recovery. The path I set here is: datadir =/data/mysql)

The path of my. cnf file is/etc/my. cnf.

Now you need to recover data:

(PS: Before restoring data, you must first check whether the datadir path contains files. If the path contains files, an error will be reported during restoration)

The -- copy-back option of the innobackupex command is used to perform the Restoration Operation. It copies all data-related files to the DATADIR directory of the mysql server to execute the restoration process. Innobackupex gets information about the DATADIR directory through a backup-my.cnf.

Syntax:

innobackupex --copy-back /path/to/BACKUP-DIR

After the data is restored to the DATADIR directory, make sure that the owner and group of all data files are correct, such as mysql. Otherwise, before starting mysqld, you must modify the owner and group of the data file in advance.

innobackupex --copy-back /backup2/

You can recover the data. Then modify the file owner and group of the database datadir path:

chown -R mysql.mysql /data/mysql/

At this time, the database can be started normally.

service mysqld startStarting MySQL. SUCCESS!

At this time, binary logs are rerecorded. In this way, the process of full backup and full backup recovery of the database is over.

Incremental database backup and Incremental backup data recovery:

Tip: After the restoration is complete, a full backup should be performed again, and subsequent Incremental backup should be performed based on this full backup.

innobackupex --user=root --password=xxxxxx --use-memory=500MB /backup3innobackupex --apply-log /backup3/2015-10-13_08-48-25/ls /backup32015-10-13_08-48-25

Then you can use innobackupex for Incremental backup.

Each InnoDB page contains an LSN. When the related data changes, the LSN of the related page automatically increases. This is the foundation for InnoDB tables to perform Incremental backup, that is, innobackupex is implemented by backing up pages that have changed since the last full backup.

To implement the first Incremental backup, run the following command:

innobackupex --user=root --password=xxxxxx --incremental /backup/ --incremental-basedir=BASEDIR

Here, BASEDIR refers to the directory where the full backup is located. After this command is executed, the innobackupex command creates a new directory named after time in the/backup/directory to store all Incremental backup data. In addition, when an incremental backup is performed again after the incremental backup is executed, its -- incremental-basedir should refer to the directory where the last incremental backup is located.

Note that Incremental Backup can only be applied to InnoDB or XtraDB tables. For MyISAM tables, full backup is actually performed during Incremental backup.

The command is as follows:

First incremental Backup: innobackupex -- user = root -- password = xxxxxx -- incremental/backup3/-- incremental-basedir =/backup3/2015-10-13_08-48-25/second incremental Backup: innobackupex -- user = root -- password = xxxxxx -- incremental/backup3/-- incremental-basedir =/backup3/2015-10-13_08-52-12/

The "prepare" (prepare) Incremental Backup differs from the full backup. Note the following in particular:

(1) On each backup (including full and Incremental Backup), you must "replay" The committed transactions ". After "replay", all the backup data will be merged to the full backup.

(2) Roll Back uncommitted transactions based on all backups ".

Therefore, the operation becomes:

Innobackupex -- apply-log -- redo-onlyBASE-DIRinnobackupex -- apply-log -- redo-only/backup3/2015-10-13_08-48-25/then execute: innobackupex -- apply-log -- redo-only BASE-DIR -- incremental-dir = INCREMENTAL-DIR-1innobackupex -- apply-log -- redo-only/backup3/2015-10-13_08-48-25/-- incremental-dir =/backup3/2015-10-13_08-52-12/is the second increment: innobackupex -- apply-log -- redo-only BASE-DIR -- incremental-dir = INCREMENTAL-DIR-2innobackupex -- apply-log -- redo-only/backup3/2015-10-13_08-48-25/-- incremental-dir =/backup3/2015-10-13_08-52-59/

Where the BASE-DIR refers to the directory where the full backup is located, while the INCREMENTAL-DIR-1 refers to the directory of the first Incremental backup, The INCREMENTAL-DIR-2 refers to the directory of the second Incremental backup, and so on, that is, if multiple incremental backups exist, the above operations must be performed each time;

cat /backup3/2015-10-13_08-48-25/xtrabackup_checkpoints backup_type = log-appliedfrom_lsn = 0to_lsn = 2063010964last_lsn = 2063010964compact = 0cat /backup3/2015-10-13_08-52-59/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 2062984015to_lsn = 2063010964last_lsn = 2063010964compact = 0

We can see that all the incremental backups are now restored to the full backup. At this time, we can recover the data.

rm -fr /data/mysql/*pkill mysqlps aux|grep mysqlroot     15699  0.0  0.0 112656   980 pts/1    R+   09:29   0:00 grep --color=auto mysqlrm -fr /data/mysql/*innobackupex --copy-back /backup3/2015-10-13_08-48-25/chown -R mysql.mysql /data/mysql/service mysqld startStarting MySQL...... SUCCESS!

Now, the simulation data loss and Incremental Backup recovery are complete, if data corruption is not as clean as my direct rm-fr, You can merge the latest Incremental backup to the full backup and restore the database. Then, import the latest binlog and restore the data.

cat /backup3/2015-10-13_08-48-25/xtrabackup_binlog_info mysql-bin.000001133183220-1-31629mysqlbinlog --start-position=13318322 /data/mysql/mysql-bin.000001 > /tmp/statements.sql

In this way, the database status before the fault can be restored. (PS this operation should be performed before starting the database)