文章目录
1. MySQL数据损坏类型
1.1 物理损坏
磁盘损坏:硬件,磁道坏,dd,格式化
文件损坏:数据文件损坏,redo损坏
1.2 逻辑损坏
drop、delete、truncate、update
2. DBA运维人员备份/恢复职责
2.1 设计备份/容灾策略
2.1.1 备份策略
备份工具原则
备份周期设计
备份监控方法
2.1.2 容灾策略
备份:用什么备份
架构: 高可用,延时从库,灾备库
容灾的好处: 在发生灾难的时候,怎么能快速的恢复业务。可以用备份或高可用的架构
2.2 定期的备份/容灾检查
备份软件 ------> 带库(磁带)
每周 北京 --> 天津 货运
一定要定时的检查数据库的备份情况,以防止自己写的脚本有bug,保证正常备份。
2.3 定期的故障恢复演练
至少每年两次的恢复演练。在测试环境进行恢复演练。
2.4 数据损坏时的快速准确恢复
每个产品数据库出现故障,要快速的恢复数据,把相关命令写清楚,每一种损坏的应对策略。
2.5 数据迁移工作
一般用备份的手段或者主从的手段来进行迁移
3. MySQL常用备份工具
3.1 逻辑备份方式
mysqldump(MDP) *****
replication(主从方式)
mydumper(自行扩展)
load data in file(自行扩展)
逻辑备份: 备份的是sql语句
3.2 物理备份方式
MySQL Enterprise Backup(企业版)
Percona Xtrabackup(PBK,XBK)*****
物理备份: 备份的是表空间文件(数据文件)
小提示:社区版没有自带的物理备份方式
3.3 架构备份方式
架构备份方式,属于逻辑的一种,比如MySQL主从。
高可用和主从主要解决物理损坏(磁盘或主机坏了),逻辑损坏时没办法解决的
4. mysqldump应用
4.1 mysqldump介绍
逻辑备份工具。备份的是SQL语句。
查看mysqldump的参数帮助:mysqldump --help
4.2 mysqldump备份方式
innodb表,备份的时候不需要锁原来的表的,原来的表该干什么干什么,不受影响。
因为这只是一个优先读的快照,不会影响其他人操作
非innodb表在mysql中基本上都是系统相关的表,系统表要备份,元数据要备份的话,要进行
全局锁表
做全备份的时候,有innodb和非innodb,备份时会有短暂的锁表的情况,尽量避免非innodb锁表的情况
mysql8.0以后,大部分的系统表都被存储到innnodb表中,备份起来性能会更好一些
4.2.1 InnoDB表
可以采用快照备份的方式
开启一个独立的事务,获取当前最新的一致性快照。
将快照数据,放在临时表中,转换成SQL(Create database,Create table,insert),保存到sql文件中
4.2.2 非InnoDB表
需要锁表备份,触发FTWRL,全局锁表。转化成SQL(Create database,Create table,insert),保存到sql文件中
4.3 mysqldump核心参数
4.3.1 mysqldump连接参数
-u 用户名 -p密码 -h IP地址 -P 端口号 -S 套接字文件
mysql也好,mysqldump也好,在你没有指定-S或者配置文件里面也没有-S的时候,也会自动去/tmp里面去找,-S 不指定也行,默认会去找/tmp/mysql.sock,如果mysql.sock在其他地方,那就必须要加-S或者在配置文件加上参数指定mysql.sock的具体位置
避免mysql密码暴露问题:(一般不采用,没有必要)
# 在配置文件中添加这些参数,登录mysql时不需要密码(一般不采用,没有必要)。
vim ~/.my.cnf
[mysql]
user=root
password=123456
[mysqldump]
# 读取mysql配置文件顺序: 后面的参数覆前面的参数
[root@db01 ~]# mysql --help --verbose | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
4.3.2 mysqldump备份参数
-A 全备
[root@db01 ~]# mkdir -p /data/backup
[root@db01 ~]# chown -R mysql.mysql /data/*
[root@db01 ~]# mysqldump -uroot -p123456 -S /tmp/mysql.sock -A > /data/backup/full.sql
[root@db01 ~]# vim /data/backup/full.sql
例子1:
[root@db01 ~]# mkdir -p /data/backup
mysqldump -uroot -p -A >/data/backup/full.sql
Enter password:
mysqldump: [Warning] Using a password on the command line interface can be insecure.
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 restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
# 补充:
# 1.常规备份是要加 --set-gtid-purged=OFF,解决备份时的警告
# [root@db01 ~]# mysqldump -uroot -p123 -A --set-gtid-purged=OFF >/backup/full.sql
# 2.构建主从时,做的备份,不需要加这个参数
# [root@db01 ~]# mysqldump -uroot -p123 -A --set-gtid-purged=ON >/backup/full.sql
-B 备份单库或多个库(-B后面跟的都是库名)
# 备份world库和test库
[root@db01 ~]# mysqldump -uroot -p123456 -B world test > /data/backup/db.sql
备份单表或多表(固定语法: 数据库 表 表 表,不能写多个库名)
# 备份world库中的city表和coutry表
[root@db01 ~]# mysqldump -uroot -p123456 -S /tmp/mysql.sock world city country > /data/backup/tab.sql
验证一下:以下两个命令的备份结果区别?
mysqldump -uroot -p123456 -B world > /data/backup/db1.sql
# 上面的比下面多出两条sql
create database world;
use world;
# 这条sql备份world库下面的所有表。应用时,world库不存在,需要手工创建,并且use到world库下再恢复。
mysqldump -uroot -p123456 world > /data/backup/db2.sql
对比两个sql文件:vimdiff db1.sql db2.sql
4.3.3 mysqldump备份高级参数
4.3.3.1 --master-data参数
场景:每周日23:00全备,周1-6进行binlog备份。所有备份时完整的。
周三时,有一个核心运维人员济宁了删库操作。
恢复思路:恢复全备 + 所有需要binlog恢复
痛点:binlog的截取。起点查找比较困难,有两种方法
起点: 一定要找到备份开启时刻准确的position号以及binlog文件名,因为对innodb来说时快照备份,对于myisam是锁表备份,所以全备的那一刻起就必须找到准确起点position号。有两种方法找起点。
方法一:备份开始时,自动记录日志文件信息(有position号) --master-data=2(–master-data=2在备份开始的那个时刻,记录一下当前日志文件的一个状态)常用方法
方法二:备份开始时,切割日志。-F(备份一开始binlog日志就是flush一下,生成一个新的文件,切割一个新的日志出来,-F 备份起始的时候,直接刷新一个新的日志,是有多少个库,刷新多少个。这个参数使用起来不太方便)
终点: drop之前的位置点
–master-data=2 参数详解:
[root@db01 binlog]# mysqldump --help
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.
功能:
(1)备份时自动记录binlog信息
(2)自动锁表和解锁
(3)配合single transaction 可以减少锁表时间
开始实验
确认起点方法一:(生产中使用–master-data=2参数)
[root@db01 backup]# mysqldump -uroot -p123456 -A --master-data=2 > /data/backup/full1.sql;
[root@db01 backup]# mysqldump -uroot -p123456 -A --master-data=1 > /data/backup/full2.sql;
对比 --master-data=2和 --master-data=1备份出来的区别:
vim full1.sql full2.sql
区别:–master-data=2多了主从的命令,但是前面有注释。 --master-data=1没有注释,我们不需要主从的命令,真要使用主从,手动去指定恢复的起点。
确认起点方法二:(一般不用这种方法,了解即可)
[root@db01 binlog]# ls
mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.000007
mysql-bin.000002 mysql-bin.000004 mysql-bin.000006 mysql-bin.index
# 这种方法切出来的日志文件比较多而且混乱,不好确定位置点
[root@db01 binlog]# mysqldump -uroot -p123456 -A -F > /data/backup/full3.sql
[root@db01 binlog]# ls
mysql-bin.000001 mysql-bin.000008 mysql-bin.000015 mysql-bin.000022 mysql-bin.000029
mysql-bin.000002 mysql-bin.000009 mysql-bin.000016 mysql-bin.000023 mysql-bin.000030
mysql-bin.000003 mysql-bin.000010 mysql-bin.000017 mysql-bin.000024 mysql-bin.000031
mysql-bin.000004 mysql-bin.000011 mysql-bin.000018 mysql-bin.000025 mysql-bin.index
mysql-bin.000005 mysql-bin.000012 mysql-bin.000019 mysql-bin.000026
mysql-bin.000006 mysql-bin.000013 mysql-bin.000020 mysql-bin.000027
mysql-bin.000007 mysql-bin.000014 mysql-bin.000021 mysql-bin.000028
4.3.3.2 --single-transaction参数
对于InnoDB引擎表备份时,开启一个独立事务,获取一致性快照,进行备份。
# --master-data=2 --single-transaction生产必加参数
[root@db01 backup]# mysqldump -uroot -p123456 -A --master-data=2 --single-transaction > /data/backup/full1.sql
[root@db01 binlog]# mysqldump --help
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
4.3.3.2 -R/-E/–triggers参数
这一组参数涉及mysql高级变成部分,涉及到的一些对象,可以理解为和表有一定的关系,所以备份时也要加上。
拿Linux系统来通俗的理解这些参数:
-R 类似于自己写的程序脚本,如果之备份linux系统,不备份自己写的脚本程序,那就缺少了功能【备份存储过程和函数,相当于再数据库里开发的一些程序】
-E 类似于计划任务,【备份事件】
–triggers类似于特殊类的脚本,【备份触发器】
# -R -E --triggers生产环境必备的参数
[root@db01 backup]# mysqldump -uroot -p123456 -A --master-data=2 --single-transaction -R -E --triggers > /data/backup/full1.sql
4.3.3.2 --max_allowed_packet参数
传输包的最大传输大小:--max_allowed_packet=64M
# 调整客户端--max_allowed_packet大小,如果64M还是不够,那就调整128M
[root@db01 backup]# mysqldump -uroot -p123456 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M > /data/backup/full1.sql
深度解析:
两种情况:(mysqldump是修改客户端–max_allowed_packet的大小)
(1) mysql发送insert指令是客户端向服务端发送数据,如果报错Got a packet bigger than ‘max_allowed_packet’ bytes
,那么就修改服务端–max_allowed_packet的大小。
(2)mysqldump命令指令是服务端向客户端发送数据,如果报错Got a packet bigger than ‘max_allowed_packet’ bytes
,那么就修改客户端–max_allowed_packet的大小。
5. mysqldump+binlog故障恢复案例
这个栗子可以用binlog也可以恢复,但是假设这个创库创表两年前就有了,用全备+binlog进行恢复。
注意:备份数据、mysql数据、日志要分开放,别放一个地方
案例场景:
基础环境: Centos 7.6 + MySQL 5.7.28, LNMT网站业务, 数据量100G, 每天5-10M数据增长。
备份策略: mysqldump每天全备,binlog定时备份。
故障说明: 周三上午10点数据故障,例如: 核心业务库被误删除。
恢复思路:
1. 挂维护页(停业务,避免数据的二次伤害)
2. 找测试库
3. 恢复周二23:00全备
4. 根据周二全备文件找到起点的position号 --> 周三上午10点误删除之前的binlog,并恢复
5. 测试业务功能正常
6. 恢复业务(在MySQL中有边缘数据库,有可能其他边缘业务使用,不敢直接进行全备恢复。从测试库中单库(核心库)导出来,然后再恢复到生产库中)
方案1: 故障库导回到原生产
方案2: 直接用测试库称当生产,先跑着
模拟数据库损坏及恢复:
1. 模拟原始数据
mysql> create database mdp charset utf8mb4;
mysql> use mdp;
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
2. 模拟周二晚上全备
mysql> mysqldump -uroot -p123456 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M > /data/backup/full_`date +%F`.sql
压缩备份:mysql> mysqldump -uroot -p123 -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
3. 模拟周二 23:00到周三 10点之间数据变化
mysql> use mdp;
mysql> create table t2 (id int);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;
4. 模拟故障,删除表(只是模拟,不代表生产操作)
mysql> drop database mdp;
5. 开始恢复(再测试环境进行恢复,如果生产环境只是损坏了一张表,那么就在测试环境全备+binlog恢复完之后,进行导出单表,然后正在恢复到生产环境)
5.1 检查全备
[root@db01 backup]# vim full_2023-02-23.sql
# 备份开始时的binlog日志的起点位置
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000031', MASTER_LOG_POS=788;
5.2 恢复全备
# 如果开启了GTID,在备份文件里面有SET @@SESSION.SQL_LOG_BIN=0作用是临时取消了二进制日志记录,但是没有自动开启,所以还是手动临时开启和关闭
mysql> set sql_log_bin=0;
mysql> source /data/backup/full_2023-02-23.sql
验证:(mdp数据库已恢复,t1表和数据也已恢复)
mysql> use mdp;
mysql> show tables;
+---------------+
| Tables_in_mdp |
+---------------+
| t1 |
+---------------+
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
用position号和GTID都可以来截取日志:(二选一)
5.3 截取binlog
position截取:
起点:(788)
[root@db01 backup]# grep "\-- CHANGE MASTER TO" /data/backup/full_2023-02-23.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000031', MASTER_LOG_POS=788
终点:(使用1210或者使用1275都可以,这里使用1210比较好)
mysql> show binlog events in 'mysql-bin.000031';
| mysql-bin.000031 | 1210 | Gtid | 6 | 1275 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:24' |
| mysql-bin.000031 | 1275 | Query | 6 | 1364 | drop database mdp
position日志截取:
# 虽然说时使用的position号截取的,但是因为binlog里面开启了GTID,所以依然要加上参数--skip-gtids
mysqlbinlog --skip-gtids --start-position=788 --stop-position=1210 /data/binlog/mysql-bin.000031 > /tmp/binlog.sql
GTID截取:(备份文件中的信息)
# GTID号进行恢复,在备份文件中存在GTID的信息SET @@GLOBAL.GTID_PURGED='XXX:1-21';
GLOBAL.GTID_PURGE这是一个命令,意思是全备恢复数据的时候把1-21号的GTID给删了,删完了之后再恢复
言外之意是,此备份中已经具备了1-21号GTID信息了,也就是说全备结束的GTID的位置是21号
[root@db01 backup]# vim full_2023-02-23.sql
SET @@GLOBAL.GTID_PURGED='638fec45-e734-11ec-bcd2-000c29d09be0:1-21';
起点: 638fec45-e734-11ec-bcd2-000c29d09be0:22
# show binlog events in 'mysql-bin.000031';查看确定终点
终点: 638fec45-e734-11ec-bcd2-000c29d09be0:23(因为gtid=24为删库操作,所以要恢复到gtid的终点是23)
| mysql-bin.000031 | 1210 | Gtid |6 |1275 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:24' |
| mysql-bin.000031 | 1275 | Query| 6 |1364 | drop database mdp
GTID日志截取:(gtid可以跨文件,position号是不可以跨文件的。这也是gtid的优势所在)
mysqlbinlog --skip-gtids --include-gtids='xxx:17-18' /data/binlog/mysql-bin.000031 > /tmp/binlog1.sql
小提示:没有开启gitd的话也要加上--skip-gtids,因为截取出还是带有gtid的(猜测应该是之前开过gtid,不加也成功的话,因备份文件里面有SET @@GLOBAL.GTID_PURGED='XXX:1-21',删除了gtid)
建议加上--skip-gtids,标准用法
5.4 恢复binlog
# 如果开启了GTID,在备份文件里面有SET @@SESSION.SQL_LOG_BIN=0作用是临时取消了二进制日志记录,但是没有自动开启,所以还是手动临时开启和关闭
mysql> set sql_log_bin=0;
# 使用position号或者gtid进行截取的日志,进行恢复
mysql> source /tmp/binlog.sql;
mysql> set sql_log_bin=1;
# 验证数据,成功恢复
mysql> use mdp;
mysql> show tables;
+---------------+
| Tables_in_mdp |
+---------------+
| t1 |
| t2 |
+---------------+
小练习:
使用rm -rf /data/3306/* 故障模拟恢复
恢复思路:
1. 需要把原来的实例给kill掉
2. kill完之后把目录清空,重新进行初始化,重新初始之后再按这上面的思路进行恢复
6. mysqldump小总结
参数:
-u -p -S -h -P
-A -B --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64
选择场景:
优点: 可读性比较强,压缩比,节省空间,不需要下载安装。
缺点:备份时间相对较长。恢复时间长。
数据量较少,建议mysqldump 100G以内。
数据量巨大: 分布式架构,数据量较大时候,可以采用分布式备份。也可以选择mysqldump。(分布式架构(分别备份不同的分片),一个T数据,砍成十份,每份100G,并发的分布式备份。然后就没有这么慢了
如果全备数据量大,但是只想恢复一张表,而这张表只有两M,这时可以从全备中截取单表)
mysqldump恢复是备份两倍以上的时间
mysqldump用压缩工具压缩备份出来的文件的压缩比高(比物理备份要高)
思考:mysql如何进行分布式备份???
扩展:
从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