1、mysqldump 介绍
mysqldump 是一个逻辑备份工具 记录 (create database \ create table \ insert) 操作的SQL语句MySQL 自带的客户端命令、可以实现远程备份 (TCP连接) 和 本地备份(socket连接)
2、连接参数
-u -p -h -P -S
3、备份基本参数
-A: 全备
1. 本地备份:
mkdir -p /data/backup
格式:
mysqldump -uuser -ppassword -S mysql.sock
举例:
mysqldump -uroot -p123 -S /tmp/mysql.sock -A > /data/backup/full.sql
2. 远程备份
格式:
mysqldump -user -ppassword -h 10.0.0.0 -P 3306
举例:
mysqldump -uadmin -p123 -h 192.168.10.13 -P 3307 -A > /data/backup/full.sql
3. 注意事项
使用 > 输出重定向, 不可改为 &>, 会把警告信息输入到sql文件, 造成无法恢复
-B: 备份单库或多库
格式:
mysqldump -uuser -ppassword -B 库1 库2 ...
举例:
mysqldump -uroot -p123 -B world test mysql > /data/backup/all.sql
备份单个表或者多个表
格式:
mysqldump -uuser -ppassword 库 表1 表2 ...
举例: world库下的city表
mysqldump -uroot -p123 world city > world_city.sql
举例: world库下的city表和country表
mysqldump -uroot -p123 world city country > world_city\&country.sql
4、备份高级参数
—master-data=2
功能1. 以注释的形式, 保存备份开始时间点的 binlog 的状态信息 # position号
=0: 默认值
=1: 以change master to 命令的形式,可以用作主从复制
=2: 以注释的形式, 备份时刻的文件名+position号
功能2. 自动锁表
功能3. 如果配合 --single-transaction, 只对非InnoDB表进行锁表备份, InnnoDB表进行"热备", 实际上是实现快照备份
举例:
mysqldump -uroot -p123 -B world --master-data=2 > world.sql
grep 'CHANGE' /data/backup/world.sql
—single-transaction
InnoDB 存储引擎开启热备功能 (快照备份)
--master-data 可以自动加锁
1.不加 --single-transaction, 启动所有表的温备份, 所有表都锁定
2.加上 --single-transaction, 对 InnoDB 表进行快照备份, 对非 InnoDB 表可以实现自动锁表功能
举例:
mysqldump -uroot -p123 -B world --master-data=2 --single-transaction > world.sql
grep 'CHANGE' /data/backup/world.sql
理解: 将全班同学拍照, 然后数人数, 拍照完成大家可自由活动
—max_allowed_packet=64M
最大允许的数据包大小
mysqldump -uroot -p123 -B world --master-data=2 --single-transaction --max_allowed_packet=64M > world.sql
小故障: 备份时超出最大数据包大小
1153 - Got a packet bigger than 'max_allowed_packet' bytes
解决办法: --max_allowed_packet=64M # 乘以2倍逐个往上试
-R、-E、—triggers 备份特殊对象使用
-R, --routines: 存储过程和函数 # 相当于Linux系统的脚本
-E, --events: 事件 # 相当于Linux系统中的计划任务
--triggers: 触发器
#备份时都要加上, 否则备份的数据不完整
mysqldump -uroot -p123 -B world --master-data=2 --single-transaction --max_allowed_packet=64M -R -E --triggers > world.sql
格式化备份文件
mysqldump -uroot -p -B world --master-data=2 --single-transaction --max_allowed_packet=64M -R -E --triggers > world_`date +%F`.sql
#备份文件名: world_2021-02-19.sql
—set-gtid-purged=OFF
值: AUTO、ON、OFF
OFF: 使用场景 (备份, 恢复)
--set-gtid-purged=OFF, 可以使用在日常备份参数中
mysqldump -uroot -p -B world --master-data=2 --single-transaction --max_allowed_packet=64M -R -E --triggers --set-gtid-purged=OFF > world_`date +%F`.sql
AUTO、ON: 使用场景 (备份, 构建主从)
在构建主从复制环境时需要的参数配置
mysqldump -uroot -p -B world --master-data=2 --single-transaction --max_allowed_packet=64M -R -E --triggers --set-gtid-purged=ON > world_`date +%F`.sql
5、mysqldump 备份恢复应用
5.1、故障恢复演练 mysqldump + binlog (每天全备)
场景
mdp库是两年前创建的, 备份策略: 每天23:00进行全备, 周一23:00进行全备, 周二白天有数据写入, 下午误删除mdp库, 通过周一的全备和binlog进行数据恢复
模拟原始数据
create database mdp charset utf8mb4;
use mdp;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
模拟每周一 23:00 的全备
mysqldump -uroot -p -A --master-data=2 --single-transaction --max_allowed_packet=64M -R -E --triggers > /data/backup/full_`date +%F`.sql
查看备份文件内容
#查看备份GTID相关信息, GTID截取起点
SET @@GLOBAL.GTID_PURGED='2e6ca7e0-45bd-11eb-bba7-000c2909345a:1-8237';
#查看备份position号相关信息, 备份开始时binlog和position
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=1102;
模拟周二白天数据变化
mysql> use mdp
mysql> insert into t1 values(4),(5),(6);
mysql> commit;
模拟周二下午14:00误删除了mdp核心库
drop database mdp;
5.2、故障恢复
1、恢复全备到周一23:00的数据
检查全备
vim vim full_2021-02-20.sql
# GTID
SET @@GLOBAL.GTID_PURGED='2e6ca7e0-45bd-11eb-bba7-000c2909345a:1-8237';
# position
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=1102;
2、截取日志
起点
#备份文件中记录的GTID,作为起点
#起点 GTID: '2e6ca7e0-45bd-11eb-bba7-000c2909345a:8237'
SET @@GLOBAL.GTID_PURGED='2e6ca7e0-45bd-11eb-bba7-000c2909345a:1-8237';
或者
#备份文件中记录的position,作为起点
#起点 position: 1102
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=1102;
终点
删库之前的position号
#通过 binlog 截取误删除时的GTID等信息
mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000004'" | grep -B 1 "drop database mdp"
mysql-bin.000004 1363 Gtid 7 1428 SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:8239'
mysql-bin.000004 1428 Query 7 1517 drop database mdp
#终点: 通过 drop database mdp 时的GTID, 向前推一位
GTID: 8239
position: 1363
进行 binlog 截取
mysqlbinlog --skip-gtids --include-gtids='2e6ca7e0-45bd-11eb-bba7-000c2909345a:8238-8238' /data/3307/binlog/mysql-bin.000004
3、恢复数据
set sql_log_bin=0;
source /data/backup/full_2021-02-20.sql
mysql> source /data/backup/bin.sql
set sql_log_bin=1;
4、检查数据
show databases;
use mdp
show tables;
+---------------+
| Tables_in_mdp |
+---------------+
| t1 |
+---------------+
mysql> select * from t1;
6、mysqldump 多种备份策略和恢复策略介绍
6.1、场景
100G 全库数据, 全库备份, 30-40分钟, 恢复整库要5倍时间, 2.5-3小时之间, 某张表 1G 被误删除了
6.2、mysqldump 备份策略
1、mysqldump 全备 + binlog 增量备份
恢复单表数据思路:
1. 提取mysqldump全备中的故障表数据, 恢复数据
sed -n '/CREATE TABLE `oldguo` /,/\;/p' /data/backup/full.sql > /data/backup/create.sql
grep -i 'INSERT INTO `oldguo`' /data/backup/full.sql > /data/backup/insert.sql
2. binlog 中截取全备到误删除表之间对于这张表的修改
2、单库单表备份 + binlog 增量备份
恢复单表数据思路:
1. 恢复单表的备份
2. binlog 中截取备份到误删除表之间对于这张表的修改
6.3、故障模拟
模拟原始数据
create database oldboy charset utf8mb4;
use oldboy;
create table oldguo (id int);
insert into oldguo values(1),(2),(3);
commit;
模拟周一23:00全备
mysqldump -uroot -p -A --master-data=2 --single-transaction --max_allowed_packet=64M -R -E --triggers > /data/backup/full.sql
模拟周二白天数据变化
use oldboy;
insert into oldguo values(11),(22),(33);
commit;
create table oldli(id int);
insert into oldli values(1),(2),(3);
commit;
insert into oldguo values(111),(222),(333);
commit;
insert into oldli values(4),(5),(6);
commit;
insert into oldguo values(1111),(2222),(3333);
commit;
模拟周二下午14:00误删除了oldguo表
drop table oldguo;
6.4、恢复过程
处理全备 # 截取建表、insert语句
#从全备中截取oldguo表建表语句
sed -n '/CREATE TABLE `oldguo` /,/\;/p' /data/backup/full.sql > /data/backup/create.sql
#从全备中截取oldguo表insert语句
grep -i 'INSERT INTO `oldguo`' /data/backup/full.sql > /data/backup/insert.sql
binlog 截取
范围
起点: 通过备份文件记录信息
#起点GTID: 2e6ca7e0-45bd-11eb-bba7-000c2909345a:8259
SET @@GLOBAL.GTID_PURGED='2e6ca7e0-45bd-11eb-bba7-000c2909345a:1-8259';
终点: 通过 drop table 发生时的GTID截取
#终点GTID: 2e6ca7e0-45bd-11eb-bba7-000c2909345a:8265
mysql -uroot -p -e "show binlog events in 'mysql-bin.000007'" | grep -B 1 'DROP TABLE\ `oldguo`'
mysql-bin.000007 2503 Gtid 7 2568 SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:8266'
mysql-bin.000007 2568 Query 7 2691 use `oldboy`; DROP TABLE `oldguo` /* generated by server */
截取全备到误删除之前, oldguo表操作记录的GTID号
mysqlbinlog --include-gtids='2e6ca7e0-45bd-11eb-bba7-000c2909345a:8259-8265' /data/3307/binlog/mysql-bin.000007 | grep -B 8 '`oldboy`.`oldguo`' | grep 'GTID_NEXT'
SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:8260'/*!*/;
SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:8263'/*!*/;
SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:8265'/*!*/;
通过GTID号进行 binlog 截取
#方法一: 全部范围: 跳过不需要的GTID号
--include-gtids='100-200'
--exclude-gtids='150-160' --exclude-gtids='180' (跳过150-160,180)
mysqlbinlog --skip-gtids --include-gtids='2e6ca7e0-45bd-11eb-bba7-000c2909345a:8260-8265' --exclude-gtids='2e6ca7e0-45bd-11eb-bba7-000c2909345a:8261-8262' --exclude-gtids='2e6ca7e0-45bd-11eb-bba7-000c2909345a:8264' /data/3307/binlog/mysql-bin.000007 > bin.sql
#方法二: 不连续的GTID,逐个添加,逗号分隔
--include-gtids='100-200'
--include-gtids='202'
mysqlbinlog --skip-gtids --include-gtids='2e6ca7e0-45bd-11eb-bba7-000c2909345a:8260','2e6ca7e0-45bd-11eb-bba7-000c2909345a:8263','2e6ca7e0-45bd-11eb-bba7-000c2909345a:8265' /data/3307/binlog/mysql-bin.000007 > /data/backup/bin.sql
恢复数据
use oldboy;
set sql_log_bin=0;
source /data/backup/create.sql
source /data/backup/insert.sql
commit;
source /data/backup/bin.sql
set sql_log_bin=1;
6.5、实现单库单表备份
mkdir -p /data/backup/tables
select concat("mysqldump -uroot -p123 --master-data=2 --single-transaction --max_allowed_packet=64M -R -E --triggers ",table_schema," ",table_name," > /data/backup/tables/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in ('sys','information_schema','performance_schema') into outfile '/tmp/tables.sh';
/bin/bash /tmp/tables.sh
可能遇到的报错:
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
解决办法:
show variables like '%secure%';
vim /etc/my.cnf
secure-file-priv=''
若有收获，就点个赞吧
0 人点赞
