Xtrabackup for MySQL backup and recovery

first, using xtrabackup for MySQL preparation Part

1. Introduction

Xtrabackup is a MySQL database backup tool provided by Percona, officially described, which is the world's only one by one open source tools that enable hot provisioning of InnoDB and XTRADB databases. Characteristics:

(1) The backup process is fast and reliable;

(2) The backup process does not interrupt the executing transaction;

(3) Can save disk space and traffic based on functions such as compression;

(4) Automatic implementation of backup inspection;

(5) Fast restore speed;

2. Installation

The latest version of the software is available from http://www.percona.com/software/percona-xtrabackup/Therefore, directly download the corresponding version of the RPM package installation, where the process is no longer demonstrated.

This tool will be installed on the host you need to backup or remote operation can also

percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm

[[Email protected] ~] #yum install./percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm

[[Email protected] ~] #rpm-ql percona-xtrabackup-24

Second, the implementation of backup

Preparation of the environment before backup

Two host computers:

172.18.77.7:mysql Primary Server

172.18.77.77: A host for recovering backup data

In order to be clear I will be the host name of each host is not the same, here backup I back up on this machine, in order to facilitate the demonstration. (It is recommended to place the backup on other hosts)

Install the percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm kit on both MySQL servers

1. Full backup

[[Email protected] ~] #mkdir/app/backups Create a directory to hold backup data [[email protected] ~] #innobackupex--user=root--password=centos- -host=localhost/app/backups/If you back up a library, you can use--databases=[[email protected] ~]cd/app/backups/2017-11-14_16-16-20/

At the same time as the backup, Innobackupex also creates the following files in the backup directory:

(1) xtrabackup_checkpoints--the backup type (such as full or incremental), the backup state (such as whether it is already in the prepared state), and the LSN (log sequence number) range information;

Each InnoDB page (typically 16k size) contains a log sequence number, the LSN. LSN is the system version number of the entire database system, and each page-related LSN can indicate how the page has changed recently.

(2) Xtrabackup_binlog_info--mysql the binary log file currently in use by the server and the location of the binary log event up to the moment the backup was made.

(3) xtrabackup_binlog_pos_innodb--binary log files and the current position for binary log files for InnoDB or xtradb tables.

(4) Xtrabackup executable files used in xtrabackup_binary--backup;

(5) backup-my.cnf--configuration option information used for backup command;

2. Data recovery

Note: recovery does not start MySQL

[[Email protected] ~] #scp-R/app/backups/2017-11-14_16-16-20/172.18.77.77:

[[Email protected] ~] #systemctl Stop mariadb

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

Prepare (Prepare) a full backup

[[Email protected] ~] #cd 2017-11-14_16-16-20/

We switch to this directory, prepare the backed up data once. This prepare is to allow the committed transaction to be synchronized, the uncommitted transaction to be rolled back, the rollback of the transaction to complete the rollback, This operation takes advantage of the--apply-log option of the Innobakupex command to implement the above functions

In general, data cannot be used for recovery operations after the backup is complete, because the data that is backed up may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state at this time. The primary role of Prep is to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to data files.

The operation is as follows:

[[email protected] 2017-11-14_16-16-20] #innobackupex--apply-log/root/2017-11-14_16-16-20/171114 16:49:02 completed Ok! The last line appears completed ok! to indicate that it has been synchronized back to application completion

[[email protected] 2017-11-14_16-16-20] #innobackupex--copy-back/root/2017-11-14_16-16-20/171114 16:52:30 completed Ok! Show success

[[email protected] 2017-11-14_16-16-20] #cd/var/lib/mysql/[[email protected] MySQL] #chown-R mysql.mysql./* Change both owner and owner to Mysql[[email protected] MySQL #systemctl start mariadb startup service

[[email protected] MySQL] #mysql-uroot-pcentos try to connect and see if the data is restored welcome to the MariaDB Monitor. Commands End With; or \g.your MariaDB connection ID is 4Server version:5.5.52-mariadb MariaDB servercopyright (c), Oracle, Mariad B Corporation Ab and others. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement. MariaDB [(None)]>

Experiment proves data recovery complete!!!!

Full, incremental, binary logs for Point-in-time restore backups

1, first to do a full-scale backup

[[Email protected] ~] #innobackupex--user=root--password=centos--host=localhost/app/backups/

2, then do an incremental backup, first connected to the database, insert a record

[[Email protected] ~] #mysql-uroot-pcentos MariaDB [(none)]>use hidb; MariaDB [hidb]> INSERT INTO students values (1001, ' Wangba ', and ' F ', ' Fanren ');

[[Email protected] ~] #innobackupex--user=root--password=centos--host=localhost--incremental/app/backups/-- Incremental-basedir=/app/backups/2017-11-14_17-18-40xtrabackup:transaction Log of LSN (3776175) to (3776175) was copied.171114 17:29:43 completed ok! Backup complete, this is the first incremental backup

[[email protected] backups] #cd  /app/backups/2017-11-14_ 17-29-39/[[EMAIL PROTECTED] 2017-11-14_17-29-39] #lsbackup-my.cnf  hidb            ibdata1.meta  performance_schema   xtrabackup_binlog_info  xtrabackup_infoddvdb           ibdata1.delta  mysql         test                 xtrabackup_checkpoints   XTRABACKUP_LOGFILE[[EMAIL PROTECTED] 2017-11-14_17-29-39] #cat  xtrabackup_ checkpointsbackup_type = incremental  backup type is an incremental backup from_lsn = 3775741to_lsn =  3776175last_lsn = 3776175compact = 0recover_binlog_info = 0 

We are in the second incremental backup

MariaDB [hidb]> Delete from students where age>90; MariaDB [hidb]> SELECT * from students;

[Email protected] ~]innobackupex--user=root--password=centos--host=localhost--incremental/app/backups/-- incremental-basedir=/app/backups/2017-11-14_17-29-39 xtrabackup:transaction Log of LSN (3794804) to (3794804) was copied.171114 17:40:01 completed ok! [[email protected] backups] #cd 2017-11-14_17-39-58[[email protected] 2017-11-14_17-39-58] #cat xtrabackup_ Checkpointsbackup_type = INCREMENTALFROM_LSN = 3776175to_lsn = 3794804last_lsn = 3794804compact = 0recover_binlog_info = 0

Perform certain operations on the data on the primary MySQL server at a time, such as when the server is down for the following operations

MariaDB [hidb]> Update students set major= ' Shepi ' where age=80; MariaDB [hidb]> SELECT * from students where age=80;

So we're going to look at the Xtrabackup_binlog_info file under the backup directory at the last backup, and determine where the binary logs are in what event

[[email protected] 2017-11-14_17-39-58] #cd/app/backups/[[email protected] backups] #ls2017 -11-14_17-18-40 2017-11-14 _17-29-39 2017-11-14_17-39-58[[email protected] backups] #cd 2017-11-14_17-39-58/[[email protected] 2017-11-14_ 17-39-58] #cat xtrabackup_binlog_infomaster-log.0000061290[[email protected] ~]# mysqlbinlog-j 1290/mydata/logs/   master-log.000006 >/root/mybinlog.sql[[email protected] ~] #scp/root/mybinlog.sql 172.18.77.77: Copy this part of the data to the server for backup

3. Data recovery

First, the first incremental backup is merged, the second increment is merged, then a directory to recover, the operation is as follows

[[email protected] 2017-11-14_17-39-58] #cd/app/backups/[[email protected] backups] #ls2017 -11-14_17-18-40 2017-11-14 _17-29-39 2017-11-14_17-39-58

[[Email protected] ~] #cd/app/backups/2017-11-14_17-18-40/[[email protected] 2017-11-14_17-18-40] #innobackupex-- Apply-log--redo-only/app/backups/2017-11-14_17-18-40/Combined Full volume 171114 19:53:22 completed OK! Success

[[email protected] 2017-11-14_17-18-40] #innobackupex--apply-log--redo-only./--incremental-dir=/app/backups/ 2017-11-14_17-29-39 Merge First increment 171114 20:00:32 completed ok!

[[email protected] 2017-11-14_17-18-40] #innobackupex--apply-log--redo-only/app/backups/2017-11-14_17-18-40/-- incremental-dir=/app/backups/2017-11-14_17-39-58 Merge second increment 171114 20:03:24 completed ok!

The above merged increments must be merged in the catalog of the full volume backup

[[email protected] backups] #scp-R 2017-11-14_17-18-40 172.18.77.77: Copy the combined full-volume directory to the server for data recovery [[email protected] MySQL] #systemctl stop Mariadb[[email protected] MySQL] #rm-rf/var/lib/mysql/*[[email protected] ~] #cd 2017-11-14_17-18-40 [[E Mail protected] 2017-11-14_17-18-40] #innobackupex--copy-back/root/2017-11-14_17-18-40/[[email protected] 2017-11-14_17-18-40] #cd/var/lib/mysql/[[email protected] MySQL] #chown-r mysql.mysql./*[[email protected] ~] #cp mybinlog.sql/tmp/

[[Email protected] ~] #systemctl start mariadb

[[email protected] ~] #mysql  -uroot -pcentosmariadb [(none)]> use hidb; mariadb [hidb]> select * from students where age=80;+------+---------+-- ----+--------+--------------+| id   | name    | age   | gender | major   |+------+---------+------+--------+--------------+|    65 | stu65   |   80 | M       | null  | |    83 | stu83   |   80 | M       | null  | |   104 | stu104  |   80 | F       | null  | |   113 | stu113  |   80 | M       |  null  | |   192 | stu192  |   80 | F       | null  | |   207 | stu207  |   80 | F       | null  | |   222 | stu222  |   80 | F       | null  | |   298 | stu298  |   80 | M       | null  | |   409 | stu409  |   80 | F       | null  | |   415 | stu415  |   80 | F       | null  | |   448 | stu448  |   80 | F       | null  | |   513 | stu513  |   80 | F       | null  | |   545 | stu545  |   80 | M       | null  | |   590 | stu590  |   80 | F       | null  | |   676 | stu676  |   80 | M       | null  | |   696 | stu696  |   80 | F       | null  | |   824 | stu824  |   80 | F       | null  | |   894 | stu894  |   80 | F       | null  | |  930 | stu930  |   80 | m       | null  | |  1000 | stu1000  |   80 | M       | null  |+------+---------+------+--------+--------------+mariadb [hidb]>  set @ @session. sql_log_bin=off; mariadb [(None)]> \. /tmp/mybinlog.sqlmariadb  [hidb]> select * from students where age=80;+------+---------+------+-------- +-------------+| id   | name    | age  |  gender | major  |+------+---------+------+--------+-------------+|   65  | stu65   |   80 | m      |  shepi | |    83 | stu83   |   80 | m      | shepi | |   104 | stu104  |   80 | F       | shepi | |   113 | stu113  |   80 | M       | shepi | |   192 | stu192  |   80 | F       | shepi | |   207 | stu207  |   80 | F       | shepi | |   222 | stu222  |   80 | F       | shepi | |   298 | stu298  |   80 | M       | shepi | |   409 | stu409  |   80 | f      | shepi | |   415 | stu415  |   80 | F       | shepi | |   448 | stu448  |   80 | F       | shepi | |   513 | stu513  |   80 | F       | shepi | |   545 | stu545  |   80 | M       | shepi | |   590 | stu590  |   80 | F       | shepi | |   676 | stu676  |   80 | M       | shepi | |   696 | stu696  |   80 | F       | shepi | |   824 | stu824  |   80 | f      |  shepi | |   894 | stu894  |   80 | F       | shepi | |   930 | stu930  |   80 | M       | shepi | |  1000 | stu1000  |   80 | M       | shepi |+------+---------+------+--------+-------------+mariadb [hidb]> set  @ @session. Sql_log_bin=on;

The experiment proves that the data has been restored, but remember to do a full-scale backup!!!!!!!!!!

Xtrabackup for MySQL backup and recovery