如何用 mysqldump 备份数据
前言
mysqldump 是 MySQL 官方提供的一款逻辑备份工具，它将生成一组可以导入数据 库中以重现原始数据库中的数据和数据库对象的SQL语句。可用于备份恢复、表结构导出、备份上云。本 SOP 介绍原理和用法。
备份开始前一定要了解数据量大小，可以使用下方查询：
SELECT TABLE_SCHEMA,
round(SUM(data_length + index_length + DATA_FREE) / 1024 / 1024, 2) AS TOTAL_MB,
round(SUM(data_length) / 1024 / 1024, 2) AS DATA_MB,
round(SUM(index_length) / 1024 / 1024, 2) AS INDEX_MB,
round(SUM(DATA_FREE) / 1024 / 1024, 2) AS FREE_MB,
COUNT(*) AS TABLES
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'INFORMATION_SCHEMA', 'performance_schema')
GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC;常用模版
1. 备份整个实例
包含函数、触发器等对象。
mysqldump -uroot -p --port=3306 --single-transaction --master-data=2 --triggers --routines --events --all-databases > ./bakup_`date +"%F_%H_%M_%S"`.sql
2. 备份单个数据库
mysqldump -uroot -p --port=3306 --single-transaction --master-data=2 --triggers --routines --events --databases db_name > ./bakup_`date +"%F_%H_%M_%S"`.sql
3. 导出单表结构与数据
恢复表时，如果目标库有同表名，会被 drop 掉，如果想避免风险需添加 skip-add-drop-table。
mysqldump -uroot -p --port=3306 --set-gtid-purged=OFF --single-transaction --databases db_name --tables table_name > ./bakup_`date +"%F_%H_%M_%S"`.sql
4. 仅导出单表数据
仅有数据，没有表结构。
mysqldump -uroot -p --port=3306 --single-transaction --set-gtid-purged=OFF --no-create-info --databases db_name --tables table_name > ./bakup_`date +"%F_%H_%M_%S"`.sql
5. 仅导出单表部分数据
使用 --where 可以过滤数据，--add-locks=0 不需要添加锁表语句，恢复不影响目标库。
mysqldump -uroot -p --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db_name table_name --where="id>900" > ./bakup_`date +"%F_%H_%M_%S"`.sql
6. 某库下表结构导出
有时研发有将表结构迁移到另外一个 DB 下的需求，使用该命令可以完成。使用 -d 可以避免结构导出有 use db 语句，使用 skip-add-drop-table 避免目标端有同名表被删除。
mysqldump -uroot -p --port=3306 --set-gtid-purged=OFF --skip-add-drop-table=ON --no-data -d db_name > ./bakup_`date +"%F_%H_%M_%S"`.sql
备份上云
1. DEFINER 问题
上云的数据库有 触发器、函数、视图 这些对象，mysqldump 导出是会有 DEFINER 直接还原到 RDS 会报错下方错误：
ERROR 1227 (42000) : Access denied; you need (at least one of) the SUPER privilege(s) for this operation
DEFINER：对象定义者，在创建对象时可以手动指定用户，不指定的话默认为当前连接用户；
SQL SECURITY：指明以谁的权限来执行该对象，有两个选项，一个为 DEFINER，一个为 INVOKER，默认情况下系统指定为 DEFINER；
--视图定义 CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW v_test --函数定义 CREATE DEFINER=`root`@`%` FUNCTION `f_test()` RETURNS varchar(100) SQL SECURITY DEFINER --存储过程定义 CREATE DEFINER=`root`@`%` PROCEDURE `p_test`() SQL SECURITY DEFINER --触发器定义 CREATE DEFINER=`root`@`%` trigger t_test --事件定义 CREATE DEFINER=`root`@`%` EVENT `e_test`
如果导入账号具有 SUPER 权限，即使对象的所有者账号不存在，也可以导入成功，但是在查询对象时，如果对象的 SQL SECURITY 为 DEFINER，则会报账号不存在的报错。
ERROR 1449 (HY000): The user specified as a definer (‘root’@’%’) does not exist
然后 RDS 不提供 ROOT 账号和 SUPER 权限，我们就需要修改备份文件中的 DEFINER 规避问题，所以我们使用 mysqldump 备份上云需要进行两次导入，第一次只备份数据，第二次只备份数据库中的其它对象，然后修改 DEFINER 再重新导入。
2. 操作过程
准备 Python 脚本 drop_definer：
import sys
content = ''
for line in sys.stdin:
content += line.replace('DEFINER=`root`@`localhost`', '')
print(content)PS：如何删除 DEFINER 定义 这篇文章也介绍许多方法。
第一次只备份数据
mysqldump -h 127.0.0.1 -u root -p --opt --default-character-set=utf8 --hex-blob db_name --skip-triggers --skip-lock-tables > ./db_name.sql
第二次只备份触发器等
mysqldump -h 127.0.0.1 -u root -p --default-character-set=utf8 --skip-add-drop-table --skip-add-drop-database --hex-blob --set-gtid-purged=OFF --databases db_name --no-data --no-create-info --no-create-db -R | python drop_definer.py > db_name_triggers.sql
云上还原：
create database db_name CHARSET utf8; use db_name; source /path/backup.sql
3. 对象数验证
通过下方 SQL 可以查询实例中对象的数量，迁移前后都可使用 SQL 查询对比验证。
select db AS '数据库', type AS '对象类型', cnt AS '对象数量' from ( select 'TABLE' type, table_schema db, COUNT(*) cnt from information_schema.`TABLES` a where table_type = 'BASE TABLE' group by table_schema union all select 'EVENTS' type, event_schema db, count(*) cnt from information_schema.`EVENTS` b group by event_schema union all select 'TRIGGER' type, trigger_schema db, count(*) cnt from information_schema.`TRIGGERS` c group by trigger_schema union all select 'PROCEDURE' type, db, count(*) cnt from mysql.proc d where `type` = 'PROCEDURE' group by db union all select 'FUNCTION' type, db, count(*) cnt from mysql.proc e where `type` = 'FUNCTION' group by db union all select 'VIEW' type, TABLE_SCHEMA, count(*) cnt from information_schema.VIEWS f group by table_schema ) t where db not in ( 'sys', 'mysql', 'INFORMATION_SCHEMA', 'performance_schema' ) order by db, type;
参数详解
Option Name | Description | unscramble |
Add DROP DATABASE statement before each CREATE DATABASE statement | 【重要】默认 FALSE | |
Add DROP TABLE statement before each CREATE TABLE statement | 【重要】默认 会加上 DROP TABLE IF EXISTS 的，恢复目标端如果有同名表需要确认风险，可以加 | |
Add DROP TRIGGER statement before each CREATE TRIGGER statement | 创建触发器语句前添加 Drop 语句。 | |
Surround each table dump with LOCK TABLES and UNLOCK TABLES statements | 在生成的备份文件中，在进行每个表备份时，默认会在 INSERT 语句之 前添加 LOCK TABLES 语句，在 INSERT 语句之后添加 UNLOCK TABLES 语句。这样当重新加载备份文件时，有助于提高导入速度。 | |
Dump all tables in all databases | 备份所有的数据库。 | |
Allow creation of column names that are keywords | 允许创建具有 MySQL 关键字的列名。 | |
Use specified network interface to connect to MySQL Server | 在具有多个网络接口的计算机上，使用此选项选择连接 到 MySQL 服务器的接口地址(使用 --host 指定域名时可能解析出多个IP地址，所以可能需 要使用这个选项指定一个IP地址，但其实直接使用 --host指定IP地址即可，该选项不常用)。 | |
Directory where character sets are installed | 指定字符集的安装目录，一般默认值即可。 | |
Add comments to dump file | 默认会附加一些注射信息。 | |
Produce more compact output | 生成紧凑的备份文件。启用此选项会同时启用 --skip-add-drop-table、-- skip-add-locks、--skip-comments、--skip-disable-keys 和 --skip-set-charset 选项，即跳过 DROP TABLE、LOCK TABLE、备份开头和结尾的注释语句 (如程序版本号、服务器版本号 等)、关闭索引、SET NAME 等语句。 | |
Produce output that is more compatible with other database systems or with older MySQL servers | 生成与其他数据库系统或老版本 MySQL 服务器兼容的备份文件。name 的值可以是 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、 maxdb、no_key_options、no_table_options 或 no_field_options。要使用多个值，请用逗号分隔。这些值与服务器系统参数 sql_mode 值的对应选项具有相同的含义。 | |
Use complete INSERT statements that include column names | 使用包含列名称的完整的 INSERT 语句生成备份文件。 | |
Compress all information sent between client and server | 尽可能压缩客户端和服务器之间发送的所有信息。 | |
Include all MySQL-specific table options in CREATE TABLE statements | 如果设置为 FALSE 那么表引擎这些字符集这些都不会记录，按照目标实例默认值设置，需要确认风险。默认为 TRUE。 | |
Interpret all name arguments as database names | 【重要】用来指定备份单库或者某几个库。 | |
Authentication plugin to use | 关于要使用的客户端验证插件的提示选项。 | |
Specify default character set | 指定默认字符集。如果不指定，则默认使用 UTF-8。 | |
Read named option file in addition to usual option files | 在读取全局选项文件(默认的配置文件读取路径 是/etc/my.cnf、/etc/mysql/my.cnf、/usr/local/mysql/etc/my.cnf，~/.my.cnf是用户配置文件) 之后、读取用户配置文件之前(在UNIX系统上)，读取此选项指定的配置文件。如果该 文件不存在或者使用其他方式无法访问，则会发生错误。如果给定的是相对路径名而不是 完整路径名，则将在当前工作目录下读取该文件。 | |
Read only named option file | 仅读取该选项指定的配置文件。如果该文件不存在或者 使用其他方式无法访问，则会发生错误。如果给定的是相对路径名而不是完整路径名，则 将在当前工作目录下读取该文件。 | |
Option group suffix value | 关于读取配置文件中选项组的参数。 | |
On a replication source server, delete the binary logs after performing the dump operation | 看官方文档的意思是，备份完成后会清理 Binlog 目测不常用。 | |
For each table, surround INSERT statements with statements to disable and enable keys | 在 INSERT 语句之前先关闭非唯一索引，在 INSERT 之后再打开非唯一索引，可以加快数据导入速度。 | |
Include dump date as "Dump completed on" comment if --comments is given | 备份的最后一行会记录备份时间。 | |
Include CHANGE MASTER statement that lists binary log coordinates of replica's source | 如果备份的是从库，会自动生成 CHANGE MASTER TO 语句。 | |
Enable cleartext authentication plugin | 密码验证插件相关问题。 | |
Dump events from dumped databases | 备份存储 EVENT 事件。 | |
Use multiple-row INSERT syntax | TRUE（默认） INSERT INTO `Course` VALUES ('01','语文','02'),('02','数学','01'),('03','英语','03'); FALSE： INSERT INTO `Course` VALUES ('01','语文','02'); INSERT INTO `Course` VALUES ('02','数学','01'); INSERT INTO `Course` VALUES ('03','英语','03'); | |
Flush MySQL server log files before starting dump | 备份前 FLUS LOGS 刷新下 BINLOG 更优雅。 | |
Emit a FLUSH PRIVILEGES statement after dumping mysql database | 直接使用 DML 语句修改权限表，并不会生效，必须触发 MySQL 服务重新加载权限表，这个命令就是 | |
Continue even if an SQL error occurs during a table dump | 忽略所有错误，强制执行。 | |
Request RSA public key from server | RSA 密钥验证相关参数。 | |
Dump binary columns using hexadecimal notation | 使用十六进制符号备份二进制列 (例如，'abc' 变为 0x616263)，受影响的数据类型有 BINARY、VARBINARY、BLOB 和 BIT。 | |
Host on which MySQL server is located | MySQL 主机地址。 | |
Ignore specified errors | 忽略指定异常。 | |
Do not dump given table | 【过滤参数】指定不备份的表，格式： | |
Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave | 上面介绍使用 --dump-slave 选项进行从库备份时会生成 CHANGE MASTER TO 语句，而该选项会为 CHANGE MASTER TO 语句添加 MASTER_HOST 和 MASTER_PORT 选项，其值为与主库对应的 TCP/IP 端口号。 | |
Write INSERT IGNORE rather than INSERT statements | 在进行备份时，将 INSERT 语句替换为 INSERT IGNORE 语句。 | |
This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA | 该选项与 --tab 选项一起使用，与 LOAD DATA INFILE 的相应 LINES 子句的作用相同。 | |
Lock all tables across all databases | 锁定所有数据库中的所有表。这是通过在整个备份期间使用 FLUSH TABLES WITH READ LOCK 语句获取全局读锁实现的，而不是为每个表都添加一条 LOCK TABLES 语句。此选项在使用 --single-transaction 和 --lock-tables 时将自动关闭。 | |
Lock all tables before dumping them | 默认会锁表备份，innodb 引擎可以使用 | |
Append warnings and errors to named file | 追加警告和异常信息。 | |
Read login path options from .mylogin.cnf | 从文件中读取登陆路径。 | |
Write the binary log file name and position to the output | 【重要】会在备份文件中生成一条包含 binlog 位点和文件信息的 使用 --master-data 选项，将自动禁用 --lock-tables 选项，同时还会启用 --lock-all-tables 选项，除非指定了 --single-transaction 选项。在指定了 --single-transaction 选项之后，只有在备份过程中获取 binlog pos 时才会加全局读锁，一旦获取到 binlog pos 之后就立即释放全局读锁。 | |
Maximum packet length to send to or receive from server | 向服务器发送或从服务器接收的最大数据包长度，默认为 25MB 。 | |
Buffer size for TCP/IP and socket communication | 指定客户端和服务器通信时的数据缓冲区的初始大小。一般不修改。 | |
Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements | 默认 FALSE，在 INSERT 前加上 SET autocommit = 0 后面加上 COMMIT。不常用。 | |
Do not write CREATE DATABASE statements | 备份数据库时，不会添加创建数据库语句。 | |
Do not write CREATE TABLE statements that re-create each dumped table | 不会添加创建数据库的相关信息。 | |
Do not dump table contents | 【重要】不备份数据，仅备份表结构。一些需要迁移表结构的场景可以使用。 | |
Read no option files | 不使用默认的配置。 | |
Same as --skip-set-charset | 忽略。 | |
Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output | 此选项禁止 mysqldump 输出 CREATE LOGFILE GROUP 和 CREATE TABLESPACE 语句。 | |
Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset | 该选项是 --add-drop-table、--add-locks、--create-options、--disable-keys、-- extended-insert、--lock-tables、--quick、--set-charset 组合的缩写，提供快速的导出操作， 并生成一个可以快速重新导入MySQL服务器的备份文件。默认启用该选项，如果要禁用 --opt 选项，则可以使用 --skip-opt 关闭。 | |
Dump each table's rows sorted by its primary key, or by its first unique index | 如果存在主键索引，则先按照主键进行排序再写入备份文件中; 如果没有主键索引，则查找表中的第一个唯一索引，按照唯一索引进行排序再写入备份文件中。这可以加快重新导入备份文件的速度，但是备份操作可能需要更长的时间。 | |
Password to use when connecting to server | MySQL 密码 | |
Connect to server using named pipe (Windows only) | 在 Windows 系统上，使用命名管道连接到服务器。此选项仅适用于服 务器支持命名管道连接的场景。 | |
Directory where plugins are installed | 要查找的插件的目录。如果使用 --default-auth 选项指定了验证插件，但是 mysqldump 却找不到它，则可以使用此选项来指定。 | |
TCP/IP port number for connection | MySQL 端口。 | |
Print default options | 打印默认参数。 | |
Transport protocol to use | 指定用于连接到数据库实例的连接协议。其中，TCP 是所有平台都支持的，SOCKET 只支持 Linux 服务器，PIPE 和 MEMORY 只支持 Windows 服务器。 | |
Retrieve rows for a table from the server a row at a time | 此选项对于备份大表非常有用。它强制 mysqldump 从服务器一次查询一行数据，而不是一次查询整个表。因为 mysqldump 的每一次查询都需要先将数据保存在本地缓冲区中，所以启用这个选项之后一次查询一行数据就可以尽量不使用缓冲区。在内 存足够用的情况下(确保备份表的数据大小绝对不会超过物理内存大小)，可以关闭该选 项，以加快备份速度。但是如果内存不够用，则可能会使用到 Swap，从而导致备份速度慢，还影响机器性能。在正常情况下，建议总是使用该选项来进行备份。 | |
Write REPLACE statements rather than INSERT statements | INSERT INTO 替换为 REPLACE INTO。 | |
Direct output to a given file | 直接将数据输出到给定的文件中。如果该文件不存在，则会生成新文件; 如果该文件存在，则会覆盖其以前的数据内容。在 Windows 系统下使用该选项，可以防止换行符“\n”被转换为“\r\n”。 如果换行符被转换，后续重新加载这个文件时会发生错误。 | |
Dump stored routines (procedures and functions) from dumped databases | 在备份数据中输出包含存储过程和函数。默认为 FALSE。 | |
Add SET NAMES default_character_set to output | 默认会在备份前执行 SET NAMES 语句。 | |
Issue a BEGIN SQL statement before dumping data from server | 将事务隔离模式设置为 REPEATABLE READ，并在备份数据之前向服务器发送 SQL 语句 START TRANSACTION 以显式开启一个事务快照。由于是在 事务快照内进行备份的，所以使得备份的数据与获取事务快照时的数据是一致的，而且不会阻塞任何应用程序访问服务器。在进行单事务备份时，为确保备份文件有效(表内容和 二进制日志位置正确)，其他连接不能使用ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE等DDL语句，否则会导致一致性状态被破坏， 使得 mysqldump 执行 SELECT 语句检索表数据时查询不到正确的内容或者备份失败。要备份大表，建议结合使用 --single-transaction 和 --quick 选项，以加快备份速度。 | |
Whether to add SET @@GLOBAL.GTID_PURGED to output | 有三个选项： auto(默认)：如果备份服务器启用了 GTID，则在备份文件中添加 SET@@SESSION.SQL_LOG_BIN=0 和 SET @@GLOBAL.GTID_PURGED 语句，否则只添加 SET @@SESSION.SQL_LOG_BIN=0 语句。 OFF：在备份文件中不添加 SET@@SESSION.SQL_LOG_BIN=0 和SET @@GLOBAL.GTID_PURGED 语句。 ON：在备份文件中添加 SET@@SESSION.SQL_LOG_BIN=0 和 SET @@GLOBAL.GTID_PURGED 语句。如果在未启用 GTID 的服务器上使用该选项，则会发生错误。 | |
Override --databases or -B option | 与 --databases 或 -B 选项同时使用时，会覆盖数据库的选项，优先使用 --tables 选项。mysqldump 将该选项之后的所有名称参数视为表名，但必须还要指定一个库名(只能指定一个库名，不能指定多个库名)，才能对表级别范围进行操作，如 mysqldump --master-data -B db_name --tables tb_name1 tb_name2 > aa.sql 或者 mysqldump --master-data db_name --tables tb_name1 tb_name2 > aa.sql。 | |
Dump triggers for each dumped table | 备份数据库中的触发器。 | |
MySQL user name to use when connecting to server | 备份用户名。 | |
Dump only rows selected by given WHERE condition | 【重要】导出某一张表时，可以按照某个条件过滤导出。 | |
Produce XML output | 使用 XML 格式输出。 |
