RDS for MySQL Mysqldump 常见问题和处理
1. GTID 特性相关
MySQL 5.6 引入了 GTID 特性，因此随 5.6 版本分发的 mysqldump 工具增加了 --set-gtid-purged 选项。
| # | 选项名称 | 默认值 | 可选值 | 作用 |
|
1 |
set-gtid-purged |
AUTO |
ON, OFF, AUTO |
是否输出 SET @@GLOBAL.GTID_PURGED 子句 |
- ON：在 mysqldump 输出中包含 SET @@GLOBAL.GTID_PURGED 语句。
- OFF：在 mysqldump 输出中不包含 SET @@GLOBAL.GTID_PURGED 语句。
- AUTO：默认值；对于启用 GTID 实例，会输出 SET @@GLOBAL.GTID_PURGED 语句；对于没有启动或者不支持 GTID 的实例，不输出任何 GTID 相关信息。
因此对于使用 MySQL 5.6 及以上版本带有的 mysqldump 工具进行 RDS for MySQL 实例数据导出时设置该选项为 OFF。
注：
如果 mysqldump 设置 set-gtid-purged=ON 从 RDS for MySQL 5.5 或 5.1 版本实例导出数据，mysqldump 会提示下面的错误：
Error: Server has GTIDs disabled.
或者
mysqldump: Couldn’t execute ‘SELECT @@GTID_MODE’: Unknown system variable ‘GTID_MODE’ <1193>
2. 避免表级锁等待
mysqldump 默认会启用 lock-tables 选项，对要导出的表加表级锁，阻止表上的 DML 操作。
RDS for MySQL 实例默认支持的 InnoDB 和 TokuDB 引擎均支持事务，建议使用 single-transaction 选项进行导出，而不要设置 lock-all-tables 或 lock-tables 选项。
| # | 选项名称 | 默认值 | 可选值 | 作用 |
| 1 | lock-all-tables | FALSE | FALSE,TRUE | 在数据导出期间放置 global read lock，所有库下的所有表在导出期间为只读。自动关闭 lock-tables 和 single-transaction 选项。RDS 不支持该选项。 |
| 2 | lock-tables | TRUE | FALSE,TRUE | 导出期间在导出表上放置表级锁。默认开启。可以通过指定 --skip-lock-tables 选项来关闭。 |
| 3 | single-transaction | FALSE | FALSE,TRUE | 导出操作被放置在一个事务中执行。自动关闭 lock-tables 选项。 |
关于表级锁的情况，请参考：RDS for MySQL InnoDB表级锁等待
3. 设置导出字符集
如果不指定，mysqldump 默认使用 UTF8 字符集进行导出。
| # | 选项名称 | 默认值 | 可选值 | 作用 |
| 1 | default-character-set | UTF8 | 实例支持的字符集 | mysqldump 到 RDS 实例导出连接的字符集 |
4. 其他导出时需要注意的选项
| # | 选项名称 | 默认值 | 可选值 | 作用 |
| 1 | no-defaults | NA | NA | 除了.mylogin.cnf，不读取任何选项文件 |
| 2 | defaults-file=file_name | NA | NA | 读取指定的选项文件 |
| 3 | add-drop-database | FALSE | FALSE,TRUE | 在 create database 语句前增加 drop database 语句 |
| 4 | add-drop-table | TRUE | FALSE,TRUE | 在 create table 语句前增加 drop table 语句，默认开启，使用选项 --skip-add-drop-table 来关闭。 |
| 5 | add-locks | TRUE | FALSE,TRUE | 在表相关语句前后增加 lock tables tab_name write; 和 unlock tables; 语句。这样在导入数据时可以加快数据导入。 |
| 6 | compatible=name | NA |
ansi,postgresql, oracle,mssql |
增强与指定的数据库类型的兼容性 |
| 7 | compact | FALSE | FALSE,TRUE | 启用 --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, --skip-set-charset 选项 |
| 8 | databases | TRUE | FALSE,TRUE | 导出多个库。默认 mysqldump 将第一个名字识别为库，其后的名字识别为表。指定该选项后，mysqldump会将所有名称识别为库，并在每个库前增加 create database 和 use database 语句。 |
| 9 | disable-keys | TRUE | FALSE,TRUE | 在插入数据前后增加 /!40000 ALTER TABLE tab_name DISABLE KEYS / 和 /!40000 ALTER TABLE tab_name ENABLE KEYS / 语句来加速插入。该选项仅对 MyISAM 引擎表的非唯一索引有效。 |
| 10 | events | FALSE | FALSE,TRUE | 导出数据库内的计划事件（定时任务） |
| 11 | extended-insert | TRUE | FALSE,TRUE | 使用扩展的 Insert 语句，一条 Insert 语句插入多行。 |
| 12 | hex-blob | FALSE | FALSE,TRUE |
以16进制导出 Binary、VarBinary、BLOB 类型数据。 如果跨版本迁移数据，建议增加该选项。 |
| 13 | ignore-table=db.tab | TRUE | FALSE,TRUE | 不导出某表或视图。格式：库名.表名（db.tab)。可以多次使用该选项来忽略多张表。 |
| 14 | max-allowed-packet | 24 MB | 24 MB - 1 GB | mysqldump 和 RDS 实例通信缓存最大值。默认24 MB。最大 1 GB。 |
| 15 | no-create-db | FALSE | FALSE,TRUE | 输出中不包含 create database 语句 |
| 16 | no-create-info | FALSE | FALSE,TRUE | 输出中不包含 create table 语句 |
| 17 | no-data | FALSE | FALSE,TRUE | 不导出数据 |
| 18 | opt | TRUE | FALSE,TRUE | 启用 --add-drop-table, --add-locks, --create-options --disable-keys, --extended-insert, --lock-tables, --quick, --set-charset; 可以通过指定 skip-opt 选项关闭默认 opt 选项。 |
| 19 | dump-date | TRUE | FALSE,TRUE | 如果指定了 --comments 选项（默认开启），在输出的注释中显示导出日期时间。 |
| 20 | routines | FALSE | FALSE,TRUE | 导出存储过程和函数（默认不导出） |
| 21 | result-file | TRUE | FALSE,TRUE | 将输出重定向到文件 |
| 22 | set-charset | TRUE | FALSE,TRUE | 在导出文件中加上 set names default_chararacter_set |
| 23 | triggers | TRUE | FALSE,TRUE | 导出表上的 Trigger |
5. 举例
5.1 导出库 jacky 下的 teacher 表，包括表上的触发器，导出字符集是 utf8mb4
mysqldump --no-defaults -hxxx.mysql.aliyun.com -uuser_name -P3306 -ppass_word --set-gtid-purged=off --default-character-set=utf8mb4 --hex-blob --single-transaction --result-file=jacky_teacher.sql jacky teacher
# -p 选项指定密码，密码和选项间不要有空格
# -P 选项指定实例的端口
# -h 选项指定 RDS 实例的 URL 地址
# -u 选项指定使用的数据库用户
# 也可以使用下面的方式进行导出
mysqldump --no-defaults -hxxx.mysql.aliyun.com -uuser_name -P3306 -ppass_word --set-gtid-purged=off --default-character-set=utf8mb4 --hex-blob --single-transaction jacky teacher > jacky_teacher.sql
5.2 导出库 jacky，包括存储过程和函数，不含 lock tables 和 unlock tables 语句
mysqldump --no-defaults -hxxx.mysql.rds.aliyuncs.com -uuser_name -ppass_word -P3306 --set-gtid-purged=off --hex-blob --single-transaction --routines --skip-add-locks --result-file=jacky.sql jacky
# --routines — 导出库涉及的存储过程和函数
# --skip-add-locks — 输出中不包括 lock tables table_name write; 和 unlock tables; 语句
5.3 导出库 jacky，包括存储过程、函数、触发器、事件，不包括数据
mysqldump --no-defaults -hxxx.mysql.rds.aliyuncs.com -uuser_name -ppass_word -P3306 --set-gtid-purged=off --hex-blob --single-transaction --routines --events --no-data --result-file=jacky.sql jacky
# 触发器选项 --triggers 默认开启，因此不需要指定
# --events — 导出库涉及的定时事件（计划任务）
# --no-data — 不导出数据
5.4 导出库 jacky，不包括 库、表创建语句，不包括 drop table 语句
mysqldump --no-defaults -hxxx.mysql.rds.aliyuncs.com -uuser_name -ppass_word -P3306 --set-gtid-purged=off --hex-blob --single-transaction --no-create-db --no-create-info --skip-add-drop-table --result-file=jacky.sql jacky
# --no-create-db — 输出中不包括库的创建语句
# --no-create-info — 输出中不包括表的创建语句
# --skip-add-drop-table — 输出中不包括表的删除语句
5.5 导出库 jacky，jerry，jason，不包括表 jacky.teacher, jason.orders, jerry.sales
mysqldump --no-defaults -hxxx.mysql.aliyun.com -uuser_name -P3306 -ppass_word --set-gtid-purged=off --hex-blob --single-transaction --result-file=jacky_jerry_jason.sql --ignore-table=jacky.teacher --ignore-table=jason.orders --ignore-table=jerry.sales --databases jacky jerry jason
# --ignore-table — 指定不进行导出的表
# --databases — 指定要进行导出的数据库名称
5.6 导出库 jacky，包括存储过程和函数，尽量兼容 SQL SERVER 语法
mysqldump --no-defaults -hxxx.mysql.aliyun.com -uuser_name -P3306 -ppass_word --set-gtid-purged=off --compatible=mssql --routines --hex-blob --single-transaction --result-file=jacky_mssql.sql jacky
# --compatible=mssql — 增加对 SQL SERVER 的语法兼容性
6. RDS for MySQL 不支持的选项
| # | 选项名称 | 默认值 | 可选值 | 作用 |
| 1 | all-databases | FALSE | FALSE, TRUE | 导出所有数据库，包括 mysql |
| 2 | flush-logs | FALSE | FALSE, TRUE | 导出前在实例中执行 flush logs; 命令 |
| 3 | flush-privileges | FALSE | FALSE, TRUE | 导出 mysql 系统库后，输出中包含 flush privileges; 命令 |
| 4 | lock-all-tables | FALSE | FALSE, TRUE | 在数据导出期间放置 global read lock，所有库下的所有表在导出期间为只读。自动关闭 lock-tables 和 single-transaction 选项。 |
| 5 | tab=dir_name | NA | NA | 在指定的目录下生成 tbl_name.sql 文件（包含表创建语句）和 以 tab 作为分隔符的tbl_name.txt文本格式的数据文件。 |
- --all-databases： RDS for MySQL 普通用户对 mysql 库中部分表没有权限，因此不能导出全部库表。
# 错误信息：
mysqldump: Couldn’t execute ‘show create table slow_log‘: SHOW command denied to user ‘xxx’@’xx.xx.xx.xx’ for table ‘slow_log’ (1142)
- --flush-logs： RDS for MySQL 普通用户没有 Reload 权限，因此不能执行 flush logs; 命令。
# 错误信息
mysqldump: Couldn’t execute ‘FLUSH TABLES’: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)
- --flush-privileges：因为 RDS for MySQL 不支持 mysql 系统库的导出，因此没必要使用该选项。
- --lock-all-tables：因为 RDS for MySQL 普通用户没有 Reload 权限，因此不能使用该选项。
# 错误信息
mysqldump: Couldn’t execute ‘FLUSH TABLES’: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)
- --tab=dir_name：该选项要求 mysqldump 和 RDS for MySQL 实例在同一物理机上，因此不支持。但该选项可以和 --no-data 选项搭配使用来获取表的创建语句。
# 和 --no-data 选项搭配，获取 jacky 库下每个表的创建语句文件 tab_name.sql
mysqldump --no-defaults -uuser_name -ppass_word -hxxx.mysql.rds.aliyuncs.com -P3306 --set-gtid-purged=off --single-transaction --tab=/tmp --no-data jacky
# 不带 --no-data 选项（希望导出数据）时候的错误信息：
mysqldump --no-defaults -uuser_name -ppass_word -hxxx.mysql.rds.aliyuncs.com -P3306 --set-gtid-purged=off --single-transaction --tab=/tmp jacky
mysqldump: Got error: 1045: Access denied for user ‘xxx’@’%’ (using password: YES) when executing ‘SELECT INTO OUTFILE’
7. RDS for MySQL 逻辑备份
- RDS for MySQL 支持实例和单库级别的逻辑备份。
- 逻辑备份执行期间不会影响主实例的正常使用。
- 逻辑备份导入权限问题请参考：RDS for MySQL权限问题（错误代码：1227，1725）