MySQL备份1 ----- 逻辑备份¶
备份恢复（重点内容）
运维的备份恢复相关职责：
- 设计备份策略
- 日常备份
- 恢复演练
- 故障恢复
- 迁移
一. 备份类型¶
热备：在数据库正常业务中备份数据并且能够一次性恢复(只能是innodb)
冷备：关闭数据库业务，数据库没有任何变更的情况下，进行备份数据。
温备：锁表备份，只能查询不能修改(myisam)
二. 备份方式及工具介绍¶
1. 逻辑备份工具：基于SQL语句进行备份¶
mysqldump *****
mysqlbinlog *****
2. 物理备份工具：基于磁盘数据文件备份¶
xtrabackup (XBK) percona 第三方 *****
MySQL Enterprise Backup(MEN)
3. 逻辑备份与物理备份的比较¶
mysqldump ----- 自带逻辑备份工具
a. 优点：
- 不需要下载安装额外服务
- 备份出来的是SQL，文本格式，可读性更高，便于备份处理
- 压缩比高，节省备份的磁盘空间
b. 缺点：
- 依赖于数据库引擎(InnoDB可以实现热备份，myisam只能实现温备份)，需要从磁盘中把数据读出来，然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低。
c. 建议：
100G以内的数据量级可以使用mysqldump，备份/恢复大概需要20-40分组
超过1TB以上，我们可以选择的是mysqldump，配合分布式系统
4. xtrabackup(XBK)---第三方物理备份工具¶
a. 优点：
- 类似于直接复制(cp)文件，不需要关逻辑结构，相对来说性能较高。
b. 缺点：
- 可读性差
- 压缩比低，需要更多磁盘空间
c. 建议：
使用xtrabackup备份的数据建议在100GB左右，低于1TB
三. 备份策略¶
全备份：全库备份，备份所有的数据
增量备份：备份变化的数据
比如：周日全备份，周一到周六增量备份
四. mysqldump(逻辑备份的客户端工具)¶
1. 客户端的通用参数¶
- -u：用户名
- -p：端口号
- -S：socket文件
- -h：数据库的IP地址
- -p：密码
mysqldump -uroot -p
2. 备份专用的基本参数¶
(1) -A 全备份
全备份参数：
例子一 --- 全备份：
(2) -B db1、db2、db3备份多个单库
例子2 --- 单库或多库备份，比较熟褐与跨版本的迁移，只备份生产库
[root@4 ~]# mysql -uroot -p -B test world >/backup/bak.sql
(3) 备份单个或多个表
例子3---world数据库下的city表、country表
[root@4 ~]# mysqldump -uroot -p world city country>/backup/bak1.sql
* 注意：-B的作用
- mysqldump -uroot -p -B world>/backup/test.sql
- mysqldump -uroot -p world>/backup/test1.sql
以上两个语句都可以备份world数据库下的所有表
-B添加了create database和use
所以和库相关的备份一般需要加-B
(4) 特殊参数1使用
-R 备份存储过程和函数
--triggers备份触发器
[root@4 ~]# mysqldump -uroot -p -A -R -triggers>/backup/full.sql
(5) 特殊参数2使用
++++++++++++++++++++++++++++++++++++++++
每天晚上12点全备份
上午十点数据库被误删除了
二进制日志截取将称为我们比较棘手的问题
起点：怎么办
终点：怎么找
++++++++++++++++++++++++++++++++++++++++
(6) -F在备份开始时，刷新一个新的binlog日志
例子5：
[root@4 ~]# mysqldump -uroot -P -A -R --triggers -F>/backup/full.sql
(7) --source-data=2
以注释的形式保存备份开始时间点的binlog的状态信息
[root@4 ~]# mysqldump -uroot -p -A -R --triggers --source-data=2>/backup/full.sql
0 默认值
1 以change replication source to 命令形式，可以做主从复制
2 以注释的形式记录，备份时刻的二进制文件名+postion号
single-transaction可以加锁
- 不加--single-transaction，启动所有表的温备份，所有表锁定
- 加上--single-transcation，对innodb可以不锁表热备份，对非innodb表可以实现自动锁表功能
例子6：备份必加参数 生产使用
[root@4 ~]# mysqldump -uroot -p -A -R -triggers --source-data=2 --single-transaction>/backup/full.sql
功能：
- (1)以注释的形式保存备份开始时间点的binlog状态信息
- (2)对innodb可以不锁表热备份
- (3)对非innodb表可以实现自动锁表的功能
五. 使用mysqldump+mysqlbinlog进行1逻辑备份及恢复¶
1. 模拟故障案例并恢复¶
- (1)晚上全备份
- (2)binlog日志是完整的
- (3)模拟白天的数据变化
- (4)模拟下午两点误删数据库
开启二进制日志
[root@3 data]# vi /etc/my.cnf
[client]
socket = /usr/local/mysql/mysql.sock #客户连接使用的socket
[mysqld]
server_id=10 #将这台 MySQL 服务器的 ID 指定为 10
port = 3306 #默认监听的端口为3306
user = mysql #mysql进程的维护用户为mysql
character-set-server = utf8mb4 #默认字符集
default_storage_engine = innodb #默认存储引擎
log_timestamps = SYSTEM #日志时间戳使用系统时间
socket = /usr/local/mysql/mysql.sock #服务端的socket文件存放的位置
basedir = /usr/local/mysql #mysql的安装目录
datadir = /data/mysql/data #mysql数据文件的存放目录
pid-file = /usr/local/mysql/mysql.pid #mysql进程id存放位置
log_error = /data/mysql/log/mysql-error.log #mysql错误日志的存放位置
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
#STRICT_TRANS_TABLES 严格模式核心 — 对事务表启用严格模式，插入/更新数据时若值无效（如类型错误、超出长度），直接报错而非发出警告
#NO_ZERO_IN_DATE 不允许月份或日为零的日期，例如 '2010-00-01'、'2024-01-00' 会被拦截。现已被废弃，但不会报错
# 不允许 '0000-00-00' 作为合法日期。现已被废弃，但不会报错
#ERROR_FOR_DIVISION_BY_ZERO 除零操作直接报错，而非返回 NULL 并给警告
#NO_ENGINE_SUBSTITUTION 建表时如果指定了被禁用的存储引擎（如 Memory 被禁用），直接报错，而非自动替换为默认引擎（InnoDB）
skip-name-resolve #跳过DNS反向解析
innodb_print_all_deadlocks = 1
log_bin = /data/mysql/log/binlog #开启二进制文件
[root@3 data]# systemctl restart mysqld
Ⅰ. 模拟晚上全备份
[root@4 data]# cd
[root@4 ~]#mysql -e 'CREATE DATABASE uuu'&& mysql -e 'SHOW DATABASES'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| uuu |
+--------------------+
[root@4 ~]# mkdir /bak
[root@4 ~]# mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction>/bak/full.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
Enter password:
#或
[root@4 ~]# mysqldump -uroot -p -A -R --triggers --source-data=2 --single-transaction>/bak/full.sql
Enter password:
[root@4 ~]# ls /bak
full.sql
Ⅱ. 模拟白天的数据操作变化：
a. 准备数据
mysql> DROP DATABASE IF EXISTS test;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE stu(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO stu VALUES(1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE stu SET id=30 WHERE id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> DELETE FROM stu WHERE id=4;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO stu VALUES(6),(7),(8);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM stu;
+------+
| id |
+------+
| 1 |
| 2 |
| 30 |
| 6 |
| 7 |
| 8 |
+------+
6 rows in set (0.00 sec)
mysql> exit
Bye
b. 破坏数据---模拟下午两点误删数据库
[root@4 ~]# ls /data/mysql/data
auto.cnf client-cert.pem '#ib_16384_1.dblwr' '#innodb_redo' mysql.ibd private_key.pem server-key.pem undo_001
ca-key.pem client-key.pem ib_buffer_pool '#innodb_temp' mysql_upgrade_history public_key.pem sys undo_002
ca.pem '#ib_16384_0.dblwr' ibdata1 mysql performance_schema server-cert.pem test uuu
[root@4 ~]# rm -rf /data/mysql/data/*
[root@4 ~]# ls /data/mysql/data
[root@4 ~]# systemctl stop mysqld
c. 恢复
(0). 初始化数据库并启动服务
[root@4 ~]# rm -rf /data/mysql/data/*
[root@4 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
[root@4 ~]# ls /data/mysql/data
auto.cnf '#ib_16384_0.dblwr' '#innodb_temp' private_key.pem undo_001
ca-key.pem '#ib_16384_1.dblwr' mysql public_key.pem undo_002
ca.pem ib_buffer_pool mysql.ibd server-cert.pem
client-cert.pem ibdata1 mysql_upgrade_history server-key.pem
client-key.pem '#innodb_redo' performance_schema sys
[root@3 ~]# systemctl start mysqld
(1). 恢复昨晚全备份
[root@3 ~]# mysql
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> source /bak/full.sql;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| uuu |
+--------------------+
5 rows in set (0.00 sec)
mysql> quit
Bye
(2). 找到binlog并截取(起点,终点)恢复
在全部的sql文件中找到binlog文件
[root@4 ~]# cat /bak/full.sql|grep '\-\- CHANGE REPLICATION'
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000002', SOURCE_LOG_POS=340;
使用mysqlbinlog命令查看并寻找截取的起点和终点
[root@4 ~]# cd /data/mysql/log
[root@4 log]# mysqlbinlog -v binlog.000002
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#260706 17:53:11 server id 10 end_log_pos 127 CRC32 0x8e72ab79 Start: binlog v 4, server v 8.4.10 created 260706 17:53:11 at startup
ROLLBACK/*!*/;
BINLOG '
B3tLag8KAAAAewAAAH8AAAAAAAQAOC40LjEwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAHe0tqEwANAAgAAAAABAAEAAAAYwAEGggAAAAAAAACAAAACgoKKioAEjQA
CigAAAF5q3KO
'/*!*/;
...
### DELETE FROM `test`.`stu`
### WHERE
### @1=4
# at 1717
#260706 17:56:33 server id 10 end_log_pos 1748 CRC32 0x89f20533 Xid = 678
COMMIT/*!*/;
...
# at 1951
#260706 17:56:37 server id 10 end_log_pos 2001 CRC32 0xe8993571 Write_rows: table id 179 flags: STMT_END_F
BINLOG '
1XtLahMKAAAAMQAAAJ8HAAAAALMAAAAAAAEABHRlc3QAA3N0dQABAwABAQEAzOX6Ow==
1XtLah4KAAAAMgAAANEHAAAAALMAAAAAAAEAAgAB/wAGAAAAAAcAAAAACAAAAHE1meg=
'/*!*/;
### INSERT INTO `test`.`stu`
### SET
### @1=6
### INSERT INTO `test`.`stu`
### SET
### @1=7
### INSERT INTO `test`.`stu`
### SET
### @1=8
# at 2001
#260706 17:56:37 server id 10 end_log_pos 2032 CRC32 0x2d4740ae Xid = 679
COMMIT/*!*/;
# at 2032
#260706 17:57:43 server id 10 end_log_pos 2055 CRC32 0x58968323 Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@4 log]# mysqlbinlog --start-position=340 --stop-position=1717 binlog.000002|mysql
[^_^] [root@4 log]#mysql -e 'use test;select * from stu;'
+------+
| id |
+------+
| 1 |
| 2 |
| 30 |
| 4 |
+------+
从1717位置到最后将678数据恢复
[root@4 log]# mysqlbinlog --start-position=1717 binlog.000002|mysql
[root@4 log]# mysql -e 'use test;select * from stu;'
+------+
| id |
+------+
| 1 |
| 2 |
| 30 |
| 4 |
| 6 |
| 7 |
| 8 |
+------+
重启mysql服务后登录数据库密码就变回备份前的了
[^_^] [root@4 log]#systemctl kill mysqld
[^_^] [root@4 log]#systemctl start mysqld
[^_^] [root@4 log]#ss -tuanp |grep 3306
tcp LISTEN 0 151 *:3306 *:* users:(("mysqld",pid=1789,fd=21))
tcp LISTEN 0 70 *:33060 *:* users:(("mysqld",pid=1789,fd=18))
(3). 利用时间截取二进制日志
如
mysqlbinlog -start-datetime="2026-05-17 16：20：54" --stop-datetime="2026-05-17 17:19:12" /data/mysql/log/mysql-bin.000003