GTID feature
The GTID feature was introduced in ApsaraDB RDS for MySQL 5.6 to add the set-gtid-purged option to the mysqldump utility bundled with MySQL 5.6.
| Option | The default value of the field. | Optional value | Purpose |
|---|---|---|---|
| set-gtid-purged | AUTO | ON|OFF|AUTO | Specifies whether to include a SET @@GLOBAL.GTID_PURGED statement in the mysqldump output. |
Note
- ON: A SET @@GLOBAL.GTID_PURGED statement is included in the mysqldump output.
- OFF: A SET @@GLOBAL.GTID_PURGED statement is not included in the mysqldump output.
- AUTO: the default value. For an instance with GTID enabled, a SET @@GLOBAL.GTID_PURGED statement is included in the mysqldump output. For an instance that is not started or does not support GTID, no GTID information is generated.
If you use MySQL 5.6 or later versions of mysqldump to export data to instances of
RDS MySQL 5.5 versions, you must set set-gtid-purged to OFF. Otherwise, the following
error occurs:
Error: Server has GTIDs disabled.
or
mysqldump: Couldn't execute 'SELECT @@GTID_MODE': Unknown system variable 'GTID_MODE' <1193>Avoid table-level lock wait
In mysqldump, the lock-tables option is enabled by default to lock all tables before dumping them and prevent DML operations on these tables.
The InnoDB and TokuDB engines supported by apsaradb RDS for MySQL instances support transactions. We recommend that you use the single-transaction option to dump tables, rather than setting the lock-all-tables or lock-tables option.
| Option | The default value of the field. | Optional value | Purpose |
|---|---|---|---|
| lock-all-tables | FALSE | TRUE|FALSE | Specifies whether to lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. All tables in all databases are read-only during the dump. This option automatically disables the lock-tables and single-transaction options. ApsaraDB for RDS does not support this option. |
| lock-tables | TRUE | TRUE|FALSE | Specifies whether to lock tables before dumping them. By default, this option is enabled. You can specify the skip-lock-tables option to disable this option. |
| single-transaction | FALSE | TRUE|FALSE | Specifies whether to start a transaction before dumping data. This option automatically disables the lock-tables option. |
Set the character set for data to be dumped
If no character set is specified, mysqldump uses the UTF-8 character set to dump data.
| Option | The default value of the field. | Optional value | Purpose |
|---|---|---|---|
| default-character-set | UTF8 | Character sets supported by ApsaraDB RDS for MySQL instances | Sets the character set for the export. |
Additional options to be noted when data is dumped
| Option | The default value of the field. | Optional value | Purpose |
|---|---|---|---|
| no-defaults | N/A | NA | Reads the .mylogin.cnf option file only. |
| defaults-file=file_name | N/A | NA | Reads a specified option file. |
| add-drop-database | FALSE | TRUE|FALSE | Specifies whether to add a DROP DATABASE statement before each CREATE DATABASE statement. |
| add-drop-table | TRUE | TRUE|FALSE | Specifies whether to add a DROP TABLE statement before each CREATE TABLE statement. By default, this option is enabled. You can use the skip-add-drop-table option to disable this option. |
| add-locks | TRUE | TRUE|FALSE | Specifies whether to surround each table-related statement with the LOCK TABLES tab_name WRITE and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded. |
| compatible=name | NA | ansi|postgresql|oracle|mssql | Enhances compatibility with a specified database type. |
| compact | FALSE | TRUE|FALSE | Specifies whether to enable the skip-add-drop-table, skip-add-locks, skip-comments, skip-disable-keys, and skip-set-charset options. |
| databases | TRUE | TRUE|FALSE | Specifies whether to dump multiple databases. By default, mysqldump treats the first name argument on the command line as a database name and following names as table names. When this option is specified, mysqldump interprets all name arguments as database names and includes the CREATE DATABASE and USE DATABASE statements in the output before each new database. |
| disable-keys | TRUE | TRUE|FALSE | Specifies whether to surround INSERT statements with /! 40000 ALTER TABLE tab_name DISABLE KEYS / and /! 40000 ALTER TABLE tab_name ENABLE KEYS / statements to speed up dump file loading. This option is effective only for non-unique
indexes of MyISAM tables.
|
| events | FALSE | TRUE|FALSE | Specifies whether to dump scheduled events from dumped databases. |
| extended-insert | TRUE | TRUE|FALSE | Specifies whether to combine single-row INSERT statements into a single statement that inserts multiple table rows. |
| hex-blob | FALSE | TRUE|FALSE | Specifies whether to export BINARY, VARBINARY, and BLOB types of data in hexadecimal notation. We recommend that you add this option when migrating data between different database versions. |
| ignore-table=db.tab | TRUE | TRUE|FALSE | Specifies whether to ignore a table or view. Format: database name.table name (db.tab). You can use this option multiple times to ignore multiple tables. |
| max-allowed-packet | 24MB | 24MB-1GB | Specifies the maximum size of the buffer for communication between mysqldump and an ApsaraDB RDS for MySQL instance. The default value is 24MB. The maximum value is 1GB. |
| no-create-db | FALSE | TRUE|FALSE | Specifies whether to exclude CREATE DATABASE statements from the output. |
| no-create-info | FALSE | TRUE|FALSE | Specifies whether to exclude CREATE TABLE statements from the output. |
| no-data | FALSE | TRUE|FALSE | Specifies whether to export an ApsaraDB RDS for MySQL database to a dump file without data. |
| opt | TRUE | TRUE|FALSE | Specifies whether to enable the add-drop-table, add-locks, create-options, disable-keys, extended-insert, lock-tables, quick, and set-charset options. You can specify the skip-opt option to disable the opt option enabled by default. |
| dump-date | TRUE | TRUE|FALSE | Specifies whether to add a dump date to the end of the output if the comments option is specified. By default, the comments option is enabled. |
| routines | FALSE | TRUE|FALSE | Specifies whether to dump stored procedures and functions from dumped databases. By default, stored procedures and functions are not dumped. |
| result-file | TRUE | TRUE|FALSE | Specifies whether to direct output to a specified file. |
| set-charset | TRUE | TRUE|FALSE | Specifies whether to add SET NAMES default_character_set to the output. |
| triggers | TRUE | TRUE|FALSE | Specifies whether to include triggers for each dumped table in the output. |
Options not supported for apsaradb RDS for MySQL
| Option | The default value of the field. | Optional value | Purpose |
|---|---|---|---|
| all-databases | FALSE | Character sets supported by ApsaraDB RDS for MySQL instances | Specifies whether to dump all databases, including the mysql database. |
| flush-logs | FALSE | TRUE|FALSE | Specifies whether to execute the flush logs; statement in an ApsaraDB RDS for MySQL instance before starting the dump.
|
| flush-privileges | FALSE | TRUE|FALSE | Specifies whether to add a flush privileges; statement to the dump output after dumping the mysql database.
|
| lock-all-tables | FALSE | TRUE|FALSE | Specifies whether to lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. All tables in all databases are read-only during the dump. This option automatically disables the lock-tables and single-transaction options. |
| tab=dir_name | N/A | NA | A tbl_name.sql file (including the table creation statement) and tbl_name.txt text format data files separated with tab are generated in a specified directory. |
Unsupported options and reasons
- all-databases:RDS MySQL common user cannot export all database tables because they
do not have permissions on some of the tables in the mysql database.
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) - The flush-logs: RDS MySQL common user does not have the reload permission and therefore
cannot be executed.
flush 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 not required because apsaradb RDS for MySQL does not allow you to export the mysql database.
- lock-all-tables:RDS MySQL common user does not have the reload permission and therefore
cannot be used.
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 mysqldump and apsaradb RDS for
MySQL instances are on the same physical machine. However, this option can be used
together with the no-data option to obtain CREATE TABLE statements that create tables.
# This option can be used with the no-data option to obtain the tab_name.sql file that 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 # The following error message appears if you use the no-data option to dump data: 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’