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

Preface

Xtrabackup provides two command-line tools:

Xtrabackup: dedicated to backing up data from InnoDB and XtraDB engines; innobackupex: is a perl script that invokes the xtrabackup command during execution, which enables you to back up both InnoDB and Myisam engine objects.

Xtrabackup is a MySQL database backup tool provided by percona, which is fast and reliable; the backup process will not interrupt ongoing transactions; it can save disk space and traffic based on compression and other functions; automatically achieve backup verification; restore speed is fast.

If you need to install xtrabackup, you can go to its official website, which provides a variety of installation methods.

Blog outline:

1. Install xtrabackup and its plug-ins 2, xtrabackup full backup + binlog incremental backup 3, xtrabackup full backup + xtrabackup incremental backup 4, innobackupex full library backup + innobackupex incremental backup

Note: two, three and four are three different backup schemes. Just choose the appropriate one in the production environment.

First, install xtrabackup and its plug-ins 1. Yum install xtrabackup

Refer to the official documentation, and various versions of the help instructions are provided on the official documentation.

[root@mysql ~] # yum-y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm [root@mysql ~] # yum-y install percona-xtrabackup-24

There are two main tools in xtrabackup:

Xtrabackup: a tool for hot backup of data in innodb,xtradb tables. It supports online hot backup. You can back up innodb tables without locking, but this tool cannot manipulate myisam engine tables. Innobackupex: it is a perl script that encapsulates xtrabackup and can handle both innodb and myisam. However, you need to add a read lock when dealing with myisam. Because you need to add a read lock when operating myisam, it will block the write operation of online services, but Innodb does not have such a restriction. 2. Download and install percona-toolkit tool [root@mysql test] # yum-y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5 [root@mysql test] # wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm[root@mysql test] # rpm-ivh percona-toolkit-2.2.19-1.noarch.rpm II. Xtrabackup full backup + binlog incremental backup 1, create backup directory [root@mysql test] # mkdir-p / opt/mysqlbackup/ {full Inc} # full: full storage directory # inc: incremental backup storage directory 2. Create backup user [root@mysql test] # mysql-uroot-p123.commysql > create user bakuser@'localhost' identified by '123.com' Mysql > revoke all privileges,grant option from 'bakuser'@'localhost';mysql > grant reload,lock tables,replication client,process on *. * to bakuser@'localhost';mysql > flush privileges;3, full backup [root@mysql test] # innobackupex-- user=bakuser-- password=123.com / opt/mysqlbackup/full/# the following message will appear when the backup is complete. # ignore some information 200116 13:09:21 completed OK!

The relevant explanations for the above implementation are as follows:

-- user: specify the user name to connect to the database;-- password: specify the password to connect to the database;-- defaults-file: specify the configuration file my.cnf,innobackupex of the database to obtain information such as datadir. If it is not specified, the file my.cnf will be searched by default in the same order as when mysql starts. -- database: specify the database to be backed up. The database specified here is only valid for myisam tables and is complete for innodb data (innodb data in all databases are backed up, not only the specified database, but also during recovery); / opt/mysqlbackup/full: the location where the backup files are stored. 4. View the files after backup

The contents of each file are as follows:

Backup-my.cnf: configuration option information used in backup commands; information related to ib_buffer_pool:buffer buffers Xtrabackup_checkpoints: this file is important to save the backup type (such as full or incremental), backup status (such as whether it is already in perpared state (state required before backup recovery), and LSN (log serial number) range information. Each innodb page (usually 16k size) contains a log serial number, which is the same system version number of the entire database system. The LSN associated with each page can show how the page has changed recently.

Before performing an incremental backup, you need to check the binlog log location (position) when you see the full backup, as follows:

[root@mysql 2020-01-17 million 10-39-52] # pwd/opt/mysqlbackup/full/2020-01-17 percent 10-39-52 [root@mysql 2020-01-17 percent 10-39-52] # cat xtrabackup_binlog_info bin_log.000001 155gets a full backup to the location of 154in the bin_log.000001 binary log

Before the incremental backup, add, delete and modify the database by yourself in order to generate a new binary log.

[root@mysql ~] # mysqlbinlog-- start-position=154 / usr/local/mysql/data/bin_log.000001 > / opt/mysqlbackup/inc/ `date +% F`.sql6, simulate data loss in order to restore data [root@mysql ~] # rm-rf / usr/local/mysql/data/* # directly delete all local data

Prepare a full backup. In general, after the backup is complete, the data cannot be used for restore operations, because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time, and the main function of "preparation" is to make the data file consistent by rolling back the uncommitted transaction and synchronizing the committed transaction to the data file. At the end of the preparation process, the Innodb table data has been rolled forward to the end of the entire backup, rather than to the point where the xtrabackup just started.

The-- apply-log option can be used to achieve the above functions, with the following command:

[root@mysql] # innobackupex-- apply-log / opt/mysqlbackup/full/2020-01-17clients 10-39-52 / [root@mysql 2020-01-17clients 10-39-52] # cat xtrabackup_checkpoints backup_type = full-prepared # when the preparation is completed, the backup type in the contents of this file in the backup directory will be: full-preparedfrom_lsn = 0to_lsn = 2841752last_lsn = 2841761compact = 0recover_binlog_info = 0flushed_lsn = 2841761

Note: / opt/mysqlbackup/full/2020-01-17upload 10-39-52 / is the name of the directory where the backup files are located. If executed correctly, the last few lines output the information as follows:

In the process of implementing "preparation", innobackupex can also use the "--user-memory" option to specify the amount of memory it can use. The default is 100m. If there is enough memory, you can allocate more memory to the prepare process to improve its completion speed.

"after the preparations are complete, you can restore using the following command:"

[root@mysql] # innobackupex-- copy-back / opt/mysqlbackup/full/2020-01-17 million 10-39-52 /

Confirm that the data has been restored:

The fully backed up data has been restored, but you need to pay attention to the permission problem. The restored data owner and group are all current user root, so you also need to change the owner and group, as follows:

[root@mysql] # cd / usr/local/mysql/data/ [root@mysql data] # chown-R mysql.mysql. [root@mysql data] # ll # confirm current group and owner total consumption 122924drwxrmuri-2 mysql mysql 90 January 17 11:15 data1-rw-r- 1 mysql mysql 324 January 17 11:15 ib_buffer_pool-rw-r- 1 mysql mysql 12582912 January 17 11:15 ibdata1-rw-r- 1 mysql mysql 50331648 January 17 11:15 ib_logfile0-rw-r- 1 mysql mysql 50331648 January 17 11:15 ib_logfile1-rw-r- 1 mysql mysql 12582912 January 17 11:15 ibtmp1drwxr-x--- 2 mysql mysql 4096 January 17 11:15 mysqldrwxr-x--- 2 mysql mysql 8192 January 17 11:15 performance_schemadrwxr-x--- 2 mysql mysql 8192 January 17 11:15 sys-rw-r- 1 mysql mysql 20 January 17 11:15 xtrabackup_binlog_pos_innodb-rw-r- 1 mysql mysql 481 January 17 11:15 xtrabackup_info-rw-r- 1 mysql mysql 1 January 17 11:15 xtrabackup_master_key_id [root@mysql ~] # systemctl restart mysqld # after data recovery The server needs to be restarted, otherwise the data will not be unified. 7. View the recovered data in the database [root@mysql ~] # mysql-uroot-p123.com-e "select * from data1.t1 "+-+-+ | id | name | +-+-+ | 1 | tom1 | | 2 | tom2 | | 3 | tom3 | +-+-+ 8. Restore incremental backup.

In order to prevent a large number of binary logs from being generated during restore, it is best to temporarily close binary logs during restore, as follows:

[root@mysql ~] # mysql-uroot-p123.com-e'set sql_log_bin=0;' # temporarily shuts down binary logs [root@mysql ~] # mysql-uroot-p123.com

< /opt/mysqlbackup/inc/2020-01-17.sql#恢复二进制日志[root@mysql ~]# mysql -uroot -p123.com -e 'set sql_log_bin=1;' #开启二进制日志[root@mysql ~]# mysql -uroot -p123.com -e "select * from data1.t1;"#确认数据恢复正确+------+------+| id | name |+------+------+| 1 | tom1 || 2 | tom2 || 3 | tom3 || 4 | tom4 || 5 | tom5 |+------+------+--+i三、xtrabackup完全备份+xtrabackup增量备份 在第一个备份方案中，增量备份使用的是备份二进制日志，其实xtrabackup还支持进行增量备份，xtrabackup的备份原理如下： 在innodb内部会维护一个redo日志我呢见，也可以叫做事务日志文件（transaction，事务日志），事务日志会存储每个innodb表数据的记录修改，当innodb启动时，innodb会检查数据文件和事务日志，并能执行两个步骤：它应用已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件，复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改，xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。这就是xtrabackup的备份过程。 所以每个innodb的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面LSN就会自动增长。这就是innodb表可以进行增量备份的基础。xtrabackup基于innodb的crash-recovery功能，它会复制innodb的data file ，由于不锁表，复制出来的数据是不一致的，在恢复的时候使用crash-recovery，使得数据恢复一致。 1、创建一个测试库，并准备一张表插入几行数据mysql>

Create database test;mysql > use test;mysql > create table xx (id int,name varchar (20)); mysql > insert into xx values (1); mysql > insert into xx values (2); mysql > select * from xx +-+-+ | id | name | +-+-+ | 1 | tom1 | | 2 | tom2 | +-+-+ 2. Xtrabackup for full backup [root@mysql ~] # xtrabackup-- defaults-file=/etc/my.cnf-- user=bakuser-- password= "123.com"-- port=3306-- backup-- target-dir=/opt/mysqlbackup/full/full_incre_$ (date'+% Flying% T')

The above instructions are interpreted as follows:

-- defaults-file: specify the configuration file of the database. If this parameter is used, it must be used as the first parameter;-- user: specify the user name to connect to the database;-- password: specify the password to connect to the database;-- port: specify the port number to connect to the database;-- backup: implement backup to target-dir;--target-dir=name: the directory path where the backup files are stored. Innobackupex needs to obtain datadir and other information from it;-- database: specify the database to be backed up. The database specified here is only valid for the table structure of myisam and innodb tables, and is a full backup for innodb databases (the same is true during recovery). 3. View the full backup file

4. Perform the first incremental backup # insert the new data [root@mysql full] # mysql-uroot-p123.com-e "insert into test.xx values (3) "# then perform an incremental backup [root@mysql full] # xtrabackup-- defaults-file=/etc/my.cnf-- user=bakuser-- password=" 123.com "--port=3306-- backup-- target-dir=/opt/mysqlbackup/inc/incre_$ (date'+% Flying% T')-- incremental-basedir=/opt/mysqlbackup/full/full_incre_2020-01-17014\: 34\: 39 /

In the above directive,-incremental-basedir specifies the location of the last full backup or incremental backup files (that is, if it is the first incremental backup, it points to the directory where the full backup is located, and when you perform an incremental backup again after performing an incremental backup, its-- incremental-basedir should point to the directory where the last incremental backup was located).

View incremental backup files:

[root@mysql full] # ll / opt/mysqlbackup/inc/ Total usage 0drwxrmuri-7 root root 274 January 17 15:12 incre_2020-01-17 January 15 opt/mysqlbackup/inc/ 1210 Note: the incremental backup here is only for innodb, for myisam, it is still a full backup. "# make a second incremental backup [root@mysql full] # xtrabackup-- defaults-file=/etc/my.cnf-- user=bakuser-- password=" 123.com "--port=3306-- backup-- target-dir=/opt/mysqlbackup/inc/incre_$ (date'+% Flying% T')-- incremental-basedir=/opt/mysqlbackup/inc/incre_2020-01-17015\: 12\: 00 /

Note: the second incremental backup-incremental-basedir should point to the location of the last incremental backup file.

6. Restore full backup and incremental backup

The approximate process for recovering data is as follows:

If the backup file is a tar package, the unpacking command is: tar-izxf xxx.tar, where the-I parameter must be used to ignore the 0 bytes in the archive.

First prepare to restore the full backup:

[root@mysql] # xtrabackup-- defaults-file=/etc/my.cnf-- prepare-- user=bakuser-- password= "123.com"-- apply-log-only-- target-dir=/opt/mysqlbackup/full/full_incre_2020-01-1711 14\: 34\: 39 /

"when you are ready to restore to the first incremental backup, as follows:"

[root@mysql] # xtrabackup-- defaults-file=/etc/my.cnf-- prepare-- user=bakuser-- password='123.com'-- apply-log-only-- target-dir=/opt/mysqlbackup/full/full_incre_2020-01-1711 14\: 34\: 39 /-- incremental-dir=/opt/mysqlbackup/inc/incre_2020-01-17 15\: 12\: 00 /

Prepare to restore the second incremental backup, as follows:

[root@mysql] # xtrabackup-- defaults-file=/etc/my.cnf-- prepare-- user=bakuser-- password='123.com'-- target-dir=/opt/mysqlbackup/full/full_incre_2020-01-1711 14\: 34\: 39 /-- incremental-dir=/opt/mysqlbackup/inc/incre_2020-01-17 15\: 20\: 32 /

Then stop the MySQL database:

[root@mysql ~] # systemctl stop mysqld

Start the rsync data file:

[root@mysql ~] # cd / opt/mysqlbackup/full/full_incre_2020-01-17mm 14\: 34\: 39 / [root@mysql full_incre_2020-01-17mm 14 rsync-rvt-- exclude 'xtrabackup_checkpoints'-- exclude' xtrabackup_logfile'. / / usr/local/mysql/data/

When the data is restored to the data directory of mysql, you also need to make sure that the owners and groups of all data files are correct users. For example, I need to change it to mysql, as follows:

Confirm that the data has been restored:

[root@mysql data] # mysql-uroot-p123.com-e "select * from test.xx "+-+-+ | id | name | +-+-+ | 1 | tom1 | | 2 | tom2 | 3 | tom3 | 4 | tom4 | +-+-+ 4, innobackupex full library backup + innobackupex incremental backup 1, full library backup # prepare test data [root@mysql data] # mysql-uroot-p123.com-e" select * from test.xx "+-+-+ | id | name | +-+-+ | 1 | tom1 | 2 | tom2 | 3 | tom3 | +-+-+ # start full backup [root@mysql ~] # innobackupex-- defaults-file=/etc/my.cnf-- user=bakuser-- password='123.com' / opt/mysqlbackup/full/full_incre_$ (date'+% F) _% H% M% S')-- no-timestamp

Note:-- no-timestamp option to prevent the command from automatically creating a directory named after time, and then you can customize the directory

2. Make an incremental backup # insert test data [root@mysql ~] # mysql-uroot-p123.com-e "insert into xx values (4) (5) (5) "test# for incremental backup [root@mysql ~] # innobackupex-- incremental / opt/mysqlbackup/inc/incre_$ (date +% Y%m%d_%H%M%S)-- incremental-basedir=/opt/mysqlbackup/full/full_incre_2020-01-17upload 165254 /-- user=bakuser-- password='123.com'-- no-timestamp#-- incremental-basedir: specify the directory of the last full backup or incremental backup # because this is the first incremental backup So you need to specify the directory of the last full backup [root@mysql ~] # ll / opt/mysqlbackup/inc/ # to view the total amount of incremental backup files. 0drwxrkashi-7 root root 274 January 17 17:04 incre_20200117_1704243, do a second incremental backup # insert new data [root@mysql ~] # mysql-uroot-p123.com-e "insert into test.xx values" # make a second incremental backup based on the directory files of the first incremental backup [root@mysql ~] # innobackupex-- incremental / opt/mysqlbackup/inc/incre_$ (date +% Y%m%d_%H%M%S)-- incremental-basedir=/opt/mysqlbackup/inc/incre_20200117_170424/-- user=bakuser-- password='123.com'-- no-timestamp4, View incremental backup files [root@mysql ~] # ll / opt/mysqlbackup/inc/ total usage 0drwxr root root-7 root root 274 January 17 17:04 incre_20200117_170424drwxr-x--- 7 root root 274 January 17 17:10 incre_20200117_1710355, delete data files Prepare to restore [root@mysql ~] # rm-rf / usr/local/mysql/data/*# restore full backup directory file [root@mysql ~] # innobackupex-- apply-log-- redo-only / opt/mysqlbackup/full/full_incre_2020-01-170165254 Universe # merge the first incremental backup into the full directory [root@mysql ~] # innobackupex-- apply-log-- redo-only / opt/mysqlbackup/full/full_incre_ 2020-01-170165254 /-- incremental-dir=/opt/mysqlbackup/inc/incre_20200117_170424/# merges the second incremental backup into the full directory [root@mysql ~] # innobackupex-- apply-log-- redo-only / opt/mysqlbackup/full/full_incre_2020-01-17upload 165254 /-- incremental-dir=/opt/mysqlbackup/inc/incre_20200117_171035/6, Restore database [root@mysql ~] # systemctl stop mysqld # stop database # restore [root@mysql ~] # innobackupex-- defaults-file=/etc/my.cnf-- user=bakuser-- password='123.com'-- copy-back / opt/mysqlbackup/full/full_incre_2020-01-17upload 165254 Universe 7, modified and restored file owners and subordinate groups [root@mysql ~] # cd / usr/local/mysql/data/ [root@mysql data] # chown-R mysql.mysql. 8, Start the MySQL database Confirm that the data has been restored [root@mysql data] # systemctl start mysqld [root@mysql data] # mysql-uroot-p123.com-e "select * from test.xx "+-+-+ | id | name | +-+-+ | 1 | tom1 | | 2 | tom2 | 3 | tom3 | 4 | tom4 | | 5 | tom5 | | 6 | tom6 | +-+

The data was recovered successfully.

5. Add-- Xtrabackup's "streaming" and "backup compression" functions

Xtrabackup supports the "stream" function for backup data files, that is, the backup data can be transferred to tar programs for archiving through STDOUT, instead of being saved directly to a backup directory by default. To use this feature, you only need to use the-- stream option. As follows:

# make a full backup [root@mysql opt] # innobackupex-- user=bakuser-- password= "123.com"-- stream=tar / opt/mysqlbackup/full/ | gzip > / opt/mysqlbackup/full/full_ `date +% Fathers% H% M% S`.tar.gz # the backup files generated by the above command are as follows: [root@mysql full] # ll/ opt/mysqlbackup/full/ Total usage 640kuwmurf-1 root root 652578 January 17 17:45 full_2020-01-17_174513.tar.gz

-this is the end of this article. Thank you for reading-

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.