RDS MySQL mysqldump options - ApsaraDB RDS - Alibaba Cloud Documentation Center

ApsaraDB RDS for MySQL imposes permission restrictions on regular users, which means some standard mysqldump options behave differently or are unavailable. This page covers the options you need to configure correctly, the options that work as expected, and the options that are blocked — along with the error you'll see if you try them.

GTID options

Global Transaction Identifier (GTID) support was introduced in MySQL 5.6, along with the --set-gtid-purged option in the mysqldump utility.

Option	Default	Values	Description
`--set-gtid-purged`	`AUTO`	`ON` \| `OFF` \| `AUTO`	Controls whether mysqldump includes a `SET @@GLOBAL.GTID_PURGED` statement in the dump output.

Values:

ON — Always includes the SET @@GLOBAL.GTID_PURGED statement.
OFF — Never includes the statement.
AUTO (default) — Includes the statement for GTID-enabled instances; generates no GTID information for instances that are not started or do not support GTID.

If you are exporting to an RDS MySQL 5.5 instance, always set --set-gtid-purged=OFF. MySQL 5.5 does not support GTID, so using a MySQL 5.6 or later mysqldump client without this flag causes the following errors:

Error: Server has GTIDs disabled.

mysqldump: Couldn't execute 'SELECT @@GTID_MODE': Unknown system variable 'GTID_MODE' (1193)

Locking options

By default, mysqldump enables --lock-tables, which locks all tables before dumping them and blocks DML operations during the dump. InnoDB and TokuDB — the engines supported by ApsaraDB RDS for MySQL — support transactions, so a transaction-based dump is both safer and faster.

Use --single-transaction to dump InnoDB or TokuDB tables without acquiring table locks.

Option	Default	Values	Description
`--lock-all-tables`	`FALSE`	`TRUE` \| `FALSE`	Acquires a global read lock for the entire dump, making all tables in all databases read-only. Automatically disables `--lock-tables` and `--single-transaction`. Not supported on ApsaraDB RDS for MySQL — see Unsupported options.
`--lock-tables`	`TRUE`	`TRUE` \| `FALSE`	Locks tables before dumping them. Use `--skip-lock-tables` to disable. When using `--single-transaction`, this option is automatically disabled.
`--single-transaction`	`FALSE`	`TRUE` \| `FALSE`	Starts a transaction before dumping data, avoiding table locks. Automatically disables `--lock-tables`. Recommended for InnoDB and TokuDB tables.

Recommended command

The following command uses the recommended options for exporting data from an ApsaraDB RDS for MySQL instance:

mysqldump --no-defaults \
  -u <username> -p<password> \
  -h <instance_endpoint> -P 3306 \
  --set-gtid-purged=OFF \
  --single-transaction \
  --hex-blob \
  --databases <database_name>

--no-defaults — Reads only .mylogin.cnf, preventing option files from overriding your settings.
--set-gtid-purged=OFF — Required when the target instance does not support GTID (for example, RDS MySQL 5.5).
--single-transaction — Dumps InnoDB and TokuDB tables consistently without table locks.
--hex-blob — Exports binary columns in hexadecimal to avoid encoding issues when migrating between MySQL versions.

Character set

If no character set is specified, mysqldump defaults to UTF-8.

Option	Default	Values	Description
`--default-character-set`	`utf8`	Any character set supported by ApsaraDB RDS for MySQL	Sets the character set for the dump output.

Additional options

Option	Default	Values	Description
`--no-defaults`	—	—	Reads only the `.mylogin.cnf` option file, ignoring all other option files.
`--defaults-file=file_name`	—	—	Reads options from the specified file only.
`--add-drop-database`	`FALSE`	`TRUE` \| `FALSE`	Adds a `DROP DATABASE` statement before each `CREATE DATABASE` statement in the dump.
`--add-drop-table`	`TRUE`	`TRUE` \| `FALSE`	Adds a `DROP TABLE` statement before each `CREATE TABLE` statement. Use `--skip-add-drop-table` to disable.
`--add-locks`	`TRUE`	`TRUE` \| `FALSE`	Wraps each table dump with `LOCK TABLES ... WRITE` and `UNLOCK TABLES`, which speeds up inserts when the dump is reloaded.
`--compatible=name`	—	`ansi` \| `postgresql` \| `oracle` \| `mssql`	Produces output compatible with the specified database type.
`--compact`	`FALSE`	`TRUE` \| `FALSE`	Enables `--skip-add-drop-table`, `--skip-add-locks`, `--skip-comments`, `--skip-disable-keys`, and `--skip-set-charset`.
`--databases`	`TRUE`	`TRUE` \| `FALSE`	Treats all name arguments as database names and includes `CREATE DATABASE` and `USE` statements in the output. Without this option, the first name argument is treated as a database and subsequent names as tables.
`--disable-keys`	`TRUE`	`TRUE` \| `FALSE`	Wraps `INSERT` statements with `ALTER TABLE ... DISABLE KEYS` and `ALTER TABLE ... ENABLE KEYS` to speed up loading. Effective only for non-unique indexes on MyISAM tables.
`--events`	`FALSE`	`TRUE` \| `FALSE`	Includes scheduled events in the dump.
`--extended-insert`	`TRUE`	`TRUE` \| `FALSE`	Combines multiple single-row `INSERT` statements into a single multi-row `INSERT`.
`--hex-blob`	`FALSE`	`TRUE` \| `FALSE`	Exports `BINARY`, `VARBINARY`, and `BLOB` columns in hexadecimal notation. Add this option when migrating data between different MySQL versions to avoid encoding issues.
`--ignore-table=db.tab`	`TRUE`	`TRUE` \| `FALSE`	Excludes the specified table or view from the dump. Format: `database_name.table_name`. Repeat the option to exclude multiple tables.
`--max-allowed-packet`	`24MB`	`24MB`–`1GB`	Maximum buffer size for communication between mysqldump and the ApsaraDB RDS for MySQL instance.
`--no-create-db`	`FALSE`	`TRUE` \| `FALSE`	Omits `CREATE DATABASE` statements from the dump.
`--no-create-info`	`FALSE`	`TRUE` \| `FALSE`	Omits `CREATE TABLE` statements from the dump.
`--no-data`	`FALSE`	`TRUE` \| `FALSE`	Dumps the schema only, without table data.
`--opt`	`TRUE`	`TRUE` \| `FALSE`	Shorthand that enables `--add-drop-table`, `--add-locks`, `--create-options`, `--disable-keys`, `--extended-insert`, `--lock-tables`, `--quick`, and `--set-charset`. Use `--skip-opt` to disable.
`--dump-date`	`TRUE`	`TRUE` \| `FALSE`	Appends a dump timestamp to the output when `--comments` is enabled (which is the default).
`--routines`	`FALSE`	`TRUE` \| `FALSE`	Includes stored procedures and functions in the dump.
`--result-file`	`TRUE`	`TRUE` \| `FALSE`	Redirects output to the specified file.
`--set-charset`	`TRUE`	`TRUE` \| `FALSE`	Adds `SET NAMES default_character_set` to the dump output.
`--triggers`	`TRUE`	`TRUE` \| `FALSE`	Includes triggers for each dumped table.

Unsupported options

The following options are not available to ApsaraDB RDS for MySQL regular users due to permission restrictions.

Option	Reason
`--all-databases`	Regular users do not have permissions on certain tables in the `mysql` database (such as `slow_log`), so a full database dump fails. To export all user databases, use `--databases` and list each database explicitly.
`--flush-logs`	Requires the `RELOAD` privilege, which is not granted to regular users.
`--flush-privileges`	Not applicable — ApsaraDB RDS for MySQL does not allow exporting the `mysql` database.
`--lock-all-tables`	Requires the `RELOAD` privilege, which is not granted to regular users.
`--tab=dir_name`	Requires mysqldump and the RDS instance to share the same physical machine, which is not the case in a managed environment.

Error messages

If you use --all-databases or --lock-all-tables:

mysqldump: Couldn't execute 'show create table slow_log': SHOW command denied to user 'xxx'@'xx.xx.xx.xx' for table 'slow_log' (1142)

If you use --flush-logs or --lock-all-tables:

mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

Using `--tab` with `--no-data`

Although --tab=dir_name cannot be used for full data dumps, combining it with --no-data lets you export the CREATE TABLE statements for each table into individual .sql files:

# Export CREATE TABLE statements only (no data)
mysqldump --no-defaults \
  -u <username> -p<password> \
  -h <instance_endpoint> -P 3306 \
  --set-gtid-purged=OFF \
  --single-transaction \
  --tab=/tmp \
  --no-data \
  <database_name>

Using --tab without --no-data returns the following error:

mysqldump: Got error: 1045: Access denied for user 'xxx'@'%' (using password: YES) when executing 'SELECT INTO OUTFILE'