本文最后更新于 65 天前，其中的信息可能已经过时，如有错误请发送邮件到wuxianglongblog@163.com
MySQL常用备份工具
一.MySQL常用备份工具概述
1.MySQL数据损坏类型
MySQL数据损坏类型我们大致分为2大类，即物理损坏和逻辑损坏。
物理损坏:
(1)磁盘损坏：
a)硬件损坏;
b)磁盘存储坏道;
c)使用dd命令删除磁盘数据;
d)格式化磁盘;
(2)文件损坏:
a)数据文件损坏;
b)Redo log文件损坏;
逻辑损坏:
例如执行了DROP,DELETE,TRUNCATE,UPDATE等命令将数据修改或删除。
温馨提示:
只要我们对MySQL数据做了完整的备份，那么无论原数据是物理损坏还是逻辑损坏，数据依旧是可以通过咱们的备份来进行来恢复哟~
数据备份是数据恢复的最后一道屏障，如果生产环境中我们不做备份，那后果可想而知，说轻一点可能是扣点工资，说严重点可能整个公司将面临倒闭！严重点有可能会承受牢狱之灾。
2.备份的类型
按备份系统的准备程度，可将其分为冷备份、温备份和热备份三大类。
冷备份 :
备份系统未安装或未配置成与当前使用的系统相同或相似的运行环境，应用系统数据没有及时装入备份系统。
一旦发生灾难，需安装配置所需的运行环境，用数据备份介质(磁带或光盘) 恢复应用数据，手工逐一或自动批量追补孤立数据，将终端用户通过通讯线路切换到备份系统，恢复业务运行。
优点 :
设备投资较少，节省通信费用，通信环境要求不高
缺点 :
恢复时间较长，一般要数天至1周，数据完整性与一致性较差
温备份 :
将备份系统已安装配置成与当前使用的系统相同或相似的系统和网络运行环境，安装应用系统业务定期备份数据。
一旦发生灾难，直接使用定期备份数据，手工逐一或自动批量追补孤立数据或将终端用户通过通讯线路切换到备份系统，恢复业务运行。
优点 :
设备投资较少，通信环境要求不高。
缺点 :
恢复时间长，一般要十几个小时至数天，数据完整性与一致性较差。
热备份 :
备份处于联机状态，当前应用系统通过高速通信线路将数据实时传送到备份系统，保持备份系统与当前应用系统数据的同步；也可定时在备份系统上恢复应用系统的数据。
一旦发生灾难，不用追补或只需追补很少的孤立数据，备份系统可快速接替生产系统运行，恢复营业。
优点 :
恢复时间短，一般几十分钟到数小时，数据完整性与一致性最好，数据丢失可能性最小。
缺点 :
设备投资大，通信费用高，通信环境要求高，平时运行管理较复杂。
在计算机服务器备份和恢复中
冷备份服务器(cold server)：
是在主服务器丢失的情况下才使用的备份服务器。冷备份服务器基本上只在软件安装和配置的情况下打开，然后关闭直到需要时再打开。
温备份服务器(warm server)：
一般都是周期性开机，根据主服务器内容进行更新，然后关机。经常用温备份服务器来进行复制和镜像操作。
热备份服务器(hot server)：
时刻处于开机状态，同主机保持同步。当主机失灵时，可以随时启用热备份服务器来代替。
3.DBA运维人员在备份，恢复时的职责
DBA运维人员的职责就是快速准确的恢复数据的一致性。
当然，我们作为DBA运维人员应该防患于未然，不能总是想着去备份恢复数据。而是应该定制一些策略来防止数据的数据丢失。
3.1设计备份，容灾策略
备份和容灾是两回事，大家千万不要将其混为一谈。
备份:
顾名思义，备份的目的是为了防止原数据损坏或丢失而进行数据恢复的一种手段。
容灾:
顾名思义，就是当发生数据损坏或丢失的情况下，如何快速恢复业务。
备份策略:
备份策略指的是我们该如何去进行备份，比如备份工具选择，备份周期设计，备份监控方法等。
容灾策略:
如何定制快速恢复业务的策略有很多种方法，比如可以基础备份或架构的方式解决。
备份:
增量备份，全部备份。
架构:
高可用架构，延迟从库架构，灾备库等。
温馨提示:
还记得2001年9月11日的"9·11恐怖袭击事件"吗？遇难者达到2996人，当日上午的两架被恐怖分子劫持的两架飞机分别撞向"美国纽约双子塔南北塔楼"和"美国华盛顿的五角大楼"，三座大楼均在损坏后坍塌。
据说当时有很多家公司的数据就在这三座大楼内，纽约银行在数据中心全毁，通讯线路中断后，缺乏灾备系统和有力的应急业务恢复计划，在一个月后不得不关闭一些分支机构，数月后不得不破产清盘。
3.2定期的备份，容灾检查
我们可以定制周期性任务计划将数据进行备份，而后我们要验证备份的数据是否真正意义上备份。即需要手动验证数据是否备份成功。3.3定期的故障恢复演练
我们可以使用测试库来定期的故障恢复演练，但千万别拿生产环境来测试哟~一年可以根据自己的精力做2-12次演练。3.4数据损坏时快速准确恢复
每个企业的"全年无故障时间"考核标准可能不太一样。当数据库损坏时，如何快速准确无误的恢复数据是一名DBA工作人员的天职。3.5数据迁移工作
生产环境中难免会遇到数据迁移的情况，有很多种解决方案，比如基于备份，架构等策略来实现数据的迁移。
类似于升级数据库的操作，建议大家不要直接动生产环境的数据库，而是先用测试的数据库进行测试。若测试成功后在业务低峰期的时候再来操作数据库。
4.MySQL常用备份工具概述
MySQL常用备份工具可以大致分为两类，即逻辑备份方式和物理备份方式。需要注意的是，基于"架构的备份方式"本质上也是属于逻辑备份方式。
逻辑备份:
我们可以暂时理解为备份的SQL语句。
物理备份:
我们可以暂时理解为备份的MySQL在操作系统是存储的文件。
逻辑备份方式
常用的逻辑备份工具比如mysqldump(简称"MDP")，mydumper，load data in file等等。
当然，我们也可以基于"replication"架构的方式来进行数据备份，比如主从复制，延迟复制，高可用架构等。
物理备份方式
MySQL企业版常用"MySQL Enterprise Backup"作为备份的工具。
MySQL社区版官方并未提供备份工具，但我们可以基于Percona公司开源的"Xtrabackup"(简称"PBK"或者"XBK")工具进行备份。
二.MySQL逻辑备份工具mysqldump实战
1.mysqldump工具概述
mysqldump介绍:
mysqldump是一个逻辑备份工具，其备份的是SQL语句。
mysqldump备份方式:
对于InnoDB存储引擎的表:
可以采取快照备份的方式，在备份期间可以开启一个独立的事务，获取当前最新的一致性快照，将快照数据放在临时表中，而后转换为SQL语句(比如DDL,DML等语句)并保存到文件中。
综上所述，mysqldump工具在备份时不需要锁原来的表，因为它只是一个读的操作。因此也不会影响到其他事物。
对于非InnoDB存储引擎的表:
需要进行短暂全局锁表才能进行备份。即触发"Flush Table With Read Lock"(简称"FTWRL")机制。将备份的数据放在临时表中，转换为SQL语句(比如DDL,DML等语句)并保存到文件中。
因为非InnoDB存储引擎并不支持事务，它没有快照功能。所以只能锁表备份，相当于"温备份"(即可以查询但不能修改)。
温馨提示:
(1)生产环境中对于非InnoDB存储引擎的表相对来说比较少，大多数都是MySQL内置的系统表，因此锁表的时间并不会特别长;
(2)综上所述，我们不能说使用mysqldump工具在备份时不锁表，除非是在单独备份InnoDB的表。因为对于非InnoDB(例如:MyIsam)的表进行备份会触发全局锁表FTWRL机制;
2.mysqldump工具的核心参数
连接参数
mysqldump工具从C/S架构来讲，它也属于一种客户端连接工具，因此他需要服务端是工作状态。
mysqldump工具的很多连接参数和mysql工具有些类似，如下所示:
-u:
指定连接MySQL服务端的用户名。
-p:
指定连接MySQL服务端的用户名所对应的密码。
-h:
指定连接MySQL服务端的主机地址。
-P:
指定连接MySQL服务端的端口。
-S:
指定连接MySQL服务端的本地套接字文件，和mysql工具类似，该参数通常是在MySQL服务器端本地操作时使用。
将连接MySQL服务器的用户名和密码写在配置文件中:
如下所示，很多运维的小伙伴为了防止MySQL备份用户名和密码泄露，很多小伙伴喜欢在用户的家目录创建一个名为".my.cnf"的隐藏文件。
[root@docker201.oldboyedu.com ~]# vim .my.cnf
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat .my.cnf
[mysql]
user=admin
password=oldboyedu
[mysqldump]
user=admin
password=oldboyedu
[root@docker201.oldboyedu.com ~]#
这样做的目的是让用户可以避免在命令行中输入用户名和密码，但这样做请一定要确定MySQL数据库的确有对应用户名的授权信息哟~否则会抛出如下所示的错误:
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)
[root@docker201.oldboyedu.com ~]#
温馨提示:
有关MySQL配置文件默认的读取顺序如下所示:
[root@docker201.oldboyedu.com ~]# mysql --help --verbose | grep my.cnf | tail -1
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
[root@docker201.oldboyedu.com ~]#
备份参数
备份数据库:
-A:
全量备份。
-B:
部分备份数据库。
备份表:
如果只备份某个数据库下的某张表或多张表，则无需指定任何参数，语法格式为: "数据库名称 表1[ 表2 表3 ...]"。
面试题:
请问以下两个命令的备份结果有什么区别呢?
mysqldump -S /tmp/mysql23307.sock -B oldboyedu > /oldboyedu/backup/oldboyedu.sql
mysqldump -S /tmp/mysql23307.sock oldboyedu > /oldboyedu/backup/oldboyedu2.sql
分析如下:
mysqldump -S /tmp/mysql23307.sock -B oldboyedu > /oldboyedu/backup/oldboyedu.sql
相比于下面的SQL语句，该命令会多出两条SQL，即"CREATE DATABASE oldboyedu;"和"USE oldboyedu;"，即会创建"oldboyedu"数据库，并备份"oldboyedu"数据库下的所有表。
mysqldump -S /tmp/mysql23307.sock oldboyedu > /oldboyedu/backup/oldboyedu2.sql
该命令可以理解为"mysqldump -S /tmp/mysql23307.sock oldboyedu * > /oldboyedu/backup/oldboyedu2.sql"，即备份"oldboyedu"数据库下的所有表，但并不会创建"oldboyedu"数据库。
综上所述，基于这种方式备份，我们在应用备份的文件进行数据恢复时，需要手动创建"oldboyedu"数据库，并使用USE命令到"oldboyedu"库下再进行恢复操作。
高级备份参数
--master-data:
该选项参数可以在备份时自动记录二进制日志的位置点和文件名称。该参数有三个值可选，即0，1，2。
当选项的值为0时:
若不指定则默认值就为0，即表示不记录备份时的日志的位置信息。
当选项的值为1时:
会将"CHANGE MASTER TO ..."命令写入到备份文件中。
当选项的值为2时:
会将"CHANGE MASTER TO ..."命令以注释的方式写入到备份文件中。生产环境中，在主从搭建的场景下，通常也是将"--master-data"设置为2。
温馨提示:
该参数备份时会自动将"--lock-all-table"功能打开，这意味着会触发全局读锁(即"FTWRL")，在数据备份完成之后，会将"--lock-tables"功能关闭，以达到释放锁的目的。
但对于生产环境中的InnoDB存储引擎没有必要给所有的表加锁，因此我们可以考虑使用"--single-transaction"来控制以减少锁表时间。
--single-transaction:
在支持多版本(multiversioning)并发控制的InnoDB存储引擎中，会开启一个独立的事物，获取一个一致性快照(consistent snapshot)进行备份，而创建快照无需锁表。
需要注意的是，其他连接不应该使用DDL操作(如"ALTER TABLE","DROP TABLE","RENAME TABLE"等)，因为一致性快照并不是与它们隔离的。
-R:
在备份时一起备份存储过程和函数。
-E:
备份事件。
--triggers:
备份触发器。
--max_allowed_packet:
max_allowed_packet既属于MySQL服务端参数，也属于MySQL客户端参数。
如果客户端执行DML语句，数据由客户端发往服务端，比如INSERT超过1000w条数据，如果服务端设置"max_allowed_packet"过小就会抛出异常。
而MySQL5 .7及以下版本的服务端"max_allowed_packet"的默认值是4MB。
而MySQL8.0及以上版本的服务端"max_allowed_packet"的默认值是64MB。3.基于mysqldump工具进行备份的实操案例
全量备份案例
(1)创建备份目录
[root@docker201.oldboyedu.com ~]# install -g mysql -o mysql -d /oldboyedu/backup
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/
总用量 0
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/ -d
drwxr-xr-x 2 mysql mysql 6 2月 12 14:26 /oldboyedu/backup/
[root@docker201.oldboyedu.com ~]#
(2)全量备份数据
[root@docker201.oldboyedu.com ~]# mysqldump -S /tmp/mysql23307.sock -A > /oldboyedu/backup/full.sql # 下面的输出参数我们暂时先忽略，因为此处我们要演示的是"全量备份"。
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to re
store GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /oldboyedu/backup/full.sql
-rw-r--r-- 1 root root 11M 2月 12 14:28 /oldboyedu/backup/full.sql
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# head -50 /oldboyedu/backup/full.sql # 请仔细观察备份的语句，不难发现，在做恢复时，是先删除已经存在的表后再进行恢复，即覆盖恢复数据的策略。
-- MySQL dump 10.13 Distrib 5.7.31, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.7.31-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
--
-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED='ecaf563f-5345-11eb-a106-000c29820c67:1-100'; # 值得注意的是，如果我们备份时将"--set-gtid-purged=OFF"加上(若不加则默认值为AUTO)，则不会有该行信息，那么急于GTID的主从复制可能会会出现问题！
--
-- Current Database: `cdh`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cdh` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `cdh`;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user`
--
LOCK TABLES `user` WRITE;
[root@docker201.oldboyedu.com ~]#
只备份部分数据库案例
(1)查看全量备份数据库生成的SQL文件大小:
[root@docker201.oldboyedu.com ~]# ll -h /oldboyedu/backup/full.sql
-rw-r--r-- 1 root root 11M 2月 12 14:28 /oldboyedu/backup/full.sql
[root@docker201.oldboyedu.com ~]#
(2)只备份"cdh","hdp"和"hive"这三个数据库。
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdh |
| cmdb |
| hdp |
| hive |
| mysql |
| performance_schema |
| school |
| sys |
| world |
| oldboyedu |
| yoolia_mall |
| zabbix |
+--------------------+
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysqldump -S /tmp/mysql23307.sock -B cdh hdp hive > /oldboyedu/backup/db.sql # 只备份"cdh","hdp"和"hive"这三个数据库。
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to re
store GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /oldboyedu/backup/db.sql
-rw-r--r-- 1 root root 5.1K 2月 12 14:34 /oldboyedu/backup/db.sql
[root@docker201.oldboyedu.com ~]#
只备份某个数据库的单表或多表
(1)查看"oldboyedu"数据库下存在的表:
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock -e "SHOW TABLES FROM oldboyedu"
+-----------------------+
| Tables_in_oldboyedu |
+-----------------------+
| call_police |
| staff |
| student |
| t1 |
+-----------------------+
[root@docker201.oldboyedu.com ~]#
(2)只备份"oldboyedu"数据库下的"call_police"和"student"这两张表:
[root@docker201.oldboyedu.com ~]# mysqldump -S /tmp/mysql23307.sock oldboyedu call_police student > /oldboyedu/backup/oldboyedu.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to re
store GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /oldboyedu/backup/full.sql
-rw-r--r-- 1 root root 11M 2月 12 14:28 /oldboyedu/backup/full.sql
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /oldboyedu/backup/oldboyedu.sql
-rw-r--r-- 1 root root 9.5M 2月 12 14:51 /oldboyedu/backup/oldboyedu.sql
[root@docker201.oldboyedu.com ~]#
备份时自动记录二进制日志的位置点和文件名称
场景案例:
某DBA运维人员每周日"23:00"对线上业务业务做全量备份，周一到周日基于binlog做增量备份，所有备份都是完整的，并没有损坏的文件。在周三时，有一个核心运维人员对线上的业务进行了删库操作。
接下里需要身为DBA的你快速准确的恢复业务数据，请说一下你的基本思路。
恢复思路:
首先恢复最新一次的全量备份，而后通过binlog来恢复所需的数据。
恢复过程的痛点:
binlog日志的截取是这次操作的痛点，终点很好定位，就是在DROP操作之前的位置点。而起点该如何准确定位呢？
解决痛点方案:
(1)在备份开始时，手动切割日志:
这种方案可以在备份时需要手动切割日志(可以通过"-F"选项)，那么备份点之后的所有日志均会写入新生成的日志文件中。但该方案会生成大量的二进制日志文件，因此生产环境中很少使用。
(2)在备份开始时，自动记录日志文件位置信息:
这种方案无需手动切割日志，而是备份时指定"--master-data=2"参数，会将备份时的日志位置点记录下来。
方案一实操案例(生产环境中不推荐使用):
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/binlog/
总用量 64
-rw-r----- 1 mysql mysql 177 2月 4 17:54 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 177 2月 4 17:59 oldboyedu-mysqld-binary.000002
-rw-r----- 1 mysql mysql 177 2月 4 18:00 oldboyedu-mysqld-binary.000003
-rw-r----- 1 mysql mysql 1974 2月 4 20:46 oldboyedu-mysqld-binary.000004
-rw-r----- 1 mysql mysql 794 2月 4 20:49 oldboyedu-mysqld-binary.000005
-rw-r----- 1 mysql mysql 794 2月 4 20:51 oldboyedu-mysqld-binary.000006
-rw-r----- 1 mysql mysql 30110 2月 11 06:40 oldboyedu-mysqld-binary.000007
-rw-r----- 1 mysql mysql 573 2月 11 07:42 oldboyedu-mysqld-binary.000008
-rw-r----- 1 mysql mysql 552 2月 11 06:40 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysqldump -S /tmp/mysql23307.sock -A -F > /oldboyedu/backup/full.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to re
store GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/binlog/ # 不难发现会滚多过个二进制文件哟~滚动文件的个数取决于非MySQL系统内置的系统库的多少。
总用量 104
-rw-r----- 1 mysql mysql 177 2月 4 17:54 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 177 2月 4 17:59 oldboyedu-mysqld-binary.000002
-rw-r----- 1 mysql mysql 177 2月 4 18:00 oldboyedu-mysqld-binary.000003
-rw-r----- 1 mysql mysql 1974 2月 4 20:46 oldboyedu-mysqld-binary.000004
-rw-r----- 1 mysql mysql 794 2月 4 20:49 oldboyedu-mysqld-binary.000005
-rw-r----- 1 mysql mysql 794 2月 4 20:51 oldboyedu-mysqld-binary.000006
-rw-r----- 1 mysql mysql 30110 2月 11 06:40 oldboyedu-mysqld-binary.000007
-rw-r----- 1 mysql mysql 636 2月 12 19:17 oldboyedu-mysqld-binary.000008
-rw-r----- 1 mysql mysql 257 2月 12 19:17 oldboyedu-mysqld-binary.000009
-rw-r----- 1 mysql mysql 257 2月 12 19:17 oldboyedu-mysqld-binary.000010
-rw-r----- 1 mysql mysql 257 2月 12 19:17 oldboyedu-mysqld-binary.000011
-rw-r----- 1 mysql mysql 257 2月 12 19:17 oldboyedu-mysqld-binary.000012
-rw-r----- 1 mysql mysql 257 2月 12 19:17 oldboyedu-mysqld-binary.000013
-rw-r----- 1 mysql mysql 257 2月 12 19:17 oldboyedu-mysqld-binary.000014
-rw-r----- 1 mysql mysql 257 2月 12 19:17 oldboyedu-mysqld-binary.000015
-rw-r----- 1 mysql mysql 257 2月 12 19:17 oldboyedu-mysqld-binary.000016
-rw-r----- 1 mysql mysql 257 2月 12 19:17 oldboyedu-mysqld-binary.000017
-rw-r----- 1 mysql mysql 194 2月 12 19:17 oldboyedu-mysqld-binary.000018
-rw-r----- 1 mysql mysql 1242 2月 12 19:17 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
方案二实操案例(生产环境中推荐使用):
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/binlog/
总用量 64
-rw-r----- 1 mysql mysql 177 2月 4 17:54 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 177 2月 4 17:59 oldboyedu-mysqld-binary.000002
-rw-r----- 1 mysql mysql 177 2月 4 18:00 oldboyedu-mysqld-binary.000003
-rw-r----- 1 mysql mysql 1974 2月 4 20:46 oldboyedu-mysqld-binary.000004
-rw-r----- 1 mysql mysql 794 2月 4 20:49 oldboyedu-mysqld-binary.000005
-rw-r----- 1 mysql mysql 794 2月 4 20:51 oldboyedu-mysqld-binary.000006
-rw-r----- 1 mysql mysql 30110 2月 11 06:40 oldboyedu-mysqld-binary.000007
-rw-r----- 1 mysql mysql 573 2月 11 07:42 oldboyedu-mysqld-binary.000008
-rw-r----- 1 mysql mysql 552 2月 11 06:40 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysqldump -S /tmp/mysql23307.sock -A --master-data=1 > /oldboyedu/backup/full1.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to re
store GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# head -30 /oldboyedu/backup/full1.sql | tail -1 # 注意哈，生成的是一条执行命令哟~
CHANGE MASTER TO MASTER_LOG_FILE='oldboyedu-mysqld-binary.000008', MASTER_LOG_POS=573;
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysqldump -S /tmp/mysql23307.sock -A --master-data=2 > /oldboyedu/backup/full2.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to re
store GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# head -30 /oldboyedu/backup/full2.sql | tail -1 # 注意哈，生成的是一条执行命令的注释信息哟~
-- CHANGE MASTER TO MASTER_LOG_FILE='oldboyedu-mysqld-binary.000008', MASTER_LOG_POS=573;
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/logs/mysql23307/binlog/
总用量 64
-rw-r----- 1 mysql mysql 177 2月 4 17:54 oldboyedu-mysqld-binary.000001
-rw-r----- 1 mysql mysql 177 2月 4 17:59 oldboyedu-mysqld-binary.000002
-rw-r----- 1 mysql mysql 177 2月 4 18:00 oldboyedu-mysqld-binary.000003
-rw-r----- 1 mysql mysql 1974 2月 4 20:46 oldboyedu-mysqld-binary.000004
-rw-r----- 1 mysql mysql 794 2月 4 20:49 oldboyedu-mysqld-binary.000005
-rw-r----- 1 mysql mysql 794 2月 4 20:51 oldboyedu-mysqld-binary.000006
-rw-r----- 1 mysql mysql 30110 2月 11 06:40 oldboyedu-mysqld-binary.000007
-rw-r----- 1 mysql mysql 573 2月 11 07:42 oldboyedu-mysqld-binary.000008
-rw-r----- 1 mysql mysql 552 2月 11 06:40 oldboyedu-mysqld-binary.index
[root@docker201.oldboyedu.com ~]# 对于InnoDB存储引擎表备份时，开启一个独立事务，获取一致性快照，进行备份
对于InnoDB存储引擎表备份时，使用"--single-transaction"参数选项可以开启一个独立事务，获取一致性快照，进行备份
[root@docker201.oldboyedu.com ~]# mysqldump -S /tmp/mysql23307.sock -A --master-data=2 --single-transaction > /oldboyedu/backup/full.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to re
store GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@docker201.oldboyedu.com ~]#
温馨提示:
在备份InnoDB存储引擎的表时，我们通常会将"--master-data"和"--single-transaction"两个参数搭配使用哟~
在备份时一起备份存储过程，函数，事件，和触发器
如下所示，在备份时，如果不指定"-E","-R","--triggers"选项参数，则可能在备份时会输出"Warning"级别的警告信息哟:
[root@docker201.oldboyedu.com ~]# mysqldump -S /tmp/mysql23307.sock -A --master-data=2 --single-transaction > /oldboyedu/backup/full.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to re
store GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /oldboyedu/backup/full.sql
-rw-r--r-- 1 root root 11M 2月 12 19:39 /oldboyedu/backup/full.sql
[root@docker201.oldboyedu.com ~]#
如下所示，在生产环境中，我们通常会采用下面的常用参数来备份MySQL数据库:
[root@docker201.oldboyedu.com ~]# mysqldump -S /tmp/mysql23307.sock -A --master-data=2 --single-transaction -E -R --triggers > /oldboyedu/backup/full.sql
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /oldboyedu/backup/full.sql
-rw-r--r-- 1 root root 11M 2月 12 19:45 /oldboyedu/backup/full.sql
[root@docker201.oldboyedu.com ~]#
备份时指定客户端接收数据包大小限制
(1)如下所示，MySQL 5.7版本"max_allowed_packet"参数的默认值为"4MB"。当然，我们也可以修改服务端默认的数据包大小哟~
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SELECT @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
| 4194304 |
+----------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT 4194304/1024;
+--------------+
| 4194304/1024 |
+--------------+
| 4096.0000 |
+--------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT 4194304/1024/1024;
+-------------------+
| 4194304/1024/1024 |
+-------------------+
| 4.00000000 |
+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> QUIT
Bye
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# vim /oldboyedu/softwares/mysql23307/my.cnf
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# tail -1 /oldboyedu/softwares/mysql23307/my.cnf
max_allowed_packet=128M
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SELECT @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
| 134217728 |
+----------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT 134217728/1024/1024;
+---------------------+
| 134217728/1024/1024 |
+---------------------+
| 128.00000000 |
+---------------------+
1 row in set (0.00 sec)
mysql>
(2)在备份时，我们通常会指定"--max_allowed_packet"选项参数。
[root@docker201.oldboyedu.com ~]# mysqldump -S /tmp/mysql23307.sock -A --master-data=2 --single-transaction -E -R --triggers --max_allowed_packet=64M > /oldboyedu/backup/full.sql
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /oldboyedu/backup/full.sql
-rw-r--r-- 1 root root 11M 2月 12 20:00 /oldboyedu/backup/full.sql
[root@docker201.oldboyedu.com ~]#
温馨提示:
(1)max_allowed_packet参数无论是在MySQL客户端还是在MySQL服务端，都有同名参数控制，如果在"[mysqld]"标签中设置，表示配置的是服务端，这意味着客户端执行DML操作，允许最大的packet数默认是4MB。
(2)--max_allowed_packet参数在mysqldump通常设置64MB即可，如果数据表较大，也可以修改为128MB测试;
(3)如果报错说是由于packet较大导致的错误要先分析到底是客户端还是服务端设置较小，找到原因后在还原即可;
4.基于mysqldump，mysqlbinlog工具进行恢复的实操案例
案例场景描述
场景描述:
基础环境:
(1)CentOS Linux release 7.9.2009 (Core)
(2)MySQL Server version: 5.7.31
(3)Nginx + Tomcat
业务规模:
MySQL数据总量1.2TB，每天50-300MB数据增长。
备份策略:
mysqldump每天全备，binlog定时备份。
故障说明:
周三下午18:00核心业务库被误删除。
恢复思路:
(1)前端挂维护页面，由于数据被损坏，这就得DBA来进行数据恢复，恢复过程是无法获取数据的，因此开发的文档必须提前定义好数据库维护相关的页面;
(2)找测试库先进行数据恢复测试;
(3)恢复周二的全备数据;
(4)截取周三上午18:00点之前的binlog并用于数据恢复;
(5)测试核心业务功能是否正常;
(6)恢复业务:
方案一:
将测试库恢复的数据导出，而后对生产库进行恢复操作。这意味着测试库已经恢复正常，但还需要占用额外的时间来恢复生产库。
方案二:
直接用测试库临时充当生产库先跑着，与此同时开始恢复生产库数据，而后在业务不繁忙的情况下在切换回生产库。
模拟原始数据
mysql> CREATE DATABASE demo CHARSET utf8mb4;
Query OK, 1 row affected (0.28 sec)
mysql>
mysql> USE demo;
Database changed
mysql>
mysql> CREATE TABLE t1 (id int);
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO t1 VALUES (100), (200), (300);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM demo.t1;
+------+
| id |
+------+
| 100 |
| 200 |
| 300 |
+------+
3 rows in set (0.00 sec)
mysql>
模拟周二晚上全量备份
[root@docker201.oldboyedu.com ~]# mysqldump -S /tmp/mysql23307.sock -A --master-data=2 --single-transaction -E -R --triggers --max_allowed_packet=64M > /oldboyedu/backup/full-`date +%F`.sql
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /oldboyedu/backup/full-*
-rw-r--r-- 1 root root 11M 2月 12 21:32 /oldboyedu/backup/full-2021-02-12.sql
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# egrep "^SET @@SESSION.SQL_LOG_BIN=" /oldboyedu/backup/full-2021-02-12.sql # 很明显，使用mysqldump进行备份时，会自动将"SQL_LOG_BIN"设置为0，即临时关闭二进制日志功能。
SET @@SESSION.SQL_LOG_BIN= 0;
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# egrep "^-- CHANGE" /oldboyedu/backup/full-2021-02-12.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='oldboyedu-mysqld-binary.000019', MASTER_LOG_POS=795;
[root@docker201.oldboyedu.com ~]#
模拟周三白天的数据变化
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| demo |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> CREATE TABLE t2 (id int);
Query OK, 0 rows affected (0.29 sec)
mysql>
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO t2 VALUES (10), (20), (30);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM demo.t2;
+------+
| id |
+------+
| 10 |
| 20 |
| 30 |
+------+
3 rows in set (0.00 sec)
mysql>
模拟删除核心库
mysql> DROP DATABASE demo;
Query OK, 2 rows affected (0.02 sec)
mysql>
开始恢复数据
(1)检查全备，找到备份文件的名称及备份点的起始位置信息
[root@docker201.oldboyedu.com ~]# egrep "^-- CHANGE" /oldboyedu/backup/full-2021-02-12.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='oldboyedu-mysqld-binary.000019', MASTER_LOG_POS=795;
[root@docker201.oldboyedu.com ~]#
(2)恢复全备
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> SET sql_log_bin=0; # 温馨提示：该步骤咱们可以省略，因为我们开启GTID后使用mysqldump工具在备份时，已经将该参数设置为0啦~只不过基于二进制日志恢复的确是需要执行该步骤！当然，执行该命令是为了兼容对于没有开启GTID的情况。
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> SOURCE /oldboyedu/backup/full-2021-02-12.sql
...
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SHOW TABLES FROM demo; # 很明显demo数据库恢复了，但仅回复了部分数据，我们只看到了t1表，此时t2表并没有恢复哟~
+----------------+
| Tables_in_demo |
+----------------+
| t1 |
+----------------+
1 row in set (0.01 sec)
mysql>
(3)查看现有的事件日志位置信息，获取结束位置
mysql> SHOW MASTER STATUS;
+----------------------------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+--------------------------------------------+
| oldboyedu-mysqld-binary.000019 | 1378 | | | ecaf563f-5345-11eb-a106-000c29820c67:1-106 |
+----------------------------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SHOW BINLOG EVENTS IN 'oldboyedu-mysqld-binary.000019'; # 很明显，删除数据库的起始位置为1286，但我们要将GTID的事件记录(Pos=1221)过滤掉，因此结束位置点应该为"Pos=1221"
+----------------------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
| oldboyedu-mysqld-binary.000019 | 4 | Format_desc | 7 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| oldboyedu-mysqld-binary.000019 | 123 | Previous_gtids | 7 | 194 | ecaf563f-5345-11eb-a106-000c29820c67:1-100 |
| oldboyedu-mysqld-binary.000019 | 194 | Gtid | 7 | 259 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:101' |
| oldboyedu-mysqld-binary.000019 | 259 | Query | 7 | 369 | CREATE DATABASE demo CHARSET utf8mb4 |
| oldboyedu-mysqld-binary.000019 | 369 | Gtid | 7 | 434 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:102' |
| oldboyedu-mysqld-binary.000019 | 434 | Query | 7 | 532 | use `demo`; CREATE TABLE t1 (id int) |
| oldboyedu-mysqld-binary.000019 | 532 | Gtid | 7 | 597 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:103' |
| oldboyedu-mysqld-binary.000019 | 597 | Query | 7 | 669 | BEGIN |
| oldboyedu-mysqld-binary.000019 | 669 | Table_map | 7 | 714 | table_id: 108 (demo.t1) |
| oldboyedu-mysqld-binary.000019 | 714 | Write_rows | 7 | 764 | table_id: 108 flags: STMT_END_F |
| oldboyedu-mysqld-binary.000019 | 764 | Xid | 7 | 795 | COMMIT /* xid=18 */ |
| oldboyedu-mysqld-binary.000019 | 795 | Gtid | 7 | 860 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:104' |
| oldboyedu-mysqld-binary.000019 | 860 | Query | 7 | 958 | use `demo`; CREATE TABLE t2 (id int) |
| oldboyedu-mysqld-binary.000019 | 958 | Gtid | 7 | 1023 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:105' |
| oldboyedu-mysqld-binary.000019 | 1023 | Query | 7 | 1095 | BEGIN |
| oldboyedu-mysqld-binary.000019 | 1095 | Table_map | 7 | 1140 | table_id: 170 (demo.t2) |
| oldboyedu-mysqld-binary.000019 | 1140 | Write_rows | 7 | 1190 | table_id: 170 flags: STMT_END_F |
| oldboyedu-mysqld-binary.000019 | 1190 | Xid | 7 | 1221 | COMMIT /* xid=996 */ |
| oldboyedu-mysqld-binary.000019 | 1221 | Gtid | 7 | 1286 | SET @@SESSION.GTID_NEXT= 'ecaf563f-5345-11eb-a106-000c29820c67:106' |
| oldboyedu-mysqld-binary.000019 | 1286 | Query | 7 | 1378 | DROP DATABASE demo |
+----------------------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
20 rows in set (0.00 sec)
mysql>
(4)使用mysqlbinlog工具截取日志(生产环境中如何开启了GTID功能，切记要添加"--skip-gtids"参数哟~)
基于"--start-position"和"--stop-position"截取日志:
[root@docker201.oldboyedu.com ~]# mysqlbinlog --skip-gtids --start-position=795 --stop-position=1221 /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000019 > /tmp/recover_de
mo.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /tmp/recover_demo.log
-rw-r--r-- 1 root root 2078 2月 12 22:47 /tmp/recover_demo.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /tmp/recover_demo.log
-rw-r--r-- 1 root root 2.1K 2月 12 22:47 /tmp/recover_demo.log
[root@docker201.oldboyedu.com ~]#
基于"--include-gtids"截取日志:
[root@docker201.oldboyedu.com ~]# mysqlbinlog --skip-gtids --include-gtids='ecaf563f-5345-11eb-a106-000c29820c67:104-105' /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000019 > /tmp/recover_demo2.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /tmp/recover_demo2.log
-rw-r--r-- 1 root root 2168 2月 12 22:52 /tmp/recover_demo2.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /tmp/recover_demo2.log
-rw-r--r-- 1 root root 2.2K 2月 12 22:52 /tmp/recover_demo2.log
[root@docker201.oldboyedu.com ~]#
(5)通过截取的日志文件"/tmp/recover_demo.log"恢复数据
mysql> SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> SHOW TABLES FROM demo;
+----------------+
| Tables_in_demo |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql>
mysql> SOURCE /tmp/recover_demo.log; # 基于"--start-position"和"--stop-position"截取日志恢复数据。
...
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SHOW TABLES FROM demo; # 很明显，数据恢复成功啦~
+----------------+
| Tables_in_demo |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM t2;
+------+
| id |
+------+
| 10 |
| 20 |
| 30 |
+------+
3 rows in set (0.00 sec)
mysql>
mysql> DROP TABLE t2; # 再次删除数据，而后基于GTID的方式恢复数据
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> SHOW TABLES FROM demo;
+----------------+
| Tables_in_demo |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql>
mysql> SOURCE /tmp/recover_demo2.log; # 注意哈，此处我们基于"--include-gtids"截取日志文件恢复数据。
...
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SHOW TABLES FROM demo;
+----------------+
| Tables_in_demo |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM t2;
+------+
| id |
+------+
| 10 |
| 20 |
| 30 |
+------+
3 rows in set (0.00 sec)
mysql>
温馨提示:
基于mysqldump恢复数据是覆盖恢复，因此强烈建议大家现在测试库进行恢复，而后根据测试库的数据和开发打配合，验证数据是否完全恢复，而后在将测试库的数据导入到生产库中。
小试牛刀
请物理删除MySQL所有数据，并尝试恢复。
如下所示，执行"rm -rf /oldboyedu/data/mysql23307/*"命令即可删除所有数据:
[root@docker201.oldboyedu.com ~]# grep datadir /oldboyedu/softwares/mysql23307/my.cnf
datadir=/oldboyedu/data/mysql23307
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307
总用量 450652
-rw-r----- 1 mysql mysql 56 1月 10 21:15 auto.cnf
-rw------- 1 mysql mysql 1676 1月 10 21:15 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 1月 10 21:15 ca.pem
drwxr-x--- 2 mysql mysql 52 2月 12 21:51 cdh
-rw-r--r-- 1 mysql mysql 1112 1月 10 21:15 client-cert.pem
-rw------- 1 mysql mysql 1676 1月 10 21:15 client-key.pem
drwxr-x--- 2 mysql mysql 52 2月 12 21:51 cmdb
drwxr-x--- 2 mysql mysql 76 2月 12 22:53 demo
-rw-r----- 1 mysql mysql 6 2月 12 20:11 docker201.oldboyedu.pid
drwxr-x--- 2 mysql mysql 132 2月 12 21:51 hdp
drwxr-x--- 2 mysql mysql 52 2月 12 21:51 hive
-rw-r----- 1 mysql mysql 2567 2月 12 20:11 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 2月 12 22:55 ibdata1
-rw-r----- 1 mysql mysql 134217728 1月 26 22:50 ibdata2
-rw-r----- 1 mysql mysql 134217728 1月 26 22:50 ibdata3
-rw-r----- 1 mysql mysql 50331648 2月 12 22:55 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 2月 12 21:51 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 2月 12 21:32 ibtmp1
drwxr-x--- 2 mysql mysql 4096 2月 12 21:51 mysql
drwxr-x--- 2 mysql mysql 8192 1月 10 21:15 performance_schema
-rw------- 1 mysql mysql 1676 1月 10 21:15 private_key.pem
-rw-r--r-- 1 mysql mysql 452 1月 10 21:15 public_key.pem
drwxr-x--- 2 mysql mysql 182 2月 12 21:51 school
-rw-r--r-- 1 mysql mysql 1112 1月 10 21:15 server-cert.pem
-rw------- 1 mysql mysql 1676 1月 10 21:15 server-key.pem
drwxr-x--- 2 mysql mysql 8192 1月 10 21:15 sys
drwxr-x--- 2 mysql mysql 144 2月 12 21:51 world
drwxr-x--- 2 mysql mysql 166 2月 12 21:51 oldboyedu
-rw-r----- 1 mysql mysql 15885 2月 3 22:26 oldboyedu-mysqld-err.log
drwxr-x--- 2 mysql mysql 4096 2月 12 21:51 yoolia_mall
drwxr-x--- 2 mysql mysql 20 2月 4 06:19 zabbix
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# rm -rf /oldboyedu/data/mysql23307/*
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307
总用量 0
[root@docker201.oldboyedu.com ~]#
温馨提示:
(1)在删除数据时，请确保你的数据文件和日志文件是分开存储的。
(2)生产环境中强烈建议将日志文件和数据文件分开存储，因为尽管数据存储的盘损坏，我们可以基于日志恢复。但如果我们将数据和日志放在同一块磁盘上存储时，这意味着我们无法基于日志来进行恢复。5.mysqldump的使用总结
做全量备份实例:(有关参数说明请参考"mysqldump工具的核心参数")
mysqldump -S /tmp/mysql23307.sock -A --master-data=2 --single-transaction -E -R --triggers --max_allowed_packet=64M > /oldboyedu/backup/full.sql
mysqldump工具的使用场景:
建议选择数据量较小的场景，比如100GB左右的数据选择mysqldump是一个不错的方案，如果数据量较大，比如超过200GB的话，建议采用物理备份，而当数据量巨大时(通常指TB级别甚至更大数据量场景)建议采用分布式架构集合mysqldump工具进行备份。
如果数据在100GB以内，使用mysqldump备份时间大概在1小时以内，就算数据量能达到200GB左右，备份时间应该也控制在1-2小时之间。在生产环境中，恢复时间可能是备份时间的双倍是很常见的情况，请耐心等待一下。
mysqldump工具的优缺点:
优点:
(1)可读性比较强;
(2)相比物理备份压缩比更高，即节省存储空间;
(3)MySQL内置的工具，无需下载安装;
(4)相比物理备份移植性更强，可以跨存储引擎;
缺点:
(1)相比物理备份，逻辑备份消耗的时间相对较长;
(2)恢复时间更长，通常是备份时间的双倍甚至更长的时间;
从mysqldump全备中获取库和表的备份:
(1)获得表结构
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q' full.sql>createtable.sql
(2)获得INSERT INTO 语句，用于数据的恢复
grep -i 'INSERT INTO `city`' full.sqll >data.sql &
(3)获取单库的备份
sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql
三.MySQL物理备份工具Percona Xtrabackup实战
1.Percona Xtrabackup安装
Percona Xtrabackup简介
Percona Xtrabackup（简称PXB）工具采用perl语言开发。它是一款物理备份工具，简单来讲就是用来拷贝Linux本地的数据文件。因此使用它备份MySQL数据瓶颈在于磁盘的I/O速度。
对于InnoDB存储引擎的表:
(1)Percona Xtrabackup工具采用热备份方式，即无需锁表进行备份，这种备份方式在业务正常发生的时候，影响较小的备份方式。
(2)Percona Xtrabackup的工作流程如下所示:
1)先执行检查点(checkpoint)，将已提交的数据页刷写到磁盘，并记录一个LSN编号;
2)拷贝InnoDB表相关的文件，例如:ibdata1,"*.frm","*.idb"等文件;
3)备份期间产生新的数据变化的redo也会备份走;
4)再次统计LSN编号，写入到专用文件;
5)将二进制日志位置记录下来;
6)将所有备份文件统一存放在一个目录下;
对于非InnoDB存储引擎的表:
(1)Percona Xtrabackup工具采用温备份方式，即需要暂时性锁表备份，对业务是有一定影响的。幸运的是，对于非InnoDB表相对较少，比如MySQL内置的系统表均是MyIsam，因此只会有短暂的锁表现象。
(2)Percona Xtrabackup的工作流程如下所示:
1)触发全局读锁(即"FTWRL")，而后执行检查点(checkpoint)，将已提交的数据页刷写到磁盘，并记录一个LSN编号;
2)拷贝非InnoDB表的数据;
3)拷贝数据完成后需要解锁;
4)将二进制日志位置记录下来;
5)将所有备份文件统一存放在一个目录下;
Percona XtraBackup工具的官方下载地址:
https://www.percona.com/downloads/
Percona XtraBackup 2.4工具官方文档:
https://www.percona.com/doc/percona-xtrabackup/2.4/index.html
温馨提示:
如下图所示，如果我们使用的是MySQL 5.7版本，建议选择最新Percona Xtrabackup 2.4的版本哟~
基于yum的方式安装Percona XtraBackup工具
Percona XtraBackup工具的安装流程:
(1)安装软件源
[root@docker201.oldboyedu.com ~]# wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo
(2)安装依赖包
[root@docker201.oldboyedu.com ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
(3)安装所有Percona XtraBackup开头的软件包
[root@docker201.oldboyedu.com ~]# yum -y install percona-xtrabackup-*.rpm
手动下载安装并安装Percona XtraBackup工具:
(1)最新版本"Percona XtraBackup 2.4"工具下载地址:
https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
(2)如下图所示，下载"percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm"软件包即可，而后基于yum命令安装
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm
(3)本地安装"Percona-XtraBackup-2.4.21"工具
[root@docker201.oldboyedu.com ~]# ll -h percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 7.7M 11月 11 15:45 percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# yum -y localinstall percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm
温馨提示:
如下图所示，我们可以下载多个软件包:
一起下载所有软件包:
Percona-XtraBackup-2.4.21-r5988af5-el7-x86_64-bundle.tar 61.9 MB
单独下载软件包:
percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm 7.6 MB
生产场景用的版本，我们下载该版本即可。
percona-xtrabackup-24-debuginfo-2.4.21-1.el7.x86_64.rpm 38.0 MB
拥有调试功能的软件包。
percona-xtrabackup-test-24-2.4.21-1.el7.x86_64.rpm 16.3 MB
用于测试的软件包。
2.Percona Xtrabackup全量备份实战应用
使用Percona Xtrabackup工具的前提
(1)启动数据库,因为XBK需要连接数据库去读取当前数据库实例有效的数据库名称及对应的表名称;
(2)能连接上数据库，还需要再"/etc/my.cnf"配置文件指定"client":
[root@docker201.oldboyedu.com ~]# vim /etc/my.cnf
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /etc/my.cnf
[client]
socket=/tmp/mysql23307.sock
[root@docker201.oldboyedu.com ~]#
(3)Percona Xtrabackup属于服务端工具，因此不要想着将它向mysqldump工具一样，安装再客户端也能进行备份操作，我们应该将Percona Xtrabackup工具直接安装在MySQL服务端实例上;
使用innobackupex命令全量备份案例
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/xbk # 注意哈，我们已经创建了"/oldboyedu/backup"目录，但并没有创建"/oldboyedu/backup/xbk"目录哟~
ls: 无法访问/oldboyedu/backup/xbk: 没有那个文件或目录
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# innobackupex /oldboyedu/backup/xbk # 很明显，使用innobackupex工具备份时可以自动创建"/oldboyedu/backup/xbk"目录哟~
xtrabackup: recog0nized server arguments:
xtrabackup: recognized client arguments:
210213 11:57:31 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!".
210213 11:57:31 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql23307.sock' (using password: NO).
210213 11:57:31 version_check Connected to MySQL server
210213 11:57:31 version_check Executing a version check against the server...
210213 11:57:31 version_check Done.
210213 11:57:31 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: /tmp/mysql23307.sock
Using server version 5.7.31-log
innobackupex version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /oldboyedu/data/mysql23307/
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:76M;ibdata2:128M;ibdata3:128M: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
210213 11:57:31 >> log scanned up to (102850175) # 此处记录了一个"LSN=102850175"
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 157 for mysql/help_topic, old maximum was 0
210213 11:57:31 [01] Copying ./ibdata1 to /oldboyedu/backup/xbk/2021-02-13_11-57-31/ibdata1
210213 11:57:32 >> log scanned up to (102850175)
210213 11:57:33 [01] ...done
210213 11:57:33 [01] Copying ./ibdata2 to /oldboyedu/backup/xbk/2021-02-13_11-57-31/ibdata2
210213 11:57:33 >> log scanned up to (102850175)
210213 11:57:34 >> log scanned up to (102850175)
210213 11:57:35 [01] ...done
210213 11:57:35 [01] Copying ./ibdata3 to /oldboyedu/backup/xbk/2021-02-13_11-57-31/ibdata3
210213 11:57:35 >> log scanned up to (102850175)
210213 11:57:36 [01] ...done
...
210213 11:57:37 [01] Copying ./hdp/t5.frm to /oldboyedu/backup/xbk/2021-02-13_11-57-31/hdp/t5.frm
210213 11:57:37 [01] ...done
210213 11:57:37 [01] Copying ./zabbix/db.opt to /oldboyedu/backup/xbk/2021-02-13_11-57-31/zabbix/db.opt
210213 11:57:37 [01] ...done
210213 11:57:37 Finished backing up non-InnoDB tables and files
210213 11:57:37 [00] Writing /oldboyedu/backup/xbk/2021-02-13_11-57-31/xtrabackup_binlog_info
210213 11:57:37 [00] ...done
210213 11:57:37 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '102850166'
xtrabackup: Stopping log copying thread.
.210213 11:57:37 >> log scanned up to (102850175)
210213 11:57:37 Executing UNLOCK TABLES # 释放锁
210213 11:57:37 All tables unlocked # 所有表都释放锁，说明在此之前很多表是有加锁的相关信息，请仔细观察你执行命令的输出哟~
210213 11:57:37 [00] Copying ib_buffer_pool to /oldboyedu/backup/xbk/2021-02-13_11-57-31/ib_buffer_pool
210213 11:57:37 [00] ...done
210213 11:57:37 Backup created in directory '/oldboyedu/backup/xbk/2021-02-13_11-57-31/'
MySQL binlog position: filename 'oldboyedu-mysqld-binary.000019', position '1378', GTID of the last change 'ecaf563f-5345-11eb-a106-000c29820c67:1-106'
210213 11:57:37 [00] Writing /oldboyedu/backup/xbk/2021-02-13_11-57-31/backup-my.cnf
210213 11:57:37 [00] ...done
210213 11:57:37 [00] Writing /oldboyedu/backup/xbk/2021-02-13_11-57-31/xtrabackup_info
210213 11:57:37 [00] ...done
xtrabackup: Transaction log of lsn (102850166) to (102850175) was copied.
210213 11:57:37 completed OK!
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/xbk/ # 备份的目录是基于当时的时间。
总用量 4
drwxr-x--- 15 root root 4096 2月 13 12:01 2021-02-13_12-00-56
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/xbk/2021-02-13_12-00-56/ # 查看备份目录的结构，不难发现多出来了一些列以"xtrabackup_*"开头的文件哟~
总用量 340024
-rw-r----- 1 root root 513 2月 13 12:01 backup-my.cnf
drwxr-x--- 2 root root 52 2月 13 12:01 cdh
drwxr-x--- 2 root root 52 2月 13 12:01 cmdb
drwxr-x--- 2 root root 76 2月 13 12:01 demo
drwxr-x--- 2 root root 132 2月 13 12:01 hdp
drwxr-x--- 2 root root 52 2月 13 12:01 hive
-rw-r----- 1 root root 2567 2月 13 12:01 ib_buffer_pool
-rw-r----- 1 root root 79691776 2月 13 12:00 ibdata1
-rw-r----- 1 root root 134217728 2月 13 12:00 ibdata2
-rw-r----- 1 root root 134217728 2月 13 12:00 ibdata3
drwxr-x--- 2 root root 4096 2月 13 12:01 mysql
drwxr-x--- 2 root root 8192 2月 13 12:01 performance_schema
drwxr-x--- 2 root root 182 2月 13 12:01 school
drwxr-x--- 2 root root 8192 2月 13 12:01 sys
drwxr-x--- 2 root root 144 2月 13 12:01 world
-rw-r----- 1 root root 81 2月 13 12:01 xtrabackup_binlog_info
-rw-r----- 1 root root 141 2月 13 12:01 xtrabackup_checkpoints
-rw-r----- 1 root root 543 2月 13 12:01 xtrabackup_info
-rw-r----- 1 root root 2560 2月 13 12:01 xtrabackup_logfile
drwxr-x--- 2 root root 166 2月 13 12:01 oldboyedu
drwxr-x--- 2 root root 4096 2月 13 12:01 yoolia_mall
drwxr-x--- 2 root root 20 2月 13 12:01 zabbix
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# du -sh /oldboyedu/backup/xbk/2021-02-13_12-00-56/ # 查看备份目录的大小
377M /oldboyedu/backup/xbk/2021-02-13_12-00-56/
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# du -sh /oldboyedu/data/mysql23307/ # 不难发现，备份的目录大小和源目录的大小并不相同
485M /oldboyedu/data/mysql23307/
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307/ # 我们查看数据库的源目录，不难发现，一些日志文件压根就没有备份，而且这些文件也没有必要去备份!
总用量 450652
-rw-r----- 1 mysql mysql 56 1月 10 21:15 auto.cnf
-rw------- 1 mysql mysql 1676 1月 10 21:15 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 1月 10 21:15 ca.pem
drwxr-x--- 2 mysql mysql 52 2月 12 21:51 cdh
-rw-r--r-- 1 mysql mysql 1112 1月 10 21:15 client-cert.pem
-rw------- 1 mysql mysql 1676 1月 10 21:15 client-key.pem
drwxr-x--- 2 mysql mysql 52 2月 12 21:51 cmdb
drwxr-x--- 2 mysql mysql 76 2月 12 22:53 demo
-rw-r----- 1 mysql mysql 6 2月 12 20:11 docker201.oldboyedu.pid
drwxr-x--- 2 mysql mysql 132 2月 12 21:51 hdp
drwxr-x--- 2 mysql mysql 52 2月 12 21:51 hive
-rw-r----- 1 mysql mysql 2567 2月 12 20:11 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 2月 12 22:55 ibdata1
-rw-r----- 1 mysql mysql 134217728 1月 26 22:50 ibdata2
-rw-r----- 1 mysql mysql 134217728 1月 26 22:50 ibdata3
-rw-r----- 1 mysql mysql 50331648 2月 12 22:55 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 2月 12 21:51 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 2月 13 12:00 ibtmp1
drwxr-x--- 2 mysql mysql 4096 2月 12 21:51 mysql
drwxr-x--- 2 mysql mysql 8192 1月 10 21:15 performance_schema
-rw------- 1 mysql mysql 1676 1月 10 21:15 private_key.pem
-rw-r--r-- 1 mysql mysql 452 1月 10 21:15 public_key.pem
drwxr-x--- 2 mysql mysql 182 2月 12 21:51 school
-rw-r--r-- 1 mysql mysql 1112 1月 10 21:15 server-cert.pem
-rw------- 1 mysql mysql 1676 1月 10 21:15 server-key.pem
drwxr-x--- 2 mysql mysql 8192 1月 10 21:15 sys
drwxr-x--- 2 mysql mysql 144 2月 12 21:51 world
drwxr-x--- 2 mysql mysql 166 2月 12 21:51 oldboyedu
-rw-r----- 1 mysql mysql 15885 2月 3 22:26 oldboyedu-mysqld-err.log
drwxr-x--- 2 mysql mysql 4096 2月 12 21:51 yoolia_mall
drwxr-x--- 2 mysql mysql 20 2月 4 06:19 zabbix
[root@docker201.oldboyedu.com ~]#
备份结果查看
如下所示，在使用innobackupex命令备份完成之后，在查看备份目录的结构时，不难发现多出来了一些列以"xtrabackup_*"开头的文件哟~
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/xbk/2021-02-13_12-00-56/
总用量 340024
-rw-r----- 1 root root 513 2月 13 12:01 backup-my.cnf
drwxr-x--- 2 root root 52 2月 13 12:01 cdh
drwxr-x--- 2 root root 52 2月 13 12:01 cmdb
drwxr-x--- 2 root root 76 2月 13 12:01 demo
drwxr-x--- 2 root root 132 2月 13 12:01 hdp
drwxr-x--- 2 root root 52 2月 13 12:01 hive
-rw-r----- 1 root root 2567 2月 13 12:01 ib_buffer_pool
-rw-r----- 1 root root 79691776 2月 13 12:00 ibdata1
-rw-r----- 1 root root 134217728 2月 13 12:00 ibdata2
-rw-r----- 1 root root 134217728 2月 13 12:00 ibdata3
drwxr-x--- 2 root root 4096 2月 13 12:01 mysql
drwxr-x--- 2 root root 8192 2月 13 12:01 performance_schema
drwxr-x--- 2 root root 182 2月 13 12:01 school
drwxr-x--- 2 root root 8192 2月 13 12:01 sys
drwxr-x--- 2 root root 144 2月 13 12:01 world
-rw-r----- 1 root root 81 2月 13 12:01 xtrabackup_binlog_info
-rw-r----- 1 root root 141 2月 13 12:01 xtrabackup_checkpoints
-rw-r----- 1 root root 543 2月 13 12:01 xtrabackup_info
-rw-r----- 1 root root 2560 2月 13 12:01 xtrabackup_logfile
drwxr-x--- 2 root root 166 2月 13 12:01 oldboyedu
drwxr-x--- 2 root root 4096 2月 13 12:01 yoolia_mall
drwxr-x--- 2 root root 20 2月 13 12:01 zabbix
[root@docker201.oldboyedu.com ~]#
如下所示，查看"xtrabackup_binlog_info"文件内容，记录的是备份时二进制日志位置点信息:
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/xbk/2021-02-13_12-00-56/xtrabackup_binlog_info
oldboyedu-mysqld-binary.000019 1378 ecaf563f-5345-11eb-a106-000c29820c67:1-106
[root@docker201.oldboyedu.com ~]#
如下所示，查看"xtrabackup_checkpoints"文件内容，记录的是检查点日志信息，包含备份类型及LSN编号(方便后期做增量备份)等:
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/xbk/2021-02-13_12-00-56/xtrabackup_checkpoints
backup_type = full-backuped # 记录的是备份类型，很明显是全量备份!
from_lsn = 0
to_lsn = 102850166
last_lsn = 102850175
compact = 0
recover_binlog_info = 0
flushed_lsn = 102850175
[root@docker201.oldboyedu.com ~]#
如下所示，查看"xtrabackup_info"文件内容，记录的总体的一些信息:
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/xbk/2021-02-13_12-00-56/xtrabackup_info
uuid = 17049b03-6db0-11eb-b6ff-000c29820c67
name =
tool_name = innobackupex
tool_command = /oldboyedu/backup/xbk
tool_version = 2.4.21
ibbackup_version = 2.4.21
server_version = 5.7.31-log
start_time = 2021-02-13 12:00:56
end_time = 2021-02-13 12:01:03
lock_time = 2
binlog_pos = filename 'oldboyedu-mysqld-binary.000019', position '1378', GTID of the last change 'ecaf563f-5345-11eb-a106-000c29820c67:1-106'
innodb_from_lsn = 0
innodb_to_lsn = 102850166
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
[root@docker201.oldboyedu.com ~]#
如下所示，"xtrabackup_logfile"文件是一个二进制文件，使用文本工具打开强行打开也获取不到我们想要的信息，该文件记录的是Redo日志的信息:
[root@docker201.oldboyedu.com ~]# du -sh /oldboyedu/backup/xbk/2021-02-13_12-00-56/xtrabackup_logfile
4.0K /oldboyedu/backup/xbk/2021-02-13_12-00-56/xtrabackup_logfile
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# file /oldboyedu/backup/xbk/2021-02-13_12-00-56/xtrabackup_logfile
/oldboyedu/backup/xbk/2021-02-13_12-00-56/xtrabackup_logfile: data
[root@docker201.oldboyedu.com ~]#
使用"--no-timestap"参数取消默认备份目录名称，而是手动指定备份目录名称
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/xbk/ # 如下所示，如果我们不指定备份目录的名称，则默认是以日期时间来命名备份目录哟~
总用量 4
drwxr-x--- 15 root root 4096 2月 13 12:01 2021-02-13_12-00-56
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# innobackupex --no-timestap /oldboyedu/backup/xbk/full_`date +%F` # 备份时，我们可以基于"--no-timestap"参数来取消默认的命名规则，而是手动指定文件名称为"full_`date +%F`"。
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments:
210213 12:42:29 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!".
210213 12:42:29 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql23307.sock' (using password: NO).
210213 12:42:29 version_check Connected to MySQL server
210213 12:42:29 version_check Executing a version check against the server...
210213 12:42:29 version_check Done.
210213 12:42:29 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: /tmp/mysql23307.sock
Using server version 5.7.31-log
innobackupex version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /oldboyedu/data/mysql23307/
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:76M;ibdata2:128M;ibdata3:128M: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
210213 12:42:29 >> log scanned up to (102850175)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 157 for mysql/help_topic, old maximum was 0
210213 12:42:29 [01] Copying ./ibdata1 to /oldboyedu/backup/xbk/full_2021-02-13/2021-02-13_12-42-29/ibdata1
210213 12:42:30 [01] ...done
...
210213 12:42:33 [01] Copying ./zabbix/db.opt to /oldboyedu/backup/xbk/full_2021-02-13/2021-02-13_12-42-29/zabbix/db.opt
210213 12:42:33 [01] ...done
210213 12:42:33 Finished backing up non-InnoDB tables and files
210213 12:42:33 [00] Writing /oldboyedu/backup/xbk/full_2021-02-13/2021-02-13_12-42-29/xtrabackup_binlog_info
210213 12:42:33 [00] ...done
210213 12:42:33 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '102850166'
xtrabackup: Stopping log copying thread.
.210213 12:42:33 >> log scanned up to (102850175)
210213 12:42:33 Executing UNLOCK TABLES
210213 12:42:33 All tables unlocked
210213 12:42:33 [00] Copying ib_buffer_pool to /oldboyedu/backup/xbk/full_2021-02-13/2021-02-13_12-42-29/ib_buffer_pool
210213 12:42:33 [00] ...done
210213 12:42:33 Backup created in directory '/oldboyedu/backup/xbk/full_2021-02-13/2021-02-13_12-42-29/'
MySQL binlog position: filename 'oldboyedu-mysqld-binary.000019', position '1378', GTID of the last change 'ecaf563f-5345-11eb-a106-000c29820c67:1-106'
210213 12:42:33 [00] Writing /oldboyedu/backup/xbk/full_2021-02-13/2021-02-13_12-42-29/backup-my.cnf
210213 12:42:33 [00] ...done
210213 12:42:33 [00] Writing /oldboyedu/backup/xbk/full_2021-02-13/2021-02-13_12-42-29/xtrabackup_info
210213 12:42:33 [00] ...done
xtrabackup: Transaction log of lsn (102850166) to (102850175) was copied.
210213 12:42:34 completed OK!
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/xbk/
总用量 4
drwxr-x--- 15 root root 4096 2月 13 12:01 2021-02-13_12-00-56
drwxr-x--- 3 root root 33 2月 13 12:42 full_2021-02-13
[root@docker201.oldboyedu.com ~]#
基于全量备份来恢复数据
(1)对数据库搞破坏，我们直接删除MySQL数据库的所有数据文件:
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307/
总用量 450632
-rw-r----- 1 mysql mysql 56 1月 10 21:15 auto.cnf
-rw------- 1 mysql mysql 1676 1月 10 21:15 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 1月 10 21:15 ca.pem
drwxr-x--- 2 mysql mysql 52 2月 12 21:51 cdh
-rw-r--r-- 1 mysql mysql 1112 1月 10 21:15 client-cert.pem
-rw------- 1 mysql mysql 1676 1月 10 21:15 client-key.pem
drwxr-x--- 2 mysql mysql 52 2月 12 21:51 cmdb
drwxr-x--- 2 mysql mysql 76 2月 12 22:53 demo
drwxr-x--- 2 mysql mysql 132 2月 12 21:51 hdp
drwxr-x--- 2 mysql mysql 52 2月 12 21:51 hive
-rw-r----- 1 mysql mysql 2567 2月 12 20:11 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 2月 12 22:55 ibdata1
-rw-r----- 1 mysql mysql 134217728 1月 26 22:50 ibdata2
-rw-r----- 1 mysql mysql 134217728 1月 26 22:50 ibdata3
-rw-r----- 1 mysql mysql 50331648 2月 12 22:55 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 2月 12 21:51 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 2月 13 12:42 ibtmp1
drwxr-x--- 2 mysql mysql 4096 2月 12 21:51 mysql
drwxr-x--- 2 mysql mysql 8192 1月 10 21:15 performance_schema
-rw------- 1 mysql mysql 1676 1月 10 21:15 private_key.pem
-rw-r--r-- 1 mysql mysql 452 1月 10 21:15 public_key.pem
drwxr-x--- 2 mysql mysql 182 2月 12 21:51 school
-rw-r--r-- 1 mysql mysql 1112 1月 10 21:15 server-cert.pem
-rw------- 1 mysql mysql 1676 1月 10 21:15 server-key.pem
drwxr-x--- 2 mysql mysql 8192 1月 10 21:15 sys
drwxr-x--- 2 mysql mysql 144 2月 12 21:51 world
drwxr-x--- 2 mysql mysql 166 2月 12 21:51 oldboyedu
drwxr-x--- 2 mysql mysql 4096 2月 12 21:51 yoolia_mall
drwxr-x--- 2 mysql mysql 20 2月 4 06:19 zabbix
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# systemctl stop mysqld23307
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# rm -rf /oldboyedu/data/mysql23307/*
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307/
总用量 0
[root@docker201.oldboyedu.com ~]#
(2)启动数据库，发现最终启动失败
[root@docker201.oldboyedu.com ~]# systemctl start mysqld23307
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307/ # 发现竟然自动生成了一些文件！但可惜并不是我们之前的数据
总用量 438312
-rw-r----- 1 mysql mysql 56 2月 13 12:51 auto.cnf
-rw------- 1 mysql mysql 1676 2月 13 12:51 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 2月 13 12:51 ca.pem
-rw-r--r-- 1 mysql mysql 1112 2月 13 12:51 client-cert.pem
-rw------- 1 mysql mysql 1680 2月 13 12:51 client-key.pem
-rw-r----- 1 mysql mysql 197 2月 13 12:51 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 2月 13 12:51 ibdata1
-rw-r----- 1 mysql mysql 134217728 2月 13 12:51 ibdata2
-rw-r----- 1 mysql mysql 134217728 2月 13 12:51 ibdata3
-rw-r----- 1 mysql mysql 50331648 2月 13 12:51 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 2月 13 12:51 ib_logfile1
-rw------- 1 mysql mysql 1680 2月 13 12:51 private_key.pem
-rw-r--r-- 1 mysql mysql 452 2月 13 12:51 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 2月 13 12:51 server-cert.pem
-rw------- 1 mysql mysql 1676 2月 13 12:51 server-key.pem
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ss -ntl # 而且23307端口并没有启动成功！
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 172.200.1.201:6379 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 80 [::]:23306 [::]:*
LISTEN 0 128 [::]:23308 [::]:*
LISTEN 0 128 [::]:80 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 70 [::]:33060 [::]:*
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# systemctl status mysqld23307 # 再次查看mysqld23307服务并没有启动成功！这个过程需要等待一会....
● mysqld23307.service - MySQL Server
Loaded: loaded (/etc/systemd/system/mysqld23307.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since 六 2021-02-13 12:51:27 CST; 2min 40s ago
Docs: man:mansql(8)
https://dev.mysql.com/doc/refman/8.0/en/using-systemd.html
Process: 105004 ExecStart=/oldboyedu/softwares/mysql/mysql/bin/mysqld --defaults-file=/oldboyedu/softwares/mysql23307/my.cnf (code=exited, status=1/FAILURE)
Main PID: 105004 (code=exited, status=1/FAILURE)
2月 13 12:51:17 docker201.oldboyedu.com systemd[1]: Started MySQL Server.
2月 13 12:51:27 docker201.oldboyedu.com systemd[1]: mysqld23307.service: main process exited, code=exited, status=1/FAILURE
2月 13 12:51:27 docker201.oldboyedu.com systemd[1]: Unit mysqld23307.service entered failed state.
2月 13 12:51:27 docker201.oldboyedu.com systemd[1]: mysqld23307.service failed.
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# rm -rf /oldboyedu/data/mysql23307/* # 既然数据库启动不成功，因此我们还是将该目录删除，而后基于咱们全量备份的数据进行恢复!
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307/
总用量 0
[root@docker201.oldboyedu.com ~]#
(3)备份预处理(prepare)，使用redo前滚，undo回滚，模仿CSR过程
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/xbk/
总用量 4
drwxr-x--- 15 root root 4096 2月 13 12:01 2021-02-13_12-00-56
drwxr-x--- 3 root root 33 2月 13 12:42 full_2021-02-13
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# innobackupex --apply-log /oldboyedu/backup/xbk/2021-02-13_12-00-56/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:76M;ibdata2:128M;ibdata3:128M:autoext
end --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=0 --redo-log-version=1 xtrabackup: recognized client arguments:
210213 13:03:08 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.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5)
xtrabackup: cd to /oldboyedu/backup/xbk/2021-02-13_12-00-56/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(102850166)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:76M;ibdata2:128M;ibdata3:128M: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:76M;ibdata2:128M;ibdata3:128M: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 102850166
InnoDB: Doing recovery: scanned up to log sequence number 102850175 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 1221, file name oldboyedu-mysqld-binary.000019
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: 5.7.32 started; log sequence number 102850175
InnoDB: xtrabackup: Last MySQL binlog file position 1221, file name oldboyedu-mysqld-binary.000019
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 102850194
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:76M;ibdata2:128M;ibdata3:128M: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=102850194
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 102850572
InnoDB: Doing recovery: scanned up to log sequence number 102850581 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 1221, file name oldboyedu-mysqld-binary.000019
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: 5.7.32 started; log sequence number 102850581
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 102850600
210213 13:03:12 completed OK!
[root@docker201.oldboyedu.com ~]#
(4)恢复数据
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/xbk/
总用量 4
drwxr-x--- 15 root root 4096 2月 13 13:03 2021-02-13_12-00-56
drwxr-x--- 3 root root 33 2月 13 12:42 full_2021-02-13
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cp -a /oldboyedu/backup/xbk/2021-02-13_12-00-56/* /oldboyedu/data/mysql23307/
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307/
总用量 458812
-rw-r----- 1 root root 513 2月 13 12:01 backup-my.cnf
drwxr-x--- 2 root root 52 2月 13 12:01 cdh
drwxr-x--- 2 root root 52 2月 13 12:01 cmdb
drwxr-x--- 2 root root 76 2月 13 12:01 demo
drwxr-x--- 2 root root 132 2月 13 12:01 hdp
drwxr-x--- 2 root root 52 2月 13 12:01 hive
-rw-r----- 1 root root 2567 2月 13 12:01 ib_buffer_pool
-rw-r----- 1 root root 79691776 2月 13 13:03 ibdata1
-rw-r----- 1 root root 134217728 2月 13 13:03 ibdata2
-rw-r----- 1 root root 134217728 2月 13 13:03 ibdata3
-rw-r----- 1 root root 50331648 2月 13 13:03 ib_logfile0
-rw-r----- 1 root root 50331648 2月 13 13:03 ib_logfile1
-rw-r----- 1 root root 12582912 2月 13 13:03 ibtmp1
drwxr-x--- 2 root root 4096 2月 13 12:01 mysql
drwxr-x--- 2 root root 8192 2月 13 12:01 performance_schema
drwxr-x--- 2 root root 182 2月 13 12:01 school
drwxr-x--- 2 root root 8192 2月 13 12:01 sys
drwxr-x--- 2 root root 144 2月 13 12:01 world
-rw-r----- 1 root root 81 2月 13 12:01 xtrabackup_binlog_info
-rw-r--r-- 1 root root 38 2月 13 13:03 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 141 2月 13 13:03 xtrabackup_checkpoints
-rw-r----- 1 root root 543 2月 13 12:01 xtrabackup_info
-rw-r----- 1 root root 8388608 2月 13 13:03 xtrabackup_logfile
-rw-r--r-- 1 root root 1 2月 13 13:03 xtrabackup_master_key_id
drwxr-x--- 2 root root 166 2月 13 12:01 oldboyedu
drwxr-x--- 2 root root 4096 2月 13 12:01 yoolia_mall
drwxr-x--- 2 root root 20 2月 13 12:01 zabbix
[root@docker201.oldboyedu.com ~]#
(5)修改授权信息
[root@docker201.oldboyedu.com ~]# chown -R mysql:mysql /oldboyedu/data/mysql23307/
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307/
总用量 458812
-rw-r----- 1 mysql mysql 513 2月 13 12:01 backup-my.cnf
drwxr-x--- 2 mysql mysql 52 2月 13 12:01 cdh
drwxr-x--- 2 mysql mysql 52 2月 13 12:01 cmdb
drwxr-x--- 2 mysql mysql 76 2月 13 12:01 demo
drwxr-x--- 2 mysql mysql 132 2月 13 12:01 hdp
drwxr-x--- 2 mysql mysql 52 2月 13 12:01 hive
-rw-r----- 1 mysql mysql 2567 2月 13 12:01 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 2月 13 13:03 ibdata1
-rw-r----- 1 mysql mysql 134217728 2月 13 13:03 ibdata2
-rw-r----- 1 mysql mysql 134217728 2月 13 13:03 ibdata3
-rw-r----- 1 mysql mysql 50331648 2月 13 13:03 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 2月 13 13:03 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 2月 13 13:03 ibtmp1
drwxr-x--- 2 mysql mysql 4096 2月 13 12:01 mysql
drwxr-x--- 2 mysql mysql 8192 2月 13 12:01 performance_schema
drwxr-x--- 2 mysql mysql 182 2月 13 12:01 school
drwxr-x--- 2 mysql mysql 8192 2月 13 12:01 sys
drwxr-x--- 2 mysql mysql 144 2月 13 12:01 world
-rw-r----- 1 mysql mysql 81 2月 13 12:01 xtrabackup_binlog_info
-rw-r--r-- 1 mysql mysql 38 2月 13 13:03 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 141 2月 13 13:03 xtrabackup_checkpoints
-rw-r----- 1 mysql mysql 543 2月 13 12:01 xtrabackup_info
-rw-r----- 1 mysql mysql 8388608 2月 13 13:03 xtrabackup_logfile
-rw-r--r-- 1 mysql mysql 1 2月 13 13:03 xtrabackup_master_key_id
drwxr-x--- 2 mysql mysql 166 2月 13 12:01 oldboyedu
drwxr-x--- 2 mysql mysql 4096 2月 13 12:01 yoolia_mall
drwxr-x--- 2 mysql mysql 20 2月 13 12:01 zabbix
[root@docker201.oldboyedu.com ~]#
(6)启动数据库，验证数据是否恢复成功
[root@docker201.oldboyedu.com ~]# systemctl start mysqld23307
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdh |
| cmdb |
| demo |
| hdp |
| hive |
| mysql |
| performance_schema |
| school |
| sys |
| world |
| oldboyedu |
| yoolia_mall |
| zabbix |
+--------------------+
14 rows in set (0.02 sec)
mysql>
mysql> QUIT
Bye
[root@docker201.oldboyedu.com ~]#
3.Percona Xtrabackup增量备份实战应用
XBK增量备份及恢复逻辑
备份时:
(1)增量备份必须依赖于全量备份;
(2)每次增量备份都是参照上一次备份的LSN号码(还记得我们使用XBK工具做全量备份时会生成"xtrabackup_checkpoints"文件吗?该文件记录的有备份类型及LSN编号信息)，在此基础上变化的数据页才会备份;
(3)需要注意的是，会将备份过程中产生新的变化的Redo也一并备份走;
恢复时:
如下图所示，需要将所有需要的增量备份按顺序合并到全量备份中，并且需要将每个备份进行prepare后，才能后续的恢复操作。
温馨提示:
所谓的增量备份并不是指数据增加了就备份，数据被删除了就不备份。增量备份是针对LSN编号而言的，因为每个操作都唯一对应一个LSN编号，尽管删除了数据，这个LSN编号是在持续增大，而我们是已于LSN编号来进行备份，所以才有增量备份一说。
增量备份恢复案例场景说明
场景说明:
现有某业务数据存储在xbk数据库中，DBA运维人员周期性计划周日做全量备份，周一到周六做增量备份，我们此处请忽略备份损坏的情况，即假设所有的备份文件都是有效可用的。
模拟损坏时间:
周三下午18:00有同学删除了xbk数据库。
恢复数据步骤参考思路:
(1)将所有的增量备份日志和全量备份日志进行合并;
(2)基于合并后的新全量备份日志进行prepare操作，并恢复数据;
(3)截取binlog日志，这些日志是全量备份和增量备份没有记录的日志信息，而后基于截取的二进制日志进行恢复;
基础环境模拟
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> CREATE DATABASE xbk CHARSET utf8mb4;
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> USE xbk;
Database changed
mysql>
mysql> CREATE TABLE t1 (id int);
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> INSERT INTO t1 VALUES (100),(200),(300);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM t1;
+------+
| id |
+------+
| 100 |
| 200 |
| 300 |
+------+
3 rows in set (0.00 sec)
mysql>
mysql>
使用innobackupex命令模拟周日的全量备份
[root@docker201.oldboyedu.com ~]# rm -rf /oldboyedu/backup/*
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# innobackupex --no-timestamp /oldboyedu/backup/full_`date +%F`
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments:
210213 17:16:20 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!".
210213 17:16:20 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql23307.sock' (using password: NO).
210213 17:16:20 version_check Connected to MySQL server
210213 17:16:20 version_check Executing a version check against the server...
210213 17:16:20 version_check Done.
210213 17:16:20 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: /tmp/mysql23307.sock
Using server version 5.7.31-log
innobackupex version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /oldboyedu/data/mysql23307/
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:76M;ibdata2:128M;ibdata3:128M: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
210213 17:16:20 >> log scanned up to (102863599)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 145 for cdh/user, old maximum was 0
210213 17:16:20 [01] Copying ./ibdata1 to /oldboyedu/backup/full_2021-02-13/ibdata1
210213 17:16:20 [01] ...done
...
210213 17:16:23 [01] Copying ./xbk/t1.frm to /oldboyedu/backup/full_2021-02-13/xbk/t1.frm
210213 17:16:23 [01] ...done
210213 17:16:23 Finished backing up non-InnoDB tables and files
210213 17:16:23 [00] Writing /oldboyedu/backup/full_2021-02-13/xtrabackup_binlog_info
210213 17:16:23 [00] ...done
210213 17:16:23 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '102863590'
xtrabackup: Stopping log copying thread.
.210213 17:16:23 >> log scanned up to (102863599)
210213 17:16:23 Executing UNLOCK TABLES
210213 17:16:23 All tables unlocked
210213 17:16:23 [00] Copying ib_buffer_pool to /oldboyedu/backup/full_2021-02-13/ib_buffer_pool
210213 17:16:23 [00] ...done
210213 17:16:23 Backup created in directory '/oldboyedu/backup/full_2021-02-13/'
MySQL binlog position: filename 'oldboyedu-mysqld-binary.000021', position '788', GTID of the last change '3cfc679a-6db9-11eb-8c6f-000c29820c67:1-3,
ecaf563f-5345-11eb-a106-000c29820c67:1-106'
210213 17:16:23 [00] Writing /oldboyedu/backup/full_2021-02-13/backup-my.cnf
210213 17:16:23 [00] ...done
210213 17:16:23 [00] Writing /oldboyedu/backup/full_2021-02-13/xtrabackup_info
210213 17:16:23 [00] ...done
xtrabackup: Transaction log of lsn (102863590) to (102863599) was copied.
210213 17:16:23 completed OK!
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/
总用量 4
drwxr-x--- 16 root root 4096 2月 13 17:16 full_2021-02-13
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/full_2021-02-13/
总用量 340024
-rw-r----- 1 root root 513 2月 13 17:16 backup-my.cnf
drwxr-x--- 2 root root 52 2月 13 17:16 cdh
drwxr-x--- 2 root root 52 2月 13 17:16 cmdb
drwxr-x--- 2 root root 76 2月 13 17:16 demo
drwxr-x--- 2 root root 132 2月 13 17:16 hdp
drwxr-x--- 2 root root 52 2月 13 17:16 hive
-rw-r----- 1 root root 2567 2月 13 17:16 ib_buffer_pool
-rw-r----- 1 root root 79691776 2月 13 17:16 ibdata1
-rw-r----- 1 root root 134217728 2月 13 17:16 ibdata2
-rw-r----- 1 root root 134217728 2月 13 17:16 ibdata3
drwxr-x--- 2 root root 4096 2月 13 17:16 mysql
drwxr-x--- 2 root root 8192 2月 13 17:16 performance_schema
drwxr-x--- 2 root root 182 2月 13 17:16 school
drwxr-x--- 2 root root 8192 2月 13 17:16 sys
drwxr-x--- 2 root root 144 2月 13 17:16 world
drwxr-x--- 2 root root 48 2月 13 17:16 xbk
-rw-r----- 1 root root 122 2月 13 17:16 xtrabackup_binlog_info
-rw-r----- 1 root root 141 2月 13 17:16 xtrabackup_checkpoints
-rw-r----- 1 root root 611 2月 13 17:16 xtrabackup_info
-rw-r----- 1 root root 2560 2月 13 17:16 xtrabackup_logfile
drwxr-x--- 2 root root 166 2月 13 17:16 oldboyedu
drwxr-x--- 2 root root 4096 2月 13 17:16 yoolia_mall
drwxr-x--- 2 root root 20 2月 13 17:16 zabbix
[root@docker201.oldboyedu.com ~]#
在全量备份后，模拟周一新增数据的操作
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SHOW TABLES FROM xbk;
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> CREATE TABLE xbk.t2 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> SHOW TABLES FROM xbk;
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
| t2 |
+---------------+
2 rows in set (0.00 sec)
mysql>
mysql> INSERT INTO xbk.t2 VALUES (111),(222),(333);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM xbk.t2;
+------+
| id |
+------+
| 111 |
| 222 |
| 333 |
+------+
3 rows in set (0.00 sec)
mysql>
使用innobackupex命令模拟周一的增量备份
在做增量备份时，可能会用到以下几个参数:
--user='admin':
指定用户名为'admin'。
--password='oldboyedu':
指定用户名所对应的密码为'oldboyedu'，请根据你数据库实际授权备份用户填写即可。
--no-timestamp:
表示不使用默认的时间戳，我们可以自定义指定备份的路径及备份目录名称。
--incremental:
表示启用增量备份功能。
--incremental-basedir=/oldboyedu/backup/full_2021-02-13:
指定基于哪个现有目录做增量备份，第一次做增量备份时，指定的目录应该为全量备份目录。换句话说，就是指定上一次备份的LSN编号的存储目录。
增量备份案例实操如下所示:
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/ # 如下所示，这是我们上一次做的全量备份的目录哟~
总用量 4
drwxr-x--- 16 root root 4096 2月 13 17:16 full_2021-02-13
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# innobackupex --no-timestamp --incremental --incremental-basedir=/oldboyedu/backup/full_2021-02-13 /oldboyedu/backup/incremental_data01
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments:
210213 17:46:46 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!".
210213 17:46:46 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql23307.sock' (using password: NO).
210213 17:46:46 version_check Connected to MySQL server
210213 17:46:46 version_check Executing a version check against the server...
210213 17:46:46 version_check Done.
210213 17:46:46 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: /tmp/mysql23307.sock
Using server version 5.7.31-log
innobackupex version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5)
incremental backup from 102863590 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /oldboyedu/data/mysql23307/
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:76M;ibdata2:128M;ibdata3:128M: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
210213 17:46:46 >> log scanned up to (102869972)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 145 for cdh/user, old maximum was 0
xtrabackup: using the full scan for incremental backup
210213 17:46:46 [01] Copying ./ibdata1 to /oldboyedu/backup/incremental_data01/ibdata1.delta
210213 17:46:46 [01] ...done
...
210213 17:46:50 [01] Copying ./xbk/t2.frm to /oldboyedu/backup/incremental_data01/xbk/t2.frm
210213 17:46:50 [01] ...done
210213 17:46:50 Finished backing up non-InnoDB tables and files
210213 17:46:50 [00] Writing /oldboyedu/backup/incremental_data01/xtrabackup_binlog_info
210213 17:46:50 [00] ...done
210213 17:46:50 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '102869963'
xtrabackup: Stopping log copying thread.
.210213 17:46:50 >> log scanned up to (102869972)
210213 17:46:50 Executing UNLOCK TABLES
210213 17:46:50 All tables unlocked
210213 17:46:50 [00] Copying ib_buffer_pool to /oldboyedu/backup/incremental_data01/ib_buffer_pool
210213 17:46:50 [00] ...done
210213 17:46:50 Backup created in directory '/oldboyedu/backup/incremental_data01/'
MySQL binlog position: filename 'oldboyedu-mysqld-binary.000021', position '1208', GTID of the last change '3cfc679a-6db9-11eb-8c6f-000c29820c67:1-5,
ecaf563f-5345-11eb-a106-000c29820c67:1-106'
210213 17:46:50 [00] Writing /oldboyedu/backup/incremental_data01/backup-my.cnf
210213 17:46:50 [00] ...done
210213 17:46:50 [00] Writing /oldboyedu/backup/incremental_data01/xtrabackup_info
210213 17:46:50 [00] ...done
xtrabackup: Transaction log of lsn (102869963) to (102869972) was copied.
210213 17:46:50 completed OK!
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/
总用量 8
drwxr-x--- 16 root root 4096 2月 13 17:16 full_2021-02-13
drwxr-x--- 16 root root 4096 2月 13 17:46 incremental_data01
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/incremental_data01/
总用量 548
-rw-r----- 1 root root 513 2月 13 17:46 backup-my.cnf
drwxr-x--- 2 root root 79 2月 13 17:46 cdh
drwxr-x--- 2 root root 79 2月 13 17:46 cmdb
drwxr-x--- 2 root root 126 2月 13 17:46 demo
drwxr-x--- 2 root root 232 2月 13 17:46 hdp
drwxr-x--- 2 root root 79 2月 13 17:46 hive
-rw-r----- 1 root root 2567 2月 13 17:46 ib_buffer_pool
-rw-r----- 1 root root 458752 2月 13 17:46 ibdata1.delta
-rw-r----- 1 root root 60 2月 13 17:46 ibdata1.meta
-rw-r----- 1 root root 16384 2月 13 17:46 ibdata2.delta
-rw-r----- 1 root root 60 2月 13 17:46 ibdata2.meta
-rw-r----- 1 root root 16384 2月 13 17:46 ibdata3.delta
-rw-r----- 1 root root 60 2月 13 17:46 ibdata3.meta
drwxr-x--- 2 root root 4096 2月 13 17:46 mysql
drwxr-x--- 2 root root 8192 2月 13 17:46 performance_schema
drwxr-x--- 2 root root 307 2月 13 17:46 school
drwxr-x--- 2 root root 8192 2月 13 17:46 sys
drwxr-x--- 2 root root 239 2月 13 17:46 world
drwxr-x--- 2 root root 126 2月 13 17:46 xbk
-rw-r----- 1 root root 123 2月 13 17:46 xtrabackup_binlog_info
-rw-r----- 1 root root 147 2月 13 17:46 xtrabackup_checkpoints
-rw-r----- 1 root root 695 2月 13 17:46 xtrabackup_info
-rw-r----- 1 root root 2560 2月 13 17:46 xtrabackup_logfile
drwxr-x--- 2 root root 283 2月 13 17:46 oldboyedu
drwxr-x--- 2 root root 4096 2月 13 17:46 yoolia_mall
drwxr-x--- 2 root root 20 2月 13 17:46 zabbix
[root@docker201.oldboyedu.com ~]#
在增量备份后，模拟周二新增数据的操作
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SHOW TABLES FROM xbk;
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
| t2 |
+---------------+
2 rows in set (0.00 sec)
mysql>
mysql> CREATE TABLE xbk.t3 (id int);
Query OK, 0 rows affected (0.30 sec)
mysql>
mysql> SHOW TABLES FROM xbk;
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
| t2 |
| t3 |
+---------------+
3 rows in set (0.00 sec)
mysql>
mysql> INSERT INTO xbk.t3 VALUES (1),(3),(5),(7),(9);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM xbk.t3;
+------+
| id |
+------+
| 1 |
| 3 |
| 5 |
| 7 |
| 9 |
+------+
5 rows in set (0.00 sec)
mysql>
mysql>
使用innobackupex命令模拟周二的增量备份
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/
总用量 8
drwxr-x--- 16 root root 4096 2月 13 17:16 full_2021-02-13
drwxr-x--- 16 root root 4096 2月 13 17:46 incremental_data01
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# innobackupex --no-timestamp --incremental --incremental-basedir=/oldboyedu/backup/incremental_data01 /oldboyedu/backup/incremental_data02
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments:
210213 18:04:14 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!".
210213 18:04:15 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql23307.sock' (using password: NO).
210213 18:04:15 version_check Connected to MySQL server
210213 18:04:15 version_check Executing a version check against the server...
210213 18:04:15 version_check Done.
210213 18:04:15 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: /tmp/mysql23307.sock
Using server version 5.7.31-log
innobackupex version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5)
incremental backup from 102869963 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /oldboyedu/data/mysql23307/
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:76M;ibdata2:128M;ibdata3:128M: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
210213 18:04:15 >> log scanned up to (102890885)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 145 for cdh/user, old maximum was 0
xtrabackup: using the full scan for incremental backup
210213 18:04:15 [01] Copying ./ibdata1 to /oldboyedu/backup/incremental_data02/ibdata1.delta
210213 18:04:15 [01] ...done
...
210213 18:04:19 [01] Copying ./xbk/t3.frm to /oldboyedu/backup/incremental_data02/xbk/t3.frm
210213 18:04:19 [01] ...done
210213 18:04:19 Finished backing up non-InnoDB tables and files
210213 18:04:19 [00] Writing /oldboyedu/backup/incremental_data02/xtrabackup_binlog_info
210213 18:04:19 [00] ...done
210213 18:04:19 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '102890876'
xtrabackup: Stopping log copying thread.
.210213 18:04:19 >> log scanned up to (102890885)
210213 18:04:19 Executing UNLOCK TABLES
210213 18:04:19 All tables unlocked
210213 18:04:19 [00] Copying ib_buffer_pool to /oldboyedu/backup/incremental_data02/ib_buffer_pool
210213 18:04:19 [00] ...done
210213 18:04:19 Backup created in directory '/oldboyedu/backup/incremental_data02/'
MySQL binlog position: filename 'oldboyedu-mysqld-binary.000021', position '1638', GTID of the last change '3cfc679a-6db9-11eb-8c6f-000c29820c67:1-7,
ecaf563f-5345-11eb-a106-000c29820c67:1-106'
210213 18:04:19 [00] Writing /oldboyedu/backup/incremental_data02/backup-my.cnf
210213 18:04:19 [00] ...done
210213 18:04:19 [00] Writing /oldboyedu/backup/incremental_data02/xtrabackup_info
210213 18:04:19 [00] ...done
xtrabackup: Transaction log of lsn (102890876) to (102890885) was copied.
210213 18:04:19 completed OK!
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/
总用量 12
drwxr-x--- 16 root root 4096 2月 13 17:16 full_2021-02-13
drwxr-x--- 16 root root 4096 2月 13 17:46 incremental_data01
drwxr-x--- 16 root root 4096 2月 13 18:04 incremental_data02
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/incremental_data02
总用量 532
-rw-r----- 1 root root 513 2月 13 18:04 backup-my.cnf
drwxr-x--- 2 root root 79 2月 13 18:04 cdh
drwxr-x--- 2 root root 79 2月 13 18:04 cmdb
drwxr-x--- 2 root root 126 2月 13 18:04 demo
drwxr-x--- 2 root root 232 2月 13 18:04 hdp
drwxr-x--- 2 root root 79 2月 13 18:04 hive
-rw-r----- 1 root root 2567 2月 13 18:04 ib_buffer_pool
-rw-r----- 1 root root 442368 2月 13 18:04 ibdata1.delta
-rw-r----- 1 root root 60 2月 13 18:04 ibdata1.meta
-rw-r----- 1 root root 16384 2月 13 18:04 ibdata2.delta
-rw-r----- 1 root root 60 2月 13 18:04 ibdata2.meta
-rw-r----- 1 root root 16384 2月 13 18:04 ibdata3.delta
-rw-r----- 1 root root 60 2月 13 18:04 ibdata3.meta
drwxr-x--- 2 root root 4096 2月 13 18:04 mysql
drwxr-x--- 2 root root 8192 2月 13 18:04 performance_schema
drwxr-x--- 2 root root 307 2月 13 18:04 school
drwxr-x--- 2 root root 8192 2月 13 18:04 sys
drwxr-x--- 2 root root 239 2月 13 18:04 world
drwxr-x--- 2 root root 179 2月 13 18:04 xbk
-rw-r----- 1 root root 123 2月 13 18:04 xtrabackup_binlog_info
-rw-r----- 1 root root 147 2月 13 18:04 xtrabackup_checkpoints
-rw-r----- 1 root root 698 2月 13 18:04 xtrabackup_info
-rw-r----- 1 root root 2560 2月 13 18:04 xtrabackup_logfile
drwxr-x--- 2 root root 283 2月 13 18:04 oldboyedu
drwxr-x--- 2 root root 4096 2月 13 18:04 yoolia_mall
drwxr-x--- 2 root root 20 2月 13 18:04 zabbix
[root@docker201.oldboyedu.com ~]#
在增量备份后，模拟周三新增数据的操作
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SHOW TABLES FROM xbk;
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
| t2 |
| t3 |
+---------------+
3 rows in set (0.00 sec)
mysql>
mysql> CREATE TABLE xbk.t4 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO xbk.t4 VALUES (10),(30),(50),(70),(90);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE xbk.t5 (id int);
Query OK, 0 rows affected (0.30 sec)
mysql>
mysql> INSERT INTO xbk.t5 VALUES (100),(300),(500),(700),(900);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SHOW TABLES FROM xbk;
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
| t2 |
| t3 |
| t4 |
| t5 |
+---------------+
5 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM xbk.t4;
+------+
| id |
+------+
| 10 |
| 30 |
| 50 |
| 70 |
| 90 |
+------+
5 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM xbk.t5;
+------+
| id |
+------+
| 100 |
| 300 |
| 500 |
| 700 |
| 900 |
+------+
5 rows in set (0.00 sec)
mysql>
搞破坏，将MySQL的所有数据删除
(1)在模拟周三新增数据的操作后，不要做增量备份。这些操作在二进制日志中都有记录，因此我们可以基于二进制日志的方式来进行恢复，如下所示，我是启用了二进制日志功能。
mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+---------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------------------+
| log_bin | ON |
| log_bin_basename | /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary |
| log_bin_index | /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------------------------------------------+
6 rows in set (0.00 sec)
mysql>
mysql> SELECT @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql>
(2)如下所示，搞破坏就比较简单，直接删除MySQL的所有物理数据。
[root@docker201.oldboyedu.com ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 172.200.1.201:6379 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 80 [::]:23306 [::]:*
LISTEN 0 80 [::]:23307 [::]:*
LISTEN 0 128 [::]:23308 [::]:*
LISTEN 0 128 [::]:80 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 70 [::]:33060 [::]:*
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# systemctl stop mysqld23307
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 172.200.1.201:6379 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 80 [::]:23306 [::]:*
LISTEN 0 128 [::]:23308 [::]:*
LISTEN 0 128 [::]:80 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 70 [::]:33060 [::]:*
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307/
总用量 446560
-rw-r----- 1 mysql mysql 56 2月 13 13:06 auto.cnf
-rw-r----- 1 mysql mysql 513 2月 13 12:01 backup-my.cnf
-rw------- 1 mysql mysql 1680 2月 13 13:06 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 2月 13 13:06 ca.pem
drwxr-x--- 2 mysql mysql 52 2月 13 12:01 cdh
-rw-r--r-- 1 mysql mysql 1112 2月 13 13:06 client-cert.pem
-rw------- 1 mysql mysql 1680 2月 13 13:06 client-key.pem
drwxr-x--- 2 mysql mysql 52 2月 13 12:01 cmdb
drwxr-x--- 2 mysql mysql 76 2月 13 12:01 demo
drwxr-x--- 2 mysql mysql 132 2月 13 12:01 hdp
drwxr-x--- 2 mysql mysql 52 2月 13 12:01 hive
-rw-r----- 1 mysql mysql 587 2月 13 18:16 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 2月 13 18:16 ibdata1
-rw-r----- 1 mysql mysql 134217728 2月 13 13:03 ibdata2
-rw-r----- 1 mysql mysql 134217728 2月 13 13:03 ibdata3
-rw-r----- 1 mysql mysql 50331648 2月 13 18:16 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 2月 13 13:03 ib_logfile1
drwxr-x--- 2 mysql mysql 4096 2月 13 12:01 mysql
drwxr-x--- 2 mysql mysql 8192 2月 13 12:01 performance_schema
-rw------- 1 mysql mysql 1680 2月 13 13:06 private_key.pem
-rw-r--r-- 1 mysql mysql 452 2月 13 13:06 public_key.pem
drwxr-x--- 2 mysql mysql 182 2月 13 12:01 school
-rw-r--r-- 1 mysql mysql 1112 2月 13 13:06 server-cert.pem
-rw------- 1 mysql mysql 1680 2月 13 13:06 server-key.pem
drwxr-x--- 2 mysql mysql 8192 2月 13 12:01 sys
drwxr-x--- 2 mysql mysql 144 2月 13 12:01 world
drwxr-x--- 2 mysql mysql 160 2月 13 18:08 xbk
-rw-r----- 1 mysql mysql 81 2月 13 12:01 xtrabackup_binlog_info
-rw-r--r-- 1 mysql mysql 38 2月 13 13:03 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 141 2月 13 13:03 xtrabackup_checkpoints
-rw-r----- 1 mysql mysql 543 2月 13 12:01 xtrabackup_info
-rw-r----- 1 mysql mysql 8388608 2月 13 13:03 xtrabackup_logfile
-rw-r--r-- 1 mysql mysql 1 2月 13 13:03 xtrabackup_master_key_id
drwxr-x--- 2 mysql mysql 166 2月 13 12:01 oldboyedu
drwxr-x--- 2 mysql mysql 4096 2月 13 12:01 yoolia_mall
drwxr-x--- 2 mysql mysql 20 2月 13 12:01 zabbix
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# rm -rf /oldboyedu/data/mysql23307/*
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307/
总用量 0
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]#
查看全量备份和增量备份的目录组织结构并检查备份完整性
(1)查看全量备份和增量备份的目录组织结构
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/
总用量 12
drwxr-x--- 16 root root 4096 2月 13 17:16 full_2021-02-13
drwxr-x--- 16 root root 4096 2月 13 17:46 incremental_data01
drwxr-x--- 16 root root 4096 2月 13 18:04 incremental_data02
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/full_2021-02-13/
总用量 340024
-rw-r----- 1 root root 513 2月 13 17:16 backup-my.cnf
drwxr-x--- 2 root root 52 2月 13 17:16 cdh
drwxr-x--- 2 root root 52 2月 13 17:16 cmdb
drwxr-x--- 2 root root 76 2月 13 17:16 demo
drwxr-x--- 2 root root 132 2月 13 17:16 hdp
drwxr-x--- 2 root root 52 2月 13 17:16 hive
-rw-r----- 1 root root 2567 2月 13 17:16 ib_buffer_pool
-rw-r----- 1 root root 79691776 2月 13 17:16 ibdata1
-rw-r----- 1 root root 134217728 2月 13 17:16 ibdata2
-rw-r----- 1 root root 134217728 2月 13 17:16 ibdata3
drwxr-x--- 2 root root 4096 2月 13 17:16 mysql
drwxr-x--- 2 root root 8192 2月 13 17:16 performance_schema
drwxr-x--- 2 root root 182 2月 13 17:16 school
drwxr-x--- 2 root root 8192 2月 13 17:16 sys
drwxr-x--- 2 root root 144 2月 13 17:16 world
drwxr-x--- 2 root root 48 2月 13 17:16 xbk
-rw-r----- 1 root root 122 2月 13 17:16 xtrabackup_binlog_info
-rw-r----- 1 root root 141 2月 13 17:16 xtrabackup_checkpoints
-rw-r----- 1 root root 611 2月 13 17:16 xtrabackup_info
-rw-r----- 1 root root 2560 2月 13 17:16 xtrabackup_logfile
drwxr-x--- 2 root root 166 2月 13 17:16 oldboyedu
drwxr-x--- 2 root root 4096 2月 13 17:16 yoolia_mall
drwxr-x--- 2 root root 20 2月 13 17:16 zabbix
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/incremental_data01/
总用量 548
-rw-r----- 1 root root 513 2月 13 17:46 backup-my.cnf
drwxr-x--- 2 root root 79 2月 13 17:46 cdh
drwxr-x--- 2 root root 79 2月 13 17:46 cmdb
drwxr-x--- 2 root root 126 2月 13 17:46 demo
drwxr-x--- 2 root root 232 2月 13 17:46 hdp
drwxr-x--- 2 root root 79 2月 13 17:46 hive
-rw-r----- 1 root root 2567 2月 13 17:46 ib_buffer_pool
-rw-r----- 1 root root 458752 2月 13 17:46 ibdata1.delta
-rw-r----- 1 root root 60 2月 13 17:46 ibdata1.meta
-rw-r----- 1 root root 16384 2月 13 17:46 ibdata2.delta
-rw-r----- 1 root root 60 2月 13 17:46 ibdata2.meta
-rw-r----- 1 root root 16384 2月 13 17:46 ibdata3.delta
-rw-r----- 1 root root 60 2月 13 17:46 ibdata3.meta
drwxr-x--- 2 root root 4096 2月 13 17:46 mysql
drwxr-x--- 2 root root 8192 2月 13 17:46 performance_schema
drwxr-x--- 2 root root 307 2月 13 17:46 school
drwxr-x--- 2 root root 8192 2月 13 17:46 sys
drwxr-x--- 2 root root 239 2月 13 17:46 world
drwxr-x--- 2 root root 126 2月 13 17:46 xbk
-rw-r----- 1 root root 123 2月 13 17:46 xtrabackup_binlog_info
-rw-r----- 1 root root 147 2月 13 17:46 xtrabackup_checkpoints
-rw-r----- 1 root root 695 2月 13 17:46 xtrabackup_info
-rw-r----- 1 root root 2560 2月 13 17:46 xtrabackup_logfile
drwxr-x--- 2 root root 283 2月 13 17:46 oldboyedu
drwxr-x--- 2 root root 4096 2月 13 17:46 yoolia_mall
drwxr-x--- 2 root root 20 2月 13 17:46 zabbix
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/full_2021-02-13/xbk/ # 注意观察目录信息
总用量 112
-rw-r----- 1 root root 67 2月 13 17:16 db.opt
-rw-r----- 1 root root 8556 2月 13 17:16 t1.frm
-rw-r----- 1 root root 98304 2月 13 17:16 t1.ibd
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/backup/incremental_data01/xbk/ # 注意观察目录变化信息
总用量 132
-rw-r----- 1 root root 67 2月 13 17:46 db.opt
-rw-r----- 1 root root 8556 2月 13 17:46 t1.frm
-rw-r----- 1 root root 16384 2月 13 17:46 t1.ibd.delta
-rw-r----- 1 root root 63 2月 13 17:46 t1.ibd.meta
-rw-r----- 1 root root 8556 2月 13 17:46 t2.frm
-rw-r----- 1 root root 81920 2月 13 17:46 t2.ibd.delta
-rw-r----- 1 root root 63 2月 13 17:46 t2.ibd.meta
[root@docker201.oldboyedu.com ~]#
(2)检查备份完整性方案有很多种，比如在备份时查看是否有"completed OK!"字样提示信息，当然也可以检查备份文件的LSN编号是否连续。
方案一: 在备份时查看是否有"completed OK!"字样提示信息
[root@docker201.oldboyedu.com ~]# innobackupex --no-timestamp --incremental --incremental-basedir=/oldboyedu/backup/incremental_data01 /oldboyedu/backup/incremental_data02
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments:
...
210213 18:04:19 completed OK! # 如果出现这一句，说明咱们备份是成功的
[root@docker201.oldboyedu.com ~]#
方案二：检查备份文件的LSN编号是否连续
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/full_2021-02-13/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 102863590
last_lsn = 102863599
compact = 0
recover_binlog_info = 0
flushed_lsn = 102863599
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/incremental_data01/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 102863590
to_lsn = 102869963
last_lsn = 102869972
compact = 0
recover_binlog_info = 0
flushed_lsn = 102869972
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/incremental_data02/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 102869963
to_lsn = 102890876
last_lsn = 102890885
compact = 0
recover_binlog_info = 0
flushed_lsn = 102890885
[root@docker201.oldboyedu.com ~]#
温馨提示:
(1)值得注意的是，"to_lsn"与"last_lsn"的编号值相差"9"的话，说明在备份期间没有任何修改操作，而之所以相差9是因为在备份时MySQL内部做了一些操作。在生产环境中，"to_lsn"与"last_lsn"的编号值相差大于"9"是很正常的事，因为这说明在备份过程中数据发生了改变。
(2)从我们的案例来说，不难发现"from_lsn"的值和上一次备份的点的"to_lsn"值是相同的，但实际上"from_lsn"的值应该等于"last_lsn - 9"。
基于全量备份和增量备份恢复已备份的数据(对应xbk数据库的t1,t2,t3这三张测试表)
(1)将最新的全量备份日志进行prepare整理
[root@docker201.oldboyedu.com ~]# innobackupex --apply-log --redo-only /oldboyedu/backup/full_2021-02-13
温馨提示:
很多小伙伴应该发现了在整理全量备份日志是加了一个"--redo-only"参数，表示只恢复redo日志，不去做undo日志的回滚，这样可以防止"last_lsn"值发生变化。
在准备基本完全备份和合并除最后一个增量备份外的所有增量备份时，应使用此选项。这迫使xtrabackup跳过“回滚”阶段，只执行“重做”。如果备份稍后将应用增量更改，则这是必需的。
(2)将incremental_data01增量备份日志合并到最新的全量备份日志并进行prepare整理
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/full_2021-02-13/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 102863590
last_lsn = 102863599 # 注意观察，这是最新的全量备份LSN编号
compact = 0
recover_binlog_info = 0
flushed_lsn = 102863599
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/incremental_data01/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 102863590
to_lsn = 102869963
last_lsn = 102869972 # 注意观察，这是第一次增量备份的LSN编号
compact = 0
recover_binlog_info = 0
flushed_lsn = 102869972
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# innobackupex --apply-log --redo-only --incremental-dir=/oldboyedu/backup/incremental_data01 /oldboyedu/backup/full_2021-02-13 # 请仔细观察前后"last_lsn"的值。
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/full_2021-02-13/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 102869963
last_lsn = 102869972 # 不难发现，该值和"incremental_data01"的LSN编号相同。
compact = 0
recover_binlog_info = 0
flushed_lsn = 102869972
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/incremental_data01/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 102863590
to_lsn = 102869963
last_lsn = 102869972
compact = 0
recover_binlog_info = 0
flushed_lsn = 102869972
[root@docker201.oldboyedu.com ~]#
(3)将incremental_data02增量备份日志合并到最新的全量备份日志并进行prepare整理，最后一次合并增量备份时无需添加"--redo-only"参数。
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/full_2021-02-13/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 102886807
last_lsn = 102886816
compact = 0
recover_binlog_info = 0
flushed_lsn = 102886816
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/incremental_data02/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 102886807
to_lsn = 102907424
last_lsn = 102907433
compact = 0
recover_binlog_info = 0
flushed_lsn = 102907433
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# innobackupex --apply-log --incremental-dir=/oldboyedu/backup/incremental_data02 /oldboyedu/backup/full_2021-02-13 # 请仔细观察前后"last_lsn"的值。
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/full_2021-02-13/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 102907424
last_lsn = 102907433
compact = 0
recover_binlog_info = 0
flushed_lsn = 102907433
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/incremental_data02/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 102886807
to_lsn = 102907424
last_lsn = 102907433
compact = 0
recover_binlog_info = 0
flushed_lsn = 102907433
[root@docker201.oldboyedu.com ~]#
(4)整体再次预处理(prepare)整个备份
[root@docker201.oldboyedu.com ~]# innobackupex --apply-log /oldboyedu/backup/full_2021-02-13
(5)恢复数据
方案一:
将"/oldboyedu/backup/full_2021-02-13"设置为MySQL的数据目录，需要修改配置文件，将"datadir"的值指向"/oldboyedu/backup/full_2021-02-13"目录，并修改权限使得运行MySQL实例的用户可以访问。
方案二:
将"/oldboyedu/backup/full_2021-02-13"目录所有内容拷贝到MySQL配置文件中的"datadir"指向的目录，并修改权限使得运行MySQL实例的用户可以访问。
我此处依旧采取的是方案二，具体操作如下所示:
[root@docker201.oldboyedu.com ~]# cp -a /oldboyedu/backup/full_2021-02-13/* /oldboyedu/data/mysql23307/
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307/
总用量 458812
drwxr-x--- 2 root root 20 2月 13 21:13 2021-02-13_20-52-29
drwxr-x--- 2 root root 20 2月 13 21:13 2021-02-13_20-54-24
drwxr-x--- 2 root root 20 2月 13 21:13 2021-02-13_20-56-02
drwxr-x--- 2 root root 20 2月 13 21:13 2021-02-13_20-56-15
-rw-r----- 1 root root 513 2月 13 21:04 backup-my.cnf
drwxr-x--- 2 root root 52 2月 13 21:13 cdh
drwxr-x--- 2 root root 52 2月 13 21:13 cmdb
drwxr-x--- 2 root root 76 2月 13 21:13 demo
drwxr-x--- 2 root root 20 2月 13 21:13 full_2021-02-13
drwxr-x--- 2 root root 132 2月 13 21:13 hdp
drwxr-x--- 2 root root 52 2月 13 21:13 hive
-rw-r----- 1 root root 2567 2月 13 21:04 ib_buffer_pool
-rw-r----- 1 root root 79691776 2月 13 21:17 ibdata1
-rw-r----- 1 root root 134217728 2月 13 21:17 ibdata2
-rw-r----- 1 root root 134217728 2月 13 21:17 ibdata3
-rw-r----- 1 root root 50331648 2月 13 21:17 ib_logfile0
-rw-r----- 1 root root 50331648 2月 13 21:13 ib_logfile1
-rw-r----- 1 root root 12582912 2月 13 21:17 ibtmp1
drwxr-x--- 2 root root 4096 2月 13 21:13 mysql
drwxr-x--- 2 root root 8192 2月 13 21:13 performance_schema
drwxr-x--- 2 root root 182 2月 13 21:13 school
drwxr-x--- 2 root root 8192 2月 13 21:13 sys
drwxr-x--- 2 root root 144 2月 13 21:13 world
drwxr-x--- 2 root root 104 2月 13 21:13 xbk
-rw-r----- 1 root root 166 2月 13 21:13 xtrabackup_binlog_info
-rw-r--r-- 1 root root 38 2月 13 21:17 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 141 2月 13 21:17 xtrabackup_checkpoints
-rw-r----- 1 root root 741 2月 13 21:13 xtrabackup_info
-rw-r----- 1 root root 8388608 2月 13 21:10 xtrabackup_logfile
-rw-r--r-- 1 root root 1 2月 13 21:17 xtrabackup_master_key_id
drwxr-x--- 2 root root 166 2月 13 21:13 oldboyedu
drwxr-x--- 2 root root 4096 2月 13 21:13 yoolia_mall
drwxr-x--- 2 root root 20 2月 13 21:13 zabbix
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# chown mysql:mysql -R /oldboyedu/data/mysql23307/
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll /oldboyedu/data/mysql23307/
总用量 458812
drwxr-x--- 2 mysql mysql 20 2月 13 21:13 2021-02-13_20-52-29
drwxr-x--- 2 mysql mysql 20 2月 13 21:13 2021-02-13_20-54-24
drwxr-x--- 2 mysql mysql 20 2月 13 21:13 2021-02-13_20-56-02
drwxr-x--- 2 mysql mysql 20 2月 13 21:13 2021-02-13_20-56-15
-rw-r----- 1 mysql mysql 513 2月 13 21:04 backup-my.cnf
drwxr-x--- 2 mysql mysql 52 2月 13 21:13 cdh
drwxr-x--- 2 mysql mysql 52 2月 13 21:13 cmdb
drwxr-x--- 2 mysql mysql 76 2月 13 21:13 demo
drwxr-x--- 2 mysql mysql 20 2月 13 21:13 full_2021-02-13
drwxr-x--- 2 mysql mysql 132 2月 13 21:13 hdp
drwxr-x--- 2 mysql mysql 52 2月 13 21:13 hive
-rw-r----- 1 mysql mysql 2567 2月 13 21:04 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 2月 13 21:17 ibdata1
-rw-r----- 1 mysql mysql 134217728 2月 13 21:17 ibdata2
-rw-r----- 1 mysql mysql 134217728 2月 13 21:17 ibdata3
-rw-r----- 1 mysql mysql 50331648 2月 13 21:17 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 2月 13 21:13 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 2月 13 21:17 ibtmp1
drwxr-x--- 2 mysql mysql 4096 2月 13 21:13 mysql
drwxr-x--- 2 mysql mysql 8192 2月 13 21:13 performance_schema
drwxr-x--- 2 mysql mysql 182 2月 13 21:13 school
drwxr-x--- 2 mysql mysql 8192 2月 13 21:13 sys
drwxr-x--- 2 mysql mysql 144 2月 13 21:13 world
drwxr-x--- 2 mysql mysql 104 2月 13 21:13 xbk
-rw-r----- 1 mysql mysql 166 2月 13 21:13 xtrabackup_binlog_info
-rw-r--r-- 1 mysql mysql 38 2月 13 21:17 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 141 2月 13 21:17 xtrabackup_checkpoints
-rw-r----- 1 mysql mysql 741 2月 13 21:13 xtrabackup_info
-rw-r----- 1 mysql mysql 8388608 2月 13 21:10 xtrabackup_logfile
-rw-r--r-- 1 mysql mysql 1 2月 13 21:17 xtrabackup_master_key_id
drwxr-x--- 2 mysql mysql 166 2月 13 21:13 oldboyedu
drwxr-x--- 2 mysql mysql 4096 2月 13 21:13 yoolia_mall
drwxr-x--- 2 mysql mysql 20 2月 13 21:13 zabbix
[root@docker201.oldboyedu.com ~]#
(6)启动MySQL数据库，验证数据是否完全恢复(不难发现xbk数据库下只有t1，t2，t3这几张表有数据)
[root@docker201.oldboyedu.com ~]# systemctl start mysqld23307
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 172.200.1.201:6379 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 80 [::]:23306 [::]:*
LISTEN 0 80 [::]:23307 [::]:*
LISTEN 0 128 [::]:23308 [::]:*
LISTEN 0 128 [::]:80 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 70 [::]:33060 [::]:*
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SHOW TABLES FROM xbk;
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
| t2 |
| t3 |
+---------------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM xbk.t1;
+------+
| id |
+------+
| 100 |
| 200 |
| 300 |
+------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM xbk.t2;
+------+
| id |
+------+
| 111 |
| 222 |
| 333 |
+------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM xbk.t3;
+------+
| id |
+------+
| 1 |
| 3 |
| 5 |
| 7 |
| 9 |
+------+
5 rows in set (0.00 sec)
mysql>
基于二进制日志(binlog)恢复未备份的数据(对应xbk数据库的t4,t5这两张测试表)
(1)在判断截取二进制日志的起始点和结束点时，我们先看看咱们基于全量备份，增量备份有关二进制日志的记录信息:
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/full_2021-02-13/xtrabackup_binlog_info
oldboyedu-mysqld-binary.000025 1844 3cfc679a-6db9-11eb-8c6f-000c29820c67:1-11,
855f764c-6dfb-11eb-b5a9-000c29820c67:1-8,
ecaf563f-5345-11eb-a106-000c29820c67:1-106
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/incremental_data01/xtrabackup_binlog_info
oldboyedu-mysqld-binary.000025 1408 3cfc679a-6db9-11eb-8c6f-000c29820c67:1-11,
855f764c-6dfb-11eb-b5a9-000c29820c67:1-6,
ecaf563f-5345-11eb-a106-000c29820c67:1-106
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /oldboyedu/backup/incremental_data02/xtrabackup_binlog_info
oldboyedu-mysqld-binary.000025 1844 3cfc679a-6db9-11eb-8c6f-000c29820c67:1-11,
855f764c-6dfb-11eb-b5a9-000c29820c67:1-8,
ecaf563f-5345-11eb-a106-000c29820c67:1-106
[root@docker201.oldboyedu.com ~]#
(2)综上所述，我们通常会看最新的增量日志文件，即"/oldboyedu/backup/incremental_data02/xtrabackup_binlog_info"，有关起始位置点信息如下所示:
基于GTID确定起始点:
对应的是最后一条记录"ecaf563f-5345-11eb-a106-000c29820c67:1-106"。
基于Pos确定起始点:
对应的位置是"Pos=1844"。
(3)如何确定结束点位置呢？由于我们搞破坏并没有指向SQL语句，而是直接删除了MySQL实例的所有数据文件，因此就无需指定结束点，指向以下操作进行日志截取:
[root@docker201.oldboyedu.com ~]# mysqlbinlog --skip-gtids --start-position=1844 /oldboyedu/logs/mysql23307/binlog/oldboyedu-mysqld-binary.000025 > /tmp/recover_db.log
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# ll -h /tmp/recover_db.log
-rw-r--r-- 1 root root 2.9K 2月 13 21:47 /tmp/recover_db.log
[root@docker201.oldboyedu.com ~]#
(4)基于截取的二进制日志恢复数据
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql23307.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql> SHOW TABLES FROM xbk; # 恢复之前只有3张表。
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
| t2 |
| t3 |
+---------------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> SET sql_log_bin=0; # 在指向下面的SOURCE命令前，先临时关闭二进制日志功能。
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> SOURCE /tmp/recover_db.log # 恢复数据
...
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SHOW TABLES FROM xbk; # 恢复后，t4和t5这两张表的数据恢复成功啦~
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
| t2 |
| t3 |
| t4 |
| t5 |
+---------------+
5 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM xbk.t4;
+------+
| id |
+------+
| 10 |
| 30 |
| 50 |
| 70 |
| 90 |
+------+
5 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM xbk.t5;
+------+
| id |
+------+
| 100 |
| 300 |
| 500 |
| 700 |
| 900 |
+------+
5 rows in set (0.00 sec)
mysql>
mysql> SET sql_log_bin=1; # 数据恢复成功后，记得开启二进制日志功能哈~
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> QUIT
Bye
[root@docker201.oldboyedu.com ~]#
4.简单版笔记
mysql 5.7全量备份到指定目录:
xtrabackup --defaults-file=/oldboyedu/etc/mysql3307/my.cnf --backup --target-dir=/tmp/`date +%F`
mysql 5.7增量备份到指定目录:
(1)准备测试数据
mysql -S /tmp/mysql3307.sock < homework-sql-init
(2)全量备份
xtrabackup --defaults-file=/oldboyedu/etc/mysql3307/my.cnf --backup --target-dir=/oldboyedu/backup/oldboyedu_movie
(3)模拟生产环境的数据
for i in `ls *.sql`;do mysql -S /tmp/mysql3307.sock < $i;done
mysql -S /tmp/mysql3307.sock -e "SELECT COUNT(*) FROM oldboyedu_movie.user;"
(4)增量备份
xtrabackup --defaults-file=/oldboyedu/etc/mysql3307/my.cnf --backup --incremental-basedir=/oldboyedu/backup/oldboyedu_movie --target-dir=/oldboyedu/backup/oldboyedu_movie_first
(5)再次修改数据
INSERT user VALUES (1,'孙悟空',3,5.8),(2,'猪八戒',10,7.3),(3,'唐僧',15,9.1);
(6)再次做增量备份
xtrabackup --defaults-file=/oldboyedu/etc/mysql3307/my.cnf --backup --incremental-basedir=/oldboyedu/backup/oldboyedu_movie_first --target-dir=/oldboyedu/backup/oldboyedu_movie_second
(7)删除数据
DROP DATABASE oldboyedu_movie;
(8)开始恢复
1)将最新的全量备份日志进行prepare整理
innobackupex --apply-log --redo-only oldboyedu_movie
2)将第一次的(incremental)增量备份日志合并到最新的全量备份日志并进行prepare整理
innobackupex --apply-log --redo-only --incremental-dir=/oldboyedu/backup/oldboyedu_movie_first oldboyedu_movie
3)将第二次的(incremental)增量备份日志合并到最新的全量备份日志并进行prepare整理,合并最后一次增量时无需使用"--redo-only"参数
innobackupex --apply-log --incremental-dir=/oldboyedu/backup/oldboyedu_movie_second oldboyedu_movie
4)整体再次预处理(prepare)整个备份
innobackupex --apply-log oldboyedu_movie 5.小彩蛋
场景:
总数据量3TB，共有13个业务，13个库3000多张表，周三下午18:00，误删除了"moubao.t1"核心业务表20GB数据，导致moubao库业务无法正常运行。
备份策略:
周日23:00做的是全量备份，周一到周六做的事增量备份，而且binlog日志功能是开启的。
问题:
如何快速恢复数据，还不影响其他业务呢？
仅供参考的解决方案:
(1)想要恢复单表，需要表结构和数据，我们可以借助mysqlfrm工具获取表结构
[root@docker201.oldboyedu.com ~]# yum -y install mysql-utilities
(2)使用"mysqlfrm --diagnostic t1.frm"获取建表语句
(3)基于上一步获取的建表语句在数据库中创建对应的表结构;
(4)创建好表结构后，立刻丢弃新建的表空间:
ALTER TABLE taobao.t1 DISCARD TABLESPACE;
(5)将表中数据拷贝会数据目录
[root@docker201.oldboyedu.com ~]# cp /oldboyedu/backup/full_data/taobao/t1.ibd /oldboyedu/data/mysql23307/taobao/t1.ibd
[root@docker201.oldboyedu.com ~]# chown mysql:mysql /oldboyedu/data/mysql23307/taobao/t1.ibd
(6)导入表空间
ALTER TABLE taobao.t1 IMPORT TABLESPACE;
小试牛刀:
请根据上述提示，写出对应的解决方案，重现故障，而后恢复故障等一些列模拟实验。四.常见的报错信息
1.Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
报错信息:
[root@docker201.oldboyedu.com ~]# innobackupex /oldboyedu/backup/xbk
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments:
210213 11:52:01 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!".
210213 11:52:02 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at -
line 1314.210213 11:52:02 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2).
[root@docker201.oldboyedu.com ~]#
错误分析:
很明显，该报错是找不到套接字文件。
解决方案:
如下所示，只需要在MySQL默认的配置文件中定义client标签，而后指定套接字文件位置即可。
[root@docker201.oldboyedu.com ~]# vim /etc/my.cnf
[root@docker201.oldboyedu.com ~]#
[root@docker201.oldboyedu.com ~]# cat /etc/my.cnf
[client]
socket=/tmp/mysql23307.sock
[root@docker201.oldboyedu.com ~]#
2.2021-07-21 17:14:15 7fb6113b5700 InnoDB: Error: page 3 log sequence number 1953995
问题分析:
未使用"innobackupex --apply-log"进行操作。
解决方案:
innobackupex --apply-log /oldboyedu/backup/oldboyedu_linux75_2021-07-21
3.Error: MySQL 8.0 and Percona Server 8.0 are not supported by Percona Xtrabackup 2.4.x series. Please use Percona Xtrabackup 8.0.x for backups and restores.
问题原因:
版本不兼容,因为 Percona Xtrabackup 2.4.x不支持MySQL 8.0.
解决方案:
推荐使用Percona Xtrabackup 8.0.x.
4.Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/tmp/mysql3307.sock'
问题原因:
MySQL实例可能未启动,如果MySQL实例正常启动请检查socket文件是否指定正确。
解决方案:
参考上面的建议即可.
5.Failed to connect to MySQL server: Access denied for user 'root'@'localhost' (using password: NO).
问题原因:
用户名或密码不正确
解决方案:
请检查是否有输入密码!可以在"my.cnf"中的"[client]"端检查配置哟~