MySQL 备份与恢复

一、备份和恢复概述

1. 可处理的崩溃类型

自动故障恢复（CSR）：操作系统崩溃、电源故障
只能使用备份恢复（磁盘数据不可读）：文件系统崩溃、硬件故障

2. 常用备份工具

mysqldump（MDP）
percona Xtrabackup（XBK/PBK）
MySQL Enterprise Backup（MEB）
mysqlbinlog

二、备份和恢复类型

1. 物理备份 VS 逻辑备份

（1）物理备份

适合大型备份，备份速度快（仅文件复制，无需转换）
备份粒度：从整个数据目录到单个文件，依存储引擎可能到表级
需停库或锁定，MEMORY 表不可用（数据不在磁盘）
工具：XtraBackup

（2）逻辑备份

适合小型备份，速度较慢（需访问数据库并转换逻辑格式）
备份粒度：服务器级、数据库级、表级
高度可移植，支持在线备份，适用于所有存储引擎（包括 MEMORY）
工具：mysqldump、binlog（flush logs + cp/scp）

2. 在线备份 VS 离线备份

（1）在线备份

特点：对客户端干扰小，需保证适当锁定以确保完整性，还原时需防止客户端访问
类型：
- 热备（InnoDB）：对业务影响最小
- 温备（MyISAM）：需长时间锁表

（2）离线备份（冷备）

客户端受影响（服务器不可用），通常从副本服务器获取
备份过程简单（无客户端活动干扰）

3. 本地备份 VS 远程备份

mysqldump：可本地或远程启动，输出至客户端
SELECT … INTO OUTFILE：可本地或远程启动，输出至服务器端
物理备份：本地启动，可本地或远程输出

4. 快照备份

MySQL 本身不提供文件系统快照功能，需依赖第三方解决方案（如 Veritas、LVM、ZFS）

5. 全备 VS 增量备份

增量备份需启用二进制日志

6. 完全恢复 VS 时间点恢复

时间点恢复基于二进制日志

三、备份方式

1. 利用 mysqldump 进行备份

关键选项：–single-transaction（实现不锁表的在线备份）

2. 通过拷贝表文件进行备份

需保证一致性：停止服务器，或锁定 + 刷新表

FLUSH TABLES tbl_list WITH READ LOCK; -- 刷新脏数据到磁盘，其他客户端可查询

3. 分隔文本文件备份（导出数据）

（1）导出数据

SELECT * INTO OUTFILE 'file_name' FROM tbl_name
mysqldump --tab

（2）重新加载数据

load data 语句
mysqlimport（load data 的接口）

4. 启用二进制日志做增量备份

增量备份为上次全备/增量备份后的二进制日志
步骤：flush logs 刷新到磁盘，备份所有二进制日志

5. 利用副本进行备份

避免主节点性能问题，需备份：
- 副本库的源信息、中继日志信息存储库
- load data 目录中的文件（对应系统变量 slave_load_tmpdir）

6. 恢复损坏的表（MyISAM 表）

REPAIR TABLE 语句
myisamchk -r 命令

7. 利用文件系统快照进行备份

依赖第三方工具（如 LVM、ZFS 等）

四、备份策略

1. 核心要求

必须启用二进制日志，并将其存储在安全介质上

2. 全备与增量备份配合

（1）刷新二进制日志

作用：关闭当前日志，开启新文件（便于确定增量备份范围）
命令：flush logs 或 mysqladmin flush-logs

（2）示例

星期日：全备
星期一：flush logs 生成 mysql-bin.000002，增量备份为 mysql-bin.000001
星期二：flush logs 生成 mysql-bin.000003，增量备份为 mysql-bin.000002

3. 二进制日志管理

（1）全备时删除

shell> mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs backup_sunday_1_PM.sql

Note：复制结构中，从节点删除可能有风险（存在未处理的日志）

（2）手动删除

PURGE BINARY LOGS; -- 具体参数需指定，如 PURGE BINARY LOGS TO 'mysql-bin.000003';

（3）设置过期时间

[mysqld]
binlog_expire_logs_seconds = 604800  # 7 天

4. 备份可用性检查

查看定时任务（crontab -l）确定备份路径和日志
检查：备份日志、文件大小、文件内容，定期进行恢复演练

五、mysqldump 详细使用

1. 备份（在线逻辑备份）

（1）常用选项

筛选选项：
- –all-databases（-A）：备份所有库，8.0 版本需额外指定 --routines 和 --events（数据字典存储）。
  
  在 8.0 版本之前，–routines 和 --events 包含的存储过程、函数、事件都保存在 mysql.proc 和 mysql.event 表中，所以备份所有库时，不需要单独指定这两个选项。
  
  但是，8.0 版本删除了这两个 mysql 表，相应的对象存储在数据字典中，所以需要单独指定这两个选项。
- –databases（-B）：备份指定库，包含 create database 和 use 语句
  - 备份单库时，可忽略此选项
```
shell> mysqldump test dump.sql
shell> mysqldump --databases test dump.sql
```
  - 如果没有 --databases，导出文件中不包含 create database 或 use 语句
  - 重新加载导出文件时，必须指定一个默认库名 (use 语句)
  - 重新加载时，可以指定与原库不同的库名
  - 如果要加载的库不存在，必须创建
  - –add-drop-database 选项不起作用，因为导出文件中不包含 create database 语句
- –events（-E）：输出事件（CREATE EVENT 语句）
- –routines（-R）：输出存储过程和函数（CREATE PROCEDURE/FUNCTION 语句）
- –triggers：输出触发器（默认启用，–skip-triggers 禁用）
- –no-data（-d）：仅备份表结构
- –tables：指定备份表（覆盖 --databases）
- –ignore-error=error[,error]…：忽略指定的错误。多个选项值之间用逗号分隔。–force 是忽略所有错误，优先级更高。
- –ignore-table=db_name.tbl_name：备份时忽略的表或视图。必须同时指定库名和表名。如果指定多个表，要多次使用此选项。
- –where（-w）：指定 WHERE 条件（如 --where=“user=‘jimf’”）
DDL 选项：–add-drop-database（重建库前删除原库）
事务选项：–single-transaction（InnoDB 一致性快照备份，不锁表）
日志选项：–flush-logs（备份前刷新日志，便于确定二进制日志起点）
主从复制选项：–master-data[=#]（记录二进制日志位置和文件名，1 二进制日志信息不加注释，2 二进制日志信息加注释）

格式选项：

–tab=dir_name：生成 tbl_name.txt（数据，存储在服务器端，tab 分隔）和 tbl_name.sql（表结构，存储在客户端），不带 --tab 写入标准输出。

分隔符设置：默认以逗号分隔列，以双引号包含列值，以\r\n分隔行。–fields-terminated-by（列分隔符）、–fields-enclosed-by（列值包含符）等。如果使用了特殊格式导出，加载时必须指定相同的格式，才能解释文件内容。

# --fields-terminated-by=str，列分隔符，默认为 tab
# --fields-enclosed-by=char，包含列值的字符，默认无
# --fields-optionally-enclosed-by=char，包含非数字列值的字符，默认无
# --fields-escaped-by=char，转义字符，默认不转义
# --lines-terminated-by=str，行分隔符，默认为换行符
shell> mysqldump --tab=/tmp --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1

分别导出表结构和数据：–no-data（不导出数据）、–no-create-info（不导出定义语句）

shell> mysqldump --no-data test dump-defs.sql
shell> mysqldump --no-create-info test dump-data.sql
shell> mysqldump --no-data --routines --events test dump-defs.sql

扩展选项：
- –set-gtid-purged=value（控制 GTID 相关语句）
  - 在基于 GTID 模式的复制结构中，控制 SET @@GLOBAL.gtid_purged 语句和 SET @@ SESSION.sql_log_bin = 0 语句。如果启用了 GTID 模式，则默认启用此选项。
  - 对于 5.6、5.7 版本，如果 dump 文件中包含系统表，则不建议在启用 GTID 时加载 dump 文件
  - 选项值
    - AUTO：(默认) 在启用 GTID 的服务器上自动启用此选项
    - OFF：禁用。禁用后，dump 文件中不会出现 GTID 信息。
    - ON：开启。如果设置了此选项，但是服务器未启用 GTID ，则报错。
      sql -- GTID state at the beginning of the backup SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '32e96078-8ec2-11eb-b651-000c290007bf:1-72, aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-13';
    - COMMENTED：以注释的形式记录 SET @@GLOBAL.gtid_purged 语句，正常记录 SET @@ SESSION.sql_log_bin = 0 语句。
```
 -- GTID state at the beginning of the backup 
 /* SET @@GLOBAL.GTID_PURGED='+32e96078-8ec2-11eb-b651-000c290007bf:1-72,
 aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-13';*/
```
- –max-allowed-packet=#（控制备份时传输数据包的大小，如 --max_allowed_packet=128M）

（2）语法

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

（3）示例

shell> mysqldump --all-databases --master-data --single-transaction backup_sunday_1_PM.sql
shell> mysqldump --single-transaction --flush-logs --master-data=2 --all-databases backup_sunday_1_PM.sql

2. 恢复操作

（1）全备恢复

shell> mysql < backup_sunday_1_PM.sql

（2）增量备份恢复

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

（3）SQL 格式备份重新加载

如果是 --databases 导出文件，包含 create database 语句，可以直接导入
```
shell> mysql < dump.sql
mysql> source dump.sql
```

如果未使用 --databases 导出文件，先建库，然后导入。因为备份中没有 use 语句

shell> mysqladmin create db1
shell> mysql db1 < dump.sql

# 或在 MySQL 客户端
mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

（4）定界文本格式备份重新加载

加载 --tab 导出的文件。先加载表结构（.sql 文件），再加载数据（.txt 文件）
- mysqlimport：
```
shell> mysql db1 < t1.sql
shell> mysqlimport db1 t1.txt
```
- load data：
```
mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;
```

如果导出时有指定格式（如逗号分隔、双引号包含）：

mysqlimport：

shell> mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt

load data：

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

（5）时间点恢复

查看二进制日志：

mysql> show binary logs; -- 日志列表
mysql> show master status; -- 当前日志

查看二进制日志内容

shell> mysqlbinlog binlog_files tmpfile

应用二进制日志：

shell> mysqlbinlog binlog_files | mysql -u root -p
or
shell> mysql -u root -p < tmpfile

应用多个二进制日志要串行，使用单个连接避免临时表问题

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

# or
shell> mysqlbinlog binlog.000001  /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >/tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"

截取部分二进制日志：

查找事件时间

shell> mysqlbinlog   --start-datetime="2020-05-27 12:59:00" --stop-datetime="2020-05-27 13:06:00" \
--verbose /var/lib/mysql/bin.123456 | grep -C 12 "DROP TABLE"

确定日志位置：这两个选项（–start-datetime、–stop-datetime）只能用来查找日志，不建议用于应用日志，丢失事件的风险很高。

查看事件列表

mysql> show binlog events in 'mysql-bin.000003';
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                       |
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------+
| mysql-bin.000003 |    4 | Format_desc    |         6 |         123 | Server ver: 5.7.31-log, Binlog ver: 4                      |
| mysql-bin.000003 |  123 | Previous_gtids |         6 |         154 |                                                            |
| mysql-bin.000003 |  154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
| mysql-bin.000003 |  219 | Query          |         6 |         291 | BEGIN                                                      |
| mysql-bin.000003 |  291 | Table_map      |         6 |         339 | table_id: 109 (test.t1)                                    |
| mysql-bin.000003 |  339 | Write_rows     |         6 |         383 | table_id: 109 flags: STMT_END_F                            |
| mysql-bin.000003 |  383 | Xid            |         6 |         414 | COMMIT /* xid=19 */                                        |
| mysql-bin.000003 |  414 | Anonymous_Gtid |         6 |         479 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
| mysql-bin.000003 |  479 | Query          |         6 |         551 | BEGIN                                                      |
| mysql-bin.000003 |  551 | Table_map      |         6 |         599 | table_id: 109 (test.t1)                                    |
| mysql-bin.000003 |  599 | Write_rows     |         6 |         644 | table_id: 109 flags: STMT_END_F                            |
| mysql-bin.000003 |  644 | Xid            |         6 |         675 | COMMIT /* xid=24 */                                        |
| mysql-bin.000003 |  675 | Anonymous_Gtid |         6 |         740 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
| mysql-bin.000003 |  740 | Query          |         6 |         855 | use `test`; create table t2(id int, name varchar(20))      |
| mysql-bin.000003 |  855 | Anonymous_Gtid |         6 |         920 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
| mysql-bin.000003 |  920 | Query          |         6 |        1056 | create database zabbix charset utf8mb4 collate utf8mb4_bin |
| mysql-bin.000003 | 1056 | Anonymous_Gtid |         6 |        1121 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                       |
| mysql-bin.000003 | 1121 | Query          |         6 |        1210 | drop database zabbix                                       |
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------+

截取事件并恢复

shell> mysqlbinlog --start-position=1006 --stop-position=1868 /var/lib/mysql/bin.123456 | mysql -u root -p
shell> mysqlbinlog --start-position=1985 /var/lib/mysql/bin.123456 | mysql -u root -p

恢复删表前的事件
跳过删表事件，继续恢复

六、数据导出

主要用于异构迁移，配合 concat 使用，可以批量生成脚本。

1. 相关配置

–secure-file-priv[=dir_name]：控制导入/导出目录

NULL（默认）：禁止导入/导出

mysql> select * from t1 into outfile '/tmp/t1.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL               |
+--------------------+

2. 语法与示例

语法：
```
SELECT 语句 INTO OUTFILE 'dir_name';
```

示例：

mysql> SELECT @@secure_file_priv; -- 查看配置
mysql> SELECT * FROM t1 INTO OUTFILE '/tmp/t1.sql'; -- 导出数据（需 secure-file-priv 允许）

红炉点雪

MySQL 备份与恢复

MySQL 备份与恢复

一、备份和恢复概述

1. 可处理的崩溃类型

2. 常用备份工具

二、备份和恢复类型

1. 物理备份 VS 逻辑备份

（1）物理备份

（2）逻辑备份

2. 在线备份 VS 离线备份

（1）在线备份

（2）离线备份（冷备）

3. 本地备份 VS 远程备份

4. 快照备份

5. 全备 VS 增量备份

6. 完全恢复 VS 时间点恢复

三、备份方式

1. 利用 mysqldump 进行备份

2. 通过拷贝表文件进行备份

3. 分隔文本文件备份（导出数据）

（1）导出数据

（2）重新加载数据

4. 启用二进制日志做增量备份

5. 利用副本进行备份

6. 恢复损坏的表（MyISAM 表）

7. 利用文件系统快照进行备份

四、备份策略

1. 核心要求

2. 全备与增量备份配合

（1）刷新二进制日志

（2）示例

3. 二进制日志管理

（1）全备时删除

（2）手动删除

（3）设置过期时间

4. 备份可用性检查

五、mysqldump 详细使用

1. 备份（在线逻辑备份）

（1）常用选项

（2）语法

（3）示例

2. 恢复操作

（1）全备恢复

（2）增量备份恢复

（3）SQL 格式备份重新加载

（4）定界文本格式备份重新加载

（5）时间点恢复

六、数据导出

1. 相关配置

2. 语法与示例