xtrabackup简介
xtrabackup是一个用来对mysql做备份的工具，它可以对innodb引擎的数据库做热备。xtrabackup备份和还原速度快,备份操作不会中断正在执行的事务,备份完成之后可以自动做校验,备份结果可以压缩（节省磁盘和带宽）。实际工作中可以用来做mysql的完全备份，增量备份，以及差异备份等。
xtrabackup有两个主要的工具：innobackupex和xtrabackup，xtrabackup只能备份InnoDB和XtraDB数据表，innobackupex封装了xtrabackup，可以备份MyISAM数据表。
MySQL数据库本身提供的工具并不支持真正的增量备份，二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份。Xtrabackup工具支持对InnoDB存储引擎的增量备份，工作原理如下：
1.首先完成一个完全备份，并记录下此时检查点的LSN(LogSequence Number)。
2.在进程增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是，则备份该页，同时记录当前检查点的LSN。
xtrabackup常用选项
2 --incremental-basedir=name < <仅适用于backup，增量备份目录3 --incremental-dir="name"> <
安装xtrabackup
1 下载xtrabackup（下载二进制版本免安装）2 # wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.5/Linux/binary/x86_64/xtrabackup-1.5.tar.gz3 # tar zxvfxtrabackup-1.5.tar.gz -C /usr/local/4 源码安装过程具体参见源码目录下BUILD.txt文件。
演示：基于innobackupex做备份和恢复（完整备份）
第一步：完全备份到/ken下
[root@ken ~]# innobackupex -uroot -pxx /ken <
第二步：删除mysql数据库（模拟故障）
[root@ken ~]# mysql -uroot -p < <登录数据库enter password: welcome to the mysql monitor. commands end with ; or \g.your connection id is 5server version: 5.7.23-log community server (gpl)copyright (c) 2000, 2018, oracle and its affiliates. all rights reserved.oracle a registered trademark of corporation itsaffiliates. other names may be trademarks their respectiveowners.type 'help;' '\h' for help. type '\c' clear current input statement.mysql> show databases; < <查看现有数据库+--------------------+| database |+--------------------+| information_schema || jobs ke mysql performance_schema sys test test5 |+--------------------+8 rows in set (0.06 sec)mysql> drop database mysql; <
第三步：退出mysql并关闭mysql
[root@ken ~]# mysqladmin -uroot -p shutdown <
第四步：整理备份数据（将备份整理就绪）
[root@ken ~]# innobackupex --apply-log /ken/2018-10-23_04-51-51/xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 181023 04:59:15 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!".......InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0InnoDB: New log files created, LSN=3366546InnoDB: Highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 3366924InnoDB: Doing recovery: scanned up to log sequence number 3366933 (0%)InnoDB: Database was not shutdown normally!InnoDB: Starting crash recovery.InnoDB: xtrabackup: Last MySQL binlog file position 817, file name mysql-bin.000003InnoDB: Removed temporary tablespace data file: "ibtmp1"InnoDB: Creating shared tablespace for temporary tablesInnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...InnoDB: File './ibtmp1' size is now 12 MB.InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.InnoDB: 32 non-redo rollback segment(s) are active.InnoDB: Waiting for purge to startInnoDB: 5.7.19 started; log sequence number 3366933xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 3366952181023 04:59:20 completed OK!
第五步：确认是否可以进行恢复
[root@ken ~]# ss -tnl <
第六步：进行恢复
[root@ken ~]# innobackupex --copy-back /ken/2018-10-23_04-51-51/xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT 181023 05:03:30 innobackupex: Starting the copy-back operationIMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!".......181023 05:03:35 [01] ...done181023 05:03:35 [01] Copying ./test5/db.opt to /data/mysql/mysql3306/data/test5/db.opt181023 05:03:35 [01] ...done181023 05:03:35 [01] Copying ./test5/ken.frm to /data/mysql/mysql3306/data/test5/ken.frm181023 05:03:35 [01] ...done181023 05:03:35 [01] Copying ./test5/ken.MYI to /data/mysql/mysql3306/data/test5/ken.MYI181023 05:03:35 [01] ...done181023 05:03:35 [01] Copying ./test5/ken.MYD to /data/mysql/mysql3306/data/test5/ken.MYD181023 05:03:35 [01] ...done181023 05:03:35 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool181023 05:03:35 [01] ...done181023 05:03:35 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info181023 05:03:35 [01] ...done181023 05:03:35 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb181023 05:03:35 [01] ...done181023 05:03:35 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id181023 05:03:35 [01] ...done181023 05:03:35 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1181023 05:03:35 [01] ...done181023 05:03:35 completed OK!
第七步：更改数据权限
[root@ken ~]# ls -l /data/mysql/mysql3306/data/total 421936-rw-r----- 1 root root 519 Oct 23 05:03 ib_buffer_pool-rw-r----- 1 root root 104857600 Oct 23 05:03 ibdata1-rw-r----- 1 root root 104857600 Oct 23 05:03 ib_logfile0-rw-r----- 1 root root 104857600 Oct 23 05:03 ib_logfile1-rw-r----- 1 root root 104857600 Oct 23 05:03 ib_logfile2-rw-r----- 1 root root 12582912 Oct 23 05:03 ibtmp1drwxr-x--- 2 root root 4096 Oct 23 05:03 jobsdrwxr-x--- 2 root root 48 Oct 23 05:03 kedrwxr-x--- 2 root root 4096 Oct 23 05:03 mysqldrwxr-x--- 2 root root 8192 Oct 23 05:03 performance_schemadrwxr-x--- 2 root root 8192 Oct 23 05:03 sysdrwxr-x--- 2 root root 200 Oct 23 05:03 testdrwxr-x--- 2 root root 65 Oct 23 05:03 test5-rw-r----- 1 root root 21 Oct 23 05:03 xtrabackup_binlog_pos_innodb-rw-r----- 1 root root 448 Oct 23 05:03 xtrabackup_info-rw-r----- 1 root root 1 Oct 23 05:03 xtrabackup_master_key_id
可以看到下面恢复的数据文件的属主已经变成root,需要更改为mysql
[root@ken ~]# chown -R mysql.mysql /data/mysql/mysql3306/data
第八步：启动mysql并查看mysql数据库是否已经恢复
[root@ken ~]# mysqld & < <启动mysql数据库[1] 7179[root@ken ~]# mysql -uroot -p <<登录mysqlenter password: welcome to the mysql monitor. commands end with ; or \g.your connection id is 2server version: 5.7.23-log community server (gpl)copyright (c) 2000, 2018, oracle and its affiliates. all rights reserved.oracle a registered trademark of corporation itsaffiliates. other names may be trademarks their respectiveowners.type 'help;' '\h' for help. type '\c' clear current input statement.mysql> show databases; <
演示：基于innobackupex做增量备份和恢复（增量备份）
第一步：首先做一次完全备份
[root@ken ~]# innobackupex -uroot -pxx /ken <
第二步：创建一张表
mysql> use jobs;Database changedmysql> create table ken1 as select * from teachers; <
第三步：做一次增量备份
[root@ken ~]# innobackupex -uroot -pxx --incremental --incremental-basedir=/ken/2018-10-23_04-51-51/ /kenxtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT 181023 05:17:04 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!".......181023 05:17:08 Executing UNLOCK TABLES181023 05:17:08 All tables unlocked181023 05:17:08 [00] Copying ib_buffer_pool to /ken/2018-10-23_05-17-04/ib_buffer_pool181023 05:17:08 [00] ...done181023 05:17:08 Backup created in directory '/ken/2018-10-23_05-17-04/'MySQL binlog position: filename 'mysql-bin.000005', position '794'181023 05:17:08 [00] Writing /ken/2018-10-23_05-17-04/backup-my.cnf181023 05:17:08 [00] ...done181023 05:17:08 [00] Writing /ken/2018-10-23_05-17-04/xtrabackup_info181023 05:17:08 [00] ...donextrabackup: Transaction log of lsn (3373349) to (3373358) was copied.181023 05:17:08 completed OK!
第四步：删除刚才创建的表（模拟故障）
mysql> use jobs;Database changedmysql> drop table ken1;Query OK, 0 rows affected (0.03 sec)
第五步：整理完整备份
[root@ken ~]# innobackupex --apply-log --redo-only /ken/2018-10-23_04-51-51/xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 181023 05:20:49 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!".......xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 3366961InnoDB: Number of pools: 1181023 05:20:50 completed OK!
第六步：将第一个增量备份文件合并到完全备份中
[root@ken ~]# innobackupex --apply-log --redo-only /ken/2018-10-23_04-51-51/ --incremental-dir=/ken/2018-10-23_05-17-04/xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 181023 05:23:15 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!".......181023 05:23:17 [01] ...done181023 05:23:17 [01] Copying /ken/2018-10-23_05-17-04/test5/db.opt to ./test5/db.opt181023 05:23:17 [01] ...done181023 05:23:17 [01] Copying /ken/2018-10-23_05-17-04/test5/ken.frm to ./test5/ken.frm181023 05:23:17 [01] ...done181023 05:23:17 [01] Copying /ken/2018-10-23_05-17-04/test5/ken.MYI to ./test5/ken.MYI181023 05:23:17 [01] ...done181023 05:23:17 [01] Copying /ken/2018-10-23_05-17-04/test5/ken.MYD to ./test5/ken.MYD181023 05:23:17 [01] ...done181023 05:23:17 [00] Copying /ken/2018-10-23_05-17-04//xtrabackup_binlog_info to ./xtrabackup_binlog_info181023 05:23:17 [00] ...done181023 05:23:17 [00] Copying /ken/2018-10-23_05-17-04//xtrabackup_info to ./xtrabackup_info181023 05:23:17 [00] ...done181023 05:23:17 completed OK!
第七步：确认是否可以进行恢复
[root@ken ~]# ss -tnlState Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:111 *:* LISTEN 0 128 *:22 *:* LISTEN 0 128 :::111 :::* LISTEN 0 128 :::22 :::* [root@ken ~]# rm -rf /data/mysql/mysql3306/data/*
第八步：数据恢复
[root@ken ~]# innobackupex --copy-back /ken/2018-10-23_04-51-51/xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=100 --innodb_flush_method=O_DIRECT 181023 05:27:13 innobackupex: Starting the copy-back operationIMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!".......181023 05:27:18 [01] Copying ./test5/db.opt to /data/mysql/mysql3306/data/test5/db.opt181023 05:27:18 [01] ...done181023 05:27:18 [01] Copying ./test5/ken.frm to /data/mysql/mysql3306/data/test5/ken.frm181023 05:27:18 [01] ...done181023 05:27:18 [01] Copying ./test5/ken.MYI to /data/mysql/mysql3306/data/test5/ken.MYI181023 05:27:18 [01] ...done181023 05:27:18 [01] Copying ./test5/ken.MYD to /data/mysql/mysql3306/data/test5/ken.MYD181023 05:27:18 [01] ...done181023 05:27:18 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool181023 05:27:18 [01] ...done181023 05:27:18 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb181023 05:27:18 [01] ...done181023 05:27:18 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id181023 05:27:18 [01] ...done181023 05:27:18 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1181023 05:27:18 [01] ...done181023 05:27:18 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info181023 05:27:18 [01] ...done181023 05:27:19 completed OK!
第九步：数据权限修改
[root@ken ~]# chown -R mysql.mysql /data/mysql/mysql3306/data/
第十步：启动mysql并查看删除的表是否应恢复
[root@ken ~]# mysqld &[1] 7358[root@ken ~]# mysql -uroot -pxxmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.23-log MySQL Community Server (GPL)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use jobs;Database changedmysql> show tables;+----------------+| Tables_in_jobs |+----------------+| classes || courses || ken || ken1 | <