Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to back up Xtrabacup. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Chapter 1 introduction to XTRABACKUP1.1 xtrabackup

InnoDB has a commercial InnoDB Hotbackup that enables online hot backup of InnoDB engine tables. Xtrabackup, produced by percona, is an open source alternative to InnoDB Hotbackup, which can physically back up the tables of the InnoDB/XtraDB engine online. Mysqldump supports online backup, but it is only a logical backup, which is relatively inefficient. Xtrabackup is an open source MySQL backup tool, physical backup, efficiency is very good.

Xtrabackup has two main tools: xtrabackup and innobackupex, in which xtrabackup can only back up InnoDB and XtraDB data tables, while innobackupex encapsulates xtrabackup and can back up MyISAM data tables at the same time. Xtrabackup can not back up table structures, triggers and so on when making backups. IDB data files are intelligent. In addition, innobackupex can not fully support incremental backup, so it needs to be combined with xtrabackup to achieve full functionality.

1.2 introduction of xtrabackup features

Written in C language, can back up Innodb, XtraD, do not copy * .frm files

The supported engines are:

InnoDB 、 Xtradb:hotbackup

MyISAM: with read lock

It has the following characteristics:

§1) the backup process is fast and reliable

§(2) the backup process does not interrupt ongoing transactions

§(3) it can save disk space and traffic based on compression and other functions.

§(4) automatic backup verification

§(5) Fast reduction speed

1.3 download and installation of Xtrabackup

Xtrabackup tool is an open source hot backup tool developed by Percona, which can be downloaded to the latest version of the file on the official website.

Https://www.percona.com/downloads/ downloads various versions, or you can download it on the system using wget.

Method 1:

Wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

Install dependent packages

Yum installperl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL installperl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

Method 2:

Download the rpm package, install it directly using yum, and automatically install the dependent package, but you need to have the support of epel source, so download and configure the epe' source.

Wget-O / etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo

Yum install-y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

1.4 permissions required by xtrabackup backup user

RELOAD and LOCKTABLES permissions in order to execute FLUSHTABLES WITH READ LOCK

In order to get the binary log location, REPLICATION CLIENT

CREATE TABLESPACE permissions in order to import table, user table level recovery

SUPER permissions are backed up in slave to start and shut down slave threads

1.5 Xtrabackup backup

Both InnoDB and non-InnoDB files are backed up by copying files, but in different ways.

InnoDB is Xtrabackup with page granularity. When reading each page, Xtrabackup checks the checksum value to ensure that the data blocks are consistent.

Non-InnoDB is a cp or tar command. Innobackupex has already done flush (FTWRL) in the cp MyISAM file, and the files on disk are complete, so all the data files in the backup set are written completely.

1.5.1 backup proc

1.5.2 complete flowchart

1.5.3 incremental backup flowchart

1.6 Xtrabackup recovery 1.6.1 recovery flowchart

1.7 use of Xtrabackup

To standardize the operation, let's first build the path of full and incremental backup.

[root@db01 backups] # pwd

/ data/backups

[root@db01 backups] # ll

Total dosage 12

Drwxr-xr-x 4 root root 4096 September 4 17:28 full

Drwxr-xr-x 4 root root 4096 September 4 17:26 inc

Operation procedure:

Mkdir/data/backups/ {full,inc}-p

1.7.1 complete

* ROW schema is preferred for data consistency in production environment, because other schema data will be lost *

Command syntax format:

Innobackupex-user=User--password=PWD/data/backup/full-slave-info-safe-slave-backup--parallel=4--safe-slave-backup-timeout=7200-rsync

-- rsync this parameter is generally used when distributed database clusters

Complete steps:

1. Prepare a xtr_test database and insert the testbackup table in the xtr_test database. The field contains two fields: id,step.

Mysql-uroot-p789-S / data/3306/mysql.sockshow databases;create database xtr_test;use xtr_test;select database (); create table testbackup (id int not nullauto_increment primary key,step varchar (50)) engine=innodb;show tables;desc testbackup;show create table testbackup\ G

The result looks like:

Mysql > desc testbackup +-+ | Field | Type | Null | Key | Default | Extra | + -+-+ | id | int (11) | NO | PRI | NULL | auto_increment | | step | varchar (50) | YES | | NULL | | +-+- -+ 2 rows in set (0.02 sec) mysql > show create table testbackup\ gateway * 1. Row** Table:testbackupCreate Table: CREATE TABLE `testbackup` (`id`int (11) NOT NULL AUTO_INCREMENT `Step`varchar (50) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

2. Insert the field "firstfull backup" into the testbackup table

Insert into testbackup (step) values ('first fullbackup')

Operation result:

Mysql > select * from testbackup;+----+-+ | id | step | +-+-+ | 1 | firstfull backup | +-+-+ 1 row in set (0.02 sec)

3. Complete execution

At this point, there is data in the testbackup table in the xtr_test database we created, and there is nothing under the / data/backups/full file we created. The following execution is complete, and the operation command has been given above, so the multi-instance used in this operation is used-- the socket parameter.

Innobackupex-user=root-password=789/data/backups/full-slave-info-safe-slave-backup--parallel=4--safe-slave-backup-timeout=7200-socket=/data/3306/mysql.sock

The execution result must see "160905 20:01:38 completedOK!" The word OK indicates success. From the log printed out, we can also see that the backup process is compared with the complete flow chart given above.

After the correct output, a directory with time and sequence number will be generated in the directory specified by / data/backups/full. This directory is where all the files are stored, such as:

[root@db01] # ls-lrt / data/backups/full/ Total usage 4drwxr lrt-8 root root 4096 September 5 20Rose 012016-09-05pm 20-01-321.7.2 incremental backup

The premise of incremental backup is that there must be a complete backup, and only with it can the so-called incremental backup be carried out!

There are two ways of incremental backup, which are:

The first one: through complete documents

Innobackupex-user=root-password=789-incremental--incremental-lsn=1783249/data/backups/inc-slave-info--safe-slave-backup-parallel=4-safe-slave-backup-timeout=7200--socket=/data/3306/mysql.sock

The second method: through LSN

Innobackupex-user=root-password=789-incremental--incremental-lsn=1783249/data/backups/inc-slave-info--safe-slave-backup-parallel=4-safe-slave-backup-timeout=7200--socket=/data/3306/mysql.sock

1. Prepare the latest complete file, which has been done only once, so there is only one directory.

[root@db01] # ls-lrt / data/backups/full/ Total consumption 4drwxr lrt-8 root root 4096 September 5 20:01 2016-09-05mm 20-01-32

2. Insert the data into the testbackup table in the xtr_test database with the content of "fist increment backup"

Mysql > insert into testbackup (step) values ('first increment backup'); Query OK, 1 row affected (0.02 sec) mysql > select * from testbackup +-+-+ | id | step | +-+-+ | 1 | firstfull backup | | 2 | firstincrement backup | +-+-+ 2 rows in set (0.01sec)

3. Perform incremental backup

Here we have to make sure that the latest complete (emphasis)!

Innobackupex-- user=root-- password=789--incremental-- incremental-basedir=/data/backups/full/2016-09-05 January-32 / data/backups/inc-- slave-info--safe-slave-backup-- parallel=4-- safe-slave-backup-timeout=7200-- socket=/data/3306/mysql.sock

You must see "completed OK!" in the execution result here. The word "OK" means that if it is distributed, it can also be used-- rsync. We will find that a directory named with time and sequence number has been created under the created inc directory, which is the increment of Xtrabackup backup.

Execution result:

[root@db01] # ll / data/backups/inc Total consumption 4drwxrmuri-8 root root 4096 September 5 20:19 2016-09-05 ll 20-19-48

TIP:innodb defaults to the system tablespace used, we can configure parameters to make idb files into independent space innodb_file_per_table= 1 this is to modify idb to independent space, if the database is online, we can configure the above parameters in the configuration file my.cnf, and then use set globalinnodb_file_per_table= 1 to take effect temporarily.

Alter table backupstep engine=innodb; modifies the engine of the table, which is time-consuming and needs to be operated at a low point of business.

1.7.3 backup on the basis of incremental backup

Insert the content "secondincrement backup" again in the testbackup table in the created xtr_test database

Mysql > insert into testbackup (step) values ('secondincrement backup'); Query OK, 1 row affected (0.01 sec) mysql > select * from testbackup +-+-- +-+ | id | step | +-+-+ | 1 | firstfull backup | | 2 | first incrementbackup | | 3 | secondincrement backup | + -+ 3 rows in set (0.00 sec)

1. Determine the latest and complete documents

[root@db01 full] # pwd/data/backups/full [root@db01 full] # ls-lrt Total consumption 4drwxr root@db01 full-8 root root 4096 September 5 20:01 2016-09-05mm 20-01-32

2. Determine the LSN value of the last incremental backup

[root@db01 inc] # ls-lrt Total consumption 4drwxr root root-8 root root 4096 September 5 20:19 2016-09-05mm 20-19-48 [root@db01 inc] # cd 2016-09-05mm 20-19-48 / [root@db01 2016-09-05mm 20-19-48] # lsbackup-my.cnf krik performance_schema xtrabackup_checkpoints xtr_testibdata1.delta mysql test xtrabackup_infoibdata1.meta oldboy xtrabackup_binlog_ Info xtrabackup_ log [root @ db01 2016-09-05 last_lsn 20-19-48] # log = incrementalfrom_lsn = 1793716 # this value must be the same as the last complete last_lsn Otherwise, the data is incomplete to_lsn = 1794019last_lsn = 1794019compact = 0recover_binlog_info = 0

4 、

Execute incremental backup command

Innobackupex-user=root-password=789-incremental--incremental-lsn=1794019 / data/backups/inc-slave-info-safe-slave-backup--parallel=4-safe-slave-backup-timeout=7200--socket=/data/3306/mysql.sock

This value is obtained by looking at the xtrabackup_checkpoints of the most recent incremental backup

You must see "completed OK!" in the execution result here. The word OK means success.

The result of execution will generate a new file in the specified directory / data/backups/inc

[root@db01 2016-09-05mm 20-19-48] # cd. [root@db01 inc] # pwd/data/backups/inc [root@db01 inc] # ls-lrt Total consumption 8drwxr Muyashi-8 root root 4096 September 5 20:19 2016-09-05mm 20-19-48drwxr Mustang-8 root root 4096 Sep 5 21:18 2016-09-05mm 21-18-30

At this point, we have made a complete backup of the data, and we will create another statement below, but we will not do an incremental backup in order to test the binary log to recover the data. Next, we are going to play the big one. Delete the library and see if we can recover it later.

ROW schema is best used in a production environment for data consistency, because other schema data is lost.

Mysql > show variables like "log_bin"; +-+-+ | Variable_name | Value | +-+-+ | log_bin | ON | +-+-+ 1 row in set (0.00 sec) mysql > show variables like "% binlog_format%" +-+-+ | Variable_name | Value | +-+-+ | binlog_format | ROW | +-+-+ 1 row in set (0.00 sec) ot root 418 September 4 15:34backup-my.cnf Chapter 2 Database recovery

Only do redo here, not undo.

2.1 prepare before restoring data

The recovery of the database is in the order of complete-> 1 increment-> 2 increments.-> N increments to the location of the point of failure.

If it is complete: the latest complete-> binlog

2.1.1 create a new statement to prepare for binary log recovery mysql > insert into testbackup (step) values ('second increment backup'); Query OK, 1 row affected (0.01 sec) mysql > select * from testbackup +-+-- +-+ | id | step | +-+-+ | 1 | firstfull backup | | 2 | firstincrement backup | | 3 | secondincrement backup | + -+ 3 rows in set (0.00 sec) mysql > insert into testbackup (step) values ('last backup') Query OK, 1 row affected (0.00 sec) mysql > select * from testbackup +-+-+ | id | step | +-+-+ | 1 | firstfull backup | | 2 | firstincrement backup | | 3 | secondincrement backup | | 4 | last backup | + -+ 4 rows in set (0.00 sec) mysql > truncate table testbackup Query OK, 0 rows affected (0.02 sec) mysql > select * from testbackup;Empty set (0.00 sec) 2.2 restore full database for the first time

The database will be restarted once when the database is restored, so we can choose to stop the database (if possible). Here is the practice of stopping the database:

1. Stop the database

[root@db01 inc] # / data/3306/mysql stopStoping MySQL... [root@db01 inc] # netstat-lntup | grep 3306 [root@db01 inc] #

2. Restore the latest full, * be sure to back up before operation *

[root@db01 backups] # for i in `ls/data/backups/ `; do cp-r $I / data/backups/$i.bak;done [root@db01 backups] # lsfull full.bak inc inc.bak [root@db01 backups] # cp-r / data/3306/data/data/backups/3306_ data.bak [root @ db01 backups] # ls3306_data.bak full full.bak inc inc.bak

3. Import complete data

Innobackupex-user=root-password=789-apply-log--redo-only / data/backups/full/2016-09-05 20-01-32--socket=/data/3306/mysql.sock

To confirm that you see the word OK, it indicates that the import is successful.

2.3 restore the first incremental backup after the first full backup

1. Determine the file directory for the first incremental backup

[root@db01 inc] # ls-lrt Total usage 8drwxr root root-8 root root 4096 September 5 20:19 2016-09-05 September 20-19-48drwxr Mustang-8 root root 4096 September 5 21:18 2016-09-05 September 21-18-30

From the chronological order, we can see that the first time here is 2016-09-05 20-19-48. Some names can be customized in the work.

2. Perform incremental recovery

Innobackupex-- user=root-- password=789-- apply-log--redo-only / data/backups/full/2016-09-05 20-01-32-- incremental-dir=/data/backups/inc/2016-09-05 20-19-48--socket=/data/3306/mysql.sock

Seeing the word OK indicates success

2.4 restore the second incremental backup innobackupex-- user=root-- password=789-- apply-log--redo-only / data/backups/full/2016-09-05 backup 20-01-32-- incremental-dir=/data/backups/inc/2016-09-05 backup 21-18-30--socket=/data/3306/mysql.sock after the first full backup

Now that Xtrabackup has done the redo operation last night, let's write it to the data file. This step is to put the data in the database.

Data is written to the data file:

Innobackupex-- user=root-- password=789--apply-log / data/backups/full/2016-09-05 20-01-32-- socket=/data/3306/mysql.sock

The word OK also indicates that the execution is successful.

2.5 restore data to the data of instance 3306

Xtrabackup recovery is to add all the increments to the full body, that is, the full recovery. It is the database data, and we have replaced the data data of the 3306 database with this complete directory.

[root@db01 full] # rm-fr / data/3306/data/ [root@db01 full] # mv 2016-09-05mm 20-01-32//data/3306/data [root@db01 full] # ll / data/3306/data Total dosage 163884Murrwashi r-1 root root 418 September 5 20:01 backup-my.cnf-rw-r- 1 root root 134217728 September 5 22:12 ibdata1-rw-r- 1 Root root 4194304 September 5 22:12 ib_logfile0-rw-r- 1 root root 4194304 September 5 22:12 ib_logfile1-rw-r- 1 root root 4194304 September 5 22:12 ib_logfile2-rw-r- 1 root root 12582912 September 5 22:12 ibtmp1drwxr-x--- 2 root root 4096 September 5 22:10 krikdrwxr-x--- 2 root root 4096 September 5 22:10 Mysqldrwxr-x--- 2 root root 4096 September 5 22:10 oldboydrwxr-x--- 2 root root 4096 September 5 22:10 performance_schemadrwxr-x--- 2 root root 4096 September 5 22:10 test-rw-r- 1 root root 22 September 5 22:10 xtrabackup_binlog_info-rw-r--r-- 1 root root 33 September 5 22:12 xtrabackup_binlog_pos_innodb-rw -r-1 root root 113 September 5 22:12 xtrabackup_checkpoints-rw-r- 1 root root 630 September 5 22:10 xtrabackup_info-rw-r- 1 root root 8388608 September 5 22:04 xtrabackup_logfiledrwxr-x--- 2 root root 4096 September 5 22:10 xtr_ test [root @ db01 full] # chown-R mysql.mysql/data/3306/data/ [root@db01 full ] # ll / data/3306/data Total usage 163884 mysql mysql r-1 mysql mysql 418 September 5 20:01 backup-my.cnf-rw-r- 1 mysql mysql 134217728 September 5 22:12 ibdata1-rw-r- 1 mysql mysql 4194304 September 5 22:12 ib_logfile0-rw-r- 1 mysql mysql 4194304 September 5 22:12 ib_logfile1-rw-r- 1 Mysql mysql 4194304 September 5 22:12 ib_logfile2-rw-r- 1 mysql mysql 12582912 September 5 22:12 ibtmp1drwxr-x--- 2 mysql mysql 4096 September 5 22:10 krikdrwxr-x--- 2 mysql mysql 4096 September 5 22:10 mysqldrwxr-x--- 2 mysql mysql 4096 September 5 22:10 oldboydrwxr-x--- 2 mysql mysql 4096 September 5 22:10 performance_schemadrwxr-x--- 2 mysql Mysql 4096 September 5 22:10 test-rw-r- 1 mysql mysql 22 September 5 22:10 xtrabackup_binlog_info-rw-r--r-- 1 mysql mysql 33 September 5 22:12 xtrabackup_binlog_pos_innodb-rw-r- 1 mysql mysql 113 September 5 22:12 xtrabackup_checkpoints-rw-r- 1 mysql mysql 630 September 5 22:10 xtrabackup _ info-rw-r- 1 mysql mysql 8388608 September 5 22:04 xtrabackup_logfiledrwxr-x--- 2 mysql mysql 4096 September 5 22:10 xtr_test

Restart the database test

[root@db01 full] # cd / data/3306 [root@db01 3306] #. / mysql startStarting MySQL... [root@db01 3306] # netstat-lntup | grep 3306tcp 0 0 0.0 0. 0. 0. 0. 0. 0. 0. 0. 0. Select * LISTEN 56714/mysqldmysql > select * from testbackup +-+-- +-+ | id | step | +-+-+ | 1 | firstfull backup | | 2 | firstincrement backup | | 3 | secondincrement backup | + -+ 3 rows in set (0.00 sec) Test increment returns to normal But the last operation we did didn't show up. Let's use binary binglog to restore 2.6binlog log recovery data.

Using binlog to recover data, we need to determine a "scope" here we need to determine the range from the second incremental backup to the execution of the sql statement to delete the table; look for the scope, we can use the time range, location point pos to get this range.

2.6.1 find the location point of the second increment [root@db01 2016-09-05 / 21-18-30] # catxtrabackup_binlog_infomysql-bin.000010 1201

Here we know the location point 1201 after the end of the second increment, and we will use this location as the starting and ending point.

2.6.2 find the location point when deleting a table statement

Due to the lack of test binlog log files, it is easy to find here. In production, it is necessary to intercept a period of time according to the time point, and then find it.

[root@db01 3306] # mysqlbinlog--base64-output=decode-rows mysql-bin.000010# at 1376 "160905 21:46:41 server id 1 end_log_pos 1403 Xid = 164COMMITA Xid = 164COMMIT balance [end_log_pos 3306] # at 1403160905 21:46:59 server id 1 end_log_pos 1495 TIMESTAMPOS 1473083219" Truncate table testbackupUniverse "at 1495160905 21:52:16 server id 1 end_log_pos 1514 StopDELIMITER # End of log fileROLLBACK / * added by mysqlbinlog * /; / *! 50003 SET completion SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

By finding the statement and finding the location point, we can export a file and search for the statement through vim editing

2.6.3 binlog recovery data

Recover the data through the start and end points found above

Mysqlbinlog-base64-output=decode-rows-v--start-position=1201-stop-position=1403 mysql-bin.000010

To reconfirm.

Restore:

[root@db01 3306] # mysqlbinlog-- start-position=1201--stop-position=1403 mysql-bin.000010 | mysql- uroot-p789-S/data/3306/mysql.sock

View in the data:

Mysql > select * from testbackup +-+-+ | id | step | +-+-+ | 1 | firstfull backup | | 2 | firstincrement backup | | 3 | secondincrement backup | | 4 | last backup | + -+ 4 rows in set (0.01sec) Appendix-detailed explanation of Xtrabackup parameters

The common parameter options for Xtrabackup are as follows:

-- slave-info:

It records the pos and name of the binary log of the master server. The recorded information will be recorded on xtrabackup_slave_info. You can use this parameter when backing up the slave library, plus an additional xtrabackup_slave_info file will be generated in the-- slave-info backup directory, where the master log file and offset will be saved, and the file content is similar to: CHANGE MASTER TOMASTER_LOG_FILE='', MASTER_LOG_POS=0.

Scenarios where this parameter is applicable: suppose there are master library An and slave library B now. Now you want to add another standby library C, and let slave library C use master library An as master. Because the main library An is the production library, the pressure is generally high, so we back up a database on standby library B, then take the backup to the C server and import it to the C library, and then execute the change master command on the C server: where master_host is the ip of A, and master_log_file and master_log_pos are the values in this xtrabackup_slave_info

Case study: http://blog.chinaunix.net/uid-26446098-id-3395111.html helps to understand

-- safe-salve-backup:

It stops the slave SQL process and reopens the slave SQL process after the backup

-- force-tar-- stream=tar / tmp

These commands are used to compress backups into tar files. Take a look at the official documents.

-- defaults-file=#

The path to the default configuration file, if not, xtrabackup will look for the configuration file / etc/my.cnf, / etc/mysql/my.cnf, / usr/local/etc/my.cnf, ~ / .my.cnf from the following location, and read the [mysqld] and [xtrabackup] configuration segments in the configuration file. In [mysqld], you only need to specify datadir, innodb_data_home_dir, innodb_data_file_path, innodb_log_group_home_dir, innodb_log_files_in_group and innodb_log_file_size6 parameters to make xtrabackup work properly.

-- defaults-extra-file=#

If this parameter is used, the configuration file specified here will be read again after the global configuration file has been read

-- target-dir=name

The directory path where the backup files are stored

-- backup

Implement backup to target-dir

-- prepare

Implement preparation before recovery of backup files (generate InnoDB log file)

-- print-param

Print the parameters required for backup or restore

-- use-memory=#

This parameter is used in prepare to control the amount of memory used by innodb instances in prepare.

-- suspend-at-end

Generate a xtrabackup_suspended file in the target-dir directory, suspend the xtrabackup process, and keep synchronizing changes in the data file to the backup file until the user manually deletes the xtrabackup_suspended file

-- throttle=#

The number of IO per second to limit the number of backup operations used to minimize the impact of backup on normal database business.

-- log-stream

This parameter is used when backup, outputs the contents of xtrabackup_logfile to standard output, and automatically uses the suspend-at-end parameter, which is used by the stream mode of the innobackupex script.

-- incremental-lsn=name

For incremental backups, only copy the ibd pages whose LSN is newer than the value specified by this parameter. Which LSN did the previous backup go to? you can see the xtrabackup_checkpoints file of the previous backup set.

-- incremental-basedir=name

This parameter is used in backup to back up a newer idb pages than the backup set at the location specified by this parameter.

-- incremental-dir=name

This parameter is used in prepare to specify the storage path of .delta files and log files generated during prepare.

-- tables=name

Used when backing up data files of type file-per-table, using regular expressions to specify the innodb tables to be backed up

-- datadir=name

The data file directory of the MySQL database

This is the end of the article on "how to back up Xtrabacup". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.