05-xtrabackup的使用
一、xtrabackup的介绍
1、简介
Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。
2、xtrabackup的优点
* 备份速度快，物理备份可靠；
* 备份过程不会打断正在执行的事物（无需锁表，不影响正常读写）；
* 能够压缩备份、加密备份；
* 自动备份校验；
* 可以备份到远程服务器，在本地磁盘不足的情况下，非常有用；
3、xtrabackup主要工具和参数选项
（1）xtrabackup主要包含两个工具
* xtrabackup：用于热备innodb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
* xtrabackupex：是对xtrabackup进行封装的perl脚本，提供了备份myisam表的能力；
（2）参数选项
* --host：指定主机
* --user：指定用户名
* --password：指定密码
* --port：指定数据库端口
* --database：指定数据库
* --incremental：创建增量备份
* --incremental-basedir：指定包含全量备份的目录
* --incremental-dir：指定包含增量备份的目录
* --apply-log：对备份进行合并，预处理操作
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
* --redo-only：不回滚未提交的事物
* --copy-back：恢复备份
二、xtrabackup全量备份与恢复
1、安装xtrabackup
注：直接使用tar.gz的源码包解压之后就能用了，不需要再安装了，已经上传到百度云盘，有需要的可以自行下载
链接：https://pan.baidu.com/s/1Yku9zp4ggT6ck8UD80-h2Q 提取码：v14o
（1）解压xtrabackup包
tar -zxvf percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt11.tar.gz
（2）把xtrabackup/bin下的innobackupex和xtrabackup拷贝到/usr/bin下
cp innobackupex /usr/bin/
cp xtrabackup /usr/bin/
---执行完这个cp，就可以在任意地方执行了
2、对数据库做全量备份
（1）执行备份
innobackupex --user=root --password=123456 --port=3308 --host=127.0.0.1 /mysql/backup/ 执行完之后出现completed OK！表示备份成功： 201215 16:25:25 Executing UNLOCK TABLES 201215 16:25:25 All tables unlocked 201215 16:25:25 [00] Copying ib_buffer_pool to /mysql/backup/2020-12-15_16-25-22/ib_buffer_pool 201215 16:25:25 [00] ...done 201215 16:25:25 Backup created in directory '/mysql/backup/2020-12-15_16-25-22/' MySQL binlog position: filename 'mysql-bin.000005', position '1164', GTID of the last change '9b014152-37a6-11eb-89b5-000c29cddf72:1-6, b6cf6565-c415-4c4d-a4f0-49a596f98fca:1-25' 201215 16:25:25 [00] Writing /mysql/backup/2020-12-15_16-25-22/backup-my.cnf 201215 16:25:25 [00] ...done 201215 16:25:25 [00] Writing /mysql/backup/2020-12-15_16-25-22/xtrabackup_info 201215 16:25:25 [00] ...done xtrabackup: Transaction log of lsn (2722906) to (2722915) was copied. 201215 16:25:25 completed OK!
（2）查看备份文件
[root@node1 bin]# cd /mysql/backup/ [root@node1 backup]# ll total 4 drwxr-x--- 6 mysql mysql 4096 Dec 15 16:25 2020-12-15_16-25-22 [root@node1 backup]# ll 2020-12-15_16-25-22/ total 12336 -rw-r----- 1 mysql mysql 487 Dec 15 16:25 backup-my.cnf drwxr-x--- 2 mysql mysql 4096 Dec 15 16:25 customer -rw-r----- 1 mysql mysql 330 Dec 15 16:25 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 Dec 15 16:25 ibdata1 drwxr-x--- 2 mysql mysql 4096 Dec 15 16:25 mysql drwxr-x--- 2 mysql mysql 4096 Dec 15 16:25 performance_schema drwxr-x--- 2 mysql mysql 12288 Dec 15 16:25 sys -rw-r----- 1 mysql mysql 106 Dec 15 16:25 xtrabackup_binlog_info -rw-r----- 1 mysql mysql 113 Dec 15 16:25 xtrabackup_checkpoints -rw-r----- 1 mysql mysql 613 Dec 15 16:25 xtrabackup_info -rw-r----- 1 mysql mysql 2560 Dec 15 16:25 xtrabackup_logfile [root@node1 backup]#
* 文件的含义：
xtrabackup_binlog_info：mysql正在使用的二进制日志文件和日志文件的偏移量信息
xtrabackup_checkpoints：里面存储备份的类型、状态和LSN号的信息
xtrabackup_info：备份的信息
3、合并全备数据目录（对备份进行预处理操作）
（1）把备份拷贝到另一台mysql服务器
scp -r 2020-12-15_16-25-22/ mysql@192.168.232.43:/mysql/backup/
（2）合并备份文件
innobackupex --apply-log /mysql/backup/2020-12-15_16-25-22/ --出现如下信息表示成功 InnoDB: Waiting for purge to start InnoDB: 5.7.19 started; log sequence number 2723349 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 2723368 201215 17:01:32 completed OK!
4、恢复全量备份
（1）停止slave库上的mysql
[root@node2 data]# service mysql stop
Shutting down MySQL.. SUCCESS!
（2）删除/mysql/mysql5.7/data下的所有数据文件
[root@node2 data]# ll total 110660 -rw-r----- 1 mysql mysql 56 Dec 15 15:50 auto.cnf drwxr-x--- 2 mysql mysql 4096 Dec 15 15:50 customer -rw-r----- 1 mysql mysql 329 Dec 15 17:05 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 Dec 15 17:05 ibdata1 -rw-r----- 1 mysql mysql 50331648 Dec 15 17:05 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 Dec 15 15:50 ib_logfile1 drwxr-x--- 2 mysql mysql 4096 Dec 15 15:50 mysql -rw-r----- 1 mysql mysql 169 Dec 15 17:05 mysql-bin.000001 -rw-r----- 1 mysql mysql 19 Dec 15 15:50 mysql-bin.index -rw-r----- 1 mysql mysql 169 Dec 15 15:50 node2-relay-bin-group_replication_applier.000001 -rw-r----- 1 mysql mysql 51 Dec 15 15:50 node2-relay-bin-group_replication_applier.index -rw-r----- 1 mysql mysql 169 Dec 15 15:50 node2-relay-bin-group_replication_recovery.000001 -rw-r----- 1 mysql mysql 52 Dec 15 15:50 node2-relay-bin-group_replication_recovery.index drwxr-x--- 2 mysql mysql 4096 Dec 15 15:50 performance_schema drwxr-x--- 2 mysql mysql 12288 Dec 15 15:50 sys -rw-r----- 1 mysql mysql 22 Dec 15 15:50 xtrabackup_binlog_pos_innodb -rw-r----- 1 mysql mysql 689 Dec 15 15:50 xtrabackup_info -rw-r----- 1 mysql mysql 1 Dec 15 15:50 xtrabackup_master_key_id [root@node2 data]# rm -rf * [root@node2 data]# ll total 0 [root@node2 data]# pwd /mysql/mysql5.7/data
（3）更改备份文件的所有者（copy的时候也可以直接使用mysql用户copy）
chown -R mysql:mysql 2020-12-15_16-25-22/
不改的话，会出现权限的问题，如下：
[mysql@node2 ~]$ innobackupex --copy-back /mysql/backup/2020-12-15_16-25-22/ xtrabackup: recognized server arguments: --datadir=/mysql/mysql5.7/data --server-id=2 --log_bin=mysql-bin xtrabackup: recognized client arguments: --datadir=/mysql/mysql5.7/data --server-id=2 --log_bin=mysql-bin 201215 17:06:50 innobackupex: Starting the copy-back operation IMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!". innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) innobackupex: File 'ib_logfile0' not found (Errcode: 13 - Permission denied) InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to the directory. [01] error: cannot open file ib_logfile0 [01] Error: copy_file() failed.
（4）恢复备份
innobackupex --copy-back /mysql/backup/2020-12-15_16-25-22/ --出现如下表示恢复成功 201215 17:10:03 [01] Copying ./sys/io_global_by_wait_by_latency.frm to /mysql/mysql5.7/data/sys/io_global_by_wait_by_latency.frm 201215 17:10:03 [01] ...done 201215 17:10:03 [01] Copying ./sys/schema_unused_indexes.frm to /mysql/mysql5.7/data/sys/schema_unused_indexes.frm 201215 17:10:03 [01] ...done 201215 17:10:03 completed OK!
查看/mysql/mysql5.7/data是否已经生成文件
（5）启动数据库，查看数据是否正常
/mysql/mysql5.7/bin/mysqld_safe defaults-file=/etc/my.cnf -user=mysql
[mysql@node2 ~]$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.22-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 its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | customer | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql>
注：发现数据库的备份已经恢复成功了。
三、xtrabackup的增量备份与恢复
1、对数据库做全量备份（与二中的全量备份方式一样）
innobackupex --user=root --password=123456 --port=3308 --host=127.0.0.1 /mysql/backup/
做完全备之后，新增一些数据
mysql> create table tb4(id int); Query OK, 0 rows affected (0.06 sec) mysql> insert into tb4 values(1),(10),(99),(1200),(3556); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from tb4; +------+ | id | +------+ | 1 | | 10 | | 99 | | 1200 | | 3556 | +------+ 5 rows in set (0.00 sec) mysql> show tables; +--------------------+ | Tables_in_customer | +--------------------+ | person | | tb2 | | tb3 | | tb4 | +--------------------+ 4 rows in set (0.00 sec)
2、对数据库做增量备份
（1）执行增量备份
innobackupex --user=root --password=123456 --port=3308 --host=127.0.0.1 --incremental /mysql/backup/ --incremental-basedir=/mysql/backup/2020-12-15_16-25-22 --出现如下表示成功 201215 17:19:34 [00] Writing /mysql/backup/2020-12-15_17-19-31/backup-my.cnf 201215 17:19:34 [00] ...done 201215 17:19:34 [00] Writing /mysql/backup/2020-12-15_17-19-31/xtrabackup_info 201215 17:19:34 [00] ...done xtrabackup: Transaction log of lsn (2729095) to (2729104) was copied. 201215 17:19:34 completed OK!
注：第一次增量备份是基于上一次全量备份的，所以要使用incremental-basedir参数指定全量备份的路径；
以后的每次增量都是基于上次的增量；
（2）查看生成的增量备份
[root@node1 backup]# ll total 8 drwxr-x--- 6 mysql mysql 4096 Dec 15 16:25 2020-12-15_16-25-22 --全量备份 drwxr-x--- 6 mysql mysql 4096 Dec 15 17:19 2020-12-15_17-19-31 --增量备份 [root@node1 backup]# pwd /mysql/backup
（3）同样的方式把增量备份拷贝到slave节点
scp -r 2020-12-15_17-19-31/ mysql@192.168.232.43:/mysql/backup/
3、合并全备数据目录（对备份进行预处理操作）
innobackupex --apply-log --redo-only /mysql/backup/2020-12-15_16-25-22/ --执行成功如下 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 2723377 InnoDB: Number of pools: 1 201215 17:28:34 completed OK!
4、将增量备份合并到全量备份目录中
innobackupex --apply-log --redo-only /mysql/backup/2020-12-15_16-25-22/ --incremental-dir=/mysql/backup/2020-12-15_17-19-31 --执行成功如下 201215 17:31:20 [00] ...done 201215 17:31:20 [00] Copying /mysql/backup/2020-12-15_17-19-31//xtrabackup_info to ./xtrabackup_info 201215 17:31:20 [00] ...done 201215 17:31:20 completed OK!
5、恢复数据库（全量+增量）
（1）停止数据库，删除数据文件
[root@node2 backup]# service mysql stop Shutting down MySQL.. SUCCESS! [root@node2 backup]# cd /mysql/mysql5.7/data/ [root@node2 data]# ll total 110660 -rw-r----- 1 mysql mysql 56 Dec 15 17:12 auto.cnf drwxr-x--- 2 mysql mysql 4096 Dec 15 17:10 customer -rw-r----- 1 mysql mysql 326 Dec 15 17:33 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 Dec 15 17:33 ibdata1 -rw-r----- 1 mysql mysql 50331648 Dec 15 17:33 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 Dec 15 17:10 ib_logfile1 drwxr-x--- 2 mysql mysql 4096 Dec 15 17:10 mysql -rw-r----- 1 mysql mysql 169 Dec 15 17:33 mysql-bin.000001 -rw-r----- 1 mysql mysql 19 Dec 15 17:12 mysql-bin.index -rw-r----- 1 mysql mysql 169 Dec 15 17:12 node2-relay-bin-group_replication_applier.000001 -rw-r----- 1 mysql mysql 51 Dec 15 17:12 node2-relay-bin-group_replication_applier.index -rw-r----- 1 mysql mysql 169 Dec 15 17:12 node2-relay-bin-group_replication_recovery.000001 -rw-r----- 1 mysql mysql 52 Dec 15 17:12 node2-relay-bin-group_replication_recovery.index drwxr-x--- 2 mysql mysql 4096 Dec 15 17:10 performance_schema drwxr-x--- 2 mysql mysql 12288 Dec 15 17:10 sys -rw-r----- 1 mysql mysql 22 Dec 15 17:10 xtrabackup_binlog_pos_innodb -rw-r----- 1 mysql mysql 613 Dec 15 17:10 xtrabackup_info -rw-r----- 1 mysql mysql 1 Dec 15 17:10 xtrabackup_master_key_id [root@node2 data]# rm -rf * [root@node2 data]# ll total 0
（2）恢复数据库备份
innobackupex --copy-back /mysql/backup/2020-12-15_16-25-22 --直接恢复全量备份即可，因为已经把差异备份合并到全量备份了 --执行成功的截图 201215 17:35:37 [01] Copying ./sys/io_global_by_wait_by_latency.frm to /mysql/mysql5.7/data/sys/io_global_by_wait_by_latency.frm 201215 17:35:37 [01] ...done 201215 17:35:37 [01] Copying ./sys/schema_unused_indexes.frm to /mysql/mysql5.7/data/sys/schema_unused_indexes.frm 201215 17:35:37 [01] ...done 201215 17:35:37 completed OK!
（3）启动数据库，查看数据是否正确
[mysql@node2 2020-12-15_17-19-31]$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.22-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 its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | customer | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use customer; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--------------------+ | Tables_in_customer | +--------------------+ | person | | tb2 | | tb3 | | tb4 | +--------------------+ 4 rows in set (0.00 sec) mysql> select * from tb4; +------+ | id | +------+ | 1 | | 10 | | 99 | | 1200 | | 3556 | +------+ 5 rows in set (0.00 sec) mysql>
至此，全量备份+增量备份也恢复成功了！
