RDS for MySQL Mysqldump 常见问题和处理

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: RDS for MySQL Mysqldump 常见问题和处理   GTID 特性相关 避免表级锁等待 设置导出字符集 其他导出时需要注意的选项 举例 RDS for MySQL 不支持的选项 RDS for MySQL 逻辑备份 1. GTID 特性相关 MySQL 5.6 引入了 GTID 特性,因此随 5.6 版本分发的 mysqldump 工具增加了 --set-gtid-purged 选项。

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 userxxx@xx.xx.xx.xx for tableslow_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 userxxx@% (using password: YES) when executingSELECT INTO OUTFILE

 

7. RDS for MySQL 逻辑备份

  • RDS for MySQL 支持实例和单库级别的逻辑备份。
  • 逻辑备份执行期间不会影响主实例的正常使用。
  • 逻辑备份导入权限问题请参考:RDS for MySQL权限问题(错误代码:1227,1725)
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
36 2
|
21天前
|
关系型数据库 MySQL Java
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
54 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
|
2月前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
313 15
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
|
22天前
|
Java 关系型数据库 MySQL
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
这篇文章是关于如何使用Spring Boot框架通过JdbcTemplate操作MySQL数据库的教程。
17 0
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
|
28天前
|
数据可视化 关系型数据库 MySQL
【IDEA】配置mysql环境并创建mysql数据库
【IDEA】配置mysql环境并创建mysql数据库
66 0
|
4月前
|
关系型数据库 MySQL Serverless
函数计算产品使用问题之调用RDS MySQL的步骤是怎样的
函数计算产品作为一种事件驱动的全托管计算服务,让用户能够专注于业务逻辑的编写,而无需关心底层服务器的管理与运维。你可以有效地利用函数计算产品来支撑各类应用场景,从简单的数据处理到复杂的业务逻辑,实现快速、高效、低成本的云上部署与运维。以下是一些关于使用函数计算产品的合集和要点,帮助你更好地理解和应用这一服务。
|
3月前
|
关系型数据库 MySQL Shell
MySQL 备份:从mysqldump全备中 匹配出某几个表
MySQL 备份:从mysqldump全备中 匹配出某几个表
|
3月前
|
SQL 关系型数据库 MySQL
MySql⭐一、配置MySql数据库,并创建一个表单
MySql⭐一、配置MySql数据库,并创建一个表单
|
3月前
|
SQL 存储 关系型数据库
运维笔记.MySQL.基于mysqldump数据备份与恢复
运维笔记.MySQL.基于mysqldump数据备份与恢复
48 0
|
4月前
|
开发框架 前端开发 关系型数据库
ABP框架使用Mysql数据库,以及基于SQLServer创建Mysql数据库的架构和数据
ABP框架使用Mysql数据库,以及基于SQLServer创建Mysql数据库的架构和数据