目录
案例四：mysqldump+binlog 完全+增量备份恢复
MYSQL备份恢复管理
一，备份类型
热备份、温备份、冷备份 （根据服务器状态）
热备份：读、写不受影响；
温备份：仅可以执行读操作；
冷备份：离线备份；读、写操作均中止；
物理备份与逻辑备份 （从对象来分）
物理备份：复制数据文件；
逻辑备份：将数据导出至文本文件中；
完全备份、增量备份、差异备份 （从数据收集来分）
完全备份：备份全部数据；
备份速度慢，恢复快
增量备份：仅备份上次完全备份或增量备份以后变化的数据；
备份速度快，恢复慢；
差异备份：仅备份上次完全备份以来变化的数据；
备份速度慢，恢复快；
二，逻辑备份优缺点
（1）逻辑备份的优点：
在备份速度上两种备份要取决于不同的存储引擎
物理备份的还原速度非常快。但是物理备份的最小力度只能做到表
逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理
逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高
逻辑备份也对保持数据的安全性有保证
（2）逻辑备份的缺点：
逻辑备份要对RDBMS产生额外的压力，而裸备份无压力
逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩
逻辑备份可能会丢失浮点数的精度信息
三，MySQL备份内容
数据文件
日志文件（比如事务日志，二进制日志）
存储过程，存储函数，触发器
配置文件（十分重要，各个配置文件都要备份）
用于实现数据库备份的脚本，数据库自身清理的Crontab等……
四，MySQL备份工具
Mysql自带的备份工具
mysqldump 逻辑备份工具，支持所有引擎，MyISAM引擎是温备，InnoDB引擎是热备，备份速度中速，还原速度非常非常慢，但是在实现还原的时候，具有很大的操作余地。具有很好的弹性。
mysqlhotcopy 物理备份工具，但只支持MyISAM引擎，基本上属于冷备的范畴，物理备份，速度比较快。
文件系统备份工具
cp 冷备份，支持所有引擎，复制命令，只能实现冷备，物理备份。使用归档工具，cp命令，对其进行备份的，备份速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差。
lvm 几乎是热备份，支持所有引擎，基于快照(LVM，ZFS)的物理备份，速度非常快，几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用，所以备份速度比较快，恢复速度比较快，没有什么弹性空间，而且LVM的限制：不能对多个逻辑卷同一时间进行备份，所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。
其它工具
ibbackup 商业工具 MyISAM是温备份，InnoDB是热备份 ，备份和还原速度都很快，这个软件它的每服务器授权版本是5000美元。
xtrabackup 开源工具 MyISAM是温备份，InnoDB是热备份 ，是ibbackup商业工具的替代工具。
mysqlbackup ORACLE公司也提供了针对企业的备份软件MySQL Enterprise Backup简称：mysqlbackup。
MySQL企业备份工具执行在线“热备“，无阻塞的MySQL数据库备份。全备份可以在所有InnoDB数据库上执行，而无需中断MySQL查询或更新。此外，支持增量备份，只备份自上次备份后有变化的数据。另外部分备份，支持特定的表或表空间按需要进行备份。
五，MySQL备份策略
1，策略一：直接拷贝数据库文件
直接拷贝数据库文件（文件系统备份工具 cp）（适合小型数据库，是最可靠的）
当你使用直接备份方法时，必须保证表不在被使用。如果服务器在你正在拷贝一个表时改变它，拷贝就失去意义。保证你的拷贝完整性的最好方法是关闭服务器，拷贝文件，然后重启服务器。
当你完成了备份时，需要重启服务器(如果关闭了它)或释放加在表上的锁定(如果你让服务器运行)。要用直接拷贝文件把一个数据库从一台机器拷贝到另一台机器上，只是将文件拷贝到另一台服务器主机的适当数据目录下即可。
要确保文件是MyIASM格式或两台机器有相同的硬件结构，否则你的数据库在另一台主机上有奇怪的内容。
准备工作
（1）在配置文件/etc/my.cnf中的[mysqld]下添加命令，使其支持中文
character-set-server=utf8
（2）重启服务
[root@node1 data]# systemctl restart mysqld
（3）查看配置后的结果
mysql> show variables like '%char%';
（4）创建数据库company；并使用
mysql> create database company;
mysql> use company;
（5）在数据库下建表emp
mysql> CREATE TABLE `emp` (
`empno` int(4) NOT NULL,
`ename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`mgr` int(4) NULL DEFAULT NULL,
`hiredate` date NOT NULL,
`sai` int(255) NOT NULL,
`comm` int(255) NULL DEFAULT NULL,
`deptno` int(2) NOT NULL,
PRIMARY KEY (`empno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
（6）向表emp中插入数据
INSERT INTO `emp` VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO `emp` VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO `emp` VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO `emp` VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO `emp` VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO `emp` VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO `emp` VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO `emp` VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO `emp` VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO `emp` VALUES (1011, '周泰', '文员', 1006, '2007-05-23', 11000, NULL, 20);
INSERT INTO `emp` VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO `emp` VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO `emp` VALUES (1015, '张三', '保洁员', 1001, '2013-05-01', 80000, 50000, 50);
（7）在数据库company中查看表emp的信息
案例：直接拷贝备份数据库company
1、停止MySQL服务器。
[root@node1 data]# systemctl stop mysqld
查看mysql是否停止
[root@node1 data]# systemctl status mysqld
2、直接复制整个数据库目录。注意：使用这种方法最好还原到相同版本服务器中，不同版本可能不兼容。
（1）把当前所有的数据目录打包放在/tmp/database.tar.gz
[root@node1 data]# tar czf /tmp/database.tar.gz *
（2）可以查看包中的所有目录
[root@node1 data]# tar tf /tmp/database.tar.gz
3、目标服务器上还原。
（1）使用scp命令将刚才的压缩文件拷贝到目标服务器上node2上的/tmp下面
[root@node1 data]# scp /tmp/database.tar.gz 192.168.206.142:/tmp
（2）在node2主机上开启二进制日志并设置可读中文
log_bin=mysql-bin
server_id=2
character-set-server=utf8
（3）node2主机上重启服务mysqld
[root@node2 ~]# systemctl restart mysqld
（4）在node2主机上关闭mysqld服务
[root@node2 ~]# systemctl stop mysqld
（5）在node2主机上删除/usr/local/mysql/data/ 目录下的所有文件
[root@node2 ~]# rm -rf /usr/local/mysql/data/*
（6）将从node1主机上拷贝过来的/tmp/database.tar.gz 解开到/usr/local/mysql/data/下面
[root@node2 ~]# tar xf /tmp/database.tar.gz -C /usr/local/mysql/data/
4、目标服务器数据库目录授权，重启服务器测试。
（如果重启失败，记得授权：chown -R mysql.mysql /usr/local/mysql/data/*）
[root@node2 ~]# systemctl restart mysqld
5，此时可以登录数据库查看是否备份成功
[root@node2 ~]# mysql -uroot -p123456 -e 'select * from company.emp'
2，策略二：mysqldump备份数据库
案例一：备份恢复单个数据库
语法：
备份：mydqldump -u 用户 –p’密码’ –default-character-set=Iatin1 数据库名 [|gzip] > 备份文件名
注意：-B 作用：创建数据库和切换到数据库，恢复时不用创建数据库和删表。备份多个库，-B 数据库1 数据库2 ...
恢复：
1）MySQL中用source命令
2）mysql命令恢复
-F 刷新日志
-A 备份所有的数据库
-B 备份数据库时包含创建数据库并切入到数据库中的语句
备份数据库company
（1）在node1主机上创建目录/back/db用于存放备份文件
[root@node1 data]# mkdir -p /backup/db
（2）将node1主机上的数据库company备份到/backup/db/下面命名为company_1.sql
[root@node1 data]# mysqldump -uroot -p123456 company > /backup/db/company_1.sql
（3）加上B选项后再次备份到/backup/db/下面命名为company_2.sql
[root@node1 data]# mysqldump -uroot -p123456 -B company > /backup/db/company_2.sql
（4）对比加上B选项后的作用；可以看出加上B选项后，多了创建数据库并使用数据库的命令
[root@node1 data]# diff /backup/db/company_2.sql /backup/db/company_1.sql
删除数据库company
mysql8.0.30 [(none)]>drop database company;
恢复数据库company
方法一：在数据库里面用source命令
（1）创建数据库company并使用
mysql8.0.30 [(none)]>create database company;
mysql8.0.30 [(none)]>use company;
（2）使用命令恢复数据库company
mysql8.0.30 [company]>source /backup/db/company_1.sql
（3）查看恢复后的数据库中的数据
mysql8.0.30 [company]>select * from company.emp;
方法二：使用mysql命令来恢复
（1）此时的company数据库已经被删除
mysql8.0.30 [company]>drop database company;
mysql8.0.30 [(none)]>show databases;
（2）恢复数据库company_2.sql
[root@node1 data]# mysql -uroot -p123456 < /backup/db/company_2.sql
（3）查看恢复后的数据库中的数据
mysql8.0.30 [(none)]>select * from company.emp;
注意：备份多个数据库用空格间隔开，备份所有数据库用-A选项
案例二：备份单个表
语法：
备份：mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名
示例：mysqldump -uroot -p school Books > /tmp/Books.$(date +%F).sql
备份多个表：mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份的文件名
备份company数据库中的表emp
（1）把数据库company的emp表备份到/backup/db/下面命名为emp.sql
[root@node1 data]# mysqldump -uroot -p123456 company emp > /backup/db/emp.sql
（2）在数据库company中删除表emp
mysql8.0.30 [company]>drop table emp;
mysql8.0.30 [company]>show tables;
恢复数据库中的表emp
[root@node1 data]# mysql -uroot -p123456 company < /backup/db/emp.sql
此时在数据库company中查看表tables，发现信息已经被恢复
mysql8.0.30 [company]>show tables;
mysql8.0.30 [company]>select * from emp;
案例三：备份数据库的结构
-d只备份库结构，不包含数据内容
备份并压缩： 备份命令 | gzip > 备份的路径文件.gz
（1）备份数据库company的结构
[root@node1 data]# mysqldump -uroot -p123456 -d company > /backup/db/company_3.sql
（2）查看备份文件/backup/db/company_3.sql的内容；发现只有创建表，并没有插入数据
案例四：mysqldump+binlog 完全+增量备份恢复
前提：1）my.cnf，是要开启MySQL log-bin日志功能，重启MySQL log_bin
= /data/mysql/data/mysql-bin
2）存在一个完全备份，生产环境一般凌晨某个时刻进行全备
示例：mysqldump -uroot -p --default-character-set=gbk --single-transaction -F -B school |gzip > /server/backup/school_$(date +%F).sql.gz
InnoDB 表在备份时，通常启用选项 --single-transaction 来保证备份的一致性
MySQL增量恢复案例图解
1，数据库的准备工作
（1）创建数据库it
mysql8.0.30 [company]>create database it DEFAULT CHARACTER SET utf8;
（2）在数据库it中创建表student
mysql8.0.30 [it]>CREATE TABLE `Student` (
`Sno` int(10) NOT NULL COMMENT '学号', `Sname` varchar(16) NOT NULL COMMENT '姓 名',
`Ssex` char(2) NOT NULL COMMENT '性别', `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄',
`Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别', PRIMARY KEY (`Sno`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
（3）向表student中插入数据
mysql8.0.30 [it]>INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');
（4）查看表student中的详细信息
mysql8.0.30 [it]>select * from Student;
2，完全备份
（1）完全备份此时的it数据库并压缩保存到/backup/db/it_`date +%F`.sql.gz中
--single-transaction 保持一致性
[root@node1 data]# mysqldump -uroot -p123456 -F -B it --single-transaction | gzip > /backup/db/it_`date +%F`.sql.gz
（2）备份文件如下所示：
[root@node1 data]# ll /backup/db/
3，等60秒后再次向表中插入数据
睡眠60秒
[root@node1 data]# sleep 60
向表中插入两条数据
mysql8.0.30 [it]>INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'),(0006,'wangzhao','男',21,'导弹专业');
查看插入数据后的student表
mysql8.0.30 [it]>select * from it.Student;
4，模拟破坏数据库
mysql8.0.30 [it]>drop database it;
此时的数据库it已经被破坏
mysql8.0.30 [(none)]>show databases;
5，检查完全备份后面的所有binlog日志
[root@node1 data]# ls -lrt /usr/local/mysql/data/mysql-bin.*
立即刷新日志，保护现场
[root@node1 data]# mysqladmin -uroot -p flush-logs
此时多出来一条8日志，我们要备份7日志
[root@node1 data]# ls -lrt /usr/local/mysql/data/mysql-bin.*
6，增量备份
（1）将日志7保存到/backup/db下面
[root@node1 data]# cp /usr/local/mysql/data/mysql-bin.000007 /backup/db
（2）恢复binlog生成sql语句
[root@node1 data]# mysqlbinlog mysql-bin.000006 > bin.sql
7，恢复数据
（1）恢复完全备份
[root@node1 db]# zcat it_2023-04-04.sql.gz | mysql -uroot -p123456
此时的it数据库的student中完全备份的信息已经被恢复
mysql8.0.30 [(none)]>show databases;
mysql8.0.30 [(none)]>select * from it.Student;
（2）增量恢复：
查看binlog生成sql语句
[root@node1 db]# mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin.000007
方法一：基于时间恢复
按时间选取要恢复的sql语句并保存到文件time1.sql
[root@node1 db]# mysqlbinlog --start-datetime='2023-04-04 12:57:34' mysql-bin.000007 --stop-datetime='2023-04-04 12:58:26' -r time1.sql
恢复增量备份的文件
[root@node1 db]# mysql -uroot -p123456 < time1.sql
查看恢复后的数据库的student表的信息
mysql8.0.30 [(none)]>select * from it.Student;
方法二：基于位置恢复
按时间选取要恢复的sql语句并保存到文件pos1.sql
[root@node1 db]# mysqlbinlog --start-position=347 --stop-position=476 mysql-bin.000007 -r pos1.sql
恢复增量备份的文件
[root@node1 db]# mysql -uroot -p123456 < pos1.sql
查看恢复后的数据库的student表的信息
mysql8.0.30 [(none)]>select * from it.Student;
高级备份参数：
-R 备份存储过程及函数
--triggers 备份触发器
-E 备份事件
-F 备份开始时间，刷新一个binlog日志
--master-data=2 以注释的形式，保存备份开始时间及binlog的状态信息
gtid默认没有开启，如果开启，下一个编号默认增加
mysql8.0.30 [it]>show variables like '%gtid%';
六，binlog日志的GTID新特性
1，什么是GTID
GTID(Global Transaction ID)，全局事务标识符。是对于一个已提交事务的编号，并且是一个全局唯一的编号。
它是MySQL 5.6加入的一个强大特性，目的在于能够实现主从自动定位和切换，而不像以前需要指定文件和位置。
2， GTID的格式与存储
（1）单个GTID
GTID与主库上提交的每个事务相关联。此标识符不仅对发起事务的库是唯一的，而且在给定复制拓扑中的所有库中都是唯一的。GTID用冒号分隔的一对坐标表示，
例如：8eed0f5b-6f9b-11e9-94a9-005056a57a4e:23
前一部分是主库的server_uuid，后面一部分是主库上按提交事务的顺序确定的序列号，提交的事务序号从1开始。
GTID = server_uuid ：transaction_id
（2）GTID集
GTID集是包括一个或多个单个GTID或GTID范围的集合。源自同一服务器的一系列GTID可以折叠为单个表达式，
例如：8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-321
上面的示例表示源自server_uuid为8eed0f5b-6f9b-11e9-94a9-005056a57a4e服务器的第1到第
321个事务。源自同一服务器的多个单GTID或GTID范围可以同时包含在由冒号分隔的单个表达式中，
例如：8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-3:11:47-49
（3）mysql.gtid_executed表
mysql.gtid_executed表结构如下：
mysql8.0.30 [(none)]>desc mysql.gtid_executed;
mysql.gtid_executed表记录的是服务器上已经执行事务的GTID。三个字段分别表示发起事务的服务器UUID、UUID集的起始和结束事务ID。对于单个GTID，后两个字段的值相同。
3，版本支持
5.6 版本新加的特性,5.7中做了加强
5.6 中不开启,没有这个功能.
5.7 中的GTID,即使不开也会有自动生成
4，如何开启
（1）编辑my.cnf文件
[root@node1 ~]# vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
（2）重启mysqld服务
[root@node1 ~]# systemctl restart mysqld
（3）此时查看gtid，发现已经开启
mysql8.0.30 [(none)]>show variables like '%gtid%';
5， DDL和DML语句查看gtid
DDL一个语句产生一个gtid
创建数据库，创建表，插入数据都会产生一个gtid
（1）查看此时的gtid与日志文件编号
mysql8.0.30 [(none)]>show master status;
（2）创建一个数据库t2
mysql8.0.30 [(none)]>create database db2;
（3）此时查看gtid，发现创建了一个数据库后多了一个gtid
服务器的uuid为：7010c99b-c62f-11ed-a197-000c293aaeaf
gtid的编号为： 1-4
mysql8.0.30 [(none)]>show master status;
（4）使用数据库db2，并不会产生新的gtid
mysql8.0.30 [(none)]>use database db2;
mysql8.0.30 [db2]>show master status;
（5）在数据库db2中创建表t1，发现产生了新的gtid
mysql8.0.30 [db2]>create table t1(id int);
mysql8.0.30 [db2]>show master status;
（6）向表t1中插入数据1，发现产生了新的gtid
mysql8.0.30 [db2]>insert into t1 values(1);
mysql8.0.30 [db2]>show master status;
DML一个事务产生一个gtid
一个事物产生一个gtid
把多个语句放入一个事物中，用begin开始，commit结束；一个事物产生一个gtid
mysql8.0.30 [db2]>begin;
mysql8.0.30 [db2]>insert into t1 values(2);
mysql8.0.30 [db2]>insert into t1 values(3);
mysql8.0.30 [db2]>insert into t1 values(4);
mysql8.0.30 [db2]>commit;
mysql8.0.30 [db2]>show master status;
6，基于GTID进行查看binlog
具备GTID后,截取查看某些事务日志:
--include-gtids
--exclude-gtids
mysql8.0.30 [db2]>show binlog events in 'mysql-bin.000011';
案例：演示跨binlog文件截取日志。
（1）此时的日志文件存在 mysql-bin.000011里面，gtid为1-7
mysql8.0.30 [db2]>show master status;
第一次操作
（2）创建一个数据库gtid，产生一个新的gtid,8
mysql8.0.30 [db2]>create database gtid;
mysql8.0.30 [db2]>show master status;
（3）创建t1表并插入数据，此时产生了两个gtid，9,10；
mysql8.0.30 [gtid]>create table t1(id int);
mysql8.0.30 [gtid]> insert into t1 values(1);
mysql8.0.30 [gtid]>show master status;
（4）刷新日志文件，此时查看，发现产生了一个新的日志文件12
mysql8.0.30 [gtid]>flush logs;
mysql8.0.30 [gtid]>show master status;
第二次操作
（5）在新的日志文件12中创建一个新表t2;插入数据，刷新日志；
mysql8.0.30 [gtid]>create table t2(id int);
mysql8.0.30 [gtid]>insert into t2 values(1);
mysql8.0.30 [gtid]>flush logs;
（6）此时会产生一个新的日志文件13
mysql8.0.30 [gtid]>show master status;
第三次操作
（7）在新的日志文件中创建表t3，并插入数据，此时的gid已经到14
mysql8.0.30 [gtid]>create table t3(id int);
mysql8.0.30 [gtid]>create table t3(id int);
mysql8.0.30 [gtid]>insert into t3 values(1);
mysql8.0.30 [gtid]>show master status;
使用binlog日志恢复误删除的gitd数据库。
首先要确定gtid的起始和结束。
此时gtid的范围为7-14
uuid为 ：7010c99b-c62f-11ed-a197-000c293aaeaf
涉及到的文件mysql-bin.000011,12,13
误操作删除数据库gtid
mysql8.0.30 [gtid]>drop database gtid;
恢复数据库的信息：
（1）binlog使用gtid截取日志，日志保存到/backup/db/t1.sql
[root@node1 data]# mysqlbinlog --skip-gtids --include-gtids='7010c99b-c62f-11ed-a197-000c293aaeaf:8-14' mysql-bin.000011 mysql-bin.000012 mysql-bin.000013 -r /backup/db/t1.sql
mysql8.0.30 [(none)]>set sql_log_bin=0;
（3）使用binlog日志恢复
mysql8.0.30 [(none)]>source /backup/db/t1.sql;
（4）查看所有数据库，发现gtid已经恢复
mysql8.0.30 [gtid]>show databases;
（5）在gtid数据库中查看所有表，发现3张表已经恢复
mysql8.0.30 [gtid]>show tables;
（6）查看三张表中的数据，发现数据已经被恢复
mysql8.0.30 [gtid]>select * from t1;
4952
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
