扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
上一篇简单介绍了percona-xtrabackup的安装，percona-xtrabackup安装（一），这篇主要介绍innobackupex的工作以及实现原理和具体的使用等。
十载的台山网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。网络营销推广的优势是能够根据用户设备显示端的尺寸不同，自动调整台山建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。创新互联从事“台山网站设计”,“台山网站推广”以来，每个客户项目都认真落实执行。
xtrabackup包含两个工具：xtrabackup和innobackupex。
1 xtrabackup只能备份innodb和xtradb两种引擎的表，不能备份myisam引擎的表；
2 innobackupex是一个封装了xtrabackup的Perl脚本，支持同时备份innodb和myisam引擎的表，但在对myisam引擎的表备份时需要加一个全局的读锁。还有就是对于myisam引擎的表不支持增量备份。
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.
上述步骤完成之后，会在目标目录生产以下文件
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.
innobackupex备份时候会读取my.cnf中的配置选项datadir, innodb_data_home_dir, innodb_data_file_path,innodb_log_group_home_dir
一、使用xtrabackup进行备份
创建备份用户
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret'; 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备份类型 （全备）
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 -rw-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 ibdata1 drwxr-x--- 2 root root 4096 Jul 26 17:11 mysql drwxr-x--- 2 root root 4096 Jul 26 17:11 performance_schema drwxr-x--- 2 root root 12288 Jul 26 17:11 sys drwxr-x--- 2 root root 4096 Jul 26 17:11 test drwxr-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准备备份
Preparing the backup
这个过程就是使得备份达到一致性。类似oracle的recover过程，但是根据prepare时使用参数的不同有所不同，--apply-log 和--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 2536488 170726 17:31:13 completed OK!
上述步骤执行完之后，备份就是一致的，可以用来进行恢复。如果应用的基础是增量备份，应该使用-apply-log-only参数
1.3恢复备份
Restoring a Backup
拷贝备份到目标数据目录（使用cp或者rsync）
如：rsync -avrP /data/backup/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
注意;xtrabackup备份的只是InnoDB 数据.
详细操作步骤如下所示;
[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/base mysql> show variables like 'datadir'; +---------------+-----------------------------+ | Variable_name | Value | +---------------+-----------------------------+ | datadir | /oracle/app/mysql-5.7/data/ | +---------------+-----------------------------+ 1 row in set (0.12 sec) mysql> show databases ; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | xtra | +--------------------+ 6 rows in set (0.16 sec) [root@ora12c backup]# service mysqld stop Shutting 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 start Starting MySQL.. [ OK ] mysql> show databases ; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | xtra | +--------------------+ 6 rows in set (0.00 sec)
二、其他类型备份
增量备份
xtrabackup 和innobackupex都支持增量备份，意味着可以备份自从上次全备之后的改变的数据。因此可以设置每周一全备，其余天增量备份，或者每天全备，每小时增量备份。
之所以可以进行增量备份，原理如下：
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创建增量备份
增量备份的创建是基于一个全备的，xtrabackup会在备份目录创建一个xtrabackup_checkpoints文件，包含了to_lsn（数据库备份结束时的LSN）。
全备
[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-backuped
from_lsn = 0
to_lsn = 2536507
last_lsn = 2536516
compact = 0
recover_binlog_info = 0
创建测试数据库、表
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 ('123@gmail.com','a') ;
Query OK, 1 row affected (0.01 sec)
mysql> insert into test (email,name) values ('234@gmail.com','b') ;
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)
增量备份一
[root@ora12c backup]# xtrabackup -ubkpuser -ps3cret -S/tmp/mysql.sock --backup --target-dir=/oracle/app/backup/incr1 --incremental-basedir=/oracle/app/backup/base
xtrabackup: Transaction log of lsn (2555122) to (2555131) was copied.
170727 10:41:06 completed OK!
/oracle/app/backup/incr1包含delta files,类似ibdata1.delta 和test/table1.ibd.delta，这些代表从
上次LSN之后的变化
[root@ora12c backup]# more incr1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2536507
to_lsn = 2555122
last_lsn = 2555131
compact = 0
recover_binlog_info = 0
表更新
mysql> insert into test (email,name) values ('345@gmail.com','c') ;
Query OK, 1 row affected (0.35 sec)
mysql> insert into test (email,name) values ('456@gmail.com','d') ;
Query OK, 1 row affected (0.02 sec)
mysql> select * from test ;
+----+---------------+------+
| id | email | name |
+----+---------------+------+
| 1 | 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)
增量备份二
[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准备增量备份
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.
对于增量备份，在prepare的时候，使用--apply-log-only参数阻止事务回滚阶段。
[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 2536525 InnoDB: Number of pools: 1 170727 11:00:47 completed OK! 应用第一次增量备份至全备上 [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 0 xtrabackup: page size for /oracle/app/backup/incr1//ibdata1.delta is 16384 bytes Applying /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 -rw-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 ibdata1 drwxr-x--- 2 root root 4096 Jul 27 11:10 mysql drwxr-x--- 2 root root 4096 Jul 27 11:10 performance_schema drwxr-x--- 2 root root 12288 Jul 27 11:10 sys drwxr-x--- 2 root root 4096 Jul 27 11:10 test drwxr-x--- 2 root root 4096 Jul 27 11:10 test_increment drwxr-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 可以看到全备目录已经有了第一次全备之后创建的数据库test_increment。 如果此时从/oracle/app/backup/base恢复备份，你应该可以看到第一次增量备份之后的数据库状态。 应用第二次增量备份至全备上 [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恢复备份
[root@ora12c backup]# service mysqld stop Shutting 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 start Starting MySQL... [ OK ] mysql> select * from test ; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 2 Current 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)
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网