xtrabakup備份與恢復

內核方面：
$ cat /etc/centos-release
　　CentOS Linux release 7.4.1708 (Core)
$ uname -r
　　3.10.0-693.el7.x86_64
下載安裝xtrabackup：
$ wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.12-r170eb8c-el7-x86_64-bundle.tar
$ tar -xf Percona-XtraBackup-2.4.12-r170eb8c-el7-x86_64-bundle.tar
$ yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
$ which xtrabackup
　　/usr/bin/xtrabackup
$ innobackupex -v
　　xtrabackup: recognized server arguments: --innodb_buffer_pool_size=1024M --datadir=/data/mysql/data --server-id=1

創建測試數據：
mysql> mysql -uroot -p
mysql> create database opop charset='utf8mb4';
mysql> use opop;
mysql> create table user(id int unsigned primary key auto_increment,name varchar(30));
mysql> show tables;
mysql> insert into user(name) values('jack'),('tom'),('lily'),('lucy');
mysql> select from user;
mysql> commit;
mysql> select from user;
Xtrabackup全量備份與恢復
$ innobackupex --defaults-file=/etc/my.cnf --user=sstuser --password="123456" --backup . 　　　　　　 #最后有個點,是當前目錄

xtrabackup: recognized server arguments: --innodb_buffer_pool_size=1024M --datadir=/data/mysql/data --server-id=1
xtrabackup: recognized client arguments: --innodb_buffer_pool_size=1024M --datadir=/data/mysql/data --server-id=1 --backup=1
181025 08:27:43 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

181025 08:27:43 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'sstuser' (using password: YES).
181025 08:27:43 version_check Connected to MySQL server
181025 08:27:43 version_check Executing a version check against the server...
181025 08:27:43 version_check Done.
181025 08:27:43 Connecting to MySQL server host: localhost, user: sstuser, password: set, port: not set, socket: not set
Using server version 5.7.23-23-57
innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
181025 08:27:43 >> log scanned up to (2605686)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
181025 08:27:44 [01] Copying ./ibdata1 to /root/2018-10-25_08-27-43/ibdata1
181025 08:27:44 [01] ...done
....
....

181025 08:27:46 Executing LOCK TABLES FOR BACKUP... 　　　　 #翻譯：執行鎖定表以進行備份
181025 08:27:46 Starting to backup non-InnoDB tables and files
181025 08:27:46 [01] Copying ./mysql/db.opt to /root/2018-10-25_08-27-43/mysql/db.opt
181025 08:27:46 [01] ...done
181025 08:27:46 [01] Copying ./mysql/db.frm to /root/2018-10-25_08-27-43/mysql/db.frm
181025 08:27:46 [01] ...done
....
....

181025 08:27:47 [01] Copying ./opop/user.frm to /root/2018-10-25_08-27-43/opop/user.frm
181025 08:27:47 [01] ...done
181025 08:27:47 Finished backing up non-InnoDB tables and files
181025 08:27:47 Executing LOCK BINLOG FOR BACKUP... 　　　　 #執行鎖定二進制文件開始備份
181025 08:27:47 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... 　　 #執行flush no_write_to_binlog引擎日志
xtrabackup: The latest check point (for incremental): '2605677'
xtrabackup: Stopping log copying thread. 　　　　　　　　　　　　 #停止日志復制線程。
.181025 08:27:47 >> log scanned up to (2605686) #日志掃描到(2605686)

181025 08:27:47 Executing UNLOCK BINLOG 　　　　　　　　　　 #執行解鎖binlog
181025 08:27:47 Executing UNLOCK TABLES 　　　　　　　　　　 #執行解鎖表
181025 08:27:47 All tables unlocked 　　　　　　　　　　　　　　 #所有表解鎖
181025 08:27:47 [00] Copying ib_buffer_pool to /root/2018-10-25_08-27-43/ib_buffer_pool
181025 08:27:47 [00] ...done
181025 08:27:47 Backup created in directory '/root/2018-10-25_08-27-43/' #在/root/2018-10-25_08-27-43/目錄中創建備份
181025 08:27:47 [00] Writing /root/2018-10-25_08-27-43/backup-my.cnf
181025 08:27:47 [00] ...done
181025 08:27:47 [00] Writing /root/2018-10-25_08-27-43/xtrabackup_info
181025 08:27:47 [00] ...done
xtrabackup: Transaction log of lsn (2605677) to (2605686) was copied. #已復制lsn（2605677）到（2605686）的事務日志。
181025 08:27:48 completed OK!　　　　　　 #完成好了！

在當前目錄下會生成一個以當前時間為名字的一個目錄:

#這里面就是相關的備份文件，同樣也可以看到我們創建的庫的名稱
使用以下命令使相關數據性文件保持一致性狀態
[root@localhost ~]# innobackupex --apply-log /root/2018-10-25_08-27-43/

xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1
181025 08:57:41 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".

innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: cd to /root/2018-10-25_08-27-43/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2605677)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2605677
InnoDB: Doing recovery: scanned up to log sequence number 2605686 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Creating shared tablespace for temporary tables
InnoDB: 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 start
InnoDB: 5.7.19 started; log sequence number 2605686

xtrabackup: Recovered WSREP position: bbe63ad7-d767-11e8-9ffc-073dc1824390:8
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2605783
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=2605783
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2606092
InnoDB: Doing recovery: scanned up to log sequence number 2606101 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: 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 start
InnoDB: 5.7.19 started; log sequence number 2606101
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2606120
181025 08:57:48 completed OK!
恢復
先刪除相應的表
注：這里需要注意的是沒有刪除庫，因為刪除庫后恢復時會報錯，因為它找不到相應的庫，要新建出來相應名字的庫再恢復
mysql -uroot -p
mysql> drop table opop.user;
mysql> select * from opop.user;
ERROR 1146 (42S02): Table 'opop.user' doesn't exist
mysql> use opop;
mysql> show tables;
mysql> exit
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2018-10-25_08-27-43/





注：這里一定要記得重啟，且在Percona XtraDB集群中主節點和其他節點的啟動、重啟方式不一樣，因為該文檔是接上一篇文檔

登陸到數據庫中查看恢復情況

Xtrabackup增量備份與恢復

增量備份僅能應用于InooDB或XtraDB表，對于MyISAM表，增量與全備相同，在上面的基礎上繼續創建一個opop2的庫然后在opop-2的庫里建一張表并插入數據
mysql> create database opop2 charset='utf8mb4';
mysql> use opop2;
mysql> create table user2(id int unsigned primary key auto_increment,name varchar(30));
mysql> insert into user2(name) values('zhangsan'),('lisi'),('wangwu'),('maliu');
mysql> commit;
mysql> select from user2;


備份開始

略去中間部分…………

#--incremental /backup/ 　　指定增量備份文件備份的目錄
#--incremental-basedir 　　 指定上一次全備或增量備份的目錄


刪除一條數據來測試增量恢復 （這里刪除的是增量備份的數據）
mysql -uroot -p
mysql> use opop2;
mysql> show tables;
mysql> desc user2;
mysql> delete from user2 where name='lisi';
mysql> select from user2; #確認lisi用戶已經刪除
mysql> exit
增量恢復操作過程如下
[root@localhost ~]# innobackupex --apply-log --redo-only /root/2018-10-25_08-27-43/ 　　#第一次的全局備份數據目錄
[root@localhost ~]# innobackupex --apply-log --redo-only /root/2018-10-25_08-27-43/ --incremental-dir=/root/2018-10-25_11-00-25/ #第二次的增量備份數據目錄
注：這里相當于一個合并的動作
恢復全部數據：
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /root/2018-10-25_08-27-43/




[root@localhost data]# systemctl restart mysql@bootstrap.service
#因為該實驗中是做的Percona XtraDB Cluster集群，所以使用該命令，如不是Percona XtraDB Cluster 集群，在centos上則使用 systemctl 或 service 啟動或重啟
[root@localhost data]# mysql -uroot -p