MySQL、备份--mysqldupm

前言

数据库备份的重要性毋庸置疑，可以说是数据安全的最后一道防线。所以接下来会介绍一下MySQL备份的相关内容。

备份分类

• 物理备份和逻辑备份

物理备份

顾名思义就是备份物理文件，备份MySQL底层存储数据的文件，比如 .ibd、.frm、.myd、.myi 等结尾的文件。

优点：

1. 备份速度快： 物理备份通常比逻辑备份快，因为它是直接复制文件，而不是通过 SQL 语句来导出数据。

2. 恢复速度快： 恢复物理备份时，只需要将备份的数据文件恢复到相应位置，过程通常比逻辑备份更快。

3. 完整性高： 物理备份保证了数据库在备份时的完整性，包括表结构、数据和索引等。

缺点：

1. 依赖于存储引擎： 物理备份一般只适用于特定的存储引擎（如 InnoDB 或 MyISAM）。对于不同的存储引擎，备份方式可能不同。

2. 对版本有要求：恢复数据时，MySQL不同的版本可能不适配。

3. 备份文件比较大。

逻辑备份

逻辑备份是通过 MySQL 提供的命令和工具将数据库中的数据导出为 SQL 脚本或其他格式（如 CSV、TSV）来备份。备份文件通常包含了 SQL 语句，用于重建数据库对象（表、索引等）、INDERT语句、DELETE语句等。

优点：

1. 可移植性强：逻辑备份生成的 SQL 文件是纯文本格式，可以在不同操作系统和 MySQL 版本之间进行迁移和恢复。

2. 不依赖于存储引擎：逻辑备份可以备份所有存储引擎的数据。

3. 灵活性高：逻辑备份可以选择备份特定的表或数据库，而不需要备份整个数据库实例。

缺点：

1. 备份速度慢： 逻辑备份通过执行 SQL 语句导出数据，速度比物理备份慢。

2. 恢复速度慢： 恢复时需要重新执行 SQL 脚本，速度相对较慢。

• 离线备份和在线备份

离线备份

离线备份指的是在备份过程中，数据库必须停止服务或者锁定数据库，以保证数据的一致性和完整性。也就是说，数据库在备份期间无法接受读写操作。离线备份也称 冷备。此时只能进行物理备份，即拷贝物理文件。

优点：

1. 数据一致性高： 因为备份时数据库不可用，所有数据在备份时不会发生变动，确保了备份的一致性。

2. 操作简单： 对于一些小型数据库，离线备份通常是最简单的备份方式，只需要将数据库文件直接复制即可。

缺点：

1. 停机时间： 离线备份需要停止数据库，导致应用系统无法访问数据库，可能会影响业务的正常运行。

在线备份

在线备份指的是在数据库仍然处于运行状态时进行的备份，数据库可以继续提供读写服务。在线备份也称 热备。此时可以进行物理备份也可以进行逻辑备份。

优点：

1. 无需停机： 在线备份能够在数据库仍然为用户提供服务的情况下进行，不会影响应用的正常运行，适合高可用环境。

2. 适用于大规模生产环境： 可以在不停机的情况下进行备份，保证业务连续性。

缺点：

1. 备份可能不完全一致： 在备份过程中，如果数据正在发生变化（如写操作），可能会出现一致性问题。通常需要使用额外的机制（如事务日志）来确保一致性。

2. 备份过程复杂： 在线备份的实施和管理可能更复杂，因为需要保证备份时的数据完整性和一致性。

• 全量备份和增量备份

全量备份

全量备份是指对整个数据库的所有数据进行备份，包括数据库中的所有表、索引、存储过程、视图、用户等信息。备份时，数据库的所有内容都会被复制并存储。

优点：

1. 恢复简单： 恢复全量备份时，只需要恢复一份备份文件，操作简单。

2. 数据一致性： 由于是对整个数据库进行备份，数据的一致性较高，适用于不频繁备份的场景。

缺点：

1. 备份速度慢： 全量备份需要备份整个数据库，备份时间较长，尤其是大规模数据库。

2. 存储需求大： 由于备份的内容多，存储需求较高。

增量备份

增量备份是指自上次备份以来发生变化的数据部分的备份。增量备份只备份自上次备份（全量备份或增量备份）后发生变化的文件或数据块，因此备份的量较小。

优点：

1. 备份速度快： 只备份发生变化的部分，备份时间较短。

2. 节省存储空间： 由于只备份变动的数据，所需的存储空间远小于全量备份。

缺点：

1. 恢复复杂： 恢复时需要先恢复最新的全量备份，再依次恢复所有增量备份，否则无法完整恢复数据。

2. 数据一致性问题： 如果增量备份之间存在依赖关系，在恢复时容易出错，需要确保备份链的完整性。

上面基于备份的类型做了分类，其实笼统的可以分为，物理备份和逻辑备份，下面介绍的MySQL相关的备份工具，我将以物理备份和逻辑备份作为区分。

物理备份的相关工具：XtraBackup、MySQL Enterprise Backup、克隆插件。

逻辑备份的相关工具：mysqldupm、mydumper、mysqlpump、MySQL Shell Dump & Load。

后面我将会分别介绍以上各种工具，并简单说一下实现原理。接下来要说的是mysqldupm，这个比较常见，也是MySQL官方提供的，日常用的也比较多。mysqldupm内置在MySQL安装包中，所以不需要额外安装。

下面简单执行一下mysqldupm一个指令：

mysqldump -h localhost -P 3306 -u root -p123456 test_db > D:\backups\mydatabase_backup.sql

我这边因为没有安装Linux环境，所以直接cmd执行该命令，不需要MySQL登录。

我们简单看一下生成的sql文件：

很清楚的看到，生成的sql脚本，先删除，再创建表，然后再批量INSERT。

下面我们就来详细介绍一下mysqldump的语法。

PS：想要了解更多，可以看下MySQL官网，https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html

mysqldump语法

• mysqldump -h xxx-P 3306 -u root -pxxx

上述语句，是不变的，也不做过多介绍，主要参数IP地址+端口+账号+密码

过滤相关语法

• --all-databases（简写 -A）

备份所有数据库，默认不会备份 information_schema、performance_schema、sys库。

• --databases（简写 -B）

备份指定库，如果要备份多个库，库与库之间用空格隔开。

• --tables 

备份指定表，如果备份所有表，后面可以不写。如果备份指定表，表与表之间用空格隔开。

mysqldump -h localhost -P 3306 -u root -p123456 test_db --tables colors products > D:\backups\mydatabase_backup.sql

• --insert-ignore = db.table

忽略执行表的备份。如果需要过滤多个，需要写两次--insert-ignore = db.table。

mysqldump -h localhost -P 3306 -u root -p123456 test_db --ignore-table=test_db.colors --ignore-table=test_db.products > D:\backups\mydatabase_backup.sql

• --events（简写 -E）

备份事件，此选项需要这些数据库的event权限。使用--events生成的输出包含CREATE EVENT语句来创建事件。

• --routines（简写 -R）

 备份存储过程和自定义函数。该选项需要全局SELECT权限，使用--routines生成的输出包含CREATE PROCEDURE和CREATE FUNCTION语句。

• --triggers

备份触发器，默认开启。可通过--skip triggers语句将其关闭。

• --no-data

只备份表结构，不备份数据。

• --where='where_condition'（简写-w）

只备份满足WHERE条件的数据，后面会说到mysqldump的底层实现原理其实是SELECT * FROM TABLE_NAME，所以可以加where条件过滤。

>mysqldump -h localhost -P 3306 -u root -p123456 --databases test_db --source-data=1 -w"id=1"  > D:\backups\mydatabase_backup.sql

 --where需要加=，而简写-w不需要=，多个条件同上面库、表一样，空格分开。注：WHERE条件中包含的字段需要库中所有表都要有，否则会报错。

DDL相关语法

• --add-drop-database

在每个CREATE DATABASE语句之前添加DROP DATABASE语句，默认不会添加。

• --add-drop-table

在每个CREATE TABLE语句之前添加DROP TABLE语句，默认是开启的。可以使用--skip-add-drop-table语句，则不会添加DROP TABLE语句。

• --no-create-info（简写 -t）

在备份之前，备份文件都会添加CREATE TABLE操作，使用该语句则不会。

• --replace

使用REPLACE INTO替代INSERT INTO，不知道REPLACE INTO可以自己百度下。

• --insert-ignore

使用INSERT IGNORE替代INSERT INTO。

• --no-create-db（简写 -n）

如果给出了--databases或--all-databases选项，则禁止在输出中包含CREATE DATABASE语句。否则备份文件就会添加CREATE DATABASE语句。

复制相关

• --master-data[=value]，MySQL8.0.26之后的版本已弃用，建议使用--source-data[=value]
• --source-data[=value]

--master-data[=value]和--source-data[=value]功能都差不多，这里只说一下--source-data[=value]。首先要知道这两个是干什么的，该选项会在导出的 SQL 文件中加入一条 CHANGE REPLICATION SOURCE TO语句，用于设置主服务器的信息，方便从服务器进行数据同步。这条语句包含了当前二进制日志的文件名和位置，主服务器上最新的二进制日志的位置。简单来说就是从库同步的时候，要告诉从库从哪开始同步主库的内容。

mysqldump -h localhost -P 3306 -u root -p123456 --databases test_db --source-data=1  > D:\backups\mydatabase_backup.sql

 value设置为1时，备份的文件这句话不会被注释，设置为2时会注释。

• --dump-replica[=value]

包括CHANGE REPLICATION SOURCE TO语句，该语句列出副本源的二进制日志坐标。

• --include-source-host-port

在使用--dump-replica生成的CHANGE REPLICATION SOURCE TO语句中包含SOURCE_HOST和SOURCE_PORT选项。

--dump-replica[=value]和--include-source-host-port是专门用于处理主从复制环境中备份的一个功能。

事务相关

• --single-transaction

在开始备份之前，发出BEGIN SQL语句，简单来说就是会开启事务，同时获取事务表的一致性快照，从而保证整个备份期间数据的一致性。

当--single-transaction转储正在进行时，为了确保转储文件有效（正确的表内容和二进制日志），其他连接不应该使用以下语句：ALTER table， CREATE table, DROP table, RENAME table, TRUNCATE table。一致性读并不是与这些语句隔离的，所以在要转储的表上使用它们可能会导致mysqldump在检索表内容时执行SELECT，从而获得不正确的内容或失败。要转储大型表，请将--single-transaction选项与--quick选项组合使用。（来自MySQL官网）

mysqldump -h localhost -P 3306 -u root -p123456 --databases test_db --source-data=1 --single-transaction  > D:\backups\mydatabase_backup.sql

• --lock-all-tables（简写 -x）

锁定所有数据库中的所有表。这是通过在整个备份期间获取全局读锁来实现的。这个选项自动关闭。在加锁期间，只能读不能写。

• --lock-tables（简写 -l）

在备份所有表之前锁定它们，跟--lock-all-tables不同的是，--lock-tables锁的是库，而--lock-all-tables锁的是MySQL实例，实例下的所有库+所有表。所以--lock-tables只能保证每个数据库的一致性，不能保证整个实例的备份一致性。

--single-transaction和--lock-tables是互斥的，因为--single-transaction是快照读，而--lock-tables是锁表。两个同时执行，不会报错，但是--single-transaction会覆盖--lock-tables。

• --add-locks

备份的INSERT语句，在INSERT操作之前添加锁表操作。

性能相关

• --opt

该命令等同于开启以下命令，--add-drop-table、--add-locks、create-options、disable-keys、--extended-insert、--lock-tables、--quick、--set-charset。

• --quick（简称 -q）

从服务器检索表的行，每次一行。这个选项对于备份大型表很有用。它强制mysqldump从服务器检索表的行，每次一行，而不是将查出的数据缓冲在内存中，然后再读取每行。

如果执行--quick，则会通过mysql_use_result()获取结果集，反正则通过mysql_store_result()获取。

mysql_store_result：会一次性获取结果集，然后缓存在客户端中，所以这就导致如果这个结果集很大，而客户端还存在内存溢出的风险，所以对内存要求很高。

mysql_use_result：会返回一个游标，后续通过mysql_fetch_row()逐行获取数据，每次只需要为一行数据分配内存，而且不用为整个结果集设置复杂的数据结构，相对来说速度更快。

• --disable-keys（简称 -k）

在数据备份完毕后，要导入数据前，现金用索引，等导入后，再开启索引。这样，可以提升导入的速度，只不过该选项仅对MyISAM表的非唯一索引有效。

• --extended-insert（简称 -e）

将多行数据放到一个INSERT语句中，默认是开启的。也可以一条数据一个INSERT，通过这个命令 --skip-extended-insert，这个肯定不建议使用。

其他重要选项

• --flush-privileges

在mysql数据库备份后，输出的文本中添加一个FLUSH PRIVILEGES操作。FLUSH PRIVILEGES，用于刷新服务器的权限缓存，让对用户权限的更改立即生效。

• --set-charset

在备份的文件中，增加SET NAMES default_character_set，在MySQL8.0中default_character_set默认为utf8mb4。

• --result-file=file_name （简写 >）

指定备份文件，可以简写成>。

其他还有一些指令，想要了解更多的，大家可以看一下MySQL的官网，上面也给过URL地址了。

mysqldump实现原理

了解一个工具的实现原理，最简单的办法就是看源码，但是这个要求有点高，除非是专业数据库的工作人员。否则也没必要看源码，这里推荐一个另一个方法去看实现原理，就是general log。

general log会记录MySQL服务器接受到的所有SQL操作，无论是否执行成功。开启general log会对MySQL的性能有一定的影响，所以正式环境上很少开启，排查问题时可以开启，之后再关闭。

//登录MySQL，开启general log
set globa general_log = on;

//查询general log保存位置
show variables like 'general_log_file';

执行命令：

mysqldump -h localhost -P 3306 -u root -pyjw123456 test_db > D:\backups\mydatabase_backup.sql

执行mysqldump命令之后，查看general log日志，我把我这边查询出来的复制出来：

2024-12-09T11:04:38.128057Z	   74 Connect	root@localhost on  using SSL/TLS
2024-12-09T11:04:38.128123Z	   74 Connect	Access denied for user 'root'@'localhost' (using password: YES)
2024-12-09T11:04:47.261870Z	   75 Connect	root@localhost on  using SSL/TLS
2024-12-09T11:04:47.262092Z	   75 Query	/*!40100 SET @@SQL_MODE='' */
2024-12-09T11:04:47.262242Z	   75 Query	/*!40103 SET TIME_ZONE='+00:00' */
2024-12-09T11:04:47.262346Z	   75 Query	/*!80000 SET SESSION information_schema_stats_expiry=0 */
2024-12-09T11:04:47.262409Z	   75 Query	SET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 86400
2024-12-09T11:04:47.262505Z	   75 Query	SHOW VARIABLES LIKE 'gtid_mode'
2024-12-09T11:04:47.264194Z	   75 Query	SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'ndbcluster' AND FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE ENGINE = 'ndbcluster' AND FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test_db'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2024-12-09T11:04:47.266576Z	   75 Query	SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test_db')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2024-12-09T11:04:47.267558Z	   75 Query	SHOW VARIABLES LIKE 'ndbinfo\_version'
2024-12-09T11:04:47.268519Z	   75 Init DB	test_db
2024-12-09T11:04:47.268659Z	   75 Query	show tables
2024-12-09T11:04:47.269408Z	   75 Query	LOCK TABLES `colors` READ /*!32311 LOCAL */,`customers` READ /*!32311 LOCAL */,`grades` READ /*!32311 LOCAL */,`middle_students` READ /*!32311 LOCAL */,`orderdetails` READ /*!32311 LOCAL */,`orders` READ /*!32311 LOCAL */,`products` READ /*!32311 LOCAL */,`student_address` READ /*!32311 LOCAL */,`students` READ /*!32311 LOCAL */
2024-12-09T11:04:47.270481Z	   75 Query	show table status like 'colors'
2024-12-09T11:04:47.271381Z	   75 Query	SET SQL_QUOTE_SHOW_CREATE=1
2024-12-09T11:04:47.271584Z	   75 Query	SET SESSION character_set_results = 'binary'
2024-12-09T11:04:47.271810Z	   75 Query	show create table `colors`
2024-12-09T11:04:47.272159Z	   75 Query	SET SESSION character_set_results = 'utf8'
2024-12-09T11:04:47.272383Z	   75 Query	show fields from `colors`
2024-12-09T11:04:47.273191Z	   75 Query	show fields from `colors`
2024-12-09T11:04:47.273929Z	   75 Query	SELECT /*!40001 SQL_NO_CACHE */ * FROM `colors`
2024-12-09T11:04:47.274156Z	   75 Query	SET SESSION character_set_results = 'binary'
2024-12-09T11:04:47.274218Z	   75 Query	use `test_db`
2024-12-09T11:04:47.274288Z	   75 Query	select @@collation_database
2024-12-09T11:04:47.274356Z	   75 Query	SHOW TRIGGERS LIKE 'colors'
2024-12-09T11:04:47.274988Z	   75 Query	SET SESSION character_set_results = 'utf8'
2024-12-09T11:04:47.275071Z	   75 Query	SET SESSION character_set_results = 'binary'
2024-12-09T11:04:47.275140Z	   75 Query	SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'test_db' AND TABLE_NAME = 'colors'
2024-12-09T11:04:47.275369Z	   75 Query	SET SESSION character_set_results = 'utf8'

**********************这里我只把其中一张表的备份过程内容展示出来，其他表备份过程都是一样**************************

2024-12-09T11:04:47.306436Z	   75 Query	UNLOCK TABLES
2024-12-09T11:04:47.307752Z	   75 Quit	

上面的日志内容很多，我只把其中一张表的备份过程展示出来，其他表备份过程其实都是一样的。下面就来说一下备份的过程（看不懂的可以省略）：

1. 验证账号密码，创建连接。
2. 查询系统表INFORMATION_SCHEMA.FILES、INFORMATION_SCHEMA.PARTITIONS，获取相关信息。
3. LOCK TABLES，会对库下的所有表加锁，下面我只以colors表为例。
4. 查询colors表的状态、查询colors表的所有字段
5. SELECT /*!40001 SQL_NO_CACHE */ * FROM `colors`，查询colors表的数据。
6. SET SESSION character_set_results = 'utf8'，设置当前会话的字符集为utf8。
7. UNLOCK TABLES，对所有表释放锁。
8. Quit，执行结束。

不同的执行命令，general log日志记录的内容肯定也不一样。我这里执行的mysqldump语句比较简单，大家可以加上其他命令，执行一下看看general log日志内容。

总结下来，mysqldump执行过程大致看下来挺简单的，查询库下的所有表，对表加锁。然后执行SELECT查询，最后释放锁。实现原理是挺简单的，但是过程肯定是有点复杂的。

mysqldump实现全量备份和增量备份

• mysqldump全量备份

mysqldump本身就是支持全量备份的，在Windows环境中写一个bat脚本，定时执行就可以实现全量备份。如果是Linux环境，也可以写一个shell命令是一样的。

• mysqldump增量备份

mysqldump本身是不支持增量备份的，但是也可以实现，有以下两种方式。

使用--where 选项进行增量备份

可以通过--where选项基于某个字段（如时间戳或自增ID）来备份数据库中自上次备份以来发生变化的数据。比如：

mysqldump -u root -p --where="last_modified > '2024-12-01 00:00:00'" database_name table_name > incremental_backup.sql

这个时间肯定是动态的，每天备份更新的数据。

使用二进制日志进行增量备份

MySQL 的二进制日志（binary log）记录了所有数据库的更改操作。通过启用二进制日志，可以在进行全量备份后，只备份增量的二进制日志来实现增量备份。具体步骤如下：

1.启用二进制日志：确保 MySQL 配置文件中启用了二进制日志

//在 my.cnf 或 my.ini 文件中添加以下内容：
[mysqld]
log-bin=mysql-bin
server-id=1

 2. 先进行一次全量备份

mysqldump -u root -p --all-databases > full_backup.sql

3.备份二进制日志

mysqlbinlog --start-position=日志文件的起始位置 mysql-bin.000001 > incremental_backup.sql

因为mysqldump本身是不支持增量备份，所以用mysqldump实现增量备份，是有点麻烦的，这个大家可以不用管，后面有比较好用的MySQL增量备份工具，这个后面再说。

总结

本篇文章总结了MySQL备份的几种类别，总的来说，其实就分为两大类，两大类下面又分为两小类。两大类分别是，物理备份和逻辑备份。两大类下的两小类分别为，全量备份和增量备份。

详细介绍了MySQL备份工具之一，mysqldump。使用方式也比较简单，大家可以根据不同的命令执行一下试试。

mysqldump的实现原理也很简单，简单来说就是，先锁表，然后执行SELECT * FROM TABLE_NAME来实现备份。具体可以根据general_log进行分析。