数据库备份与恢复

在任何数据库环境中，总会有不确定的意外情况发生，比如意外的停电，计算机系统中的各种软硬件故障，人为破坏，管理员误操作等是不可避免的，这些情况可能会导致数据的丢失，服务器瘫痪等严重的后果。存在多个服务器时，会出现主从服务器之间的数据同步问题。

为了有效防止数据丢失，并将损失降到最低，应定期对 MySQL 数据库服务器做备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复。主从服务器之间的数据同步问题可以通过复制功能实现。

备份非常非常重要的一点：

在进行数据库备份时，不能将备份文件放在数据库服务器中。备份文件要单独存放（如果数据库服务器坏了，备份文件也完蛋了！）。

1. 物理备份与逻辑备份

物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL 中可以用 xtrabackup 工具来进行物理备份。

逻辑备份：对数据对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为mysqldump。逻辑备份就是将数据转为 sql 文件备份，在恢复的时候执行备份的 sql 文件实现数据库的还原/恢复/迁移。

2. mysqldump 实现逻辑备份

mysqldump 是 MySQL 提供的一个非常有用的数据库备份工具。

2.1 备份一个数据库 - 不包含 "创建库" 的 sql 语句

mysqldump 命令执行时，可以将数据库备份成一个文件文件（.sql），该文件中实际上包含多个 create 和 insert 语句，使用这些语句可以重新创建库，表和插入数据。

查出需要备份的库/表的结构，在文本文件中生成一个 create 语句。
将库/表中的所有记录转换成一条 insert 语句。

基本语法：

mysqldump -h主机地址 -P端口 -u用户名 -p密码 数据库名称 > 备份文件名称.sql

mysqldump : mysql的逻辑备份工具
-h：mysql服务的主机地址（ip）
-P：mysql服务的端口
-u ：mysql服务的用户名
-p ：mysql服务的密码
">" : 备份输出的方向
备份文件名称.sql ：表示备份文件（可以指明文件的全路径，不指明默认在当前目录下）

例如：

mysqldump -h127.0.0.1 -P3306 -uroot -pxld123 xld > /usr/mysql/back/xld.sql

说明：

备份的文件并非一定要求后缀名为 .sql，.txt / .log等也是可以的

举例：备份一个数据库

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld > C:\Users\Administrator\Desktop\xld.sql

备份文件剖析：

-- MySQL dump 10.13  Distrib 8.0.20, for Win64 (x86_64)
--
-- Host: 10.10.77.51    Database: xld
-- ------------------------------------------------------
-- Server version	8.0.20

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `xld_master_slave_text`
--

DROP TABLE IF EXISTS `xld_master_slave_text`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `xld_master_slave_text` (
  `id` int NOT NULL COMMENT '主键id',
  `age` tinyint DEFAULT '0' COMMENT '年龄',
  `xld_name` varchar(25) NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `xld_master_slave_text`
--

LOCK TABLES `xld_master_slave_text` WRITE;
/*!40000 ALTER TABLE `xld_master_slave_text` DISABLE KEYS */;
INSERT INTO `xld_master_slave_text` VALUES (1,0,'xld1'),(2,2,'xld2'),(3,3,'xld3'),(4,4,'xld4'),(5,5,'xld5'),(6,66,'xld6');
/*!40000 ALTER TABLE `xld_master_slave_text` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-09-03 16:18:07

-- 开头的都是 sql 语句的注释。
*以 /*!开头 ~ / 结尾的语句为执行的 MySQL 注释，这些语句可以被 MySQL 执行，但在其他数据库管理系统中被作为注释忽略，这可以提高数据库的可移植性。
文件开头指明了备份文件使用的 MySQL dump工具的版本号，接下来是备份账户的名称和主机信息，以及备份的数据库的名称，最后是 MySQL 服务器的版本号（我这里是：8.0.20）
文件中接下来的部分是一些 set 语句，这些语句将一些系统变量值赋给用户定义变量，以确保被恢复的数据库的系统变量和原来备份时的变量相同。

例如：

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

文件中的最后几行 MySQL 使用 set 语句恢复服务器系统变量原来的值。

例如：

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

文件的 drop 语句，create 语句和 insert 语句都是还原时使用的。

例如：

drop table if exists xld：语句用来判断数据库中是否存在名为 xld 的表，如果存在，就删除。

create 语句用来创建 xld 表。

insert 语句用来还原数据。

文件中开始的一些语句以数字开头。这些数字代表了 MySQL 版本号，告诉我们这些语句只有在对应的 MySQL 版本或者比该版本高的情况下才能执行。

例如：

40101 表明这些语句只有在 MySQL 版本号为了 4.01.01 或者更高的条件下才可以被执行。

文件的最后记录了备份的时间

2.2 备份全部数据库

若想用 mysqldump 备份整个实例，可以使用 --all-databases 或者 -A 参数。语法如下：

mysqldump -h主机地址 -P端口 -u用户名 -p密码 -all-databases > 备份文件名称.sql
# 或者
mysqldump -h主机地址 -P端口 -u用户名 -p密码 -A > 备份文件名称.sql

例如：

mysqldump -h127.0.0.1 -P3306 -uroot -pxld123 --all-databases > /usr/mysql/back/mysql.sql
# 或者
mysqldump -h127.0.0.1 -P3306 -uroot -pxld123 -A > /usr/mysql/back/mysql.sql

注意：mysql 中的4个系统库：information_schema，performance_schema，mysql，sys。在备份时只会（只能）备份 mysql 库，其他库是不需要备份的，也允许备份。

2.3 备份部分数据库 - 包含 "创建库" 的 sql 语句

使用 --databases 或 -B 参数了，该参数后面跟数据库名称，多个数据库之间用空格隔开。如果指定 --databases 或 -B 参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。语法如下：

mysqldump -h主机地址 -P端口 -u用户名 -p密码 --databases [数据库名称 数据库名称 ...] > 备份文件名称.sql
# 或者
mysqldump -h主机地址 -P端口 -u用户名 -p密码 -B [数据库名称 数据库名称 ...] >  备份文件名称.sql

例如：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 --databases xld dome > C:\Users\Administrator\Desktop\xld.sql
# 或者
mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 -B xld dome > C:\Users\Administrator\Desktop\xld.sql

2.4 备份部分表

备份时需要指定具体的数据库多个表之间用空格隔开。需要注意的是：此时的备份文件只包含表的drop，create 和 insert 语句，是没有关于数据库语句的。语法如下：

mysqldump -h主机地址 -P端口 -u用户名 -p密码 数据库名称 [表名 表名1 ...] > 备份文件名称.sql

例如：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld xld_master_slave_text > C:\Users\Administrator\Desktop\xld.sql
# 备份多张表
mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld xld_master_slave_text xld > C:\Users\Administrator\Desktop\xld.sql

2.5 备份单表的部分数据

有些时候一张表的数据量很大，但我们只需要部分数据，这是就可以使用 --where 选项了。--where 后面附带需要满足的条件。备份时需要指定具体的数据和具体的表。语法如下：

mysqldump -h主机地址 -P端口 -u用户名 -p密码 数据库名称 表名 --where= '过滤条件' > 备份文件名称.sql

例如：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld xld_master_slave_text --where="id < 6 and age > 3" > C:\Users\Administrator\Desktop\xld.sql

注意：此时的备份文件中不仅仅是只有 insert 的语句，也有 drop 该表（删除表）的语句。那么这就意味着，恢复数据的时候会先删除原有整张表，然后再恢复数据。（原有的表会被删除 - 适用于全量数据恢复）

2.6 排除某些表的备份

如果我们想备份某个库，但是库中某些表数据量很大或者业务关联不大，这个时候可以考虑排除掉这些表。这个时候就要使用 --ignore-table 选项了（注意：必须指明数据库（全库/单库/多个库等...））。

排除单张表的语法如下：

mysqldump -h主机地址 -P端口 -u用户名 -p密码 数据库名称 --ignore-table=数据库名称.表名 > 备份文件名称.sql

例如：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld --ignore-table=xld.xld > C:\Users\Administrator\Desktop\xld.sql

排除多张表的语法如下：

mysqldump -h主机地址 -P端口 -u用户名 -p密码 数据库名称 --ignore-table=数据库名称.表名 [--ignore-table=数据库名称.表名 ...] > 备份文件名称.sql

例如：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld --ignore-table=xld.xld --ignore-table=xld.xld_dome > C:\Users\Administrator\Desktop\xld.sql

2.7 只备份结构或只备份数据

2.7.1 只备份结构

只备份结构的话可以使用 --no-data 或者 -d 选项。语法如下：

mysqldump -h主机地址 -P端口 -u用户名 -p密码 数据库名称 --no-data > 备份文件名称.sql

备份一个数据库的结构 - 不包含 "库" 的 sql 语句：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld --no-data > C:\Users\Administrator\Desktop\xld.sql

备份全部数据库的结构：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 --all-databases --no-data > C:\Users\Administrator\Desktop\xld.sql

备份部分数据库的结构 - 包含 "库" 的 sql 语句：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 --databases xld mysql --no-data > C:\Users\Administrator\Desktop\xld.sql

备份部分表的结构：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld xld_master_slave_text xld --no-data > C:\Users\Administrator\Desktop\xld.sql

排除某些表的结构 - 排除单个表：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld --ignore-table=xld.xld --no-data > C:\Users\Administrator\Desktop\xld.sql

2.7.2 只备份数据

只备份数据可以使用 --no-create-info 或者 -t 选项。语法如下：

mysqldump -h主机地址 -P端口 -u用户名 -p密码 数据库名称 --no-create-info > 备份文件名称.sql

备份一个数据库的数据 - 不包含 "库" 的 sql 语句：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld --no-create-info > C:\Users\Administrator\Desktop\xld.sql

备份部分数据库的数据 - 包含 "库" 的 sql 语句：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 --databases xld mysql --no-create-info > C:\Users\Administrator\Desktop\xld.sql

备份部分表的数据：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld xld_master_slave_text xld  --no-create-info > C:\Users\Administrator\Desktop\xld.sql

排除某些表的数据 - 排除多个表：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld --ignore-table=xld.xld --ignore-table=xld.xld_dome --no-create-info > C:\Users\Administrator\Desktop\xld.sql

备份单表的部分数据（是一个亮点哦！）：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld xld_master_slave_text --where="id < 6 and age > 3" --no-create-info > C:\Users\Administrator\Desktop\xld.sql

注意：通过 "备份单表的部分数据" 可以做到单表增量数据恢复。

2.8 备份中包含存储过程，函数，事件

mysqldump 备份默认是不包含存储过程，自定义函数及事件的。可以使用 --routines 或 -R 选项备份存储过程及函数，使用 --events 或 -E 选项来备份事件。

扩展：查看当前库有哪些存储过程或函数
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'xld';

2.8.1 备份时包含存储过程和函数

在备份数据库时，想要包含存储过程和函数，则需要使用 --routines 或 -R 选项。语法如下：

mysqldump -h主机地址 -P端口 -u用户名 -p密码 --routines 数据库名称 > 备份文件名称.sql
# 或者
mysqldump -h主机地址 -P端口 -u用户名 -p密码 -R 数据库名称 > 备份文件名称.sql

例如：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 --routines --databases xld  > C:\Users\Administrator\Desktop\xld.sql
# 或者
mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 -R xld  > C:\Users\Administrator\Desktop\xld.sql

2.8.2 备份时包含事件

在备份数据库时，想要包含存储过程和函数，则需要使用 --events 或 -E 选项。语法如下：

mysqldump -h主机地址 -P端口 -u用户名 -p密码 --events 数据库名称 > 备份文件名称.sql
# 或者
mysqldump -h主机地址 -P端口 -u用户名 -p密码 -E 数据库名称 > 备份文件名称.sql

例如：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 --events --databases xld  > C:\Users\Administrator\Desktop\xld.sql
# 或者
mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 -E xld  > C:\Users\Administrator\Desktop\xld.sql

2.9 mysqldump 常用选项

mysqldump 常用选项如下：

--add-drop-database：在每个 create database 语句前添加 drop database 语句、

--add-drop-tables：在每个 create table 语句前添加 drop tables 语句。

--add-locking：用 lock tables 和 unlock tables 语句引用每个表转储。承载转储文件时插入得更快。

--all-databases，-A：转储所有数据库中的所有表。

--comment[=0|1]：（默认为：1）如果设置为 0，禁止转储文件中其他信息（例如：程序版本，服务器版本和主机）。

--skip-comments：和 --comment=0时效果相同。

--quick，-q：该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。

--compact：产生少量输出，该选项禁用注释并启用 --skip-add-drop-tables，--no-set-names，--skip-disable-keys 和 --skip-add-locking 选项。

--complete_insert，-C：使用包括列名的完整的 insert 语句。

--delete，-D：导入文本文件前清空表。

--default-character-set=charset：使用 charset 默认字符集。如果没有指定，就使用 utf8。

--delete-master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选择自动启用 --master-data。

--extended-insert，-e：使用包括几个 values 列表的多行 insert 语法。这样使得转储文件更小，重载文件时可能加速插入。

--flush-logs，-F：开始转储前刷新  MySQL 服务器日志文件。该选项要求用户要有 reload 权限

--force，-f：在表转储过程中，即使出现 SQL 错误也继续。

--lock-all-tables，-X：对所有数据库中的所有表加锁，在整体转储过程中通过全局锁定来实现。该选项自动关闭 --single-transaction 和 --lock-tables

--lock-table，-l：开始转储前锁定所有表。对于 InnoDB 的表来说，使用 --single-transaction 是一个更好的选项，因为它根本不需要锁定表。

--no-create-db，-n：不导出 create databases 建库语句。

--no-create-info, -t：只导出数据，不包含 create table 建表语句。

--no-data，-d：只导出 create table 建表语句。

--opt：该选项是速记，它可以快速进行转储操作并产生一个能很快装入 MySQL 服务器的转储文件、该选项默认开启，但可以用 --skip-opt 禁用。

--protocol={TCP|SOCKET|MEMORY}：使用的连接协议。

--host=host_name，-h host：将数据导入给定主机上的 MySQL 服务器，不指定默认 localhost。

--port，-P：端口号

--user，-u：用户名

--password，-p：密码

--socket=path，-S path：当连接localhost时使用的套接字文件（为默认主机）

--replace，-r：--replace 和 --ignore 选项控制复制唯一键值已有记录的输入记录的处理。
	1.如果指定 --replace，新行替换有相同唯一键值的已有行。**
	2.如果指定 --ignore，复制已有唯一键值的输入行被跳过。**
	3.如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。

--silent，-s：沉默模式。只有出现错误时才输出信息。

--verbose，-v：冗长模式，打印出程序操作的详细信息。

--xml，-X：产生xml输出。

运行帮助命令 mysqldump --help，可以获得特定版本的完整选项列表。

提示：

如果运行 mysqldump 时没有 --quick 或 --opt 选项，mysqldump 在转储结果前将整个结果集装入内存，那么在转储大数据库时可能会出现问题。

注意：该选项（--opt）是默认启用，但可以用 --skip-opt 禁用

如果使用最新版本的 mysqldump 程序备份数据，并用于恢复到比较旧版本的 MySQL 服务器中，则不要使用 --opt 或 -e 选项。

3. mysql 命令恢复数据

使用 mysqldump 命令将数据库中的数据备份成一个文本文件。需要恢复时，可以使用 mysql 命令来恢复备份的数据。

mysql 命令可以执行备份文件中的 create 语句和 insert 语句。通过 create语句来创建数据库和表。通过 insert 语句来插入备份的数据。

基本语法：

mysql -h主机地址 -P端口 -u用户 -p密码 [数据库名称] < 备份文件名称.sql

其中 "数据库名称" 是可选参数。可以指定数据库名称，也可以不指定。

指定数据库名时，表示还原数据库下的表，此时必须确保 MySQL 服务器中已经创建了该数据库。
不指定数据库名时，表示还原文件中所有的数据库。此时必须确保 sql 文件中包含了 create database 语句。

3.1 单库备份中恢复单库

备份文件中包含了创建数据库的语句，恢复数据时不需要指定数据库名称。

mysql -h10.10.77.51 -P3306 -uroot -pxld123 < C:\Users\Administrator\Desktop\xld_dome.sql

备份文件中不包含创建数据库的语句，恢复数据时需要指定数据库名称。

mysql -h10.10.77.51 -P3306 -uroot -pxld123 xld < C:\Users\Administrator\Desktop\xld_dome.sql

3.2 全量备份恢复

现在有一个全量备份文件，恢复全量数据。命令如下：

mysql -h10.10.77.51 -P3306 -uroot -pxld123 < C:\Users\Administrator\Desktop\all_data.sql

补充：

如果是使用 --all-databases 或 -A 选项备份了所有的数据库，那么恢复时不需要指定数据库。因为对应的 sql 文件会包含有 create database 语句，可通过该语句创建数据库。创建数据库后，可以执行 sql 文件中的 use 语句选择数据库，再创建表并插入数据。

3.3 从全量备份中恢复单个库

可能会有这样的需求，比如说我们只想恢复某一个库，但是我们有的是所有数据库的备份，这个时候我们可以从全量备份中分离出单个库的备份。

Linux 下的分离命令 - 语法如下：

sed -n '/^-- Current Database: `数据库名称`/,/^-- Current Database: `/p' 全量备份.sql > 备份文件名称.sql

例如：

sed -n '/^-- Current Database: `xld`/,/^-- Current Database: `/p' /usr/mysql/back/all_data.sql > /usr/mysql/back/xld.sql

Windows 下的分离命令：

# 兄弟，你自己想办法吧！

分离完成后我们再使用 mysql 命令恢复单个库中的数据。

3.4 从单个库备份中恢复单个表

这个需求还是比较常见的。例如说我们知道哪个表误操作了，那么就可以用单个表恢复的方式来恢复。

Linux 下的分离命令 - 语法如下：

cat 单个数据库备份文件.sql | sed -n -e '/DROP TABLE.*表名/,/UNLOCK TABLES/p' > 备份文件名称.sql

意思是：打印 DROP TABLE.*表名 存在的行和下一个 UNLOCK TABLES 存在的行之间的内容; DROP TABLE.*表名 中间的 .* 是正则所有的意思

例如：

cat /usr/mysql/back/xld.sql | sed -n -e '/DROP TABLE.*xld_dome/,/UNLOCK TABLES/p' > /usr/mysql/back/xld_dome.sql

Windows 下的分离命令：

# 兄弟，你自己想办法吧！

分离完成后我们建议使用 source 语句依次恢复表结构，表数据即可。

语法如下：

source 备份文件名称.sql

例如：

source /usr/mysql/back/xld_dome.sql

注意：source 语句是msyql服务中的命令，需要登录数据库执行。

4. 物理备份：直接复制整个数据库

直接将 MySQL 中的数据库文件复制出来。这种方法最简单，速度也最快（MySQL 的数据库目录位置不一定相同）。

在 windows 平台下，MySQL 8.0 存放数据库的目录通常默认为："C:\Program Files\MySQL\MySQL Server 8.0\Data" 或者其他用户自定义目录：
在 Linux 平台下，数据目录位置通常为 "/var/lib/mysql"
在 MAC OSX 平台下，数据库目录位置通常为 "/usr/local/mysql/data"

但为了保证备份的一致性。需要保证：

方式1：备份前，将服务器停止。
方式2：备份前，对相关表执行 flush tables with read lock 操作。这样当复制数据库目录中的文件时，允许其他客户继续查询表。同时，flush tables 语句来确保开始备份前将所有激活的索引页写入磁盘。

这种方式方便，快速，但不是最好的备份方法，因为实际情况可能不允许停止 MySQL 服务器或锁住表，而且这种方法对InnoDB存储引擎的表不适用。对于 MyISAM 存储引擎的表，这样备份和还原很方便，但是还原时最好是相同版本的 MySQL 数据库，否者可能会存在文件类型不同的情况。

注意：物理备份完毕后，执行 unlock tables 来结算其他客户对表的修改行为。

说明：

在 MySQL 版本号中，第一个数据表示版本号，主版本号相同的 MySQL 数据库文件格式相同。

此外，还可以考虑使用相关工具实现备份。比如，MySQLhotcopy 工具。MySQLhotcopy 是一个Per脚本，它使用 lock tables，flush tables 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表最快的途径，但它只能运行在数据库目录所在机器上，并且只能备份 MyISAM 类型的表。多用于 mysql 5.5 之前。

5. 物理恢复：直接复制到数据库目录

步骤：

将备份的数据库文件拷贝到数据目录下，并重启 MySQL 服务器。
查询相关表的数据是否恢复。需要使用下面的 chown操作。

要求：

必须确保备份数据的数据库和待恢复的数据库的主版本号相同。
- 因为只有 MySQL 数据库主版本相同时，才能保证这两个 MySQL 数据库文件类型是相同的。
这种方式对 MyISAM类型的表比较有效，对于 InnoDB 类型的表则不可用。
- 因为 InnoDB 表的表空间不能直接复制。
在 Linux 操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成 mysql。

chown -R mysql.mysql 数据库目录
# 例如
chown -R mysql.mysql /var/lib/mysql/dbname

说明：其中，两个 mysql 分别表示组和用户。"-R" 参数可以改变文件下的所有子文件的用户和组。"dbname" 参数表示数据库目录。

提示：

Linux 操作系统下的权限设置非常严格。通常情况下，MySQL 数据库只有 root 用户和 mysql 用户组下的 mysql 用户才可以访问，因此将数据库目录复制到指定文件夹后，一定要使用 chown 命令将文件夹的用户组变为 mysql，将用户变为 mysql。

6. 表的导出与导入

6.1 表的导出

6.1.1 使用 select ... into outfile 导出文件文件

在 MySQL 中，可以使用 select ... into outfile 语句将表的内容导出成一个文件文件。语法如下：

select * from 表名 into outfile "secure_file_priv参数指向的路径/导出的文件名称.txt";

mysql 默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作（是通过 secure_file_priv 来控制的）。

查询 secure_file_priv（全局级别）的值：

show variables like '%secure_file_priv%';
# 或者
select @@global.secure_file_priv;

# 查询的结果
-----------------+----------------------+
Variables_name	 | Value				|
-----------------+----------------------+
secure_file_priv | /var/lib/mysql-files/|

secure_file_priv 的可选值和作用说明：

如果为 empty，表示不限制文件生成的位置，这是不安全的设置。
如果为一个表示路径的字符串（/var/mysql/export），就要求生成的文件只能放在这个指定的目录，或者它的子目录。
如果为 null，就表示禁止执行 select ... into outfile 操作。

导出表数据：可以看到上面结果中显示，secure_file_priv 的值为 /var/lib/mysql-files/，则导出的文件必须在该目录下。SQL 语句如下：

select * from xld_master_slave_text into outfile "/var/lib/mysql-files/xld_master_slave_text.txt";

查看导出的文件（查看：/var/lib/mysql-files/xld_master_slave_text.txt）

1	0	xld1
2	2	xld2
3	3	xld3
4	4	xld4
5	5	xld5
6	66	xld6

扩展：

使用 fields 选项，要求字段之间使用逗号 "," 间隔，所有字符类型字段值用双引号括起来：
select * from xld_master_slave_text into outfile "/var/lib/mysql-files/xld_master_slave_text.txt" fields terminated by ',' enclosed by '\"';

6.1.2 使用 mysqldump 命令导出文本文件

Linux 系统下使用 mysqldump 导出表数据的语法如下：

mysqldump -h主机地址 -P端口 -u用户名 -p密码 -T "secure_file_priv参数指向的路径" 数据名 表名

例如：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 -T "/var/lib/mysql-files/" xld xld_master_slave_text

当 mysqldump 命令执行完毕后，在指定的目录 /var/lib/mysql-files/ 下生成了 xld_master_slave_text.sql 和 xld_master_slave_text.txt 文件。

查看 xld_master_slave_text.sql 文件（注意：该文件内容只包含创建表的 create 语句，是没有表数据的。表数据在 .txt 结尾的文件中。）：

-- MySQL dump 10.13  Distrib 5.7.35, for el7 (x86_64)
--
-- Host: 10.10.77.51    Database: xld
-- ------------------------------------------------------
-- Server version	8.0.20

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `xld_master_slave_text`
--

DROP TABLE IF EXISTS `xld_master_slave_text`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `xld_master_slave_text` (
  `id` int NOT NULL COMMENT '主键id',
  `age` tinyint DEFAULT '0' COMMENT '年龄',
  `xld_name` varchar(25) NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-09-05 14:23:33

扩展：

使用 fields 选项，要求字段之间使用逗号 "," 间隔，所有字符类型字段值用双引号括起来：
mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 -T "/var/lib/mysql-files/" xld xld_master_slave_text --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

Windows 系统下使用 mysqldump 导出表数据的语法如下：

mysqldump -h10.10.77.51 -P3306 -uroot -pxld123 xld xld_master_slave_text -T C:\Users\Administrator\Desktop\

错误：
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

（mysqldump:获取错误：1290:MySQL服务器正在使用--secure file priv选项运行，因此在执行“SELECT INTO OUTFILE”时无法执行此语句）

错误说明：由于你本机上的 MySQL 服务中的 secure-file-priv 是 null，所以无法导出。可以发现其实 mysqldump 导出的本质还是：select ... into outfile 语句

6.1.3 使用 mysql 命令导出文本文件

Linux 系统下使用 mysql 导出表数据的语法如下：

mysql -h主机地址 -P端口 -u用户名 -p密码 --execute="select * from 表名;" 数据库名称 > "导出的文件名称.txt"

例如：

mysql -h10.10.77.51 -P3306 -uroot -pxld123 --execute="select * from xld_master_slave_text;" xld > "/var/lib/mysql-files/xld.txt"

Windows 系统下使用 mysql 导出表数据的语法如下：

mysql -h10.10.77.51 -P3306 -uroot -pxld123 --execute="select * from xld_master_slave_text;" xld > C:\Users\Administrator\Desktop\xld.txt

说明：该命令导出数据更加的灵活可以编写 SQL 语句，并且导出的文件地址也可以随意指定。

扩展：

使用 --vertical 选项将该条记录分为多行显示：

mysql -h10.10.77.51 -P3306 -uroot -pxld123 --vertical --execute="select * from xld_master_slave_text;" xld > "/var/lib/mysql-files/xld.txt"

使用 --xml 选项将表数据导出到 xml 文件：

mysql -h10.10.77.51 -P3306 -uroot -pxld123 --xml --execute="select * from xld_master_slave_text;" xld > "/var/lib/mysql-files/xld.xml"

使用 --html 选项将表数据导出到 html 文件：

mysql -h10.10.77.51 -P3306 -uroot -pxld123 --html --execute="select * from xld_master_slave_text;" xld > "/var/lib/mysql-files/xld.html"

6.2 表的导入

6.2.1 使用 load data infile 方式导入文本文件

在 MySQL 中，可以使用 load data infile 语句将导出的数据恢复到数据库中。语法如下：

load data infile '文件的绝对路径' into table 数据库名称.表名;

例如：

load data infile '/var/lib/mysql-files/xld_master_slave_text.txt' into tables xld.xld_master_slave_text;

扩展：对标在导出文件时，使用了 fields 和 lines 选项，要求字段之间使用逗号 "," 间隔，所有字段值用双引号括起来：
load data infile '/var/lib/mysql-files/xld_master_slave_text.txt' into tables xld.xld_master_slave_text fields terminated by ',' enclosed by '\"';

6.2.2 使用 mysqlimport 方式导入文本文件

Linux 系统下使用 mysqlimprot 命令导入文本文件的语法如下：

mysqlimport -h主机地址 -P端口 -u用户名 -p密码 数据库名称 '文件的绝对路径'

例如：

mysqlimport -h10.10.77.51 -P3306 -uroot -pxld123 xld '/var/lib/mysql-files/xld_master_slave_text.txt'

Windows 系统下使用 mysqlimprot 命令导入文本文件的语法如下：

mysqlimport -h主机地址 -P端口 -u用户名 -p密码 数据库名称 文件的绝对路径

例如：

mysqlimport -uroot -pxld123 xld C:\Users\Administrator\Desktop\xld_dome.txt

错误：
mysqlimport: Error: 1290, The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, when using table: xld_dome

（mysqlimport:Error:1290，MySQL服务器使用--secure file priv选项运行，因此在使用表xld_dome时无法执行此语句）

错误说明：由于你本机上的 MySQL 服务中的 secure-file-priv 是 null，所以无法导入。

mysqlimport 的相关选项/参数

--columns=column_list，-c column_list：该选项采用逗号分隔的列名作为其值。列名的顺序只是如何匹配数据文件列和表列。

--compress，-C：压缩在客户端和服务器之间发送的所有信息。

--local，-L：从本地客户端读入输入文件。

--delete，-d：导入文本文件前清空表。

--lock-tables，-l：处理文本文件前锁定所有表，以便写入（这样可以确保所有表保存同步）。

--protocol={TCP|SOCKET|MEMORY}：使用的连接协议。

--host=host_name，-h host：将数据导入给定主机上的 MySQL 服务器，不指定默认 localhost。

--port，-P：端口号

--user，-u：用户名

--password，-p：密码

--replace，-r：--replace 和 --ignore 选项控制复制唯一键值已有记录的输入记录的处理。

如果指定 --replace，新行替换有相同唯一键值的已有行。

如果指定 --ignore，复制已有唯一键值的输入行被跳过。

如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。

--ignore，-i：参见 --replace 选项的描述。

--ignore-lines=n：忽视数据文件的前 n 行。

--force，-f：忽视错误。例如，如果某个文本文件的表不存在，就继续处理其他文件。不使用 --force，若表不存在，则 mysqlimport 退出。

--silent，-s：沉默模式。只有出现错误时才输出信息。

--verbose，-v：冗长模式，打印出程序操作的详细信息。

--version，-V：显示版本信息并退出。

7. 数据库迁移

7.1 数据库迁移概述

数据迁移（data migration）是指选择，准备，提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外，验证迁移数据的完整性和退役原来旧的数据存储，也被认为整个数据迁移过程的一部分。

数据库迁移的原因是多样的，包括服务器或存储设备更换，维护或升级，应用程序迁移，网站集成，灾难恢复和数据中心迁移。

根据不同的需求可能要采取不同迁移方案，但总体来讲，MySQL 数据迁移方案大致可以分为物理迁移和逻辑迁移两类。通常以尽可能自动化的方式执行，从而将人力资源从繁琐的任务中解放出来。

7.2 迁移方案

物理迁移

物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。

物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。

不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装同版本的数据库管理系统，创建相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置文件组权限，之后再重启新的数据库。

逻辑迁移

逻辑迁移适用范围更广，无论是 "部分迁移" 还是 "全量迁移"，都可以使用 "逻辑迁移"。逻辑迁移中使用最多的就是通过 mysqldump 等备份工具。

7.3 迁移注意点

7.3.1 相同版本的数据库之间迁移注意点

指的是在主版本号相同的 MySQL 数据库之间进行数据库移动。

方式1：因为迁移前后 MySQL 数据库的主版本号相同，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于 MyISAM 引擎的表。对于 InnoDB 表，不能用直接复制文件的方式备份数据库。
方式2：最常见和最安全的方式是使用 mysqldump 命令导出数据，然后再目标数据库服务器中使用 mysql 命令导入。

例如：备份 host1 上的所有的数据库，并将数据库迁移到 host2 机子上
mysqldump -hhost1  -P端口 -u用户名 -p密码 --all-databases | mysql -hhost2 -P端口 -u用户名 -p密码
在上述语句中，"|" 符号表示管道，其作用是将 mysqldump 备份的文件给 mysql 命令；通过这种方式也可以直接实现迁移。

7.3.2 不同版本的数据库之间迁移注意点

例如，原来很多服务器使用 5.7 版本的 MySQL 数据库，在 8.0 版本推出来以后，改进了 5.7 版本的很多缺陷，因此需要把数据库升级到 8.0 版本。

旧版本与新版本的 MySQL 可能使用不用的默认字符集，例如有的旧版本使用 latin1 作为默认字符集，而最新版本的 MySQL 默认字符集为 utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对默认字符集进行修改，不然可能无法正常显示数据。

高版本的 MySQL 数据库通常都会兼容低版本，因此可以从低版本的 MySQL 数据库迁移到高版本的 MySQL 数据库。

7.3.3 不同数据库之间迁移注意点

MySQL 官方提供了 MySQL Migration Toolkit 工具来实现不同数据库之间的数据迁移。
MySQL 和 SQL Server 可以通过使用 MyODBC 实现数据相互迁移。
MySQL 迁移到 Oracle 时，需要使用 mysqldump 命令导出 sql 文件，然后，手动更改 sql 文件中的 create 语句。

7.3.3 迁移小结

8. 删库了不敢跑，能干点啥？

传统的高可用架构是不能预防误删数据的，因为主库的一个 drop table 命令，会通过 bin log 传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。

为了能找到解决误删数据的方法，我们需要先对 MySQL 中误删数据的操作，做下分类：

使用 delete 语句误删数据行。
使用 drop table 或 truncate table 语句误删数据表。
使用 drop database 语句误删数据库。
使用 rm 命令误删整个 MySQL 实例。

8.1 delete ：误删行

处理措施：数据恢复

使用 Flashback 工具恢复数据。

原理：修改 bin log 内容，拿回原库重放。如果误删数据涉及到了多个事务的话，需要将事务顺序调过来再执行。

使用前提：binlog_format = row 和 binlog_image = FULL。

处理措施：预防

代码上线前，必须 SQL 审查，审计。

建议可以打开安全模式，把 sql_safe_updates 参数设置为 on。强制要求加 where 条件且 where 条件需要是索引字段，否则必须使用 limit。不然就会报错。

8.2 truncate / drop ：误删库 / 表

背景：

delete 全表是很慢的，需要生成回滚日志，写 redo，写 bin log。所以，从性能角度考虑，优先考虑使用 truncate table 或者 drop table 命令。

使用 delete 命令删除的数据，你还可以用 Flashback 来恢复。而使用 truncate / drop table 和 drop database 命令删除的数据，就没办法通过 Flashback 来恢复了。因为，即使我们配置了 binlog_format = row，执行三个命令时，记录的 bin log 还是 statement 格式。bin log 里面就只有一个 truncate / drop 语句，这些信息是恢复不来数据的。

方案：

这种情况下恢复数据，需要使用全量备份与增量日志结合的方式。

方案的前提：有定期的全量备份，并且开启写入 bin log。

恢复的步骤：

取最近一次全量备份。假设设置数据库是一天一备，最近备份数据是当天凌晨2点。
用备份恢复出一个临时库（注意：这里选择临时库，而不是直接操作主库）。
取出凌晨2点之后的 bin log 日志。
剔除误删除数据的语句外，其他语句全部应用到临时库（bin log 的恢复）。
最后恢复到主库。

8.3 预防使用 truncate / drop 误删库 / 表

刚刚我们说了使用 truncate / drop 语句误删库 / 表的恢复方案。那么我们接下来说说如何在生产环境下尽量的避免类似的误操作。

权限分离
- 限制账户权限，核心的数据库，一般都不能随便分配写权限，想要获取写权限需要审批。
- 不同的账号，不同的数据之间要进行权限分离，避免一个账号可以删除所有库。
制定操作规范
- 比如在删除数据库表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。
- 比如在删除数据库表之前，必须先对表做复制操作。
设置延迟复制备库

简单的说延迟复制就是设置一个固定的延迟时间，比如1个小时，让从库落后主库一个小时。出现误删除操作1小时内，到这个备库上执行 stop slave（关闭复制），再通过之前介绍的方法，跳过误操作语句，就可以恢复出需要的数据。这里通过 change master to master_delay = n 语句，可以指定这个备库持续保持跟主库有 n 秒的延迟。

8.4 rm：误删 MySQL 实例

对于一个有高可用机制的 MySQL 集群来说，不用担心 rm 删除数据。因为只删除其中一个节点数据的话，HA系统就会选出一个新的主库，从而保证整个集群的正常工作。我们把这个节点上的数据恢复回来后，再进入整个集群就好了。

但如果是恶意的删除整个集群，那就需要考虑跨机房备份，跨城市备份了。

备份

MySQL

526互联

MySQL备份与恢复

数据库备份与恢复

1. 物理备份与逻辑备份

2. mysqldump 实现逻辑备份

2.1 备份一个数据库 - 不包含 "创建库" 的 sql 语句

2.2 备份全部数据库

2.3 备份部分数据库 - 包含 "创建库" 的 sql 语句

2.4 备份部分表

2.5 备份单表的部分数据

2.6 排除某些表的备份

2.7 只备份结构或只备份数据

2.7.1 只备份结构

2.7.2 只备份数据

2.8 备份中包含存储过程，函数，事件

2.8.1 备份时包含存储过程和函数

2.8.2 备份时包含事件

2.9 mysqldump 常用选项

3. mysql 命令恢复数据

3.1 单库备份中恢复单库

3.2 全量备份恢复

3.3 从全量备份中恢复单个库

3.4 从单个库备份中恢复单个表

4. 物理备份：直接复制整个数据库

5. 物理恢复：直接复制到数据库目录

6. 表的导出与导入

6.1 表的导出

6.1.1 使用 select ... into outfile 导出文件文件

6.1.2 使用 mysqldump 命令导出文本文件

6.1.3 使用 mysql 命令导出文本文件

6.2 表的导入

6.2.1 使用 load data infile 方式导入文本文件

6.2.2 使用 mysqlimport 方式导入文本文件

7. 数据库迁移

7.1 数据库迁移概述

7.2 迁移方案

7.3 迁移注意点

7.3.1 相同版本的数据库之间迁移注意点

7.3.2 不同版本的数据库之间迁移注意点

7.3.3 不同数据库之间迁移注意点

7.3.3 迁移小结

8. 删库了不敢跑，能干点啥？

8.1 delete ：误删行

8.2 truncate / drop ：误删库 / 表

8.3 预防使用 truncate / drop 误删库 / 表

8.4 rm：误删 MySQL 实例