Mysqldump options for MySQL compatibility and usage - ApsaraDB RDS

This page describes the mysqldump options relevant to ApsaraDB RDS for MySQL, including recommended settings, RDS-specific constraints, and unsupported options.

Recommended command

For most exports from ApsaraDB RDS for MySQL, use the following command as a starting point:

mysqldump \
  --no-defaults \
  -u <user_name> \
  -p<password> \
  -h <instance_endpoint> \
  -P 3306 \
  --set-gtid-purged=OFF \
  --single-transaction \
  --routines \
  --triggers \
  --hex-blob \
  --databases <database_name> > dump.sql

Replace the placeholders with your actual values:

Placeholder	Description	Example
`<user_name>`	Database account	`root`
`<password>`	Database account password	—
`<instance_endpoint>`	Public or internal endpoint of the RDS instance	`xxx.mysql.rds.aliyuncs.com`
`<database_name>`	Name of the database to export	`mydb`

--set-gtid-purged=OFF is required when exporting from an instance with GTID enabled to an import target that does not support GTID (such as RDS MySQL 5.5). See GTID for details.

GTID

Global Transaction Identifiers (GTID) were introduced in ApsaraDB RDS for MySQL 5.6. The mysqldump bundled with MySQL 5.6 and later adds the --set-gtid-purged option to control whether GTID information is included in the dump output.

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

Value behavior:

`ON`: Includes a SET @@GLOBAL.GTID_PURGED statement in the output.
`OFF`: Excludes the SET @@GLOBAL.GTID_PURGED statement from the output.
`AUTO` (default): Includes the statement if the source instance has GTID enabled. For instances where GTID is not started or not supported, no GTID information is generated.

Cross-version export: When using MySQL 5.6 or later mysqldump to export data into an RDS MySQL 5.5 instance, set --set-gtid-purged=OFF. Otherwise, the import fails with one of 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 for the duration of the dump.

InnoDB and TokuDB — the engines supported by ApsaraDB RDS for MySQL — support transactions. Use --single-transaction instead of --lock-tables or --lock-all-tables to avoid table-level locks.

Option	Default	Values	Description
`lock-all-tables`	`FALSE`	`TRUE` \| `FALSE`	Acquires a global read lock for the entire dump. All tables across all databases become read-only. Automatically disables `--lock-tables` and `--single-transaction`. Not supported on ApsaraDB RDS for MySQL.
`lock-tables`	`TRUE`	`TRUE` \| `FALSE`	Locks tables before dumping them. Enabled by default. Use `--skip-lock-tables` to disable.
`single-transaction`	`FALSE`	`TRUE` \| `FALSE`	Starts a transaction before dumping. Automatically disables `--lock-tables`. Recommended for InnoDB and TokuDB tables.

Character set

If no character set is specified, mysqldump uses UTF-8 by default.

Option	Default	Values	Description
`default-character-set`	`UTF8`	Any character set supported by ApsaraDB RDS for MySQL	Sets the character set for the export.

Additional options

Option	Default	Values	Description
`no-defaults`	—	—	Reads the `.mylogin.cnf` option file only.
`defaults-file=<file_name>`	—	—	Reads 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. Use `--skip-add-drop-table` to disable.
`add-locks`	`TRUE`	`TRUE` \| `FALSE`	Surrounds each table dump with `LOCK TABLES ... WRITE` and `UNLOCK TABLES` statements. Speeds up inserts when reloading the dump.
`compatible=<name>`	—	`ansi` \| `postgresql` \| `oracle` \| `mssql`	Generates 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. Includes `CREATE DATABASE` and `USE` statements before each database in the output.
`disable-keys`	`TRUE`	`TRUE` \| `FALSE`	Wraps `INSERT` statements with `ALTER TABLE ... DISABLE KEYS` and `ALTER TABLE ... ENABLE KEYS`. Effective only for non-unique indexes on MyISAM tables.
`events`	`FALSE`	`TRUE` \| `FALSE`	Dumps scheduled events from the exported databases.
`extended-insert`	`TRUE`	`TRUE` \| `FALSE`	Combines single-row `INSERT` statements into multi-row `INSERT` statements for faster imports.
`hex-blob`	`FALSE`	`TRUE` \| `FALSE`	Exports `BINARY`, `VARBINARY`, and `BLOB` columns in hexadecimal notation. Use this option when migrating between different database versions.
`ignore-table=<db.table>`	`TRUE`	`TRUE` \| `FALSE`	Excludes the specified table or view. Use the `database.table` format. Repeat the option to exclude multiple tables.
`max-allowed-packet`	`24MB`	`24MB`–`1GB`	Maximum size of the communication buffer between mysqldump and the RDS instance. Default is 24 MB; maximum is 1 GB.
`no-create-db`	`FALSE`	`TRUE` \| `FALSE`	Excludes `CREATE DATABASE` statements from the output.
`no-create-info`	`FALSE`	`TRUE` \| `FALSE`	Excludes `CREATE TABLE` statements from the output.
`no-data`	`FALSE`	`TRUE` \| `FALSE`	Exports the schema only, without row 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 the dump date to the output when `--comments` is enabled (which it is by default).
`routines`	`FALSE`	`TRUE` \| `FALSE`	Dumps stored procedures and functions. Not included by default.
`result-file`	`TRUE`	`TRUE` \| `FALSE`	Directs output to the specified file.
`set-charset`	`TRUE`	`TRUE` \| `FALSE`	Adds `SET NAMES <default_character_set>` to the output.
`triggers`	`TRUE`	`TRUE` \| `FALSE`	Includes triggers for each exported table.

Options not supported on ApsaraDB RDS for MySQL

The following options are not supported. Attempting to use them results in permission errors, because ApsaraDB RDS for MySQL accounts do not have the required privileges.

Option	Reason
`all-databases`	Requires access to all tables in the `mysql` database. RDS MySQL accounts do not have the necessary permissions.
`flush-logs`	Requires the `RELOAD` privilege, which RDS MySQL accounts do not have.
`flush-privileges`	Not applicable — ApsaraDB RDS for MySQL does not allow exporting the `mysql` database.
`lock-all-tables`	Requires the `RELOAD` privilege, which RDS MySQL accounts do not have.
`tab=<dir_name>`	Requires mysqldump and the RDS instance to be on the same physical machine, which is not the case for RDS.

Error messages by option:

all-databases:

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 and 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)

tab=<dir_name>:

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

Partial workaround for `--tab`: Combine --tab with --no-data to export only the CREATE TABLE statements (.sql files) for each table, without data:

mysqldump \
  --no-defaults \
  -u <user_name> \
  -p<password> \
  -h <instance_endpoint> \
  -P 3306 \
  --set-gtid-purged=OFF \
  --single-transaction \
  --tab=/tmp \
  --no-data \
  <database_name>