mysqldump options for ApsaraDB RDS for MySQL-ApsaraDB RDS(RDS)-阿里云帮助中心

GTID feature

MySQL 5.6 introduced the Global Transaction Identifier (GTID) feature. As a result, the mysqldump utility in MySQL 5.6 and later includes the set-gtid-purged option.

Parameter	Default	Value	Description
set-gtid-purged	AUTO	ON\|OFF\|AUTO	Controls whether to include the `SET @@GLOBAL.GTID_PURGED` statement in the output.

Note

ON: Includes the SET @@GLOBAL.GTID_PURGED statement in the mysqldump output.
OFF: Does not include the SET @@GLOBAL.GTID_PURGED statement in the mysqldump output.
AUTO: The default. Includes the SET @@GLOBAL.GTID_PURGED statement if GTID is enabled on the source instance. Otherwise, no GTID information is included.

Therefore, when using mysqldump from MySQL 5.6 or later to export data from an ApsaraDB RDS for MySQL 5.5 instance, you must set set-gtid-purged to OFF. Otherwise, one of the following errors occurs:

Error: Server has GTIDs disabled.
or
mysqldump: Couldn’t execute ‘SELECT @@GTID_MODE’: Unknown system variable ‘GTID_MODE’ <1193>

Avoiding table-level lock waits

By default, mysqldump enables the lock-tables option. This option applies a table-level lock to the exported tables and blocks Data Manipulation Language (DML) operations on them.

The InnoDB and TokuDB storage engines, which are supported by default on ApsaraDB RDS for MySQL instances, both support transactions. We recommend that you use the single-transaction option to export the data instead of using the lock-all-tables or lock-tables options.

Parameter	Default	Value	Description
lock-all-tables	FALSE	TRUE\|FALSE	Acquires a global read lock for the duration of the export. This makes all tables in all databases read-only. This option automatically disables the `lock-tables` and `single-transaction` options. This option is not supported by ApsaraDB RDS for MySQL.
lock-tables	TRUE	TRUE\|FALSE	Locks the exported tables during the export. This option is enabled by default. To disable it, use the `skip-lock-tables` option.
single-transaction	FALSE	TRUE\|FALSE	Performs the export within a single transaction. This option automatically disables the `lock-tables` option.

Setting the export character set

If you do not specify a character set, mysqldump uses UTF8 by default.

Parameter	Default	Value	Description
default-character-set	UTF8	Character sets supported by the instance	Sets the character set for the export.

Other export options

Parameter	Default	Value	Description
no-defaults	NA	NA	Does not read any option files, except for `.mylogin.cnf`.
defaults-file=file_name	NA	NA	Reads only the specified option file.
add-drop-database	FALSE	TRUE\|FALSE	Adds a `DROP DATABASE` statement before each `CREATE DATABASE` statement.
add-drop-table	TRUE	TRUE\|FALSE	Adds a `DROP TABLE` statement before each `CREATE TABLE` statement. This option is enabled by default. To disable it, use the `skip-add-drop-table` option.
add-locks	TRUE	TRUE\|FALSE	Surrounds each table dump with `LOCK TABLES` and `UNLOCK TABLES` statements. This can speed up data import when reloading the dump file.
compatible=name	NA	ansi\|postgresql\|oracle\|mssql	Produces output that is more compatible with the specified database type.
compact	FALSE	TRUE\|FALSE	Enables the `skip-add-drop-table`, `skip-add-locks`, `skip-comments`, `skip-disable-keys`, and `skip-set-charset` options.
databases	FALSE	TRUE\|FALSE	Exports multiple databases. By default, `mysqldump` treats the first name argument as a database name and subsequent names as table names. With this option, `mysqldump` treats all name arguments as database names and adds `CREATE DATABASE` and `USE` statements before each database dump.
disable-keys	TRUE	TRUE\|FALSE	Surrounds `INSERT` statements with`/!40000 ALTER TABLE tab_name DISABLE KEYS /` and`/!40000 ALTER TABLE tab_name ENABLE KEYS /` statements. This can speed up data insertion. This option is effective only for non-unique indexes of MyISAM tables.
events	FALSE	TRUE\|FALSE	Exports scheduled events from the dumped databases.
extended-insert	TRUE	TRUE\|FALSE	Uses extended `INSERT` syntax to write multiple rows in a single statement.
hex-blob	FALSE	TRUE\|FALSE	Exports `BINARY`, `VARBINARY`, and `BLOB` columns using hexadecimal notation. We recommend that you use this option when migrating data between different versions.
ignore-table=db.tab	NA	NA	Excludes the specified table or view. The format is `db_name.table_name`. You can use this option multiple times to ignore multiple tables.
max-allowed-packet	24 MB	24 MB to 1 GB	Sets the maximum buffer size for client-server communication. The default value is 24 MB. The maximum value is 1 GB.
no-create-db	FALSE	TRUE\|FALSE	Suppresses the `CREATE DATABASE` statements in the output.
no-create-info	FALSE	TRUE\|FALSE	Suppresses the `CREATE TABLE` statements in the output.
no-data	FALSE	TRUE\|FALSE	Does not write any row data.
opt	TRUE	TRUE\|FALSE	Enables the `add-drop-table`, `add-locks`, `create-options`, `disable-keys`, `extended-insert`, `lock-tables`, `quick`, and `set-charset` options. This option is enabled by default. To disable it, use `skip-opt`.
dump-date	TRUE	TRUE\|FALSE	Adds a dump timestamp as a comment at the end of the output. This requires the `comments` option. Both this option and `comments` are enabled by default.
routines	FALSE	TRUE\|FALSE	Exports stored procedures and functions. This option is disabled by default.
result-file	NA	NA	Redirects the output to a specified file.
set-charset	TRUE	TRUE\|FALSE	Adds `SET NAMES default_character_set` to the output.
triggers	TRUE	TRUE\|FALSE	Exports the triggers for each dumped table.

Unsupported options

Parameter	Default	Value	Description
all-databases	FALSE	TRUE\|FALSE	Exports all databases, including the `mysql` database.
flush-logs	FALSE	TRUE\|FALSE	Executes a`flush logs;` command before starting the export.
flush-privileges	FALSE	TRUE\|FALSE	Adds a`flush privileges;` command to the output after the `mysql` database is exported.
lock-all-tables	FALSE	TRUE\|FALSE	Acquires a global read lock for the duration of the export, which makes all tables in all databases read-only. This option automatically disables the `lock-tables` and `single-transaction` options.
tab=dir_name	NA	NA	Generates a `tbl_name.sql` file (containing the `CREATE TABLE` statement) and a tab-separated `tbl_name.txt` data file in the specified directory.

Unsupported options and reasons

all-databases: This option is not supported. Standard user accounts in ApsaraDB RDS for MySQL do not have permissions for some tables in the mysql database. Using this option causes an error.
Error message
```
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: This option is not supported. Standard user accounts in ApsaraDB RDS for MySQL do not have the RELOAD privilege and therefore cannot execute theflush logs; command.
Error message
```
mysqldump: Couldn’t execute ‘FLUSH TABLES’: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)
```
flush-privileges: This option is unnecessary because ApsaraDB RDS for MySQL does not support exporting the mysql database.

lock-all-tables: This option is not supported. Standard user accounts in ApsaraDB RDS for MySQL do not have the RELOAD privilege.

Error message

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: This option is not supported because it requires mysqldump to run on the same physical machine that hosts the ApsaraDB RDS for MySQL instance. However, you can use this option with the no-data option to generate files containing CREATE TABLE statements.

# Use this option with the no-data option to obtain the tab_name.sql file, which contains the CREATE TABLE statement for each table in the test database.
mysqldump  —no-defaults -uuser_name -ppass_word -hxxx.mysql.rds.aliyuncs.com -P3306 —set-gtid-purged=off —single-transaction —tab=/tmp —no-data test
# Error message returned when you export data without using the --no-data option:
mysqldump  —no-defaults -uuser_name -ppass_word -hxxx.mysql.rds.aliyuncs.com -P3306 —set-gtid-purged=off —single-transaction —tab=/tmp test
mysqldump: Got error: 1045: Access denied for user ‘xxx’@’%’ (using password: YES) when executing ‘SELECT INTO OUTFILE’