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

The previous article briefly introduced the installation of percona-xtrabackup, percona-xtrabackup installation (1), this article mainly introduces the work of innobackupex and the implementation principle and specific use and so on.

Xtrabackup contains two tools: xtrabackup and innobackupex.

1 xtrabackup can only back up tables of innodb and xtradb engines, but not myisam engines

2 innobackupex is a Perl script that encapsulates xtrabackup, which supports backing up tables of both innodb and myisam engines, but requires a global read lock when backing up tables of myisam engines. Also, incremental backups are not supported for myisam engine tables.

Innobackupex is a script written in Perl that wraps the xtrabackup and performs the tasks where the performance and efficiency of C program isn't needed. In this way, it provides a convenient and integrated approach to backing up in many common scenarios.

1.Making a Backup If no mode is specified, innobackupex will assume the backup mode.

2.By default, it starts xtrabackup with the-- suspend-at-end option, and lets it copy the InnoDB data files. When xtrabackup finishes that, innobackupex sees it create the xtrabackup_suspended_2 file and executes FLUSH TABLES WITH READ LOCK. Then it begins copying the rest of the files.

3.innobackupex will then check MySQL variables to determine which features are supported by server. Special interest are backup locks, changed page bitmaps, GTID mode, etc. If everything goes well, the binary is started as a child process.

4.innobackupex will wait for slaves in a replication setup if the option-safe-slave-backup is set and will flush all tables with READ LOCK, preventing all MyISAM tables from writing (unless option-no-lock is specified).

Note: Locking is done only for MyISAM and other non-InnoDB tables, and only after Percona XtraBackup is finished backing up all InnoDB/XtraDB data and logs.

5.Once this is done, the backup of the files will begin. It will backup .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, .par, and .opt files.

6.When all the files are backed up, it resumes ibbackup and wait until it finishes copying the transactions done while the backup was done. Then, the tables are unlocked, the slave is started (if the option-- safe-slave-backup was used) and the connection with the server is closed. Then, it removes the xtrabackup_suspended_2 file and permits xtrabackup to exit.

After the above steps are completed, the following files are produced in the target directory

Xtrabackup_checkpoints containing the LSN and the type of backup

Xtrabackup_binlog_info containing the position of the binary log at the moment of backing up

Xtrabackup_binlog_pos_innodb containing the position of the binary log at the moment of backing up relative to InnoDB transactions

Xtrabackup_slave_info containing the MySQL binlog position of the master server in a replication setup via SHOW SLAVE STATUS if the-slave-info option is passed

Backup-my.cnf containing only the my.cnf options required for the backup. For example, innodb_data_file_path, innodb_log_files_in_group, innodb_log_file_size, innodb_fast_checksum, innodb_page_size, innodb_log_block_size

Xtrabackup_binary containing the binary used for the backup

Mysql-stderr containing the STDERR of mysqld during the process and

Mysql-stdout containing the STDOUT of the server.

Note that the STDERR of innobackupex is not written in any file. You will have to redirect it to a file, e.g.innobackupex OPTIONS 2 > backupout.log.

The configuration options datadir, innodb_data_home_dir, innodb_data_file_path,innodb_log_group_home_dir in my.cnf are read during innobackupex backup.

First, use xtrabackup for backup

Create backup user

Mysql > CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY's 3 cretters cross query OK, 0 rows affected (0.49 sec) mysql > GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,PROCESS ON *. * TO' bkpuser'@'localhost';Query OK, 0 rows affected (0.00 sec) mysql > FLUSH PRIVILEGES;Query OK, 0 rows affected (0.35 sec)

1.1 backup type (full)

Creating a Backup

[root@ora12c backup] # xtrabackup-- user=bkpuser-- password=s3cret-- socket=/tmp/mysql.sock-- backup--target-dir=/oracle/app/backup/base.xtrabackup: Transaction log of lsn (2536404) to (2536413) was copied.170726 17:11:44 completed OK! [root@ora12c backup] # ls-l base/total 12340 RWMI r-1 root root 426 Jul 26 17:11 backup-my.cnf-rw-r- 1 root Root 313 Jul 26 17:11 ib_buffer_pool-rw-r- 1 root root 12582912 Jul 26 17:11 ibdata1drwxr-x--- 2 root root 4096 Jul 26 17:11 mysqldrwxr-x--- 2 root root 4096 Jul 26 17:11 performance_schemadrwxr-x--- 2 root root 12288 Jul 26 17:11 sysdrwxr-x--- 2 root root 4096 Jul 26 17:11 testdrwxr-x--- 2 root root 4096 Jul 26 17:11 xtra- Rw-r- 1 root root 21 Jul 26 17:11 xtrabackup_binlog_info-rw-r- 1 root root 113 Jul 26 17:11 xtrabackup_checkpoints-rw-r- 1 root root 526 Jul 26 17:11 xtrabackup_info-rw-r- 1 root root 2560 Jul 26 17:11 xtrabackup_logfile

1.2 prepare backup

Preparing the backup

This process is to make the backup consistent. Recover process similar to oracle, but varies depending on the parameters used in prepare,-- apply-log and-- apply-log-only

After you make a backup with-backup, the next step is to prepare it. The data files are not point-in-time consistent until they've been prepared, because they were copied at different times as the program ran, and they might have been changed while this was happening. If you try to start InnoDB with these data files, it will detect corruption and crash itself to prevent you from running on damaged data. The-prepare step makes the files perfectly consistent at a

Single instant in time, so you can run InnoDB on them.

[root@ora12c backup] # xtrabackup-user=bkpuser-password=s3cret-socket=/tmp/mysql.sock-prepare-target-dir=/oracle/app/backup/base.InnoDB: Shutdown completed; log sequence number 2536488170726 17:31:13 completed OK!

After the above steps are performed, the backup is consistent and can be used for recovery. If the application is based on incremental backup, you should use the-apply-log-only parameter

1.3 restore backup

Restoring a Backup

Copy backup to the destination data directory (using cp or rsync)

For example: rsync-avrP / data/backup/ / var/lib/mysql/

Chown-R mysql:mysql / var/lib/mysql

Note that only InnoDB data is backed up by xtrabackup.

The detailed steps are as follows

[root@ora12c backup] # xtrabackup-- user=bkpuser-- password=s3cret-- socket=/tmp/mysql.sock-- backup-- target-dir=/oracle/app/backup/base [root@ora12c backup] # xtrabackup-user=bkpuser-- password=s3cret-- socket=/tmp/mysql.sock-- prepare-- target-dir=/oracle/app/backup/basemysql > show variables like 'datadir' +-+-+ | Variable_name | Value | +-+-+ | datadir | / oracle/app/ Mysql-5.7/data/ | +-+-- + 1 row in set (0.12sec) mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | test | | xtra | +-+ 6 rows in set (0. 16 sec) [root@ora12c backup] # service mysqld stopShutting down MySQL.... [OK] [root@ora12c backup] # mv / oracle/app/mysql-5.7/data/ / oracle/app/mysql-5.7/data-bak [root@ora12c backup] # cp-r / oracle/app/backup/base / oracle/app/mysql-5.7/data/ [root@ora12c backup] # chown-R mysql.mysql / oracle/app/mysql-5.7/data [root@ora12c backup] # service mysqld startStarting MySQL.. [OK] mysql > show databases; +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | test | | xtra | +-+ 6 rows in set (0.00 sec)

II. Other types of backup

Incremental backup

Both xtrabackup and innobackupex support incremental backups, which means that data that has changed since the last complete backup can be backed up. So you can set up full backup every Monday, incremental backup for the rest of the day, or full daily, hourly incremental backup.

The reason why incremental backups can be performed is as follows:

Incremental backups work because each InnoDB page (usually 16kb in size) contains a log sequence number, or LSN.The LSN is the system version number for the entire database. Each page's LSN shows how recently it was changed.An incremental backup copies each page whose LSN is newer than the previous incremental or full backup's LSN.There are two algorithms in use to find the set of such pages to be copied. The first one, available with all the server types and versions, is to check the page LSN directly by reading all the data pages. The second one, available with Percona Server, is to enable the changed page tracking feature on the server, which will note the pages as they are being changed. This information will be then written out in a compact separate so-called bitmap file. The xtrabackup binary will use that file to read only the data pages it needs for the incremental backup, potentially saving many read

Requests. The latter algorithm is enabled by default if the xtrabackup binary finds the bitmap file. It is possible to specify-- incremental-force-scan to read all the pages even if the bitmap data is available.

2.1 create incremental backup

The incremental backup is created on a full basis, and xtrabackup creates a xtrabackup_checkpoints file in the backup directory that contains the to_lsn (LSN at the end of the database backup).

Full [root@ora12c backup] # xtrabackup-ubkpuser-ps3cret-S/tmp/mysql.sock-- backup-- target-dir=/oracle/app/backup/base.xtrabackup: Transaction log of lsn (2536507) to (2536516) was copied.170727 10:27:45 completed OK! [root@ora12c backup] # more base/xtrabackup_checkpoints backup_type = full-backupedfrom_lsn = 0to_lsn = 2536507last_lsn = 0recover_binlog_info = 0 create test database, table mysql > create database test_increment default character set utf8 Query OK, 1 row affected (0.36 sec) mysql > create table test (id int (11) NOT NULL AUTO_INCREMENT,email varchar (100) DEFAULT NULL,name varchar (10) DEFAULT NULL,PRIMARY KEY (id)); Query OK, 0 rows affected (0.10 sec) mysql > insert into test (email,name) values Query OK, 1 row affected (0.01 sec) mysql > insert into test (email,name) values; Query OK, 1 row affected (0.67 sec) mysql > select * from test +-+ | id | email | name | +-+ | 1 | 123@gmail.com | a | 2 | 234@ Gmail.com | b | +-+ 2 rows in set (0.00 sec) incremental backup-[root@ora12c backup] # xtrabackup-ubkpuser-ps3cret-S/tmp/mysql.sock-- backup-- target-dir=/oracle/app/backup/incr1-- incremental-basedir=/oracle/app/backup/basextrabackup: Transaction log of lsn (2555122) to (2555131) was copied.170727 10:41:06 completed Oklaxincr1 contains delta files Similar to ibdata1.delta and test/table1.ibd.delta These representatives update mysql > insert into test (email,name) values from the changes since the last LSN [root@ora12c backup] # more incr1/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 2536507to_lsn = 2555122last_lsn = 2555131compact = 0recover_binlog_info = 0 table ('345 mail.compose Query OK, 1 row affected (0.35 sec) mysql > insert into test (email,name) values; Query OK, 1 row affected (0.02 sec) mysql > select * from test +-123@gmail.com | a | 2 | 234@gmail.com | b | 3 | 345@gmail.com | c | 4 | 456@gmail.com | d | + -+-+ 4 rows in set (0.00 sec) mysql > delete from test where id = 1 ^ [AQuery OK, 1 row affected (0.42 sec) mysql > select * from test +-+ | id | email | name | +-+ | 2 | 234@gmail.com | b | 3 | 345@gmail.com | c | 4 | 456@gmail.com | d | + +-+ 3 rows in set (0.00 sec) incremental backup two [root@ora12c backup] # xtrabackup-ubkpuser-ps3cret-S/tmp/mysql.sock-- backup-- target-dir=/oracle/app/backup/incr2-- incremental-basedir=/oracle/app/backup/incr1.xtrabackup: Transaction log of lsn (2560224) to (2560233) was copied.170727 10:55:09 completed OK!

2.2 prepare incremental backup

Preparing the Incremental Backups

The-prepare step for incremental backups is not the same as for normal

Backups. In normal backups, two types of operations are performed to make the database consistent: committed transactions are replayed from the log file against the data files, and uncommitted transactions are rolled back. You must skip the rollback of uncommitted transactions when preparing a backup, because transactions that were uncommitted

At the time of your backup may be in progress, and it's likely that they will be committed in the next incremental backup. You should use the-- apply-log-only option to prevent the rollback phase.

For incremental backups, the-- apply-log-only parameter is used to prevent the transaction rollback phase during prepare.

[root@ora12c backup] # xtrabackup-ubkpuser-ps3cret-S/tmp/mysql.sock-prepare-- apply-log-only-- target-dir=/oracle/app/backup/base.InnoDB: Shutdown completed; log sequence number 2536525InnoDB: Number of pools: 1170727 11:00:47 completed OK! Apply the first incremental backup to the full [root@ora12c backup] # xtrabackup-ubkpuser-ps3cret-S/tmp/mysql.sock-- prepare-- apply-log-only-- target-dir=/oracle/app/backup/base-- incremental-dir=/oracle/app/backup/incr1.InnoDB: Allocated tablespace ID 21 for sys/sys_config Old maximum was 0xtrabackup: page size for / oracle/app/backup/incr1//ibdata1.delta is 16384 bytesApplying / oracle/app/backup/incr1//ibdata1.delta to. / ibdata1...xtrabackup: page size for / oracle/app/backup/incr1//sys/sys_config.ibd.delta is 16384 bytes.170727 11:10:33 completed OK! [root@ora12c backup] # ls-l base/total 20536 Murray r-1 root root 426 Jul 27 10:27 Backup-my.cnf-rw-r- 1 root root 313 Jul 27 10:27 ib_buffer_pool-rw-r- 1 root root 12582912 Jul 27 11:10 ibdata1drwxr-x--- 2 root root 4096 Jul 27 11:10 mysqldrwxr-x--- 2 root root 4096 Jul 27 11:10 performance_schemadrwxr-x--- 2 root root 12288 Jul 27 11:10 sysdrwxr-x--- 2 root root 4096 Jul 27 11:10 testdrwxr-x -2 root root 4096 Jul 27 11:10 test_incrementdrwxr-x--- 2 root root 4096 Jul 27 11:10 xtra-rw-r- 1 root root 22 Jul 27 11:10 xtrabackup_binlog_info-rw-r--r-- 1 root root 22 Jul 27 11:10 xtrabackup_binlog_pos_innodb-rw-r- 1 root root 111 Jul 27 11:10 xtrabackup_checkpoints-rw-r- 1 root root 562 Jul 27 11:10 xtrabackup_info-rw-r- 1 root root 8388608 Jul 27 11:00 xtrabackup_logfile can see that the full directory already has the database test_increment created after the first complete. If you restore the backup from / oracle/app/backup/base at this time, you should be able to see the database status after the first incremental backup. Apply the second incremental backup to the full [root@ora12c backup] # xtrabackup-ubkpuser-ps3cret-S/tmp/mysql.sock-- prepare-- target-dir=/oracle/app/backup/base-- incremental-dir=/oracle/app/backup/incr2

Note:-- apply-log-only should be used when merging all incrementals except the last one. That's why the previous line doesn't contain the-- apply-log-only option. Even if the-apply-log-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase.

2.3 restore backup

[root@ora12c backup] # service mysqld stopShutting down MySQL. [OK] [root@ora12c backup] # mv / oracle/app/mysql-5.7/data / oracle/app/mysql-5.7/data-bak [root@ora12c backup] # cp-r / oracle/app/backup/base / oracle/app/mysql-5.7/data [root@ora12c backup] # chown-R mysql.mysql / oracle/app/mysql-5.7/data [root@ora12c backup] # service mysqld startStarting MySQL... [OK] mysql > select * from test; ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 2Current database: test_increment+----+ | id | email | name | 2 | 234@gmail.com | b | 3 | 345@gmail.com | c | 4 | 456@gmail.com | d | + +-+-+ 3 rows in set (0.45 sec)

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.