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

Xtrabackup detailed explanation

Xtrabackup is an online hot backup tool based on InnoDB developed by CTO Vadim of Percona company. It has the characteristics of open source, free, supporting online hot backup, fast backup recovery, small disk space, and supports a variety of backup forms in different situations. The official download address of xtrabackup is http://www.percona.com/software/percona-xtrabackup.

Xtrabackup contains two main tools, xtrabackup and innobackupex, which differ as follows:

(1) xtrabackup can only back up tables of innodb and xtradb engines, but cannot back up tables of myisam engines

(2) innobackupex is a Perl script that encapsulates xtrabackup, which supports backing up innodb and myisam at the same time, but a global read lock is needed when backing up myisam. Also, myisam does not support incremental backups.

1. Backup process

The innobackupex backup process is shown in the following figure:

(figure 1 innobackupex backup process, all figures in this article are obtained by google)

In figure 1, at the beginning of the backup, a background detection process is started to detect changes in mysql redo in real time. As soon as a new log write is found in redo, the log is recorded in the background log file xtrabackup_log. Then copy the data file of innodb and the system tablespace file ibdata1, after the copy is finished, perform flush tableswith read lock operation, copy .frm, MYI,MYD, and other files (the purpose of executing flush tableswith read lock is to prevent the data table from DDL operation, and get the location of binlog at this time) will finally issue unlock tables, set the table to be readable and writable, and finally stop xtrabackup_log.

two。 Full recovery

This phase starts the innodb instance embedded in xtrabackup, plays back the xtrabackup log xtrabackup _ log, applies committed transaction information changes to innodb data / tablespaces, and rolls back uncommitted transactions (a process similar to innodb instance recovery). The recovery process is shown in the following figure:

(figure 2 innobackupex recovery process)

3. Incremental backup

The "incremental" processing in the process of innobackupex incremental backup is mainly that it is still a full copy (full backup) for myisam and other storage engines relative to innodb.

The process of "incremental" backup is mainly by copying "pages" with changes in innodb (these changed data pages mean that the LSN of "pages" is greater than the LSN given in xtrabackup_checkpoints). Incremental backup is based on complete, the first additional data must be based on the previous complete, and then each additional is based on the last addition, and finally achieve a consistent increase. The process of incremental backup is as follows, which is similar to the complete process, except in step 2.

(figure 3 innobackupex incremental backup process)

4. Incremental backup recovery

Similar to full recovery, it requires two steps: one is the recovery of data files, as shown in figure 4, where the data source consists of three parts: full backup, incremental backup, and xtrabackup log. The second is the rollback of uncommitted transactions, as shown in figure 5:

(figure 4 innobackupex incremental backup recovery process 1)

(figure 5 innobackupex incremental backup recovery process 2)

Example of using 5.innobackupex

(1) installation using xtrabackup, the installation is relatively simple, we use binary compiled on the line, this tool does not need source code compilation, because there is no need for special customization.

[root@MySQL01 ~] # wget http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/Linux/x86_64/percona-xtrabackup-2.1.8-733-Linux-x86_64.tar.gz[root@MySQL-01 ~] # tar xf percona-xtrabackup-2.1.8- 733-Linux-x86_64.tar.gz-C / usr/local/ [root @ MySQL-01 ~] # mv / usr/local/percona-xtrabackup-2.1.8-Linux -x86 profile 64 / / usr/local/ xtrabackup [root @ MySQL-01 ~] # echo "export PATH=\ $PATH:/usr/local/xtrabackup/bin" > > / etc/ profile [root @ MySQL-01 ~] # source / etc/profile

(2) full backup

Create a backup user:

Mysql > create user 'backup'@'%' identified by' hello'

Query OK, 0 rows affected (0.01 sec) mysql > grant reload,lock tables,replication client,create tablespace,super on *. * to 'backup'@'%';Query OK, 0 rows affected (0 sec) for full backup data is stored under / data/backup/, and innobackupex automatically creates a folder, which is the current system timestamp mysql > select * from peng.t1 +-+-+ | id | name | +-+-+ | 1 | peng | | 2 | atlas | +-+-+ 2 rows in set (0.00 sec) the test data is the T1 table [root@MySQL-01] # xtrabackup: Creating suspend file'/ data/backup/2014-04-07cm 23-05-04 in the peng database. With pid '57608'xtrabackup: Transaction log of lsn (5324782783) to (5324782783) was copied.140407 23:06:14 innobackupex: All tables unlockedinnobackupex: Backup created in directory' / data/backup/2014-04-07 July 23-05-04'innobackupex: MySQL binlog position: filename 'mysqlbin.000014' Position 2983140407 23:06:14 innobackupex: Connection to database server closed140407 23:06:14 innobackupex: completed OK! The above process, mainly depends on whether the final prompt innobackupex completed ok, you can see that the backup is successful. Let's see what happens in the / data/backup directory! [root@MySQL-01 backup] # pwd/data/ backup [root @ MySQL-01 backup] # lltotal 4drwxr-xr-x 12 root root 4096 Apr 7 23:06 2014-04-07 Apr 23-05-04 [root@MySQL-01 backup] # cd 2014-04-07 cd 23-05-04 / [root@MySQL-01 2014-04-07 cd 23-05-04] # lltotal 845888RWLYURAR Apr-1 root root 261 Apr 7 23:05 backup-my.cnfdrwx-- -2 root root 4096 Apr 7 23:06 employeesdrwx- 2 root root 4096 Apr 7 23:06 host-rw-r- 1 root root 866123776 Apr 7 23:05 ibdata1drwx- 2 root root 4096 Apr 7 23:06 menageriedrwxr-xr-x 2 root root 4096 Apr 7 23:06 mysqldrwxr-xr-x 2 root root 4096 Apr 7 23:06 performance_schemadrwx- 2 root root 4096 Apr 7 23 : 06 sakiladrwx- 2 root root 4096 Apr 7 23:06 testdrwx- 2 root root 4096 Apr 7 23:06 world_innodbdrwxr-xr-x 2 root root 4096 Apr 7 23:06 world_myisam-rw-r--r-- 1 root root 13 Apr 7 23:06 xtrabackup_binary-rw-r--r-- 1 root root 24 Apr 7 23:06 xtrabackup_binlog_info-rw-r- 1 root Root 95 Apr 7 23:06 xtrabackup_checkpoints-rw-r- 1 root root 2560 Apr 7 23:06 xtrabackup_logfiledrwx- 2 root root 4096 Apr 7 23:06 peng can see the name of the corresponding database For example, peng, there is also a directory named after the timestamp. Let's take a look at the contents of the corresponding file. These are more important: [root@MySQL-01 2014-04-07, 23-05-04] # cat xtrabackup_checkpoints backup_type = full-backupedfrom_lsn = 0to_lsn = 5324782783last_lsn = 5324782783compact = 0 [root@MySQL-01 2014-04-07, 23-05-04] # cat xtrabackup_binlog_info mysql-bin.000014 2983

You can see that the relevant files record LSN, log offset, and you can also see that this is a full backup. I believe smart children's shoes can understand it at a glance. ^ _ ^

Delete the database and restore it completely (don't do this online)

Mysql > drop database peng;Query OK, 1 row affected (0.04 sec)

Restore full readiness

To restore the data file directory backed up to mysql, the process is to close the mysql database, rename or delete the original data file directory, then create a new data file directory, copy the backup data to the new data file directory, empower, modify permissions, and start the database.

[root@MySQL-01] # / etc/init.d/mysqld stopShutting down MySQL. [OK] [root@MySQL-01 ~] # mv / data/mysql / data/mysql_ bak [root @ MySQL-01 ~] # mkdir / data/ MySQL [root @ MySQL-01 ~] # innobackupex-- apply-log / data/backup/2014-04-07 October 23-05-04 / xtrabackup: starting shutdown with innodb_fast_shutdown = 1140407 23:22:36 InnoDB: Starting shutdown...140407 23:22:40 InnoDB: Shutdown completed; log sequence number 5324784140140407 23:22:40 innobackupex: completed OK! The corresponding directory above is the directory created by innobackupex full backup. [root@MySQL-01 ~] # innobackupex: Starting to copy InnoDB log filesinnobackupex: in'/ data/backup/2014-04-07 October 23-05-04'innobackupex: back to original InnoDB log directory'/ data/mysql'innobackupex: Copying'/ data/backup/2014-04-07 logfile23-05-04 to to'/ data/mysql/ib_logfile1'innobackupex: Copying'/ data/backup/2014-04-07 logfile23-05-04 Universe ibaths logfile0' to'/ data/mysql/ib _ logfile0'innobackupex: Finished copying back files.140407 23:27:38 innobackupex: completed OK! You can see that it has been successfully restored, modify the permissions of the data directory, start mysql, verify that the data is normal, and view the data in the T1 table under the peng library. [root@MySQL-01 ~] # chown-R mysql.mysql / data/ MySQL [root @ MySQL-01 ~] # / etc/init.d/mysqld startStarting MySQL. [OK] mysql > use pengReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from T1 + | id | name | +-+-+ | 1 | peng | | 2 | atlas | +-+-+ 2 rows in set (0.00 sec)

It is found that the data has been successfully restored.

(3) incremental backup

When performing an incremental backup, you should first perform a full backup, the first incremental backup is based on the full backup, the subsequent incremental backup is based on the last incremental backup, and so on.

Full backup on / data/backup/full, incremental backup on / data/backup/incremental

[root@MySQL-01 ~] # tree / data/backup/

/ data/backup/ ├── full └── incremental2 directories, 0 files nonsense, let's first make a full backup [root@MySQL-01] # innobackupex: Backup created in directory'/ data/backup/full/2014-04-07 July 23-37-20'innobackupex: MySQL binlog position: filename 'mysqlbin.000001', position 107140407 23:38:29 innobackupex: Connection to database server closed140407 23:38:29 innobackupex: completed OK! In order to test the effect, we insert the data mysql > select * from T1 into the T1 table | id | name | +-+-+ | 1 | peng | | 2 | atlas | +-+-+ 2 rows in set (0.00 sec) mysql > insert into T1 select 1 select sql';Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > select * from T1 +-+-+ | id | name | +-+-+ | 1 | peng | 2 | atlas | | 1 | love sql | +-+-+ 3 rows in set (0.00 sec) now make an incremental backup 1 [root@MySQL-01 ~] # innobackupex: Backup created in directory'/ data/backup/incremental/2014-04-07 / 23-42-46'innobackupex: MySQL binlog position: filename 'mysqlbin.000001' Position 301140407 23:43:25 innobackupex: Connection to database server closed140407 23:43:25 innobackupex: completed OK! Let's take a look at the size of the incremental backup and the contents of the file [root@MySQL-01 ~] # du-sh / data/backup/full/2014-04-07 backup 23-37-20 / 1.2G / data/backup/full/2014-04-07 backup 23-37-20 / [root @ MySQL-01 ~] # du-sh / data/backup/incremental/2014-04-07 backup 23-42-46 3.6m / data/backup/incremental/2014-04-07 _ 23-42-46 / see the incremental backup data is very small, right? It's just backing up the changed data. [root@MySQL-01 2014-04-07 / 23-42-46] # pwd/data/backup/incremental/2014-04-07 / 23-42-46 [root@MySQL-01 2014-04-07 / 23-42-46] # cat xtrabackup_checkpoints from_lsn = 5324784718to_lsn = 5324785066compact = 0 it is clearly stated that it is an incremental backup. The tool is very humane. Hehe, let's insert data into the T1 table again, and then create an incremental backup 2mysql > select * from T1. +-+-+ | id | name | +-+-+ | 1 | peng | 2 | atlas | 1 | love sql | +-+-+ 3 rows in set (0.00 sec) mysql > insert into T1 select 1 row affected dba';Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > select * from T1 +-+-+ | id | name | +-+-+ | 1 | peng | | 2 | atlas | | 1 | love sql | | 1 | mysql dba | +-+-+ 4 rows in set (0.00 sec) create incremental backup 2 (this time Based on the last incremental backup, oh) [root@MySQL-01 ~] # innobackupex: Backup created in directory'/ data/backup/incremental/2014-04-07 backup 23-51-15'innobackupex: MySQL binlog position: filename 'mysqlbin.000001' Position 496140407 23:51:55 innobackupex: Connection to database server closed140407 23:51:55 innobackupex: completed OK! [root@MySQL-01 ~] # ls-ltr / data/backup/full/total 4drwxr-xr-x 12 root root 4096 Apr 7 23:38 2014-04-07 * 23-37-20 [root@MySQL-01 ~] # ls-ltr / data/backup/incremental/total 8drwxr-xr-x 12 root root 4096 Apr 7 23:43 2014-04-07 * 23-42-46drwxr-xr-x 12 root Root 4096 Apr 7 23:51 2014-04-07 backup 23-51-15 (4) incremental backup recovery incremental backup recovery generally consists of three steps to restore a full backup (note that the-redo-only parameter must be added here This parameter means that only committed transaction data in the xtrabackup log will be applied, and uncommitted data will not be rolled back) [root@MySQL-01 ~] # xtrabackup: starting shutdown with innodb_fast_shutdown = 1140407 23:59:43 InnoDB: Starting shutdown...140407 23:59:43 InnoDB: Shutdown completed Log sequence number 5324784718140407 23:59:43 innobackupex: completed OK! Apply incremental backup 1 to the full backup [root@MySQL-01 ~] # innobackupex: Copying'/ data/backup/incremental/2014-04-07 backup 23-42-46 * .frm' to'/ data/backup/full/2014- 04-07 20/mysql/help_relation.frm'innobackupex 23-37-20/mysql/help_relation.frm'innobackupex: Copying'/ data/backup/incremental/2014-04-07 October 23-42-46 MyqlUnip help accounty.MYD'to'/ data/backup/full/2014-04-07 October 23-37-20/mysql/help_category.MYD'innobackupex: Copying'/ data/backup/incremental/2014-04-07 October 23-42-46 Universe mysqlUniverse ndbbinlog index.frm' to'/ data/backup/full/ 2014-04-07 July 23-37-20/mysql/ndb_binlog_index.frm'140408 00:02:07 innobackupex: completed OK! Apply incremental backup 2 to full backup (note that the-redo-only parameter needs to be removed when restoring the last incremental backup Roll back those data in the xtrabackup log that have not yet been submitted) [root@MySQL-01 ~] # innobackupex: Copying'/ data/backup/incremental/2014-04-07 accounts 23-51-15 mysql.frm'to'/ data/backup/full/2014-04-07 accounts 23-37-20/mysql/help_relation.frm'innobackupex: Copying'/ data/backup/incremental/2014-04-07 accounts 23-51-15 MYD'to'/ data / backup/full/2014-04-07 / 23-37-20/mysql/help_category.MYD'innobackupex: Copying'/ data/backup/incremental/2014-04-07 / 23-51-15 * index.frm'to'/ data/backup/full/2014-04-07 * * 23-37-20/mysql/ndb_binlog_index.frm'140408 00:04:33 innobackupex: completed OK! Perform an apply operation on all the combined full backups and roll back the uncommitted data: [root@MySQL-01 ~] # xtrabackup: starting shutdown with innodb_fast_shutdown = 1140408 0:06:32 InnoDB: Starting shutdown...140408 0:06:36 InnoDB: Shutdown completed; log sequence number 5324785676140408 00:06:36 innobackupex: completed OK! Copy the restored backup to the database catalog file, give weight, and then start the mysql database to check the correctness of the data [root@MySQL-01 ~] # / etc/init.d/mysqld stopShutting down MySQL. [OK] [root@MySQL-01 ~] # mv / data/mysql / data/mysql_ bak [root @ MySQL-01 ~] # mkdir / data/ MySQL [root @ MySQL-01 ~] # innobackupex: Starting to copy InnoDB log filesinnobackupex: in'/ data/backup/full/2014-04-07 logfile23-37-20'innobackupex: back to original InnoDB log directory'/ data/mysql'innobackupex: Copying'/ data/backup/full/2014-04-07 logfile23-37-20 '/ data/mysql/ib_logfile1'innobackupex: Copying' / data/backup/full/2014-04-07 October 23-37-20 Universe iblem logfile0' to'/ data/mysql/ib_logfile0'innobackupex: root@MySQL-01 00:12:42 innobackupex: completed OK! [root@MySQL-01 ~] # chown-R mysql.mysql / data/ MySQL [root @ MySQL-01 ~] # / etc/init.d/mysqld startStarting MySQL.... [OK]

Check to see if the data is correct

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 1 | peng |

| | 2 | atlas |

| | 1 | love sql |

| | 1 | mysql dba |

+-+ +

4 rows in set (0.00 sec)

(5) Clone slave

In our daily work, we sometimes need to add slave libraries online, for example, there are one master and one slave database online, but due to the needs of business, the reading of one slave library cannot meet the current needs, so we need to add slave libraries online. For security reasons, we usually need to clone slave online from the slave library.

When cloning slave, the common parameters-- slave-info and-- safe-slave-backup.

-- slave-info saves the binlog file name and offset location of master to the xtrabackup_slave_info file

-- safe-slave-backup pauses the SQL thread of slave until the backup starts when there are no open temporary tables. The SQL thread starts automatically after the backup ends, and the main purpose of this operation is to ensure a consistent replication state.

The following example describes how to build a new slave database online in the case of one master and one slave. The environment is as follows:

Master 192.168.0.10 # main library

Slave 192.168.0.20 # slave library

Newslave 192.168.0.100 # New slave library

In the above example, newslave is the slave library to be newly built. Back up the old slave library:

[root@MySQL-02 ~] # innobackupex: Backup created in directory'/ data/cloneslave'innobackupex: MySQL binlog position: filename 'mysql-bin.000022', position 107innobackupex: MySQL slave binlog position: master host' 192.168.0.10 master host, filename 'mysql-bin.000006', position 732140413 23:25:13 innobackupex: completed OK! If the / data/cloneslave directory here does not exist, it will report an error if it does. View the files generated under the directory: [root@MySQL-02 ~] # ll / data/cloneslave/total 26668 root root Apr 13 23:24 backup-my.cnf-rw-r--r-- 1 root root 27262976 Apr 13 23:24 ibdata1drwxr-xr-x 2 root root 4096 Apr 13 23:25 mysqldrwxr-xr-x 2 root root 4096 Apr 13 23:25 performance_schemadrwxr-xr-x 2 root root 4096 Apr 13 23: 25 sakiladrwxr-xr-x 2 root root 4096 Apr 13 23:25 world_innodb-rw-r--r-- 1 root root 13 Apr 13 23:25 xtrabackup_binary-rw-r--r-- 1 root root 23 Apr 13 23:25 xtrabackup_binlog_info-rw-r--r-- 1 root root 79 Apr 13 23:25 xtrabackup_checkpoints-rw-r--r-- 1 root root 2560 Apr 13 23:25 xtrabackup_logfile-rw- Xtrabackup_slave_infodrwxr-xr-x 2 root root 4096 Apr 13 23:25 peng view the contents of the xtrabackup_slave_info file This content is to build the slave library when you need change master to parameters: [root@MySQL-02 ~] # cat / data/cloneslave/xtrabackup_slave_info CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=732 restore on the old slave server, that is, 192.168.0.20 [root @ MySQL-02 ~] # xtrabackup: starting shutdown with innodb_fast_shutdown = 1140413 23:30:37 InnoDB: Starting shutdown...140413 23:30:37 InnoDB: Shutdown completed Log sequence number 12981048140413 23:30:37 innobackupex: completed OK! Copy the restored files to the new slave library newslave, that is, 192.168.0.100 [root @ MySQL-02 data] # rsync-avprP-e ssh / data/cloneslave/ 192.168.0.100:/data/mysql/ add the authorization to the new slave library newslave on the master library master: mysql > grant replication slave on *. * to 'repl'@'192.168.0.100' identified by' 123456 slave library query OK, 0 rows affected (0.00 sec) mysql > flush privileges Query OK, 0 rows affected (0.02 sec) copy the configuration file of the old slave library to the new slave library newslave, and modify the server-id parameters. After the modification, start the new slave library. [root@MySQL-02 data] # scp / etc/my.cnf 192.168.0.100:/etc/ root@192.168.0.100's password: my.cnf 100% 4881 4.8KB/s 00:00 [root@newslave mysql] # egrep 'log-slave | ^ server-id | skip_slave' / etc/my.cnf server-id = 3skip_slave_startlog-slave-updates=1 [root@newslave mysql] # chown-R mysql.mysql. [root@newslave mysql] # / etc/init.d/mysqld restartShutting down MySQL. [OK] Starting MySQL.. [OK] find the xtrabackup_slave_info file generated from the backup of the old slave library, extract the master_log_file and master_log_pos information, and then change master to on the new slave library: synchronize on the new slave library: mysql > CHANGE MASTER TO MASTER_HOST='192.168.0.10',MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=732 Query OK, 0 rows affected (0.09 sec) start iothread and SQL thread and observe whether replication is normal: mysql > start slave Query OK 0 rows affected (0.00 sec) mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.10 Master_ User: repl Master_Port: 3306 Connect_Retry: 2 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 1309 Relay_Log_File: MySQL-02-relay-bin.000002 Relay_Log_Pos: 830 Relay_Master_Log_File: mysql-bin.000006 Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: peng.% Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1309 Relay_Log_Space: 989 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11 row in set (0.00 sec)

Look at the main library and find that there are already two threads (Binlog Dump)

Mysql > show processlist\ G

* * 1. Row *

Id: 8 User: slave Host: 192.168.0.20:44251 db: NULL Command: Binlog Dump Time: 1088 State: Master has sent all binlog to slave Waiting for binlog to be updated Info: NULL * * 2. Row * * Id: 9 User: root Host: localhost db: pengCommand: Query Time: 0 State: NULL Info: show processlist * * 3. Row * * Id: 10 User: repl Host: 192.168.0.100 User 45844 db: NULL Command: Binlog Dump Time: 124 State: Master has sent all binlog to slave Waiting for binlog to be updated Info: NULL3 rows in set (0.00 sec)

Working normally, this is the end of online cloning slave.

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.