主页 > Div+Css > RDS for MySQL Mysqldump一般的常见问题以及处理办法
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 会提示下面的错误:


  1. Error: Server has GTIDs disabled. 或者 mysqldump: Couldnt 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 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


  1. 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 语句


  1. 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,包括存储过程、函数、触发器、事件,不包括数据


  1. 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 语句


  1. 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


  1. 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 语法


  1. 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 库中部分表没有权限,因此不能导出全部库表。

  1. # 错误信息: mysqldump: Couldnt 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; 命令。

  1. # 错误信息 mysqldump: Couldnt 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 权限,因此不能使用该选项。

  1. # 错误信息 mysqldump: Couldnt 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 选项搭配使用来获取表的创建语句。

  1. # 和 —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 逻辑备份

返回顶部