CALL mysql.rds_set_configuration('binlog retention hours', 144); After replication has been started, you can verify that changes have been applied to your replica by running the `SHOW SLAVE STATUS` (Aurora MySQL version 2) or `SHOW REPLICA STATUS` (Aurora MySQL version 3) command on your replica and checking the `Seconds behind master` field. If the `Seconds behind master` field is 0, then there is no replica lag. When there is no replica lag, reduce the length of time that binlog files are retained by setting the `binlog retention hours` configuration parameter to a smaller time frame. If this setting isn't specified, the default for Aurora MySQL is 24 (1 day). If you specify a value for `'binlog retention hours'` that is higher than the maximum value, then Aurora MySQL uses the maximum. |
| RDS for MySQL | **To retain binary logs on an Amazon RDS DB instance** You can retain binary log files on an Amazon RDS DB instance by setting the binlog retention hours just as with an Aurora MySQL DB cluster, described in the previous row. You can also retain binlog files on an Amazon RDS DB instance by creating a read replica for the DB instance. This read replica is temporary and solely for the purpose of retaining binlog files. After the read replica has been created, call the [mysql.rds\$1stop\$1replication](mysql-stored-proc-replicating.md#mysql_rds_stop_replication) procedure on the read replica. While replication is stopped, Amazon RDS doesn't delete any of the binlog files on the replication source. After you have set up replication with your permanent replica, you can delete the read replica when the replica lag (`Seconds behind master` field) between your replication source and your permanent replica reaches 0. |
| MySQL (external) | **To retain binary logs on an external MySQL database** Because binlog files on an external MySQL database are not managed by Amazon RDS, they are retained until you delete them. After replication has been started, you can verify that changes have been applied to your replica by running the `SHOW SLAVE STATUS` (Aurora MySQL version 2) or `SHOW REPLICA STATUS` (Aurora MySQL version 3) command on your replica and checking the `Seconds behind master` field. If the `Seconds behind master` field is 0, then there is no replica lag. When there is no replica lag, you can delete old binlog files. |
## 3. Create a copy or dump of your replication source
You use a snapshot, clone, or dump of your replication source to load a baseline copy of your data onto your replica. Then you start replicating from that point.
Use the following instructions to create a copy or dump of the replication source for your database engine.
| Database engine | Instructions |
| --- | --- |
| Aurora MySQL | **To create a copy of an Aurora MySQL DB cluster** Use one of the following methods: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) **To determine the binlog file name and position** Use one of the following methods: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) **To create a dump of an Aurora MySQL DB cluster** If your replica target is an external MySQL database or an RDS for MySQL DB instance, then you must create a dump file from your Aurora DB cluster. Be sure to run the `mysqldump` command against the copy of your source DB cluster that you created. This is to avoid locking considerations when taking the dump. If the dump were taken on the source DB cluster directly, it would be necessary to lock the source tables to prevent concurrent writes to them while the dump is in progress. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
| RDS for MySQL | **To create a snapshot of an Amazon RDS DB instance** Create a read replica of your Amazon RDS DB instance. For more information, see [Creating a read replica](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html#USER_ReadRepl.Create) in the *Amazon Relational Database Service User Guide*. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
| MySQL (external) | **To create a dump of an external MySQL database** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
## 4. Load the dump into your replica target (if needed)
If you plan to load data from a dump of a MySQL database that is external to Amazon RDS, you might want to create an EC2 instance to copy the dump files to. Then you can load the data into your DB cluster or DB instance from that EC2 instance. Using this approach, you can compress the dump file(s) before copying them to the EC2 instance in order to reduce the network costs associated with copying data to Amazon RDS. You can also encrypt the dump file or files to secure the data as it is being transferred across the network.
**Note**
If you create a new Aurora MySQL DB cluster as your replica target, then you don't need to load a dump file:
You can restore from a DB cluster snapshot to create a new DB cluster. For more information, see [Restoring from a DB cluster snapshot](aurora-restore-snapshot.md).
You can clone your source DB cluster to create a new DB cluster. For more information, see [Cloning a volume for an Amazon Aurora DB cluster](Aurora.Managing.Clone.md).
You can migrate the data from a DB instance snapshot into a new DB cluster. For more information, see [Migrating data to an Amazon Aurora MySQL DB cluster](AuroraMySQL.Migrating.md).
Use the following instructions to load the dump of your replication source into your replica target for your database engine.
| Database engine | Instructions |
| --- | --- |
| Aurora MySQL | **To load a dump into an Aurora MySQL DB cluster** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
| RDS for MySQL | **To load a dump into an Amazon RDS DB instance** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
| MySQL (external) | **To load a dump into an external MySQL database** You can't load a DB snapshot or a DB cluster snapshot into an external MySQL database. Instead, you must use the output from the `mysqldump` command. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
## 5. Create a replication user on your replication source
Create a user ID on the source that is used solely for replication. The following example is for RDS for MySQL or external MySQL source databases.
```
mysql> CREATE USER 'repl_user'@'domain_name' IDENTIFIED BY 'password';
```
For Aurora MySQL source databases, the `skip_name_resolve` DB cluster parameter is set to `1` (`ON`) and can't be modified, so you must use an IP address for the host instead of a domain name. For more information, see [skip\$1name\$1resolve](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_skip_name_resolve) in the MySQL documentation.
```
mysql> CREATE USER 'repl_user'@'IP_address' IDENTIFIED BY 'password';
```
The user requires the `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges. Grant these privileges to the user.
If you need to use encrypted replication, require SSL connections for the replication user. For example, you can use one of the following statements to require SSL connections on the user account `repl_user`.
```
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'IP_address';
```
```
GRANT USAGE ON *.* TO 'repl_user'@'IP_address' REQUIRE SSL;
```
**Note**
If `REQUIRE SSL` isn't included, the replication connection might silently fall back to an unencrypted connection.
## 6. Turn on replication on your replica target
Before you turn on replication, we recommend that you take a manual snapshot of the Aurora MySQL DB cluster or RDS for MySQL DB instance replica target. If a problem arises and you need to re-establish replication with the DB cluster or DB instance replica target, you can restore the DB cluster or DB instance from this snapshot instead of having to import the data into your replica target again.
Use the following instructions to turn on replication for your database engine.
| Database engine | Instructions |
| --- | --- |
| Aurora MySQL | **To turn on replication from an Aurora MySQL DB cluster** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) To use SSL encryption, set the final value to `1` instead of `0`. |
| RDS for MySQL | **To turn on replication from an Amazon RDS DB instance** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) To use SSL encryption, set the final value to `1` instead of `0`. |
| MySQL (external) | **To turn on replication from an external MySQL database** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.SettingUp.html) |
If replication fails, it can result in a large increase in unintentional I/O on the replica, which can degrade performance. If replication fails or is no longer needed, you can run the [mysql.rds\$1reset\$1external\$1master (Aurora MySQL version 2)](mysql-stored-proc-replicating.md#mysql_rds_reset_external_master) or [mysql.rds\$1reset\$1external\$1source (Aurora MySQL version 3)](mysql-stored-proc-replicating.md#mysql_rds_reset_external_source) stored procedure to remove the replication configuration.
### Setting a location to stop replication to a read replica
In Aurora MySQL version 3.04 and higher, you can start replication and then stop it at a specified binary log file location using the [mysql.rds\$1start\$1replication\$1until(Aurora MySQL version 3)](mysql-stored-proc-replicating.md#mysql_rds_start_replication_until) stored procedure.
**To start replication to a read replica and stop replication at a specific location**
1. Using a MySQL client, connect to the replica Aurora MySQL DB cluster as the master user.
1. Run the [mysql.rds\$1start\$1replication\$1until(Aurora MySQL version 3)](mysql-stored-proc-replicating.md#mysql_rds_start_replication_until) stored procedure.
The following example initiates replication and replicates changes until it reaches location `120` in the `mysql-bin-changelog.000777` binary log file. In a disaster recovery scenario, assume that location `120` is just before the disaster.
```
call mysql.rds_start_replication_until(
'mysql-bin-changelog.000777',
120);
```
Replication stops automatically when the stop point is reached. The following RDS event is generated: `Replication has been stopped since the replica reached the stop point specified by the rds_start_replication_until stored procedure`.
If you use GTID-based replication, use the [mysql.rds\$1start\$1replication\$1until\$1gtid(Aurora MySQL version 3)](mysql-stored-proc-gtid.md#mysql_rds_start_replication_until_gtid) stored procedure instead of the [mysql.rds\$1start\$1replication\$1until(Aurora MySQL version 3)](mysql-stored-proc-replicating.md#mysql_rds_start_replication_until) stored procedure. For more information about GTID-based replication, see [Using GTID-based replication](mysql-replication-gtid.md).
## 7. Monitor your replica
When you set up MySQL replication with an Aurora MySQL DB cluster, you must monitor failover events for the Aurora MySQL DB cluster when it is the replica target. If a failover occurs, then the DB cluster that is your replica target might be recreated on a new host with a different network address. For information on how to monitor failover events, see [Working with Amazon RDS event notification](USER_Events.md).
You can also monitor how far the replica target is behind the replication source by connecting to the replica target and running the `SHOW SLAVE STATUS` (Aurora MySQL version 2) or `SHOW REPLICA STATUS` (Aurora MySQL version 3) command. In the command output, the `Seconds Behind Master` field tells you how far the replica target is behind the source.
**Important**
If you upgrade your DB cluster and specify a custom parameter group, make sure to manually reboot the cluster after the upgrade finishes. Doing so makes the cluster use your new custom parameter settings, and restarts binlog replication.
## Synchronizing passwords between replication source and target
When you change user accounts and passwords on the replication source using SQL statements, those changes are replicated to the replication target automatically.
If you use the AWS Management Console, the AWS CLI, or the RDS API to change the master password on the replication source, those changes are not automatically replicated to the replication target. If you want to synchronize the master user and master password between the source and target systems, you must make the same change on the replication target yourself.
# Stopping binary log replication for Aurora MySQL
Stopping binlog replication
To stop binary log replication with a MySQL DB instance, external MySQL database, or another Aurora DB cluster, follow these steps, discussed in detail following in this topic.
[1. Stop binary log replication on the replica target](#AuroraMySQL.Replication.MySQL.Stopping.StopReplication)
[2. Turn off binary logging on the replication source](#AuroraMySQL.Replication.MySQL.Stopping.DisableBinaryLogging)
## 1. Stop binary log replication on the replica target
Use the following instructions to stop binary log replication for your database engine.
| Database engine | Instructions |
| --- | --- |
| Aurora MySQL | **To stop binary log replication on an Aurora MySQL DB cluster replica target** Connect to the Aurora DB cluster that is the replica target, and call the [mysql.rds\$1stop\$1replication](mysql-stored-proc-replicating.md#mysql_rds_stop_replication) procedure. |
| RDS for MySQL | **To stop binary log replication on an Amazon RDS DB instance** Connect to the RDS DB instance that is the replica target and call the [mysql.rds\$1stop\$1replication](mysql-stored-proc-replicating.md#mysql_rds_stop_replication) procedure. |
| MySQL (external) | **To stop binary log replication on an external MySQL database** Connect to the MySQL database and run the `STOP SLAVE` (version 5.7) or `STOP REPLICA` (version 8.0) command. |
## 2. Turn off binary logging on the replication source
Use the instructions in the following table to turn off binary logging on the replication source for your database engine.
| Database engine | Instructions |
| --- | --- |
| Aurora MySQL | **To turn off binary logging on an Amazon Aurora DB cluster** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.Stopping.html) |
| RDS for MySQL | **To turn off binary logging on an Amazon RDS DB instance** You can't turn off binary logging directly for an Amazon RDS DB instance, but you can turn it off by doing the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.Stopping.html) |
| MySQL (external) | **To turn off binary logging on an external MySQL database** Connect to the MySQL database and call the `STOP REPLICATION` command. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.Stopping.html) |
# Scaling reads for your MySQL database with Amazon Aurora
Scaling MySQL reads
You can use Amazon Aurora with your MySQL DB instance to take advantage of the read scaling capabilities of Amazon Aurora and expand the read workload for your MySQL DB instance. To use Aurora to scale reads for your MySQL DB instance, create an Amazon Aurora MySQL DB cluster and make it a read replica of your MySQL DB instance. This applies to an RDS for MySQL DB instance, or a MySQL database running external to Amazon RDS.
For information on creating an Amazon Aurora DB cluster, see [Creating an Amazon Aurora DB cluster](Aurora.CreateInstance.md).
When you set up replication between your MySQL DB instance and your Amazon Aurora DB cluster, be sure to follow these guidelines:
+ Use the Amazon Aurora DB cluster endpoint address when you reference your Amazon Aurora MySQL DB cluster. If a failover occurs, then the Aurora Replica that is promoted to the primary instance for the Aurora MySQL DB cluster continues to use the DB cluster endpoint address.
+ Maintain the binlogs on your writer instance until you have verified that they have been applied to the Aurora Replica. This maintenance ensures that you can restore your writer instance in the event of a failure.
**Important**
When using self-managed replication, you're responsible for monitoring and resolving any replication issues that may occur. For more information, see [Diagnosing and resolving lag between read replicas](CHAP_Troubleshooting.md#CHAP_Troubleshooting.MySQL.ReplicaLag).
**Note**
The permissions required to start replication on an Aurora MySQL DB cluster are restricted and not available to your Amazon RDS master user. Therefore, you must use the [mysql.rds\$1set\$1external\$1master (Aurora MySQL version 2)](mysql-stored-proc-replicating.md#mysql_rds_set_external_master) or [mysql.rds\$1set\$1external\$1source (Aurora MySQL version 3)](mysql-stored-proc-replicating.md#mysql_rds_set_external_source) and [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#mysql_rds_start_replication) procedures to set up replication between your Aurora MySQL DB cluster and your MySQL DB instance.
## Start replication between an external source instance and an Aurora MySQL DB cluster
1. Make the source MySQL DB instance read-only:
```
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;
```
1. Run the `SHOW MASTER STATUS` command on the source MySQL DB instance to determine the binlog location. You receive output similar to the following example:
```
File Position
------------------------------------
mysql-bin-changelog.000031 107
------------------------------------
```
1. Copy the database from the external MySQL DB instance to the Amazon Aurora MySQL DB cluster using `mysqldump`. For very large databases, you might want to use the procedure in [Importing data to an Amazon RDS for MySQL database with reduced downtime](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-importing-data-reduced-downtime.html) in the *Amazon Relational Database Service User Guide*.
For Linux, macOS, or Unix:
```
mysqldump \
--databases SET optimizer_switch="hash_join=off"In Aurora MySQL version 3, this parameter doesn't apply for users who have the `CONNECTION_ADMIN` privilege. This includes the Aurora master user. For more information, see [Role-based privilege model](AuroraMySQL.Compare-80-v3.md#AuroraMySQL.privilege-model). | | `innodb_adaptive_hash_index` | Yes | You can modify this parameter at the DB cluster level in Aurora MySQL versions 2 and 3. The Adaptive Hash Index isn't supported on reader DB instances. | | `innodb_aurora_instant_alter_column_allowed` | Yes | Controls whether the `INSTANT` algorithm can be used for `ALTER COLUMN` operations at the global level. The allowed values are the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.ParameterGroups.html) For more information, see [Column Operations](https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-column-operations) in the MySQL documentation. This parameter applies to Aurora MySQL version 3.05 and higher. | | `innodb_autoinc_lock_mode` | Yes | None | | `innodb_checksums` | No | Removed from Aurora MySQL version 3. | | `innodb_cmp_per_index_enabled` | Yes | None | | `innodb_commit_concurrency` | Yes | None | | `innodb_data_home_dir` | No | Aurora MySQL uses managed instances where you don't access the file system directly. | | `innodb_deadlock_detect` | Yes | This option is used to disable deadlock detection in Aurora MySQL version 2.11 and higher and version 3. On high-concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. Consult the MySQL documentation for more information on this parameter. | | `innodb_default_row_format` | Yes | This parameter defines the default row format for InnoDB tables (including user-created InnoDB temporary tables). It applies to Aurora MySQL versions 2 and 3. Its value can be `DYNAMIC`, `COMPACT`, or `REDUNDANT.` | | `innodb_file_per_table` | Yes | This parameter affects how table storage is organized. For more information, see [Storage scaling](Aurora.Managing.Performance.md#Aurora.Managing.Performance.StorageScaling). | | `innodb_flush_log_at_trx_commit` | Yes | We highly recommend that you use the default value of `1`. In Aurora MySQL version 3, before you can set this parameter to a value other than `1`, you must set the value of `innodb_trx_commit_allow_data_loss` to `1`. For more information, see [Configuring how frequently the log buffer is flushed](AuroraMySQL.BestPractices.FeatureRecommendations.md#AuroraMySQL.BestPractices.Flush). | | `innodb_ft_max_token_size` | Yes | None | | `innodb_ft_min_token_size` | Yes | None | | `innodb_ft_num_word_optimize` | Yes | None | | `innodb_ft_sort_pll_degree` | Yes | None | | `innodb_online_alter_log_max_size` | Yes | None | | `innodb_optimize_fulltext_only` | Yes | None | | `innodb_page_size` | No | None | | `innodb_print_all_deadlocks` | Yes | When turned on, records information about all InnoDB deadlocks in the Aurora MySQL error log. For more information, see [Minimizing and troubleshooting Aurora MySQL deadlocks](AuroraMySQL.BestPractices.FeatureRecommendations.md#AuroraMySQL.BestPractices.deadlocks). | | `innodb_purge_batch_size` | Yes | None | | `innodb_purge_threads` | Yes | None | | `innodb_rollback_on_timeout` | Yes | None | | `innodb_rollback_segments` | Yes | None | | `innodb_spin_wait_delay` | Yes | None | | `innodb_strict_mode` | Yes | None | | `innodb_support_xa` | Yes | Removed from Aurora MySQL version 3. | | `innodb_sync_array_size` | Yes | None | | `innodb_sync_spin_loops` | Yes | None | | `innodb_stats_include_delete_marked` | Yes | When this parameter is enabled, InnoDB includes delete-marked records when calculating persistent optimizer statistics. This parameter applies to Aurora MySQL version 2.12 and higher, and version 3. | | `innodb_table_locks` | Yes | None | | `innodb_trx_commit_allow_data_loss` | Yes | In Aurora MySQL version 3, set the value of this parameter to `1` so that you can change the value of `innodb_flush_log_at_trx_commit`. The default value of `innodb_trx_commit_allow_data_loss` is `0`. For more information, see [Configuring how frequently the log buffer is flushed](AuroraMySQL.BestPractices.FeatureRecommendations.md#AuroraMySQL.BestPractices.Flush). | | `innodb_undo_directory` | No | Aurora MySQL uses managed instances where you don't access the file system directly. | | `internal_tmp_disk_storage_engine` | Yes | Controls which in-memory storage engine is used for internal temporary tables. Allowed values are `INNODB` and `MYISAM`. This parameter applies to Aurora MySQL version 2. | | `internal_tmp_mem_storage_engine` | Yes | Controls which in-memory storage engine is used for internal temporary tables. Allowed values are `MEMORY` and `TempTable`. This parameter applies to Aurora MySQL version 3. | | `key_buffer_size` | Yes | Key cache for MyISAM tables. For more information, see [keycache->cache\$1lock mutex](AuroraMySQL.Reference.Waitevents.md#key-cache.cache-lock). | | `lc_time_names` | Yes | None | | `log_error_suppression_list` | Yes | Specifies a list of error codes that aren't logged in the MySQL error log. This allows you to ignore certain noncritical error conditions to help keep your error logs clean. For more information, see [log\$1error\$1suppression\$1list](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_error_suppression_list) in the MySQL documentation. This parameter applies to Aurora MySQL version 3.03 and higher. | | `low_priority_updates` | Yes | `INSERT`, `UPDATE`, `DELETE`, and `LOCK TABLE WRITE` operations wait until there's no pending `SELECT` operation. This parameter affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE). This parameter applies to Aurora MySQL version 3. | | `lower_case_table_names` | Yes (Aurora MySQL version 2) Only at cluster creation time (Aurora MySQL version 3) | In Aurora MySQL version 2.10 and higher 2.x versions, make sure to reboot all reader instances after changing this setting and rebooting the writer instance. For details, see [Rebooting an Aurora cluster with read availability](aurora-mysql-survivable-replicas.md). In Aurora MySQL version 3, the value of this parameter is set permanently at the time the cluster is created. If you use a nondefault value for this option, set up your Aurora MySQL version 3 custom parameter group before upgrading, and specify the parameter group during the snapshot restore operation that creates the version 3 cluster. With an Aurora global database based on Aurora MySQL, you can't perform an in-place upgrade from Aurora MySQL version 2 to version 3 if the `lower_case_table_names` parameter is turned on. For more information on the methods that you can use, see [Major version upgrades](aurora-global-database-upgrade.md#aurora-global-database-upgrade.major). | | `master-info-repository` | Yes | Removed from Aurora MySQL version 3. | | `master_verify_checksum` | Yes | Aurora MySQL version 2. Use `source_verify_checksum` in Aurora MySQL version 3. | | `max_delayed_threads` | Yes | Sets the maximum number of threads to handle `INSERT DELAYED` statements. This parameter applies to Aurora MySQL version 3. | | `max_error_count` | Yes | The maximum number of error, warning, and note messages to be stored for display. This parameter applies to Aurora MySQL version 3. | | `max_execution_time` | Yes | The timeout for running `SELECT` statements, in milliseconds. The value can be from `0`–`18446744073709551615`. When set to `0`, there is no timeout. For more information, see [max\$1execution\$1time](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_execution_time) in the MySQL documentation. | | `min_examined_row_limit` | Yes | Use this parameter to prevent queries that examine fewer than the specified number of rows from being logged. | | `partial_revokes` | No | This parameter applies to Aurora MySQL version 3. | | `preload_buffer_size` | Yes | The size of the buffer that's allocated when preloading indexes. This parameter applies to Aurora MySQL version 3. | | `query_cache_type` | Yes | Removed from Aurora MySQL version 3. | | `read_only` | Yes | When this parameter is turned on, the server permits no updates except from those performed by replica threads. For Aurora MySQL version 2, valid values are the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.ParameterGroups.html) For Aurora MySQL version 3, valid values are the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.ParameterGroups.html) In Aurora MySQL version 3, this parameter doesn't apply for users who have the `CONNECTION_ADMIN` privilege. This includes the Aurora master user. For more information, see [Role-based privilege model](AuroraMySQL.Compare-80-v3.md#AuroraMySQL.privilege-model). | | `relay-log-space-limit` | Yes | This parameter applies to Aurora MySQL version 3. | | `replica_parallel_type` | Yes | This parameter enables parallel execution on the replica of all uncommitted threads already in the prepare phase, without violating consistency. It applies to Aurora MySQL version 3. In Aurora MySQL version 3.03.\$1 and lower, the default value is DATABASE. In Aurora MySQL version 3.04 and higher, the default value is LOGICAL\$1CLOCK. | | `replica_preserve_commit_order` | Yes | This parameter applies to Aurora MySQL version 3. | | `replica_transaction_retries` | Yes | This parameter applies to Aurora MySQL version 3. | | `replica_type_conversions` | Yes | This parameter determines the type conversions used on replicas. The allowed values are: `ALL_LOSSY`, `ALL_NON_LOSSY`, `ALL_SIGNED`, and `ALL_UNSIGNED`. For more information, see [Replication with differing table definitions on source and replica](https://dev.mysql.com/doc/refman/8.0/en/replication-features-differing-tables.html) in the MySQL documentation. This parameter applies to Aurora MySQL version 3. | | `replicate-do-db` | Yes | This parameter applies to Aurora MySQL version 3. | | `replicate-do-table` | Yes | This parameter applies to Aurora MySQL version 3. | | `replicate-ignore-db` | Yes | This parameter applies to Aurora MySQL version 3. | | `replicate-ignore-table` | Yes | This parameter applies to Aurora MySQL version 3. | | `replicate-wild-do-table` | Yes | This parameter applies to Aurora MySQL version 3. | | `replicate-wild-ignore-table` | Yes | This parameter applies to Aurora MySQL version 3. | | `require_secure_transport` | Yes | This parameter applies to Aurora MySQL version 2 and 3. For more information, see [TLS connections to Aurora MySQL DB clusters](AuroraMySQL.Security.md#AuroraMySQL.Security.SSL). | | `rpl_read_size` | Yes | This parameter applies to Aurora MySQL version 3. | | `server_audit_cw_upload` | No | This parameter has been deprecated in Aurora MySQL. Use `server_audit_logs_upload`. For more information, see [Publishing Amazon Aurora MySQL logs to Amazon CloudWatch Logs](AuroraMySQL.Integrating.CloudWatch.md). | | `server_audit_events` | Yes | For more information, see [Using Advanced Auditing with an Amazon Aurora MySQL DB cluster](AuroraMySQL.Auditing.md). | | `server_audit_excl_users` | Yes | For more information, see [Using Advanced Auditing with an Amazon Aurora MySQL DB cluster](AuroraMySQL.Auditing.md). | | `server_audit_incl_users` | Yes | For more information, see [Using Advanced Auditing with an Amazon Aurora MySQL DB cluster](AuroraMySQL.Auditing.md). | | `server_audit_logging` | Yes | For instructions on uploading the logs to Amazon CloudWatch Logs, see [Publishing Amazon Aurora MySQL logs to Amazon CloudWatch Logs](AuroraMySQL.Integrating.CloudWatch.md). | | `server_audit_logs_upload` | Yes | You can publish audit logs to CloudWatch Logs by enabling Advanced Auditing and setting this parameter to `1`. The default for the `server_audit_logs_upload` parameter is `0`. For more information, see [Publishing Amazon Aurora MySQL logs to Amazon CloudWatch Logs](AuroraMySQL.Integrating.CloudWatch.md). | | `server_id` | No | None | | `skip-character-set-client-handshake` | Yes | None | | `skip_name_resolve` | No | None | | `slave-skip-errors` | Yes | Only applies to Aurora MySQL version 2 clusters, with MySQL 5.7 compatibility. | | `source_verify_checksum` | Yes | This parameter applies to Aurora MySQL version 3. | | `sync_frm` | Yes | Removed from Aurora MySQL version 3. | | `thread_cache_size` | Yes | The number of threads to be cached. This parameter applies to Aurora MySQL versions 2 and 3. | | `time_zone` | Yes | By default, the time zone for an Aurora DB cluster is Universal Time Coordinated (UTC). You can set the time zone for instances in your DB cluster to the local time zone for your application instead. For more information, see [Local time zone for Amazon Aurora DB clusters](Concepts.RegionsAndAvailabilityZones.md#Aurora.Overview.LocalTimeZone). | | `tls_version` | Yes | For more information, see [TLS versions for Aurora MySQL](AuroraMySQL.Security.md#AuroraMySQL.Security.SSL.TLS_Version). | ## Instance-level parameters The following table shows all of the parameters that apply to a specific DB instance in an Aurora MySQL DB cluster. | Parameter name | Modifiable | Notes | | --- | --- | --- | | `activate_all_roles_on_login` | Yes | This parameter applies to Aurora MySQL version 3. | | `allow-suspicious-udfs` | No | None | | `aurora_disable_hash_join` | Yes | Set this parameter to `ON` to turn off hash join optimization in Aurora MySQL version 2.09 or higher. It isn't supported for version 3. For more information, see [Parallel query for Amazon Aurora MySQL](aurora-mysql-parallel-query.md). | | `aurora_lab_mode` | Yes | For more information, see [Amazon Aurora MySQL lab mode](AuroraMySQL.Updates.LabMode.md). Removed from Aurora MySQL version 3. | | `aurora_oom_response` | Yes | This parameter is supported for Aurora MySQL versions 2 and 3. For more information, see [Troubleshooting out-of-memory issues for Aurora MySQL databases](AuroraMySQLOOM.md). | | `aurora_parallel_query` | Yes | Set to `ON` to turn on parallel query in Aurora MySQL version 2.09 or higher. The old `aurora_pq` parameter isn't used in these versions. For more information, see [Parallel query for Amazon Aurora MySQL](aurora-mysql-parallel-query.md). | | `aurora_pq` | Yes | Set to `OFF` to turn off parallel query for specific DB instances in Aurora MySQL versions before 2.09. In version 2.09 or higher, turn parallel query on and off with `aurora_parallel_query` instead. For more information, see [Parallel query for Amazon Aurora MySQL](aurora-mysql-parallel-query.md). | | `aurora_read_replica_read_committed` | Yes | Enables `READ COMMITTED` isolation level for Aurora Replicas and changes the isolation behavior to reduce purge lag during long-running queries. Enable this setting only if you understand the behavior changes and how they affect your query results. For example, this setting uses less-strict isolation than the MySQL default. When it's enabled, long-running queries might see more than one copy of the same row because Aurora reorganizes the table data while the query is running. For more information, see [Aurora MySQL isolation levels](AuroraMySQL.Reference.IsolationLevels.md). | | `aurora_tmptable_enable_per_table_limit` | Yes | Determines whether the `tmp_table_size` parameter controls the maximum size of in-memory temporary tables created by the `TempTable` storage engine in Aurora MySQL version 3.04 and higher. For more information, see [Limiting the size of internal, in-memory temporary tables](ams3-temptable-behavior.md#ams3-temptable-behavior-limit). | | `aurora_use_vector_instructions` | Yes | When this parameter is enabled, Aurora MySQL uses optimized vector processing instructions provided by modern CPUs to improve performance on I/O-intensive workloads. This setting is enabled by default in Aurora MySQL version 3.05 and higher. | | `autocommit` | Yes | None | | `automatic_sp_privileges` | Yes | None | | `back_log` | Yes | None | | `basedir` | No | Aurora MySQL uses managed instances where you don't access the file system directly. | | `binlog_cache_size` | Yes | None | | `binlog_max_flush_queue_time` | Yes | None | | `binlog_order_commits` | Yes | None | | `binlog_stmt_cache_size` | Yes | None | | `binlog_transaction_compression` | Yes | This parameter applies to Aurora MySQL version 3. | | `binlog_transaction_compression_level_zstd` | Yes | This parameter applies to Aurora MySQL version 3. | | `bulk_insert_buffer_size` | Yes | None | | `concurrent_insert` | Yes | None | | `connect_timeout` | Yes | None | | `core-file` | No | Aurora MySQL uses managed instances where you don't access the file system directly. | | `datadir` | No | Aurora MySQL uses managed instances where you don't access the file system directly. | | `default_authentication_plugin` | No | This parameter applies to Aurora MySQL version 3. | | `default_time_zone` | No | None | | `default_tmp_storage_engine` | Yes | The default storage engine for temporary tables. | | `default_week_format` | Yes | None | | `delay_key_write` | Yes | None | | `delayed_insert_limit` | Yes | None | | `delayed_insert_timeout` | Yes | None | | `delayed_queue_size` | Yes | None | | `div_precision_increment` | Yes | None | | `end_markers_in_json` | Yes | None | | `eq_range_index_dive_limit` | Yes | None | | `event_scheduler` | Sometimes | Indicates the status of the Event Scheduler. Modifiable only at the cluster level in Aurora MySQL version 3. | | `explicit_defaults_for_timestamp` | Yes | None | | `flush` | No | None | | `flush_time` | Yes | None | | `ft_boolean_syntax` | No | None | | `ft_max_word_len` | Yes | None | | `ft_min_word_len` | Yes | None | | `ft_query_expansion_limit` | Yes | None | | `ft_stopword_file` | Yes | None | | `general_log` | Yes | For instructions on uploading the logs to CloudWatch Logs, see [Publishing Amazon Aurora MySQL logs to Amazon CloudWatch Logs](AuroraMySQL.Integrating.CloudWatch.md). | | `general_log_file` | No | Aurora MySQL uses managed instances where you don't access the file system directly. | | `group_concat_max_len` | Yes | None | | `host_cache_size` | Yes | None | | `init_connect` | Yes | The command to be run by the server for each client that connects. Use double quotes (") for settings to avoid connection failures, for example:
SET optimizer_switch="hash_join=off"In Aurora MySQL version 3, this parameter doesn't apply for users who have the `CONNECTION_ADMIN` privilege, including the Aurora master user. For more information, see [Role-based privilege model](AuroraMySQL.Compare-80-v3.md#AuroraMySQL.privilege-model). | | `innodb_adaptive_hash_index` | Yes | You can modify this parameter at the DB instance level in Aurora MySQL version 2. It's modifiable only at the DB cluster level in Aurora MySQL version 3. The Adaptive Hash Index isn't supported on reader DB instances. | | `innodb_adaptive_max_sleep_delay` | Yes | Modifying this parameter has no effect because `innodb_thread_concurrency` is always 0 for Aurora. | | `innodb_aurora_max_partitions_for_range` | Yes | In some cases where persisted statistics aren't available, you can use this parameter to improve the performance of row count estimations on partitioned tables. You can set it to a value from 0–8192, where the value determines the number of partitions to check during row count estimation. The default value is 0, which estimates using all of the partitions, consistent with default MySQL behavior. This parameter is available for Aurora MySQL version 3.03.1 and higher. | | `innodb_autoextend_increment` | Yes | None | | `innodb_buffer_pool_dump_at_shutdown` | No | None | | `innodb_buffer_pool_dump_now` | No | None | | `innodb_buffer_pool_filename` | No | None | | `innodb_buffer_pool_load_abort` | No | None | | `innodb_buffer_pool_load_at_startup` | No | None | | `innodb_buffer_pool_load_now` | No | None | | `innodb_buffer_pool_size` | Yes | The default value is represented by a formula. For details about how the `DBInstanceClassMemory` value in the formula is calculated, see [DB parameter formula variables](USER_ParamValuesRef.md#USER_FormulaVariables). | | `innodb_change_buffer_max_size` | No | Aurora MySQL doesn't use the InnoDB change buffer at all. | | `innodb_compression_failure_threshold_pct` | Yes | None | | `innodb_compression_level` | Yes | None | | `innodb_compression_pad_pct_max` | Yes | None | | `innodb_concurrency_tickets` | Yes | Modifying this parameter has no effect, because `innodb_thread_concurrency` is always 0 for Aurora. | | `innodb_deadlock_detect` | Yes | This option is used to disable deadlock detection in Aurora MySQL version 2.11 and higher and version 3. On high-concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. Consult the MySQL documentation for more information on this parameter. | | `innodb_file_format` | Yes | Removed from Aurora MySQL version 3. | | `innodb_flushing_avg_loops` | No | None | | `innodb_force_load_corrupted` | No | None | | `innodb_ft_aux_table` | Yes | None | | `innodb_ft_cache_size` | Yes | None | | `innodb_ft_enable_stopword` | Yes | None | | `innodb_ft_server_stopword_table` | Yes | None | | `innodb_ft_user_stopword_table` | Yes | None | | `innodb_large_prefix` | Yes | Removed from Aurora MySQL version 3. | | `innodb_lock_wait_timeout` | Yes | None | | `innodb_log_compressed_pages` | No | None | | `innodb_lru_scan_depth` | Yes | None | | `innodb_max_purge_lag` | Yes | None | | `innodb_max_purge_lag_delay` | Yes | None | | `innodb_monitor_disable` | Yes | None | | `innodb_monitor_enable` | Yes | None | | `innodb_monitor_reset` | Yes | None | | `innodb_monitor_reset_all` | Yes | None | | `innodb_old_blocks_pct` | Yes | None | | `innodb_old_blocks_time` | Yes | None | | `innodb_open_files` | Yes | None | | `innodb_print_all_deadlocks` | Yes | When turned on, records information about all InnoDB deadlocks in the Aurora MySQL error log. For more information, see [Minimizing and troubleshooting Aurora MySQL deadlocks](AuroraMySQL.BestPractices.FeatureRecommendations.md#AuroraMySQL.BestPractices.deadlocks). | | `innodb_random_read_ahead` | Yes | None | | `innodb_read_ahead_threshold` | Yes | None | | `innodb_read_io_threads` | No | None | | `innodb_read_only` | No | Aurora MySQL manages the read-only and read/write state of DB instances based on the type of cluster. For example, a provisioned cluster has one read/write DB instance (the *primary instance*) and any other instances in the cluster are read-only (the Aurora Replicas). | | `innodb_replication_delay` | Yes | None | | `innodb_sort_buffer_size` | Yes | None | | `innodb_stats_auto_recalc` | Yes | None | | `innodb_stats_method` | Yes | None | | `innodb_stats_on_metadata` | Yes | None | | `innodb_stats_persistent` | Yes | None | | `innodb_stats_persistent_sample_pages` | Yes | None | | `innodb_stats_transient_sample_pages` | Yes | None | | `innodb_thread_concurrency` | No | None | | `innodb_thread_sleep_delay` | Yes | Modifying this parameter has no effect because `innodb_thread_concurrency` is always 0 for Aurora. | | `interactive_timeout` | Yes | Aurora evaluates the minimum value of `interactive_timeout` and `wait_timeout`. It then uses that minimum as the timeout to end all idle sessions, both interactive and noninteractive. | | `internal_tmp_disk_storage_engine` | Yes | Controls which in-memory storage engine is used for internal temporary tables. Allowed values are `INNODB` and `MYISAM`. This parameter applies to Aurora MySQL version 2. | | `internal_tmp_mem_storage_engine` | Sometimes | Controls which in-memory storage engine is used for internal temporary tables. Allowed values for writer DB instances are `MEMORY` and `TempTable`. For reader DB instances, this parameter is set to `TempTable` and can't be modified. This parameter applies to Aurora MySQL version 3. | | `join_buffer_size` | Yes | None | | `keep_files_on_create` | Yes | None | | `key_buffer_size` | Yes | Key cache for MyISAM tables. For more information, see [keycache->cache\$1lock mutex](AuroraMySQL.Reference.Waitevents.md#key-cache.cache-lock). | | `key_cache_age_threshold` | Yes | None | | `key_cache_block_size` | Yes | None | | `key_cache_division_limit` | Yes | None | | `local_infile` | Yes | None | | `lock_wait_timeout` | Yes | None | | `log-bin` | No | Setting `binlog_format` to `STATEMENT`, `MIXED`, or `ROW` automatically sets `log-bin` to `ON`. Setting `binlog_format` to `OFF` automatically sets `log-bin` to `OFF`. For more information, see [Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)](AuroraMySQL.Replication.MySQL.md). | | `log_bin_trust_function_creators` | Yes | None | | `log_bin_use_v1_row_events` | Yes | Removed from Aurora MySQL version 3. | | `log_error` | No | None | | `log_error_suppression_list` | Yes | Specifies a list of error codes that aren't logged in the MySQL error log. This allows you to ignore certain noncritical error conditions to help keep your error logs clean. For more information, see [log\$1error\$1suppression\$1list](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_error_suppression_list) in the MySQL documentation. This parameter applies to Aurora MySQL version 3.03 and higher. | | `log_output` | Yes | None | | `log_queries_not_using_indexes` | Yes | None | | `log_slave_updates` | No | Aurora MySQL version 2. Use `log_replica_updates` in Aurora MySQL version 3. | | `log_replica_updates` | No | Aurora MySQL version 3 | | `log_throttle_queries_not_using_indexes` | Yes | None | | `log_warnings` | Yes | Removed from Aurora MySQL version 3. | | `long_query_time` | Yes | None | | `low_priority_updates` | Yes | `INSERT`, `UPDATE`, `DELETE`, and `LOCK TABLE WRITE` operations wait until there's no pending `SELECT` operation. This parameter affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE). This parameter applies to Aurora MySQL version 3. | | `max_allowed_packet` | Yes | None | | `max_binlog_cache_size` | Yes | None | | `max_binlog_size` | No | None | | `max_binlog_stmt_cache_size` | Yes | None | | `max_connect_errors` | Yes | None | | `max_connections` | Yes | The default value is represented by a formula. For details about how the `DBInstanceClassMemory` value in the formula is calculated, see [DB parameter formula variables](USER_ParamValuesRef.md#USER_FormulaVariables). For the default values depending on the instance class, see [Maximum connections to an Aurora MySQL DB instance](AuroraMySQL.Managing.Performance.md#AuroraMySQL.Managing.MaxConnections). | | `max_delayed_threads` | Yes | Sets the maximum number of threads to handle `INSERT DELAYED` statements. This parameter applies to Aurora MySQL version 3. | | `max_error_count` | Yes | The maximum number of error, warning, and note messages to be stored for display. This parameter applies to Aurora MySQL version 3. | | `max_execution_time` | Yes | The timeout for running `SELECT` statements, in milliseconds. The value can be from `0`–`18446744073709551615`. When set to `0`, there is no timeout. For more information, see [max\$1execution\$1time](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_execution_time) in the MySQL documentation. | | `max_heap_table_size` | Yes | None | | `max_insert_delayed_threads` | Yes | None | | `max_join_size` | Yes | None | | `max_length_for_sort_data` | Yes | Removed from Aurora MySQL version 3. | | `max_prepared_stmt_count` | Yes | None | | `max_seeks_for_key` | Yes | None | | `max_sort_length` | Yes | None | | `max_sp_recursion_depth` | Yes | None | | `max_tmp_tables` | Yes | Removed from Aurora MySQL version 3. | | `max_user_connections` | Yes | None | | `max_write_lock_count` | Yes | None | | `metadata_locks_cache_size` | Yes | Removed from Aurora MySQL version 3. | | `min_examined_row_limit` | Yes | Use this parameter to prevent queries that examine fewer than the specified number of rows from being logged. This parameter applies to Aurora MySQL version 3. | | `myisam_data_pointer_size` | Yes | None | | `myisam_max_sort_file_size` | Yes | None | | `myisam_mmap_size` | Yes | None | | `myisam_sort_buffer_size` | Yes | None | | `myisam_stats_method` | Yes | None | | `myisam_use_mmap` | Yes | None | | `net_buffer_length` | Yes | None | | `net_read_timeout` | Yes | None | | `net_retry_count` | Yes | None | | `net_write_timeout` | Yes | None | | `old-style-user-limits` | Yes | None | | `old_passwords` | Yes | Removed from Aurora MySQL version 3. | | `optimizer_prune_level` | Yes | None | | `optimizer_search_depth` | Yes | None | | `optimizer_switch` | Yes | For information about Aurora MySQL features that use this switch, see [Best practices with Amazon Aurora MySQL](AuroraMySQL.BestPractices.md). | | `optimizer_trace` | Yes | None | | `optimizer_trace_features` | Yes | None | | `optimizer_trace_limit` | Yes | None | | `optimizer_trace_max_mem_size` | Yes | None | | `optimizer_trace_offset` | Yes | None | | `performance-schema-consumer-events-waits-current` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance-schema-consumer-events-waits-current`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance-schema-instrument` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance-schema-instrument`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema` | Yes | If the **Source** column is set to `Modified`, Performance Insights is managing the Performance Schema. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_accounts_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_accounts_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_consumer_global_instrumentation` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_consumer_global_instrumentation`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_consumer_thread_instrumentation` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_consumer_thread_instrumentation`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_consumer_events_stages_current` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_consumer_events_stages_current`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_consumer_events_stages_history` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_consumer_events_stages_history`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_consumer_events_stages_history_long` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_consumer_events_stages_history_long`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_consumer_events_statements_current` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_consumer_events_statements_current`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_consumer_events_statements_history` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_consumer_events_statements_history`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_consumer_events_statements_history_long` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_consumer_events_statements_history_long`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_consumer_events_waits_history` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_consumer_events_waits_history`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_consumer_events_waits_history_long` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_consumer_events_waits_history_long`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_consumer_statements_digest` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_consumer_statements_digest`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_digests_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_digests_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_events_stages_history_long_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_events_stages_history_long_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_events_stages_history_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_events_stages_history_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_events_statements_history_long_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_events_statements_history_long_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_events_statements_history_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_events_statements_history_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_events_transactions_history_long_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_events_transactions_history_long_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_events_transactions_history_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_events_transactions_history_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_events_waits_history_long_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_events_waits_history_long_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_events_waits_history_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_events_waits_history_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_hosts_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_hosts_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_cond_classes` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_cond_classes`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_cond_instances` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_cond_instances`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_digest_length` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_digest_length`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_file_classes` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_file_classes`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_file_handles` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_file_handles`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_file_instances` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_file_instances`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_index_stat` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_index_stat`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_memory_classes` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_memory_classes`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_metadata_locks` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_metadata_locks`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_mutex_classes` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_mutex_classes`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_mutex_instances` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_mutex_instances`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_prepared_statements_instances` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_prepared_statements_instances`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_program_instances` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_program_instances`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_rwlock_classes` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_rwlock_classes`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_rwlock_instances` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_rwlock_instances`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_socket_classes` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_socket_classes`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_socket_instances` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_socket_instances`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_sql_text_length` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_sql_text_length`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_stage_classes` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_stage_classes`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_statement_classes` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_statement_classes`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_statement_stack` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_statement_stack`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_table_handles` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_table_handles`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_table_instances` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_table_instances`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_table_lock_stat` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_table_lock_stat`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_thread_classes` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_thread_classes`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_max_thread_instances` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_max_thread_instances`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_session_connect_attrs_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_session_connect_attrs_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_setup_actors_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_setup_actors_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_setup_objects_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_setup_objects_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_show_processlist` | Yes | This parameter determines which SHOW PROCESSLIST implementation to use: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.ParameterGroups.html)This parameter applies to Aurora MySQL version 2.12 and higher, and version 3. If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_show_processlist`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `performance_schema_users_size` | Yes | If the **Source** column for the parameter `performance_schema` is set to `Modified`, performance schema is using the parameter `performance_schema_users_size`. For more information about enabling the Performance Schema, see [Determining whether Performance Insights is managing the Performance Schema](USER_PerfInsights.EnableMySQL.determining-status.md). | | `pid_file` | No | None | | `plugin_dir` | No | Aurora MySQL uses managed instances where you don't access the file system directly. | | `port` | No | Aurora MySQL manages the connection properties and enforces consistent settings for all DB instances in a cluster. | | `preload_buffer_size` | Yes | The size of the buffer that's allocated when preloading indexes. This parameter applies to Aurora MySQL version 3. | | `profiling_history_size` | Yes | None | | `query_alloc_block_size` | Yes | None | | `query_cache_limit` | Yes | Removed from Aurora MySQL version 3. | | `query_cache_min_res_unit` | Yes | Removed from Aurora MySQL version 3. | | `query_cache_size` | Yes | The default value is represented by a formula. For details about how the `DBInstanceClassMemory` value in the formula is calculated, see [DB parameter formula variables](USER_ParamValuesRef.md#USER_FormulaVariables). Removed from Aurora MySQL version 3. | | `query_cache_type` | Yes | Removed from Aurora MySQL version 3. | | `query_cache_wlock_invalidate` | Yes | Removed from Aurora MySQL version 3. | | `query_prealloc_size` | Yes | None | | `range_alloc_block_size` | Yes | None | | `read_buffer_size` | Yes | None | | `read_only` | Yes | When this parameter is turned on, the server permits no updates except from those performed by replica threads. For Aurora MySQL version 2, valid values are the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.ParameterGroups.html) We recommend that you use the DB cluster parameter group in Aurora MySQL version 2 to make sure that the `read_only` parameter is applied to new writer instances on failover. Reader instances are always read only, because Aurora MySQL sets `innodb_read_only` to `1` on all readers. Therefore, `read_only` is redundant on reader instances. Removed at the instance level from Aurora MySQL version 3. | | `read_rnd_buffer_size` | Yes | None | | `relay-log` | No | None | | `relay_log_info_repository` | Yes | Removed from Aurora MySQL version 3. | | `relay_log_recovery` | No | None | | `replica_checkpoint_group` | Yes | Aurora MySQL version 3 | | `replica_checkpoint_period` | Yes | Aurora MySQL version 3 | | `replica_parallel_workers` | Yes | Aurora MySQL version 3 | | `replica_pending_jobs_size_max` | Yes | Aurora MySQL version 3 | | `replica_skip_errors` | Yes | Aurora MySQL version 3 | | `replica_sql_verify_checksum` | Yes | Aurora MySQL version 3 | | `safe-user-create` | Yes | None | | `secure_auth` | Yes | This parameter is always turned on in Aurora MySQL version 2. Trying to turn it off generates an error. Removed from Aurora MySQL version 3. | | `secure_file_priv` | No | Aurora MySQL uses managed instances where you don't access the file system directly. | | `show_create_table_verbosity` | Yes | Enabling this variable causes [SHOW\$1CREATE\$1TABLE](https://dev.mysql.com/doc/refman/5.7/en/show-create-table.html) to display the `ROW_FORMAT` regardless of whether it's the default format. This parameter applies to Aurora MySQL version 2.12 and higher, and version 3. | | `skip-slave-start` | No | None | | `skip_external_locking` | No | None | | `skip_show_database` | Yes | None | | `slave_checkpoint_group` | Yes | Aurora MySQL version 2. Use `replica_checkpoint_group` in Aurora MySQL version 3. | | `slave_checkpoint_period` | Yes | Aurora MySQL version 2. Use `replica_checkpoint_period` in Aurora MySQL version 3. | | `slave_parallel_workers` | Yes | Aurora MySQL version 2. Use `replica_parallel_workers` in Aurora MySQL version 3. | | `slave_pending_jobs_size_max` | Yes | Aurora MySQL version 2. Use `replica_pending_jobs_size_max` in Aurora MySQL version 3. | | `slave_sql_verify_checksum` | Yes | Aurora MySQL version 2. Use `replica_sql_verify_checksum` in Aurora MySQL version 3. | | `slow_launch_time` | Yes | None | | `slow_query_log` | Yes | For instructions on uploading the logs to CloudWatch Logs, see [Publishing Amazon Aurora MySQL logs to Amazon CloudWatch Logs](AuroraMySQL.Integrating.CloudWatch.md). | | `slow_query_log_file` | No | Aurora MySQL uses managed instances where you don't access the file system directly. | | `socket` | No | None | | `sort_buffer_size` | Yes | None | | `sql_mode` | Yes | None | | `sql_select_limit` | Yes | None | | `stored_program_cache` | Yes | None | | `sync_binlog` | No | None | | `sync_master_info` | Yes | None | | `sync_source_info` | Yes | This parameter applies to Aurora MySQL version 3. | | `sync_relay_log` | Yes | Removed from Aurora MySQL version 3. | | `sync_relay_log_info` | Yes | None | | `sysdate-is-now` | Yes | None | | `table_cache_element_entry_ttl` | No | None | | `table_definition_cache` | Yes | The default value is represented by a formula. For details about how the `DBInstanceClassMemory` value in the formula is calculated, see [DB parameter formula variables](USER_ParamValuesRef.md#USER_FormulaVariables). | | `table_open_cache` | Yes | The default value is represented by a formula. For details about how the `DBInstanceClassMemory` value in the formula is calculated, see [DB parameter formula variables](USER_ParamValuesRef.md#USER_FormulaVariables). | | `table_open_cache_instances` | Yes | None | | `temp-pool` | Yes | Removed from Aurora MySQL version 3. | | `temptable_max_mmap` | Yes | This parameter applies to Aurora MySQL version 3. For details, see [New temporary table behavior in Aurora MySQL version 3](ams3-temptable-behavior.md). | | `temptable_max_ram` | Yes | This parameter applies to Aurora MySQL version 3. For details, see [New temporary table behavior in Aurora MySQL version 3](ams3-temptable-behavior.md). | | `temptable_use_mmap` | Yes | This parameter applies to Aurora MySQL version 3. For details, see [New temporary table behavior in Aurora MySQL version 3](ams3-temptable-behavior.md). | | `thread_cache_size` | Yes | The number of threads to be cached. This parameter applies to Aurora MySQL versions 2 and 3. | | `thread_handling` | No | None | | `thread_stack` | Yes | None | | `timed_mutexes` | Yes | None | | `tmp_table_size` | Yes | Defines the maximum size of internal in-memory temporary tables created by the `MEMORY` storage engine in Aurora MySQL version 3. In Aurora MySQL version 3.04 and higher, defines the maximum size of internal in-memory temporary tables created by the `TempTable` storage engine when `aurora_tmptable_enable_per_table_limit` is `ON`. For more information, see [Limiting the size of internal, in-memory temporary tables](ams3-temptable-behavior.md#ams3-temptable-behavior-limit). | | `tmpdir` | No | Aurora MySQL uses managed instances where you don't access the file system directly. | | `transaction_alloc_block_size` | Yes | None | | `transaction_isolation` | Yes | This parameter applies to Aurora MySQL version 3. It replaces `tx_isolation`. | | `transaction_prealloc_size` | Yes | None | | `tx_isolation` | Yes | Removed from Aurora MySQL version 3. It is replaced by `transaction_isolation`. | | `updatable_views_with_limit` | Yes | None | | `validate-password` | No | None | | `validate_password_dictionary_file` | No | None | | `validate_password_length` | No | None | | `validate_password_mixed_case_count` | No | None | | `validate_password_number_count` | No | None | | `validate_password_policy` | No | None | | `validate_password_special_char_count` | No | None | | `wait_timeout` | Yes | Aurora evaluates the minimum value of `interactive_timeout` and `wait_timeout`. It then uses that minimum as the timeout to end all idle sessions, both interactive and noninteractive. | ## MySQL parameters that don't apply to Aurora MySQL Because of architectural differences between Aurora MySQL and MySQL, some MySQL parameters don't apply to Aurora MySQL. The following MySQL parameters don't apply to Aurora MySQL. This list isn't exhaustive. + `activate_all_roles_on_login` – This parameter doesn't apply to Aurora MySQL version 2. It is available in Aurora MySQL version 3. + `big_tables` + `bind_address` + `character_sets_dir` + `innodb_adaptive_flushing` + `innodb_adaptive_flushing_lwm` + `innodb_buffer_pool_chunk_size` + `innodb_buffer_pool_instances` + `innodb_change_buffering` + `innodb_checksum_algorithm` + `innodb_data_file_path` + `innodb_dedicated_server` + `innodb_doublewrite` + `innodb_flush_log_at_timeout` – This parameter doesn't apply to Aurora MySQL. For more information, see [Configuring how frequently the log buffer is flushed](AuroraMySQL.BestPractices.FeatureRecommendations.md#AuroraMySQL.BestPractices.Flush). + `innodb_flush_method` + `innodb_flush_neighbors` + `innodb_io_capacity` + `innodb_io_capacity_max` + `innodb_log_buffer_size` + `innodb_log_file_size` + `innodb_log_files_in_group` + `innodb_log_spin_cpu_abs_lwm` + `innodb_log_spin_cpu_pct_hwm` + `innodb_log_writer_threads` + `innodb_max_dirty_pages_pct` + `innodb_numa_interleave` + `innodb_page_size` + `innodb_redo_log_capacity` + `innodb_redo_log_encrypt` + `innodb_undo_log_encrypt` + `innodb_undo_log_truncate` + `innodb_undo_logs` + `innodb_undo_tablespaces` + `innodb_use_native_aio` + `innodb_write_io_threads` # Aurora MySQL global status variables Global status variables Aurora MySQL includes status variables from community MySQL and variables that are unique to Aurora. You can examine these variables to learn about what's happening inside the database engine. For more information about the status variables in community MySQL, see [Server Status Variables](https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html) in the community MySQL 8.0 documentation. You can find the current values for Aurora MySQL global status variables by using a statement such as the following: ``` show global status like '%aurora%'; ``` **Note** Global status variables are cleared when the DB engine reboots. The following table describes the global status variables that Aurora MySQL uses. | Name | Description | | --- | --- | | `AuroraDb_commits` | The total number of commits since the last restart. | | `AuroraDb_commit_latency` | The aggregate commit latency since the last restart. | | `AuroraDb_ddl_stmt_duration` | The aggregate DDL latency since the last restart. | | `AuroraDb_select_stmt_duration` | The aggregate `SELECT` statement latency since the last restart. | | `AuroraDb_insert_stmt_duration` | The aggregate `INSERT` statement latency since the last restart. | | `AuroraDb_update_stmt_duration` | The aggregate `UPDATE` statement latency since the last restart. | | `AuroraDb_delete_stmt_duration` | The aggregate `DELETE` statement latency since the last restart. | | `Aurora_binlog_io_cache_allocated` | The number of bytes allocated to the binlog I/O cache. | | `Aurora_binlog_io_cache_read_requests` | The number of read requests made to the binlog I/O cache. | | `Aurora_binlog_io_cache_reads` | The number of read requests that were served from the binlog I/O cache. | | `Aurora_enhanced_binlog` | Indicates whether enhanced binlog is enabled or disabled for this DB instance. For more information, see [Setting up enhanced binlog for Aurora MySQL](AuroraMySQL.Enhanced.binlog.md). | | `Aurora_external_connection_count` | The number of database connections to the DB instance, excluding RDS service connections used for database health checks. | | `Aurora_fast_insert_cache_hits` | A counter that's incremented when the cached cursor is successfully retrieved and verified. For more information on the fast insert cache, see [Amazon Aurora MySQL performance enhancements](Aurora.AuroraMySQL.Overview.md#Aurora.AuroraMySQL.Performance). | | `Aurora_fast_insert_cache_misses` | A counter that's incremented when the cached cursor is no longer valid and Aurora performs a normal index traversal. For more information on the fast insert cache, see [Amazon Aurora MySQL performance enhancements](Aurora.AuroraMySQL.Overview.md#Aurora.AuroraMySQL.Performance). | | `Aurora_fts_cache_memory_used` | The amount of memory in bytes that the InnoDB full-text search system is using. This variable applies to Aurora MySQL version 3.07 and higher. | | `Aurora_fwd_master_dml_stmt_count` | The total number of DML statements forwarded to this writer DB instance. This variable applies to Aurora MySQL version 2. | | `Aurora_fwd_master_dml_stmt_duration` | The total duration of DML statements forwarded to this writer DB instance. This variable applies to Aurora MySQL version 2. | | `Aurora_fwd_master_errors_rpc_timeout` | The number of times a forwarded connection failed to be established on the writer. | | `Aurora_fwd_master_errors_session_limit` | The number of forwarded queries that get rejected due to `session full` on the writer. | | `Aurora_fwd_master_errors_session_timeout` | The number of times a forwarding session is ended due to a timeout on the writer. | | `Aurora_fwd_master_open_sessions` | The number of forwarded sessions on the writer DB instance. This variable applies to Aurora MySQL version 2. | | `Aurora_fwd_master_select_stmt_count` | The total number of `SELECT` statements forwarded to this writer DB instance. This variable applies to Aurora MySQL version 2. | | `Aurora_fwd_master_select_stmt_duration` | The total duration of `SELECT` statements forwarded to this writer DB instance. This variable applies to Aurora MySQL version 2. | | `Aurora_fwd_writer_dml_stmt_count` | The total number of DML statements forwarded to this writer DB instance. This variable applies to Aurora MySQL version 3. | | `Aurora_fwd_writer_dml_stmt_duration` | The total duration of DML statements forwarded to this writer DB instance. This variable applies to Aurora MySQL version 3. | | `Aurora_fwd_writer_errors_rpc_timeout` | The number of times a forwarded connection failed to be established on the writer. | | `Aurora_fwd_writer_errors_session_limit` | The number of forwarded queries that get rejected due to `session full` on the writer. | | `Aurora_fwd_writer_errors_session_timeout` | The number of times a forwarding session is ended due to a timeout on the writer. | | `Aurora_fwd_writer_open_sessions` | The number of forwarded sessions on the writer DB instance. This variable applies to Aurora MySQL version 3. | | `Aurora_fwd_writer_select_stmt_count` | The total number of `SELECT` statements forwarded to this writer DB instance. This variable applies to Aurora MySQL version 3. | | `Aurora_fwd_writer_select_stmt_duration` | The total duration of `SELECT` statements forwarded to this writer DB instance. This variable applies to Aurora MySQL version 3. | | `Aurora_lockmgr_buffer_pool_memory_used` | The amount of buffer pool memory in bytes that the Aurora MySQL lock manager is using. | | `Aurora_lockmgr_memory_used` | The amount of memory in bytes that the Aurora MySQL lock manager is using. | | `Aurora_ml_actual_request_cnt` | The aggregate request count that Aurora MySQLmakes to the Aurora machine learning services across all queries run by users of the DB instance. For more information, see [Using Amazon Aurora machine learning with Aurora MySQL](mysql-ml.md). | | `Aurora_ml_actual_response_cnt` | The aggregate response count that Aurora MySQL receives from the Aurora machine learning services across all queries run by users of the DB instance. For more information, see [Using Amazon Aurora machine learning with Aurora MySQL](mysql-ml.md). | | `Aurora_ml_cache_hit_cnt` | The aggregate internal cache hit count that Aurora MySQL receives from the Aurora machine learning services across all queries run by users of the DB instance. For more information, see [Using Amazon Aurora machine learning with Aurora MySQL](mysql-ml.md). | | `Aurora_ml_logical_request_cnt` | The number of logical requests that the DB instance has evaluated to be sent to the Aurora machine learning services since the last status reset. Depending on whether batching has been used, this value can be higher than `Aurora_ml_actual_request_cnt`. For more information, see [Using Amazon Aurora machine learning with Aurora MySQL](mysql-ml.md). | | `Aurora_ml_logical_response_cnt` | The aggregate response count that Aurora MySQL receives from the Aurora machine learning services across all queries run by users of the DB instance. For more information, see [Using Amazon Aurora machine learning with Aurora MySQL](mysql-ml.md). | | `Aurora_ml_retry_request_cnt` | The number of retried requests that the DB instance has sent to the Aurora machine learning services since the last status reset. For more information, see [Using Amazon Aurora machine learning with Aurora MySQL](mysql-ml.md). | | `Aurora_ml_single_request_cnt` | The aggregate count of Aurora machine learning functions that are evaluated by non-batch mode across all queries run by users of the DB instance. For more information, see [Using Amazon Aurora machine learning with Aurora MySQL](mysql-ml.md). | | `aurora_oom_avoidance_recovery_state` | Indicates whether Aurora out-of-memory (OOM) avoidance recovery is in the `ACTIVE` or `INACTIVE` state for this DB instance. This variable applies to Aurora MySQL version 3.06.0 and higher. | | `aurora_oom_reserved_mem_enter_kb` | Represents the threshold for entering the `RESERVED` state in Aurora's OOM handling mechanism. When the available memory on the server falls below this threshold, `aurora_oom_status` changes to `RESERVED`, indicating that the server is approaching a critical level of memory usage. This variable applies to Aurora MySQL version 3.06.0 and higher. | | `aurora_oom_reserved_mem_exit_kb` | Represents the threshold for exiting the `RESERVED` state in Aurora's OOM handling mechanism. When the available memory on the server rises above this threshold, `aurora_oom_status` reverts to `NORMAL`, indicating that the server has returned to a more stable state with sufficient memory resources. This variable applies to Aurora MySQL version 3.06.0 and higher. | | `aurora_oom_status` | Represents the current OOM status of this DB instance. When the value is `NORMAL`, it indicates that there are sufficient memory resources. If the value changes to `RESERVED`, it indicates that the server has low available memory. Actions are taken based on the `aurora_oom_response` parameter configuration. For more information, see [Troubleshooting out-of-memory issues for Aurora MySQL databases](AuroraMySQLOOM.md). This variable applies to Aurora MySQL version 3.06.0 and higher. | | `Aurora_pq_bytes_returned` | The number of bytes for the tuple data structures transmitted to the head node during parallel queries. Divide by 16,384 to compare against `Aurora_pq_pages_pushed_down`. | | `Aurora_pq_max_concurrent_requests` | The maximum number of parallel query sessions that can run concurrently on this Aurora DB instance. This is a fixed number that depends on the AWS DB instance class. | | `Aurora_pq_pages_pushed_down` | The number of data pages (each with a fixed size of 16 KiB) where parallel query avoided a network transmission to the head node. | | `Aurora_pq_request_attempted` | The number of parallel query sessions requested. This value might represent more than one session per query, depending on SQL constructs such as subqueries and joins. | | `Aurora_pq_request_executed` | The number of parallel query sessions run successfully. | | `Aurora_pq_request_failed` | The number of parallel query sessions that returned an error to the client. In some cases, a request for a parallel query might fail, for example due to a problem in the storage layer. In these cases, the query part that failed is retried using the nonparallel query mechanism. If the retried query also fails, an error is returned to the client and this counter is incremented. | | `Aurora_pq_request_in_progress` | The number of parallel query sessions currently in progress. This number applies to the particular Aurora DB instance that you are connected to, not the entire Aurora DB cluster. To see if a DB instance is close to its concurrency limit, compare this value to `Aurora_pq_max_concurrent_requests`. | | `Aurora_pq_request_not_chosen` | The number of times parallel query wasn't chosen to satisfy a query. This value is the sum of several other more granular counters. An `EXPLAIN` statement can increment this counter even though the query isn't actually performed. | | `Aurora_pq_request_not_chosen_below_min_rows` | The number of times parallel query wasn't chosen due to the number of rows in the table. An `EXPLAIN` statement can increment this counter even though the query isn't actually performed. | | `Aurora_pq_request_not_chosen_column_bit` | The number of parallel query requests that use the nonparallel query processing path because of an unsupported data type in the list of projected columns. | | `Aurora_pq_request_not_chosen_column_geometry` | The number of parallel query requests that use the nonparallel query processing path because the table has columns with the `GEOMETRY` data type. For information about Aurora MySQL versions that remove this limitation, see [Upgrading parallel query clusters to Aurora MySQL version 3](aurora-mysql-parallel-query-optimizing.md#aurora-mysql-parallel-query-upgrade-pqv2). | | `Aurora_pq_request_not_chosen_column_lob` | The number of parallel query requests that use the nonparallel query processing path because the table has columns with a `LOB` data type, or `VARCHAR` columns that are stored externally due to the declared length. For information about Aurora MySQL versions that remove this limitation, see [Upgrading parallel query clusters to Aurora MySQL version 3](aurora-mysql-parallel-query-optimizing.md#aurora-mysql-parallel-query-upgrade-pqv2). | | `Aurora_pq_request_not_chosen_column_virtual` | The number of parallel query requests that use the nonparallel query processing path because the table contains a virtual column. | | `Aurora_pq_request_not_chosen_custom_charset` | The number of parallel query requests that use the nonparallel query processing path because the table has columns with a custom character set. | | `Aurora_pq_request_not_chosen_fast_ddl` | The number of parallel query requests that use the nonparallel query processing path because the table is currently being altered by a fast DDL `ALTER` statement. | | `Aurora_pq_request_not_chosen_few_pages_outside_buffer_pool` | The number of times parallel query wasn't chosen, even though less than 95 percent of the table data was in the buffer pool, because there wasn't enough unbuffered table data to make parallel query worthwhile. | | `Aurora_pq_request_not_chosen_full_text_index` | The number of parallel query requests that use the nonparallel query processing path because the table has full-text indexes. | | `Aurora_pq_request_not_chosen_high_buffer_pool_pct` | The number of times parallel query wasn't chosen because a high percentage of the table data (currently, greater than 95 percent) was already in the buffer pool. In these cases, the optimizer determines that reading the data from the buffer pool is more efficient. An `EXPLAIN` statement can increment this counter even though the query isn't actually performed. | | `Aurora_pq_request_not_chosen_index_hint` | The number of parallel query requests that use the nonparallel query processing path because the query includes an index hint. | | `Aurora_pq_request_not_chosen_innodb_table_format` | The number of parallel query requests that use the nonparallel query processing path because the table uses an unsupported InnoDB row format. Aurora parallel query only applies to the `COMPACT`, `REDUNDANT`, and `DYNAMIC` row formats. | | `Aurora_pq_request_not_chosen_long_trx` | The number of parallel query requests that used the nonparallel query processing path, due to the query being started inside a long-running transaction. An `EXPLAIN` statement can increment this counter even though the query isn't actually performed. | | `Aurora_pq_request_not_chosen_no_where_clause` | The number of parallel query requests that use the nonparallel query processing path because the query doesn't include any `WHERE` clause. | | `Aurora_pq_request_not_chosen_range_scan` | The number of parallel query requests that use the nonparallel query processing path because the query uses a range scan on an index. | | `Aurora_pq_request_not_chosen_row_length_too_long` | The number of parallel query requests that use the nonparallel query processing path because the total combined length of all the columns is too long. | | `Aurora_pq_request_not_chosen_small_table` | The number of times parallel query wasn't chosen due to the overall size of the table, as determined by number of rows and average row length. An `EXPLAIN` statement can increment this counter even though the query isn't actually performed. | | `Aurora_pq_request_not_chosen_temporary_table` | The number of parallel query requests that use the nonparallel query processing path because the query refers to temporary tables that use the unsupported `MyISAM` or `memory` table types. | | `Aurora_pq_request_not_chosen_tx_isolation` | The number of parallel query requests that use the nonparallel query processing path because query uses an unsupported transaction isolation level. On reader DB instances, parallel query only applies to the `REPEATABLE READ` and `READ COMMITTED` isolation levels. | | `Aurora_pq_request_not_chosen_update_delete_stmts` | The number of parallel query requests that use the nonparallel query processing path because the query is part of an `UPDATE` or `DELETE` statement. | | `Aurora_pq_request_not_chosen_unsupported_access` | The number of parallel query requests that use the nonparallel query processing path because the `WHERE` clause doesn't meet the criteria for parallel query. This result can occur if the query doesn't require a data-intensive scan, or if the query is a `DELETE` or `UPDATE` statement. | | `Aurora_pq_request_not_chosen_unsupported_storage_type` | The number of parallel query requests that use the nonparallel query processing path because the Aurora MySQL DB cluster isn't using a supported Aurora cluster storage configuration. For more information, see [Limitations](aurora-mysql-parallel-query.md#aurora-mysql-parallel-query-limitations). This parameter applies to Aurora MySQL version 3.04 and higher. | | `Aurora_pq_request_throttled` | The number of times parallel query wasn't chosen due to the maximum number of concurrent parallel queries already running on a particular Aurora DB instance. | | `Aurora_repl_bytes_received` | Number of bytes replicated to an Aurora MySQL reader database instance since the last restart. For more information, see [Replication with Amazon Aurora MySQL](AuroraMySQL.Replication.md). | | `Aurora_reserved_mem_exceeded_incidents` | The number of times since the last restart that the engine has exceeded reserved memory limits. If `aurora_oom_response` is configured, this threshold defines when out-of-memory (OOM) avoidance activities are triggered. For more information on the Aurora MySQL OOM response, see [Troubleshooting out-of-memory issues for Aurora MySQL databases](AuroraMySQLOOM.md). | | `aurora_temptable_max_ram_allocation` | The maximum amount of memory, in bytes, used at any point by internal temporary tables since the last restart. | | `aurora_temptable_ram_allocation` | The current amount of memory, in bytes, used by internal temporary tables. | | `Aurora_in_memory_relaylog_status` | The current status of in memory relay log feature, the value can be ENABLED or DISABLED. | | `Aurora_in_memory_relaylog_disabled_reason` | Shows the reason of current in memory relay log feature status, if the feature is disabled, display a message of explanation on why the feature is disabled. | | `Aurora_in_memory_relaylog_fallback_count` | Show the total number of fallbacks of in memory relay log feature to persistent relay log mode (legacy). Fallback can be caused by either single event larger than cache size (currently 128MB) or transaction retry exceed the replica transaction retry limit replica\$1transaction\$1retries. | | `Aurora_in_memory_relaylog_recovery_count` | Shows the total number of in memory relay log recovery performed automatically. This count includes the total number of fallbacks and the number of automatic mode switch back to in memory relay log mode after the temporary fallbacks. | | `Aurora_thread_pool_thread_count` | The current number of threads in the Aurora thread pool. For more information on the thread pool in Aurora MySQL, see [Thread pool](AuroraMySQL.Managing.Tuning.concepts.md#AuroraMySQL.Managing.Tuning.concepts.processes.pool). | | `Aurora_tmz_version` | Denotes the current version of the time zone information used by the DB cluster. The values follow the Internet Assigned Numbers Authority (IANA) format: `YYYYsuffix`, for example `2022a` and `2023c`. This parameter applies to Aurora MySQL version 2.12 and higher, and version 3.04 and higher. | | `Aurora_zdr_oom_threshold` | Represents the memory threshold, in kilobytes (KB), for an Aurora DB instance to initiate a zero downtime restart (ZDR) to recover from potential memory-related issues. | | `server_aurora_das_running` | Indicates whether Database Activity Streams (DAS) are enabled or disabled on this DB instance. For more information, see [Monitoring Amazon Aurora with Database Activity Streams](DBActivityStreams.md). | ## MySQL status variables that don't apply to Aurora MySQL Because of architectural differences between Aurora MySQL and MySQL, some MySQL status variables don't apply to Aurora MySQL. The following MySQL status variables don't apply to Aurora MySQL. This list isn't exhaustive. + `innodb_buffer_pool_bytes_dirty` + `innodb_buffer_pool_pages_dirty` + `innodb_buffer_pool_pages_flushed` Aurora MySQL version 3 removes the following status variables that were in Aurora MySQL version 2: + `AuroraDb_lockmgr_bitmaps0_in_use` + `AuroraDb_lockmgr_bitmaps1_in_use` + `AuroraDb_lockmgr_bitmaps_mem_used` + `AuroraDb_thread_deadlocks` + `available_alter_table_log_entries` + `Aurora_lockmgr_memory_used` + `Aurora_missing_history_on_replica_incidents` + `Aurora_new_lock_manager_lock_release_cnt` + `Aurora_new_lock_manager_lock_release_total_duration_micro` + `Aurora_new_lock_manager_lock_timeout_cnt` + `Aurora_total_op_memory` + `Aurora_total_op_temp_space` + `Aurora_used_alter_table_log_entries` + `Aurora_using_new_lock_manager` + `Aurora_volume_bytes_allocated` + `Aurora_volume_bytes_left_extent` + `Aurora_volume_bytes_left_total` + `Com_alter_db_upgrade` + `Compression` + `External_threads_connected` + `Innodb_available_undo_logs` + `Last_query_cost` + `Last_query_partial_plans` + `Slave_heartbeat_period` + `Slave_last_heartbeat` + `Slave_received_heartbeats` + `Slave_retried_transactions` + `Slave_running` + `Time_since_zero_connections` These MySQL status variables are available in Aurora MySQL version 2, but they aren't available in Aurora MySQL version 3: + `Innodb_redo_log_enabled` + `Innodb_undo_tablespaces_total` + `Innodb_undo_tablespaces_implicit` + `Innodb_undo_tablespaces_explicit` + `Innodb_undo_tablespaces_active` # Aurora MySQL wait events Wait events The following are some common wait events for Aurora MySQL. **Note** For information on tuning Aurora MySQL performance using wait events, see [Tuning Aurora MySQL with wait events](AuroraMySQL.Managing.Tuning.wait-events.md). For information about the naming conventions used in MySQL wait events, see [ Performance Schema instrument naming conventions](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-instrument-naming.html) in the MySQL documentation. **cpu** The number of active connections that are ready to run is consistently higher than the number of vCPUs. For more information, see [cpu](ams-waits.cpu.md). **io/aurora\$1redo\$1log\$1flush** A session is persisting data to Aurora storage. Typically, this wait event is for a write I/O operation in Aurora MySQL. For more information, see [io/aurora\$1redo\$1log\$1flush](ams-waits.io-auredologflush.md). **io/aurora\$1respond\$1to\$1client** Query processing has completed and results are being returned to the application client for the following Aurora MySQL versions: 2.10.2 and higher 2.10 versions, 2.09.3 and higher 2.09 versions, and 2.07.7 and higher 2.07 versions. Compare the network bandwidth of the DB instance class with the size of the result set being returned. Also, check client-side response times. If the client is unresponsive and can't process the TCP packets, packet drops and TCP retransmissions can occur. This situation negatively affects network bandwidth. In versions lower than 2.10.2, 2.09.3, and 2.07.7, the wait event erroneously includes idle time. To learn how to tune your database when this wait is prominent, see [io/aurora\$1respond\$1to\$1client](ams-waits.respond-to-client.md). **io/file/csv/data** Threads are writing to tables in comma-separated value (CSV) format. Check your CSV table usage. A typical cause of this event is setting `log_output` on a table. **io/file/sql/binlog** A thread is waiting on a binary log (binlog) file that is being written to disk. **io/redo\$1log\$1flush** A session is persisting data to Aurora storage. Typically, this wait event is for a write I/O operation in Aurora MySQL. For more information, see [io/redo\$1log\$1flush](ams-waits.io-redologflush.md). **io/socket/sql/client\$1connection** The `mysqld` program is busy creating threads to handle incoming new client connections. For more information, see [io/socket/sql/client\$1connection](ams-waits.client-connection.md). **io/table/sql/handler** The engine is waiting for access to a table. This event occurs regardless of whether the data is cached in the buffer pool or accessed on disk. For more information, see [io/table/sql/handler](ams-waits.waitio.md). **lock/table/sql/handler** This wait event is a table lock wait event handler. For more information about atom and molecule events in the Performance Schema, see [ Performance Schema atom and molecule events](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-atom-molecule-events.html) in the MySQL documentation. **synch/cond/innodb/row\$1lock\$1wait** Multiple data manipulation language (DML) statements are accessing the same database rows at the same time. For more information, see [synch/cond/innodb/row\$1lock\$1wait](ams-waits.row-lock-wait.md). **synch/cond/innodb/row\$1lock\$1wait\$1cond** Multiple DML statements are accessing the same database rows at the same time. For more information, see [synch/cond/innodb/row\$1lock\$1wait\$1cond](ams-waits.row-lock-wait-cond.md). **synch/cond/sql/MDL\$1context::COND\$1wait\$1status** Threads are waiting on a table metadata lock. The engine uses this type of lock to manage concurrent access to a database schema and to ensure data consistency. For more information, see [Optimizing locking operations](https://dev.mysql.com/doc/refman/8.0/en/locking-issues.html) in the MySQL documentation. To learn how to tune your database when this event is prominent, see [synch/cond/sql/MDL\$1context::COND\$1wait\$1status](ams-waits.cond-wait-status.md). **synch/cond/sql/MYSQL\$1BIN\$1LOG::COND\$1done** You have turned on binary logging. There might be a high commit throughput, large number transactions committing, or replicas reading binlogs. Consider using multirow statements or bundling statements into one transaction. In Aurora, use global databases instead of binary log replication, or use the `aurora_binlog_*` parameters. **synch/mutex/innodb/aurora\$1lock\$1thread\$1slot\$1futex** Multiple DML statements are accessing the same database rows at the same time. For more information, see [synch/mutex/innodb/aurora\$1lock\$1thread\$1slot\$1futex](ams-waits.waitsynch.md). **synch/mutex/innodb/buf\$1pool\$1mutex** The buffer pool isn't large enough to hold the working data set. Or the workload accesses pages from a specific table, which leads to contention in the buffer pool. For more information, see [synch/mutex/innodb/buf\$1pool\$1mutex](ams-waits.bufpoolmutex.md). **synch/mutex/innodb/fil\$1system\$1mutex** The process is waiting for access to the tablespace memory cache. For more information, see [synch/mutex/innodb/fil\$1system\$1mutex](ams-waits.innodb-fil-system-mutex.md). **synch/mutex/innodb/trx\$1sys\$1mutex** Operations are checking, updating, deleting, or adding transaction IDs in InnoDB in a consistent or controlled manner. These operations require a `trx_sys` mutex call, which is tracked by Performance Schema instrumentation. Operations include management of the transaction system when the database starts or shuts down, rollbacks, undo cleanups, row read access, and buffer pool loads. High database load with a large number of transactions results in the frequent appearance of this wait event. For more information, see [synch/mutex/innodb/trx\$1sys\$1mutex](ams-waits.trxsysmutex.md). **synch/mutex/mysys/KEY\$1CACHE::cache\$1lock** The `keycache->cache_lock` mutex controls access to the key cache for MyISAM tables. While Aurora MySQL doesn't allow usage of MyISAM tables to store persistent data, they are used to store internal temporary tables. Consider checking the `created_tmp_tables` or `created_tmp_disk_tables` status counters, because in certain situations, temporary tables are written to disk when they no longer fit in memory. **synch/mutex/sql/FILE\$1AS\$1TABLE::LOCK\$1offsets** The engine acquires this mutex when opening or creating a table metadata file. When this wait event occurs with excessive frequency, the number of tables being created or opened has spiked. **synch/mutex/sql/FILE\$1AS\$1TABLE::LOCK\$1shim\$1lists** The engine acquires this mutex while performing operations such as `reset_size`, `detach_contents`, or `add_contents` on the internal structure that keeps track of opened tables. The mutex synchronizes access to the list contents. When this wait event occurs with high frequency, it indicates a sudden change in the set of tables that were previously accessed. The engine needs to access new tables or let go of the context related to previously accessed tables. **synch/mutex/sql/LOCK\$1open** The number of tables that your sessions are opening exceeds the size of the table definition cache or the table open cache. Increase the size of these caches. For more information, see [How MySQL opens and closes tables](https://dev.mysql.com/doc/refman/8.0/en/table-cache.html). **synch/mutex/sql/LOCK\$1table\$1cache** The number of tables that your sessions are opening exceeds the size of the table definition cache or the table open cache. Increase the size of these caches. For more information, see [How MySQL opens and closes tables](https://dev.mysql.com/doc/refman/8.0/en/table-cache.html). **synch/mutex/sql/LOG** In this wait event, there are threads waiting on a log lock. For example, a thread might wait for a lock to write to the slow query log file. **synch/mutex/sql/MYSQL\$1BIN\$1LOG::LOCK\$1commit** In this wait event, there is a thread that is waiting to acquire a lock with the intention of committing to the binary log. Binary logging contention can occur on databases with a very high change rate. Depending on your version of MySQL, there are certain locks being used to protect the consistency and durability of the binary log. In RDS for MySQL, binary logs are used for replication and the automated backup process. In Aurora MySQL, binary logs are not needed for native replication or backups. They are disabled by default but can be enabled and used for external replication or change data capture. For more information, see [The binary log](https://dev.mysql.com/doc/refman/8.0/en/binary-log.html) in the MySQL documentation. **sync/mutex/sql/MYSQL\$1BIN\$1LOG::LOCK\$1dump\$1thread\$1metrics\$1collection** If binary logging is turned on, the engine acquires this mutex when it prints active dump threads metrics to the engine error log and to the internal operations map. **sync/mutex/sql/MYSQL\$1BIN\$1LOG::LOCK\$1inactive\$1binlogs\$1map** If binary logging is turned on, the engine acquires this mutex when it adds to, deletes from, or searches through the list of binlog files behind the latest one. **sync/mutex/sql/MYSQL\$1BIN\$1LOG::LOCK\$1io\$1cache** If binary logging is turned on, the engine acquires this mutex during Aurora binlog IO cache operations: allocate, resize, free, write, read, purge, and access cache info. If this event occurs frequently, the engine is accessing the cache where binlog events are stored. To reduce wait times, reduce commits. Try grouping multiple statements into a single transaction. **synch/mutex/sql/MYSQL\$1BIN\$1LOG::LOCK\$1log** You have turned on binary logging. There might be high commit throughput, many transactions committing, or replicas reading binlogs. Consider using multirow statements or bundling statements into one transaction. In Aurora, use global databases instead of binary log replication or use the `aurora_binlog_*` parameters. **synch/mutex/sql/SERVER\$1THREAD::LOCK\$1sync** The mutex `SERVER_THREAD::LOCK_sync` is acquired during the scheduling, processing, or launching of threads for file writes. The excessive occurrence of this wait event indicates increased write activity in the database. **synch/mutex/sql/TABLESPACES:lock** The engine acquires the `TABLESPACES:lock` mutex during the following tablespace operations: create, delete, truncate, and extend. The excessive occurrence of this wait event indicates a high frequency of tablespace operations. An example is loading a large amount of data into the database. **synch/rwlock/innodb/dict** In this wait event, there are threads waiting on an rwlock held on the InnoDB data dictionary. **synch/rwlock/innodb/dict\$1operation\$1lock** In this wait event, there are threads holding locks on InnoDB data dictionary operations. **synch/rwlock/innodb/dict sys RW lock** A high number of concurrent data control language statements (DCLs) in data definition language code (DDLs) are triggered at the same time. Reduce the application's dependency on DDLs during regular application activity. **synch/rwlock/innodb/index\$1tree\$1rw\$1lock** A large number of similar data manipulation language (DML) statements are accessing the same database object at the same time. Try using multirow statements. Also, spread the workload over different database objects. For example, implement partitioning. **synch/sxlock/innodb/dict\$1operation\$1lock** A high number of concurrent data control language statements (DCLs) in data definition language code (DDLs) are triggered at the same time. Reduce the application's dependency on DDLs during regular application activity. **synch/sxlock/innodb/dict\$1sys\$1lock** A high number of concurrent data control language statements (DCLs) in data definition language code (DDLs) are triggered at the same time. Reduce the application's dependency on DDLs during regular application activity. **synch/sxlock/innodb/hash\$1table\$1locks** The session couldn't find pages in the buffer pool. The engine either needs to read a file or modify the least-recently used (LRU) list for the buffer pool. Consider increasing the buffer cache size and improving access paths for the relevant queries. **synch/sxlock/innodb/index\$1tree\$1rw\$1lock** Many similar data manipulation language (DML) statements are accessing the same database object at the same time. Try using multirow statements. Also, spread the workload over different database objects. For example, implement partitioning. **synch/mutex/innodb/temp\$1pool\$1manager\$1mutex** This wait event occurs when a session is waiting to acquire a mutex for managing the pool of session temporary tablespaces. For more information on troubleshooting synch wait events, see [Why is my MySQL DB instance showing a high number of active sessions waiting on SYNCH wait events in Performance Insights?](https://aws.amazon.com/premiumsupport/knowledge-center/aurora-mysql-synch-wait-events/). # Aurora MySQL thread states Thread states The following are some common thread states for Aurora MySQL. **checking permissions** The thread is checking whether the server has the required privileges to run the statement. **checking query cache for query** The server is checking whether the current query is present in the query cache. **cleaned up** This is the final state of a connection whose work is complete but which hasn't been closed by the client. The best solution is to explicitly close the connection in code. Or you can set a lower value for `wait_timeout` in your parameter group. **closing tables** The thread is flushing the changed table data to disk and closing the used tables. If this isn't a fast operation, verify the network bandwidth consumption metrics against the instance class network bandwidth. Also, check that the parameter values for `table_open_cache` and `table_definition_cache` parameter allow for enough tables to be simultaneously open so that the engine doesn't need to open and close tables frequently. These parameters influence the memory consumption on the instance. **converting HEAP to MyISAM** The query is converting a temporary table from in-memory to on-disk. This conversion is necessary because the temporary tables created by MySQL in the intermediate steps of query processing grew too big for memory. Check the values of `tmp_table_size` and `max_heap_table_size`. In later versions, this thread state name is `converting HEAP to ondisk`. **converting HEAP to ondisk** The thread is converting an internal temporary table from an in-memory table to an on-disk table. **copy to tmp table** The thread is processing an `ALTER TABLE` statement. This state occurs after the table with the new structure has been created but before rows are copied into it. For a thread in this state, you can use the Performance Schema to obtain information about the progress of the copy operation. **creating sort index** Aurora MySQL is performing a sort because it can't use an existing index to satisfy the `ORDER BY` or `GROUP BY` clause of a query. For more information, see [creating sort index](ams-states.sort-index.md). **creating table** The thread is creating a permanent or temporary table. **delayed commit ok done** An asynchronous commit in Aurora MySQL has received an acknowledgement and is complete. **delayed commit ok initiated** The Aurora MySQL thread has started the async commit process but is waiting for acknowledgement. This is usually the genuine commit time of a transaction. **delayed send ok done** An Aurora MySQL worker thread that is tied to a connection can be freed while a response is sent to the client. The thread can begin other work. The state `delayed send ok` means that the asynchronous acknowledgement to the client completed. **delayed send ok initiated** An Aurora MySQL worker thread has sent a response asynchronously to a client and is now free to do work for other connections. The transaction has started an async commit process that hasn't yet been acknowledged. **executing** The thread has begun running a statement. **freeing items** The thread has run a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up. **init** This state occurs before the initialization of `ALTER TABLE`, `DELETE`, `INSERT`, `SELECT`, or `UPDATE` statements. Actions in this state include flushing the binary log or InnoDB log, and some cleanup of the query cache. **Source has sent all binlog to replica; waiting for more updates** The primary node has finished its part of the replication. The thread is waiting for more queries to run so that it can write to the binary log (binlog). **opening tables** The thread is trying to open a table. This operation is fast unless an `ALTER TABLE` or a `LOCK TABLE` statement needs to finish, or it exceeds the value of `table_open_cache`. **optimizing** The server is performing initial optimizations for a query. **preparing** This state occurs during query optimization. **query end** This state occurs after processing a query but before the freeing items state. **removing duplicates** Aurora MySQL couldn't optimize a `DISTINCT` operation in the early stage of a query. Aurora MySQL must remove all duplicated rows before sending the result to the client. **searching rows for update** The thread is finding all matching rows before updating them. This stage is necessary if the `UPDATE` is changing the index that the engine uses to find the rows. **sending binlog event to slave** The thread read an event from the binary log and is sending it to the replica. **sending cached result to client** The server is taking the result of a query from the query cache and sending it to the client. **sending data** The thread is reading and processing rows for a `SELECT` statement but hasn't yet started sending data to the client. The process is identifying which pages contain the results necessary to satisfy the query. For more information, see [sending data](ams-states.sending-data.md). **sending to client** The server is writing a packet to the client. In earlier MySQL versions, this wait event was labeled `writing to net`. **starting** This is the first stage at the beginning of statement execution. **statistics** The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long time, the server is probably disk-bound while performing other work. **storing result in query cache** The server is storing the result of a query in the query cache. **system lock** The thread has called `mysql_lock_tables`, but the thread state hasn't been updated since the call. This general state occurs for many reasons. **update** The thread is preparing to start updating the table. **updating** The thread is searching for rows and is updating them. **user lock** The thread issued a `GET_LOCK` call. The thread either requested an advisory lock and is waiting for it, or is planning to request it. **waiting for more updates** The primary node has finished its part of the replication. The thread is waiting for more queries to run so that it can write to the binary log (binlog). **waiting for schema metadata lock** This is a wait for a metadata lock. **waiting for stored function metadata lock** This is a wait for a metadata lock. **waiting for stored procedure metadata lock** This is a wait for a metadata lock. **waiting for table flush** The thread is executing `FLUSH TABLES` and is waiting for all threads to close their tables. Or the thread received notification that the underlying structure for a table changed, so it must reopen the table to get the new structure. To reopen the table, the thread must wait until all other threads have closed the table. This notification takes place if another thread has used one of the following statements on the table: `FLUSH TABLES`, `ALTER TABLE`, `RENAME TABLE`, `REPAIR TABLE`, `ANALYZE TABLE`, or `OPTIMIZE TABLE`. **waiting for table level lock** One session is holding a lock on a table while another session tries to acquire the same lock on the same table. **waiting for table metadata lock** Aurora MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. In this wait event, one session is holding a metadata lock on a table while another session tries to acquire the same lock on the same table. When the Performance Schema is enabled, this thread state is reported as the wait event `synch/cond/sql/MDL_context::COND_wait_status`. **writing to net** The server is writing a packet to the network. In later MySQL versions, this wait event is labeled `Sending to client`. # Aurora MySQL isolation levels Isolation levels Learn how DB instances in an Aurora MySQL cluster implement the database property of isolation. This topic explains how the Aurora MySQL default behavior balances between strict consistency and high performance. You can use this information to help you decide when to change the default settings based on the traits of your workload. ## Available isolation levels for writer instances You can use the isolation levels `REPEATABLE READ`, `READ COMMITTED`, `READ UNCOMMITTED`, and `SERIALIZABLE` on the primary instance of an Aurora MySQL DB cluster. These isolation levels work the same in Aurora MySQL as in RDS for MySQL. ## REPEATABLE READ isolation level for reader instances By default, Aurora MySQL DB instances that are configured as read-only Aurora Replicas always use the `REPEATABLE READ` isolation level. These DB instances ignore any `SET TRANSACTION ISOLATION LEVEL` statements and continue using the `REPEATABLE READ` isolation level. ## READ COMMITTED isolation level for reader instances If your application includes a write-intensive workload on the primary instance and long-running queries on the Aurora Replicas, you might experience substantial purge lag. *Purge lag* happens when internal garbage collection is blocked by long-running queries. The symptom that you see is a high value for `history list length` in the output from the `SHOW ENGINE INNODB STATUS` command. You can monitor this value using the `RollbackSegmentHistoryListLength` metric in CloudWatch. Substantial purge lag can reduce the effectiveness of secondary indexes, decrease overall query performance, and lead to wasted storage space. If you experience such issues, you can set an Aurora MySQL session-level configuration setting, `aurora_read_replica_read_committed`, to use the `READ COMMITTED` isolation level on Aurora Replicas. When you apply this setting, you can help reduce slowdowns and wasted space that can result from performing long-running queries at the same time as transactions that modify your tables. We recommend making sure that you understand the specific Aurora MySQL behavior of the `READ COMMITTED` isolation before using this setting. The Aurora Replica `READ COMMITTED` behavior complies with the ANSI SQL standard. However, the isolation is less strict than typical MySQL `READ COMMITTED` behavior that you might be familiar with. Therefore, you might see different query results under `READ COMMITTED` on an Aurora MySQL read replica than you might see for the same query under `READ COMMITTED` on the Aurora MySQL primary instance or on RDS for MySQL. You might consider using the `aurora_read_replica_read_committed` setting for such cases as a comprehensive report that scans a very large database. In contrast, you might avoid it for short queries with small result sets, where precision and repeatability are important. The `READ COMMITTED` isolation level isn't available for sessions within a secondary cluster in an Aurora global database that use the write forwarding feature. For information about write forwarding, see [Using write forwarding in an Amazon Aurora global database](aurora-global-database-write-forwarding.md). ### Using READ COMMITTED for readers To use the `READ COMMITTED` isolation level for Aurora Replicas, set the `aurora_read_replica_read_committed` configuration setting to `ON`. Use this setting at the session level while connected to a specific Aurora Replica. To do so, run the following SQL commands. ``` set session aurora_read_replica_read_committed = ON; set session transaction isolation level read committed; ``` You might use this configuration setting temporarily to perform interactive, one-time queries. You might also want to run a reporting or data analysis application that benefits from the `READ COMMITTED` isolation level, while leaving the default setting unchanged for other applications. When the `aurora_read_replica_read_committed` setting is turned on, use the `SET TRANSACTION ISOLATION LEVEL` command to specify the isolation level for the appropriate transactions. ``` set transaction isolation level read committed; ``` ### Differences in READ COMMITTED behavior on Aurora replicas The `aurora_read_replica_read_committed` setting makes the `READ COMMITTED` isolation level available for an Aurora Replica, with consistency behavior that is optimized for long-running transactions. The `READ COMMITTED` isolation level on Aurora Replicas has less strict isolation than on Aurora primary instances. For that reason, enable this setting only on Aurora Replicas where you know that your queries can accept the possibility of certain types of inconsistent results. Your queries can experience certain kinds of read anomalies when the `aurora_read_replica_read_committed` setting is turned on. Two kinds of anomalies are especially important to understand and handle in your application code. A *non-repeatable read* occurs when another transaction commits while your query is running. A long-running query can see different data at the start of the query than it sees at the end. A *phantom read* occurs when other transactions cause existing rows to be reorganized while your query is running, and one or more rows are read twice by your query. Your queries might experience inconsistent row counts as a result of phantom reads. Your queries might also return incomplete or inconsistent results due to non-repeatable reads. For example, suppose that a join operation refers to tables that are concurrently modified by SQL statements such as `INSERT` or `DELETE`. In this case, the join query might read a row from one table but not the corresponding row from another table. The ANSI SQL standard allows both of these behaviors for the `READ COMMITTED` isolation level. However, those behaviors are different than the typical MySQL implementation of `READ COMMITTED`. Thus, before enabling the `aurora_read_replica_read_committed` setting, check any existing SQL code to verify if it operates as expected under the looser consistency model. Row counts and other results might not be strongly consistent under the `READ COMMITTED` isolation level while this setting is enabled. Thus, you typically enable the setting only while running analytic queries that aggregate large amounts of data and don't require absolute precision. If you don't have these kinds of long-running queries alongside a write-intensive workload, you probably don't need the `aurora_read_replica_read_committed` setting. Without the combination of long-running queries and a write-intensive workload, you're unlikely to encounter issues with the length of the history list. **Example Queries showing isolation behavior for READ COMMITTED on Aurora Replicas** The following example shows how `READ COMMITTED` queries on an Aurora Replica might return non-repeatable results if transactions modify the associated tables at the same time. The table `BIG_TABLE` contains 1 million rows before any queries start. Other data manipulation language (DML) statements add, remove, or change rows while they are running. The queries on the Aurora primary instance under the `READ COMMITTED` isolation level produce predictable results. However, the overhead of keeping the consistent read view for the lifetime of every long-running query can lead to expensive garbage collection later. The queries on the Aurora Replica under the `READ COMMITTED` isolation level are optimized to minimize this garbage collection overhead. The tradeoff is that the results might vary depending on whether the queries retrieve rows that are added, removed, or reorganized by transactions that are committed while the query is running. The queries are allowed to consider these rows, but aren't required to. For demonstration purposes, the queries check only the number of rows in the table by using the `COUNT(*)` function. | Time | DML statement on Aurora primary instance | Query on Aurora primary instance with READ COMMITTED | Query on Aurora Replica with READ COMMITTED | | --- | --- | --- | --- | | T1 | INSERT INTO big\$1table SELECT \$1 FROM other\$1table LIMIT 1000000; COMMIT; | | | | T2 | | Q1: SELECT COUNT(\$1) FROM big\$1table; | Q2: SELECT COUNT(\$1) FROM big\$1table; | | T3 | INSERT INTO big\$1table (c1, c2) VALUES (1, 'one more row'); COMMIT; | | | | T4 | | If Q1 finishes now, result is 1,000,000. | If Q2 finishes now, result is 1,000,000 or 1,000,001. | | T5 | DELETE FROM big\$1table LIMIT 2; COMMIT; | | | | T6 | | If Q1 finishes now, result is 1,000,000. | If Q2 finishes now, result is 1,000,000 or 1,000,001 or 999,999 or 999,998. | | T7 | UPDATE big\$1table SET c2 = CONCAT(c2,c2,c2); COMMIT; | | | | T8 | | If Q1 finishes now, result is 1,000,000. | If Q2 finishes now, result is 1,000,000 or 1,000,001 or 999,999, or possibly some higher number. | | T9 | | Q3: SELECT COUNT(\$1) FROM big\$1table; | Q4: SELECT COUNT(\$1) FROM big\$1table; | | T10 | | If Q3 finishes now, result is 999,999. | If Q4 finishes now, result is 999,999. | | T11 | | Q5: SELECT COUNT(\$1) FROM parent\$1table p JOIN child\$1table c ON (p.id = c.id) WHERE p.id = 1000; | Q6: SELECT COUNT(\$1) FROM parent\$1table p JOIN child\$1table c ON (p.id = c.id) WHERE p.id = 1000; | | T12 | INSERT INTO parent\$1table (id, s) VALUES (1000, 'hello'); INSERT INTO child\$1table (id, s) VALUES (1000, 'world'); COMMIT; | | | | T13 | | If Q5 finishes now, result is 0. | If Q6 finishes now, result is 0 or 1. | If the queries finish quickly, before any other transactions perform DML statements and commit, the results are predictable and the same between the primary instance and the Aurora Replica. Let's examine the differences in behavior in detail, starting with the first query. The results for Q1 are highly predictable because `READ COMMITTED` on the primary instance uses a strong consistency model similar to the `REPEATABLE READ` isolation level. The results for Q2 might vary depending on what transactions are committed while that query is running. For example, suppose that other transactions perform DML statements and commit while the queries are running. In this case, the query on the Aurora Replica with the `READ COMMITTED` isolation level might or might not take the changes into account. The row counts aren't predictable in the same way as under the `REPEATABLE READ` isolation level. They also aren't as predictable as queries running under the `READ COMMITTED` isolation level on the primary instance, or on an RDS for MySQL instance. The `UPDATE` statement at T7 doesn't actually change the number of rows in the table. However, by changing the length of a variable-length column, this statement can cause rows to be reorganized internally. A long-running `READ COMMITTED` transaction might see the old version of a row, and later within the same query see the new version of the same row. The query can also skip both the old and new versions of the row, so the row count might be different than expected. The results of Q5 and Q6 might be identical or slightly different. Query Q6 on the Aurora Replica under `READ COMMITTED` is able to see, but is not required to see, the new rows that are committed while the query is running. It might also see the row from one table, but not from the other table. If the join query doesn't find a matching row in both tables, it returns a count of zero. If the query does find both the new rows in `PARENT_TABLE` and `CHILD_TABLE`, the query returns a count of one. In a long-running query, the lookups from the joined tables might happen at widely separated times. These differences in behavior depend on the timing of when transactions are committed and when the queries process the underlying table rows. Thus, you're most likely to see such differences in report queries that take minutes or hours and that run on Aurora clusters processing OLTP transactions at the same time. These are the kinds of mixed workloads that benefit the most from the `READ COMMITTED` isolation level on Aurora Replicas. # Aurora MySQL hints Hints You can use SQL hints with Aurora MySQL queries to fine-tune performance. You can also use hints to prevent execution plans for important queries from changing because of unpredictable conditions. **Tip** To verify the effect that a hint has on a query, examine the query plan produced by the `EXPLAIN` statement. Compare the query plans with and without the hint. In Aurora MySQL version 3, you can use all the hints that are available in MySQL Community Edition 8.0. For more information about these hints, see [Optimizer Hints](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html) in the *MySQL Reference Manual*. The following hints are available in Aurora MySQL version 2. These hints apply to queries that use the hash join feature in Aurora MySQL version 2, especially queries that use parallel query optimization. **PQ, NO\$1PQ** Specifies whether to force the optimizer to use parallel query on a per-table or per-query basis. `PQ` forces the optimizer to use parallel query for specified tables or the whole query (block). `NO_PQ` prevents the optimizer from using parallel query for specified tables or the whole query (block). This hint is available in Aurora MySQL version 2.11 and higher. The following examples show you how to use this hint. Specifying a table name forces the optimizer to apply the `PQ/NO_PQ` hint only on those select tables. Not specifying a table name forces the `PQ/NO_PQ` hint on all tables affected by the query block. ``` EXPLAIN SELECT /*+ PQ() */ f1, f2 FROM num1 t1 WHERE f1 > 10 and f2 < 100; EXPLAIN SELECT /*+ PQ(t1) */ f1, f2 FROM num1 t1 WHERE f1 > 10 and f2 < 100; EXPLAIN SELECT /*+ PQ(t1,t2) */ f1, f2 FROM num1 t1, num1 t2 WHERE t1.f1 = t2.f21; EXPLAIN SELECT /*+ NO_PQ() */ f1, f2 FROM num1 t1 WHERE f1 > 10 and f2 < 100; EXPLAIN SELECT /*+ NO_PQ(t1) */ f1, f2 FROM num1 t1 WHERE f1 > 10 and f2 < 100; EXPLAIN SELECT /*+ NO_PQ(t1,t2) */ f1, f2 FROM num1 t1, num1 t2 WHERE t1.f1 = t2.f21; ``` **HASH\$1JOIN, NO\$1HASH\$1JOIN** Turns on or off the ability of the parallel query optimizer to choose whether to use the hash join optimization method for a query. `HASH_JOIN` lets the optimizer use hash join if that mechanism is more efficient. `NO_HASH_JOIN` prevents the optimizer from using hash join for the query. This hint is available in Aurora MySQL version 2.08 and higher. It has no effect in Aurora MySQL version 3. The following examples show you how to use this hint. ``` EXPLAIN SELECT/*+ HASH_JOIN(t2) */ f1, f2 FROM t1, t2 WHERE t1.f1 = t2.f1; EXPLAIN SELECT /*+ NO_HASH_JOIN(t2) */ f1, f2 FROM t1, t2 WHERE t1.f1 = t2.f1; ``` **HASH\$1JOIN\$1PROBING, NO\$1HASH\$1JOIN\$1PROBING** In a hash join query, specifies whether to use the specified table for the probe side of the join. The query tests if column values from the build table exist in the probe table, instead of reading the entire contents of the probe table. You can use `HASH_JOIN_PROBING` and `HASH_JOIN_BUILDING` to specify how hash join queries are processed without reordering the tables within the query text. This hint is available in Aurora MySQL version 2.08 and higher. It has no effect in Aurora MySQL version 3. The following examples show how to use this hint. Specifying the `HASH_JOIN_PROBING` hint for the table `T2` has the same effect as specifying `NO_HASH_JOIN_PROBING` for the table `T1`. ``` EXPLAIN SELECT /*+ HASH_JOIN(t2) HASH_JOIN_PROBING(t2) */ f1, f2 FROM t1, t2 WHERE t1.f1 = t2.f1; EXPLAIN SELECT /*+ HASH_JOIN(t2) NO_HASH_JOIN_PROBING(t1) */ f1, f2 FROM t1, t2 WHERE t1.f1 = t2.f1; ``` **HASH\$1JOIN\$1BUILDING, NO\$1HASH\$1JOIN\$1BUILDING** In a hash join query, specifies whether to use the specified table for the build side of the join. The query processes all the rows from this table to build the list of column values to cross-reference with the other table. You can use `HASH_JOIN_PROBING` and `HASH_JOIN_BUILDING` to specify how hash join queries are processed without reordering the tables within the query text. This hint is available in Aurora MySQL version 2.08 and higher. It has no effect in Aurora MySQL version 3. The following example shows you how to use this hint. Specifying the `HASH_JOIN_BUILDING` hint for the table `T2` has the same effect as specifying `NO_HASH_JOIN_BUILDING` for the table `T1`. ``` EXPLAIN SELECT /*+ HASH_JOIN(t2) HASH_JOIN_BUILDING(t2) */ f1, f2 FROM t1, t2 WHERE t1.f1 = t2.f1; EXPLAIN SELECT /*+ HASH_JOIN(t2) NO_HASH_JOIN_BUILDING(t1) */ f1, f2 FROM t1, t2 WHERE t1.f1 = t2.f1; ``` **JOIN\$1FIXED\$1ORDER** Specifies that tables in the query are joined based on the order they are listed in the query. It is useful with queries involving three or more tables. It is intended as a replacement for the MySQL `STRAIGHT_JOIN` hint and is equivalent to the MySQL [JOIN\$1FIXED\$1ORDER](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html) hint. This hint is available in Aurora MySQL version 2.08 and higher. The following example shows you how to use this hint. ``` EXPLAIN SELECT /*+ JOIN_FIXED_ORDER() */ f1, f2 FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id); ``` **JOIN\$1ORDER** Specifies the join order for the tables in the query. It is useful with queries involving three or more tables. It is equivalent to the MySQL [JOIN\$1ORDER](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html) hint. This hint is available in Aurora MySQL version 2.08 and higher. The following example shows you how to use this hint. ``` EXPLAIN SELECT /*+ JOIN_ORDER (t4, t2, t1, t3) */ f1, f2 FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id); ``` **JOIN\$1PREFIX** Specifies the tables to put first in the join order. It is useful with queries involving three or more tables. It is equivalent to the MySQL [JOIN\$1PREFIX](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html) hint. This hint is available in Aurora MySQL version 2.08 and higher. The following example shows you how to use this hint. ``` EXPLAIN SELECT /*+ JOIN_PREFIX (t4, t2) */ f1, f2 FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id); ``` **JOIN\$1SUFFIX** Specifies the tables to put last in the join order. It is useful with queries involving three or more tables. It is equivalent to the MySQL [JOIN\$1SUFFIX](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html) hint. This hint is available in Aurora MySQL version 2.08 and higher. The following example shows you how to use this hint. ``` EXPLAIN SELECT /*+ JOIN_SUFFIX (t1) */ f1, f2 FROM t1 JOIN t2 USING (id) JOIN t3 USING (id) JOIN t4 USING (id); ``` For information about using hash join queries, see [Optimizing large Aurora MySQL join queries with hash joins](AuroraMySQL.BestPractices.Performance.md#Aurora.BestPractices.HashJoin). # Aurora MySQL stored procedure reference Stored procedure reference You can manage your Aurora MySQL DB cluster by calling built-in stored procedures. **Topics** + [ # Collecting and maintaining the Global Status History ](mysql-stored-proc-gsh.md) + [ # Configuring, starting, and stopping binary log (binlog) replication ](mysql-stored-proc-replicating.md) + [ # Ending a session or query ](mysql-stored-proc-ending.md) + [ # Replicating transactions using GTIDs ](mysql-stored-proc-gtid.md) + [ # Rotating the query logs ](mysql-stored-proc-logging.md) + [ # Setting and showing binary log configuration ](mysql-stored-proc-configuring.md) # Collecting and maintaining the Global Status History Amazon RDS provides a set of procedures that take snapshots of the values of status variables over time and write them to a table, along with any changes since the last snapshot. This infrastructure is called Global Status History. For more information, see [Managing the Global Status History](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html#Appendix.MySQL.CommonDBATasks.GoSH). The following stored procedures manage how the Global Status History is collected and maintained. **Topics** + [ ## mysql.rds\$1collect\$1global\$1status\$1history ](#mysql_rds_collect_global_status_history) + [ ## mysql.rds\$1disable\$1gsh\$1collector ](#mysql_rds_disable_gsh_collector) + [ ## mysql.rds\$1disable\$1gsh\$1rotation ](#mysql_rds_disable_gsh_rotation) + [ ## mysql.rds\$1enable\$1gsh\$1collector ](#mysql_rds_enable_gsh_collector) + [ ## mysql.rds\$1enable\$1gsh\$1rotation ](#mysql_rds_enable_gsh_rotation) + [ ## mysql.rds\$1rotate\$1global\$1status\$1history ](#mysql_rds_rotate_global_status_history) + [ ## mysql.rds\$1set\$1gsh\$1collector ](#mysql_rds_set_gsh_collector) + [ ## mysql.rds\$1set\$1gsh\$1rotation ](#mysql_rds_set_gsh_rotation) ## mysql.rds\$1collect\$1global\$1status\$1history mysql.rds\$1collect\$1global\$1status\$1history Takes a snapshot on demand for the Global Status History. ### Syntax Syntax ``` CALL mysql.rds_collect_global_status_history; ``` ## mysql.rds\$1disable\$1gsh\$1collector mysql.rds\$1disable\$1gsh\$1collector Turns off snapshots taken by the Global Status History. ### Syntax Syntax ``` CALL mysql.rds_disable_gsh_collector; ``` ## mysql.rds\$1disable\$1gsh\$1rotation mysql.rds\$1disable\$1gsh\$1rotation Turns off rotation of the `mysql.global_status_history` table. ### Syntax Syntax ``` CALL mysql.rds_disable_gsh_rotation; ``` ## mysql.rds\$1enable\$1gsh\$1collector mysql.rds\$1enable\$1gsh\$1collector Turns on the Global Status History to take default snapshots at intervals specified by `rds_set_gsh_collector`. ### Syntax Syntax ``` CALL mysql.rds_enable_gsh_collector; ``` ## mysql.rds\$1enable\$1gsh\$1rotation mysql.rds\$1enable\$1gsh\$1rotation Turns on rotation of the contents of the `mysql.global_status_history` table to `mysql.global_status_history_old` at intervals specified by `rds_set_gsh_rotation`. ### Syntax Syntax ``` CALL mysql.rds_enable_gsh_rotation; ``` ## mysql.rds\$1rotate\$1global\$1status\$1history mysql.rds\$1rotate\$1global\$1status\$1history Rotates the contents of the `mysql.global_status_history` table to `mysql.global_status_history_old` on demand. ### Syntax Syntax ``` CALL mysql.rds_rotate_global_status_history; ``` ## mysql.rds\$1set\$1gsh\$1collector mysql.rds\$1set\$1gsh\$1collector Specifies the interval, in minutes, between snapshots taken by the Global Status History. ### Syntax Syntax ``` CALL mysql.rds_set_gsh_collector(intervalPeriod); ``` ### Parameters Parameters *intervalPeriod* The interval, in minutes, between snapshots. Default value is `5`. ## mysql.rds\$1set\$1gsh\$1rotation mysql.rds\$1set\$1gsh\$1rotation Specifies the interval, in days, between rotations of the `mysql.global_status_history` table. ### Syntax Syntax ``` CALL mysql.rds_set_gsh_rotation(intervalPeriod); ``` ### Parameters Parameters *intervalPeriod* The interval, in days, between table rotations. Default value is `7`. # Configuring, starting, and stopping binary log (binlog) replication You can call the following stored procedures while connected to the primary instance in an Aurora MySQL cluster. These procedures control how transactions are replicated from an external database into Aurora MySQL, or from Aurora MySQL to an external database. **Topics** + [ ## mysql.rds\$1disable\$1session\$1binlog (Aurora MySQL version 2) ](#mysql_rds_disable_session_binlog) + [ ## mysql.rds\$1enable\$1session\$1binlog (Aurora MySQL version 2) ](#mysql_rds_enable_session_binlog) + [ ## mysql.rds\$1import\$1binlog\$1ssl\$1material ](#mysql_rds_import_binlog_ssl_material) + [ ## mysql.rds\$1next\$1master\$1log (Aurora MySQL version 2) ](#mysql_rds_next_master_log) + [ ## mysql.rds\$1next\$1source\$1log (Aurora MySQL version 3) ](#mysql_rds_next_source_log) + [ ## mysql.rds\$1remove\$1binlog\$1ssl\$1material ](#mysql_rds_remove_binlog_ssl_material) + [ ## mysql.rds\$1reset\$1external\$1master (Aurora MySQL version 2) ](#mysql_rds_reset_external_master) + [ ## mysql.rds\$1reset\$1external\$1source (Aurora MySQL version 3) ](#mysql_rds_reset_external_source) + [ ## mysql.rds\$1set\$1binlog\$1source\$1ssl (Aurora MySQL version 3) ](#mysql_rds_set_binlog_source_ssl) + [ ## mysql.rds\$1set\$1external\$1master (Aurora MySQL version 2) ](#mysql_rds_set_external_master) + [ ## mysql.rds\$1set\$1external\$1source (Aurora MySQL version 3) ](#mysql_rds_set_external_source) + [ ## mysql.rds\$1set\$1external\$1master\$1with\$1auto\$1position (Aurora MySQL version 2) ](#mysql_rds_set_external_master_with_auto_position) + [ ## mysql.rds\$1set\$1external\$1source\$1with\$1auto\$1position (Aurora MySQL version 3) ](#mysql_rds_set_external_source_with_auto_position) + [ ## mysql.rds\$1set\$1master\$1auto\$1position (Aurora MySQL version 2) ](#mysql_rds_set_master_auto_position) + [ ## mysql.rds\$1set\$1read\$1only (Aurora MySQL version 3) ](#mysql_rds_set_read_only) + [ ## mysql.rds\$1set\$1session\$1binlog\$1format (Aurora MySQL version 2) ](#mysql_rds_set_session_binlog_format) + [ ## mysql.rds\$1set\$1source\$1auto\$1position (Aurora MySQL version 3) ](#mysql_rds_set_source_auto_position) + [ ## mysql.rds\$1skip\$1repl\$1error ](#mysql_rds_skip_repl_error) + [ ## mysql.rds\$1start\$1replication ](#mysql_rds_start_replication) + [ ## mysql.rds\$1start\$1replication\$1until(Aurora MySQL version 3) ](#mysql_rds_start_replication_until) + [ ## mysql.rds\$1stop\$1replication ](#mysql_rds_stop_replication) ## mysql.rds\$1disable\$1session\$1binlog (Aurora MySQL version 2) mysql.rds\$1disable\$1session\$1binlog Turns off binary logging for the current session by setting the `sql_log_bin` variable to `OFF`. ### Syntax ``` CALL mysql.rds_disable_session_binlog; ``` ### Parameters None ### Usage notes For an Aurora MySQL DB cluster, you call this stored procedure while connected to the primary instance. For Aurora, this procedure is supported for Aurora MySQL version 2.12 and higher MySQL 5.7–compatible versions. **Note** In Aurora MySQL version 3, you can use the following command to disable binary logging for the current session if you have the `SESSION_VARIABLES_ADMIN` privilege: ``` SET SESSION sql_log_bin = OFF; ``` ## mysql.rds\$1enable\$1session\$1binlog (Aurora MySQL version 2) mysql.rds\$1enable\$1session\$1binlog Turns on binary logging for the current session by setting the `sql_log_bin` variable to `ON`. ### Syntax ``` CALL mysql.rds_enable_session_binlog; ``` ### Parameters None ### Usage notes For an Aurora MySQL DB cluster, you call this stored procedure while connected to the primary instance. For Aurora, this procedure is supported for Aurora MySQL version 2.12 and higher MySQL 5.7–compatible versions. **Note** In Aurora MySQL version 3, you can use the following command to enable binary logging for the current session if you have the `SESSION_VARIABLES_ADMIN` privilege: ``` SET SESSION sql_log_bin = ON; ``` ## mysql.rds\$1import\$1binlog\$1ssl\$1material Imports the certificate authority certificate, client certificate, and client key into an Aurora MySQL DB cluster. The information is required for SSL communication and encrypted replication. **Note** Currently, this procedure is supported for Aurora MySQL version 2: 2.09.2, 2.10.0, 2.10.1, and 2.11.0; and version 3: 3.01.1 and higher. ### Syntax ``` CALL mysql.rds_import_binlog_ssl_material ( ssl_material ); ``` ### Parameters *ssl\$1material* JSON payload that contains the contents of the following .pem format files for a MySQL client: + "ssl\$1ca":"*Certificate authority certificate*" + "ssl\$1cert":"*Client certificate*" + "ssl\$1key":"*Client key*" ### Usage notes Prepare for encrypted replication before you run this procedure: + If you don't have SSL enabled on the external MySQL source database instance and don't have a client key and client certificate prepared, enable SSL on the MySQL database server and generate the required client key and client certificate. + If SSL is enabled on the external source database instance, supply a client key and certificate for the Aurora MySQL DB cluster. If you don't have these, generate a new key and certificate for the Aurora MySQL DB cluster. To sign the client certificate, you must have the certificate authority key you used to configure SSL on the external MySQL source database instance. For more information, see [ Creating SSL certificates and keys using openssl](https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html) in the MySQL documentation. **Important** After you prepare for encrypted replication, use an SSL connection to run this procedure. The client key must not be transferred across an insecure connection. This procedure imports SSL information from an external MySQL database into an Aurora MySQL DB cluster. The SSL information is in .pem format files that contain the SSL information for the Aurora MySQL DB cluster. During encrypted replication, the Aurora MySQL DB cluster acts a client to the MySQL database server. The certificates and keys for the Aurora MySQL client are in files in .pem format. You can copy the information from these files into the `ssl_material` parameter in the correct JSON payload. To support encrypted replication, import this SSL information into the Aurora MySQL DB cluster. The JSON payload must be in the following format. ``` '{"ssl_ca":"-----BEGIN CERTIFICATE----- ssl_ca_pem_body_code -----END CERTIFICATE-----\n","ssl_cert":"-----BEGIN CERTIFICATE----- ssl_cert_pem_body_code -----END CERTIFICATE-----\n","ssl_key":"-----BEGIN RSA PRIVATE KEY----- ssl_key_pem_body_code -----END RSA PRIVATE KEY-----\n"}' ``` ### Examples The following example imports SSL information into an Aurora MySQL. In .pem format files, the body code typically is longer than the body code shown in the example. ``` call mysql.rds_import_binlog_ssl_material( '{"ssl_ca":"-----BEGIN CERTIFICATE----- AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE -----END CERTIFICATE-----\n","ssl_cert":"-----BEGIN CERTIFICATE----- AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE -----END CERTIFICATE-----\n","ssl_key":"-----BEGIN RSA PRIVATE KEY----- AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE -----END RSA PRIVATE KEY-----\n"}'); ``` ## mysql.rds\$1next\$1master\$1log (Aurora MySQL version 2) mysql.rds\$1next\$1master\$1log Changes the source database instance log position to the start of the next binary log on the source database instance. Use this procedure only if you are receiving replication I/O error 1236 on a read replica. ### Syntax ``` CALL mysql.rds_next_master_log( curr_master_log ); ``` ### Parameters *curr\$1master\$1log* The index of the current master log file. For example, if the current file is named `mysql-bin-changelog.012345`, then the index is 12345. To determine the current master log file name, run the `SHOW REPLICA STATUS` command and view the `Master_Log_File` field. ### Usage notes The master user must run the `mysql.rds_next_master_log` procedure. **Warning** Call `mysql.rds_next_master_log` only if replication fails after a failover of a Multi-AZ DB instance that is the replication source, and the `Last_IO_Errno` field of `SHOW REPLICA STATUS` reports I/O error 1236. Calling `mysql.rds_next_master_log` can result in data loss in the read replica if transactions in the source instance were not written to the binary log on disk before the failover event occurred. ### Examples Assume replication fails on an Aurora MySQL read replica. Running `SHOW REPLICA STATUS\G` on the read replica returns the following result: ``` *************************** 1. row *************************** Replica_IO_State: Source_Host: myhost.XXXXXXXXXXXXXXX.rr-rrrr-1.rds.amazonaws.com Source_User: MasterUser Source_Port: 3306 Connect_Retry: 10 Source_Log_File: mysql-bin-changelog.012345 Read_Source_Log_Pos: 1219393 Relay_Log_File: relaylog.012340 Relay_Log_Pos: 30223388 Relay_Source_Log_File: mysql-bin-changelog.012345 Replica_IO_Running: No Replica_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Source_Log_Pos: 30223232 Relay_Log_Space: 5248928866 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Master: NULL Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin-changelog.013406' at 1219393, the last event read from '/rdsdbdata/log/binlog/mysql-bin-changelog.012345' at 4, the last byte read from '/rdsdbdata/log/binlog/mysql-bin-changelog.012345' at 4.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 67285976 ``` The `Last_IO_Errno` field shows that the instance is receiving I/O error 1236. The `Master_Log_File` field shows that the file name is `mysql-bin-changelog.012345`, which means that the log file index is `12345`. To resolve the error, you can call `mysql.rds_next_master_log` with the following parameter: ``` CALL mysql.rds_next_master_log(12345); ``` ## mysql.rds\$1next\$1source\$1log (Aurora MySQL version 3) mysql.rds\$1next\$1source\$1log Changes the source database instance log position to the start of the next binary log on the source database instance. Use this procedure only if you are receiving replication I/O error 1236 on a read replica. ### Syntax ``` CALL mysql.rds_next_source_log( curr_source_log ); ``` ### Parameters *curr\$1source\$1log* The index of the current source log file. For example, if the current file is named `mysql-bin-changelog.012345`, then the index is 12345. To determine the current source log file name, run the `SHOW REPLICA STATUS` command and view the `Source_Log_File` field. ### Usage notes The administrative user must run the `mysql.rds_next_source_log` procedure. **Warning** Call `mysql.rds_next_source_log` only if replication fails after a failover of a Multi-AZ DB instance that is the replication source, and the `Last_IO_Errno` field of `SHOW REPLICA STATUS` reports I/O error 1236. Calling `mysql.rds_next_source_log` can result in data loss in the read replica if transactions in the source instance were not written to the binary log on disk before the failover event occurred. You can reduce the chance of this happening by setting the source instance parameters `sync_binlog` and `innodb_support_xa` to `1`, even though this might reduce performance. ### Examples Assume replication fails on an Aurora MySQL read replica. Running `SHOW REPLICA STATUS\G` on the read replica returns the following result: ``` *************************** 1. row *************************** Replica_IO_State: Source_Host: myhost.XXXXXXXXXXXXXXX.rr-rrrr-1.rds.amazonaws.com Source_User: MasterUser Source_Port: 3306 Connect_Retry: 10 Source_Log_File: mysql-bin-changelog.012345 Read_Source_Log_Pos: 1219393 Relay_Log_File: relaylog.012340 Relay_Log_Pos: 30223388 Relay_Source_Log_File: mysql-bin-changelog.012345 Replica_IO_Running: No Replica_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Source_Log_Pos: 30223232 Relay_Log_Space: 5248928866 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: NULL Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'Client requested source to start replication from impossible position; the first event 'mysql-bin-changelog.013406' at 1219393, the last event read from '/rdsdbdata/log/binlog/mysql-bin-changelog.012345' at 4, the last byte read from '/rdsdbdata/log/binlog/mysql-bin-changelog.012345' at 4.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 67285976 ``` The `Last_IO_Errno` field shows that the instance is receiving I/O error 1236. The `Source_Log_File` field shows that the file name is `mysql-bin-changelog.012345`, which means that the log file index is `12345`. To resolve the error, you can call `mysql.rds_next_source_log` with the following parameter: ``` CALL mysql.rds_next_source_log(12345); ``` ## mysql.rds\$1remove\$1binlog\$1ssl\$1material Removes the certificate authority certificate, client certificate, and client key for SSL communication and encrypted replication. This information is imported by using [mysql.rds\$1import\$1binlog\$1ssl\$1material](#mysql_rds_import_binlog_ssl_material). ### Syntax ``` CALL mysql.rds_remove_binlog_ssl_material; ``` ## mysql.rds\$1reset\$1external\$1master (Aurora MySQL version 2) mysql.rds\$1reset\$1external\$1master Reconfigures an Aurora MySQL DB instance to no longer be a read replica of an instance of MySQL running external to Amazon RDS. **Important** To run this procedure, `autocommit` must be enabled. To enable it, set the `autocommit` parameter to `1`. For information about modifying parameters, see [Modifying parameters in a DB parameter group in Amazon Aurora](USER_WorkingWithParamGroups.Modifying.md). ### Syntax ``` CALL mysql.rds_reset_external_master; ``` ### Usage notes The master user must run the `mysql.rds_reset_external_master` procedure. This procedure must be run on the MySQL DB instance to be removed as a read replica of a MySQL instance running external to Amazon RDS. **Note** We offer these stored procedures primarily to enable replication with MySQL instances running external to Amazon RDS. We recommend that you use Aurora Replicas to manage replication within an Aurora MySQL DB cluster when possible. For information about managing replication in Aurora MySQL DB clusters, see [Using Aurora Replicas](AuroraMySQL.Replication.md#AuroraMySQL.Replication.Replicas). For more information about using replication to import data from an instance of MySQL running external to Aurora MySQL, see [Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)](AuroraMySQL.Replication.MySQL.md). ## mysql.rds\$1reset\$1external\$1source (Aurora MySQL version 3) mysql.rds\$1reset\$1external\$1source Reconfigures an Aurora MySQL DB instance to no longer be a read replica of an instance of MySQL running external to Amazon RDS. **Important** To run this procedure, `autocommit` must be enabled. To enable it, set the `autocommit` parameter to `1`. For information about modifying parameters, see [Modifying parameters in a DB parameter group in Amazon Aurora](USER_WorkingWithParamGroups.Modifying.md). ### Syntax ``` CALL mysql.rds_reset_external_source; ``` ### Usage notes The administrative user must run the `mysql.rds_reset_external_source` procedure. This procedure must be run on the MySQL DB instance to be removed as a read replica of a MySQL instance running external to Amazon RDS. **Note** We offer these stored procedures primarily to enable replication with MySQL instances running external to Amazon RDS. We recommend that you use Aurora Replicas to manage replication within an Aurora MySQL DB cluster when possible. For information about managing replication in Aurora MySQL DB clusters, see [Using Aurora Replicas](AuroraMySQL.Replication.md#AuroraMySQL.Replication.Replicas). ## mysql.rds\$1set\$1binlog\$1source\$1ssl (Aurora MySQL version 3) mysql.rds\$1set\$1binlog\$1source\$1ssl Enables `SOURCE_SSL` encryption for binlog replication. For more information, see [CHANGE REPLICATION SOURCE TO statement](https://dev.mysql.com/doc/refman/8.0/en/change-replication-source-to.html) in the MySQL documentation. ### Syntax ``` CALL mysql.rds_set_binlog_source_ssl(mode); ``` ### Parameters *mode* A value that indicates whether `SOURCE_SSL` encryption is enabled: + `0` – `SOURCE_SSL` encryption is disabled. The default is `0`. + `1` – `SOURCE_SSL` encryption is enabled. You can configure encryption using SSL or TLS. ### Usage notes This procedure is supported for Aurora MySQL version 3.06 and higher. ## mysql.rds\$1set\$1external\$1master (Aurora MySQL version 2) mysql.rds\$1set\$1external\$1master Configures an Aurora MySQL DB instance to be a read replica of an instance of MySQL running external to Amazon RDS. The `mysql.rds_set_external_master` procedure is deprecated and will be removed in a future release. Use `mysql.rds\$1set\$1external\$1source` instead. **Important** To run this procedure, `autocommit` must be enabled. To enable it, set the `autocommit` parameter to `1`. For information about modifying parameters, see [Modifying parameters in a DB parameter group in Amazon Aurora](USER_WorkingWithParamGroups.Modifying.md). ### Syntax ``` CALL mysql.rds_set_external_master ( host_name , host_port , replication_user_name , replication_user_password , mysql_binary_log_file_name , mysql_binary_log_file_location , ssl_encryption ); ``` ### Parameters *host\$1name* The host name or IP address of the MySQL instance running external to Amazon RDS to become the source database instance. *host\$1port* The port used by the MySQL instance running external to Amazon RDS to be configured as the source database instance. If your network configuration includes Secure Shell (SSH) port replication that converts the port number, specify the port number that is exposed by SSH. *replication\$1user\$1name* The ID of a user with `REPLICATION CLIENT` and `REPLICATION SLAVE` permissions on the MySQL instance running external to Amazon RDS. We recommend that you provide an account that is used solely for replication with the external instance. *replication\$1user\$1password* The password of the user ID specified in `replication_user_name`. *mysql\$1binary\$1log\$1file\$1name* The name of the binary log on the source database instance that contains the replication information. *mysql\$1binary\$1log\$1file\$1location* The location in the `mysql_binary_log_file_name` binary log at which replication starts reading the replication information. You can determine the binlog file name and location by running `SHOW MASTER STATUS` on the source database instance. *ssl\$1encryption* A value that specifies whether Secure Socket Layer (SSL) encryption is used on the replication connection. 1 specifies to use SSL encryption, 0 specifies to not use encryption. The default is 0. The `MASTER_SSL_VERIFY_SERVER_CERT` option isn't supported. This option is set to 0, which means that the connection is encrypted, but the certificates aren't verified. ### Usage notes The master user must run the `mysql.rds_set_external_master` procedure. This procedure must be run on the MySQL DB instance to be configured as the read replica of a MySQL instance running external to Amazon RDS. Before you run `mysql.rds_set_external_master`, you must configure the instance of MySQL running external to Amazon RDS to be a source database instance. To connect to the MySQL instance running external to Amazon RDS, you must specify `replication_user_name` and `replication_user_password` values that indicate a replication user that has `REPLICATION CLIENT` and `REPLICATION SLAVE` permissions on the external instance of MySQL. **To configure an external instance of MySQL as a source database instance** 1. Using the MySQL client of your choice, connect to the external instance of MySQL and create a user account to be used for replication. The following is an example. **MySQL 5.7** ``` CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY 'password'; ``` **MySQL 8.0** ``` CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED WITH mysql_native_password BY 'password'; ``` **Note** Specify a password other than the prompt shown here as a security best practice. 1. On the external instance of MySQL, grant `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges to your replication user. The following example grants `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges on all databases for the 'repl\$1user' user for your domain. **MySQL 5.7** ``` GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com' IDENTIFIED BY 'password'; ``` **MySQL 8.0** ``` GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com'; ``` To use encrypted replication, configure source database instance to use SSL connections. Also, import the certificate authority certificate, client certificate, and client key into the DB instance or DB cluster using the [mysql.rds\$1import\$1binlog\$1ssl\$1material](#mysql_rds_import_binlog_ssl_material) procedure. **Note** We offer these stored procedures primarily to enable replication with MySQL instances running external to Amazon RDS. We recommend that you use Aurora Replicas to manage replication within an Aurora MySQL DB cluster when possible. For information about managing replication in Aurora MySQL DB clusters, see [Using Aurora Replicas](AuroraMySQL.Replication.md#AuroraMySQL.Replication.Replicas). After calling `mysql.rds_set_external_master` to configure an Amazon RDS DB instance as a read replica, you can call [mysql.rds\$1start\$1replication](#mysql_rds_start_replication) on the read replica to start the replication process. You can call [mysql.rds\$1reset\$1external\$1master (Aurora MySQL version 2)](#mysql_rds_reset_external_master) to remove the read replica configuration. When `mysql.rds_set_external_master` is called, Amazon RDS records the time, user, and an action of `set master` in the `mysql.rds_history` and `mysql.rds_replication_status` tables. ### Examples When run on a MySQL DB instance, the following example configures the DB instance to be a read replica of an instance of MySQL running external to Amazon RDS. ``` call mysql.rds_set_external_master( 'Externaldb.some.com', 3306, 'repl_user', 'password', 'mysql-bin-changelog.0777', 120, 1); ``` ## mysql.rds\$1set\$1external\$1source (Aurora MySQL version 3) mysql.rds\$1set\$1external\$1source Configures an Aurora MySQL DB instance to be a read replica of an instance of MySQL running external to Amazon RDS. **Important** To run this procedure, `autocommit` must be enabled. To enable it, set the `autocommit` parameter to `1`. For information about modifying parameters, see [Modifying parameters in a DB parameter group in Amazon Aurora](USER_WorkingWithParamGroups.Modifying.md). ### Syntax ``` CALL mysql.rds_set_external_source ( host_name , host_port , replication_user_name , replication_user_password , mysql_binary_log_file_name , mysql_binary_log_file_location , ssl_encryption ); ``` ### Parameters *host\$1name* The host name or IP address of the MySQL instance running external to Amazon RDS to become the source database instance. *host\$1port* The port used by the MySQL instance running external to Amazon RDS to be configured as the source database instance. If your network configuration includes Secure Shell (SSH) port replication that converts the port number, specify the port number that is exposed by SSH. *replication\$1user\$1name* The ID of a user with `REPLICATION CLIENT` and `REPLICATION SLAVE` permissions on the MySQL instance running external to Amazon RDS. We recommend that you provide an account that is used solely for replication with the external instance. *replication\$1user\$1password* The password of the user ID specified in `replication_user_name`. *mysql\$1binary\$1log\$1file\$1name* The name of the binary log on the source database instance that contains the replication information. *mysql\$1binary\$1log\$1file\$1location* The location in the `mysql_binary_log_file_name` binary log at which replication starts reading the replication information. You can determine the binlog file name and location by running `SHOW MASTER STATUS` on the source database instance. *ssl\$1encryption* A value that specifies whether Secure Socket Layer (SSL) encryption is used on the replication connection. 1 specifies to use SSL encryption, 0 specifies to not use encryption. The default is 0. You must have imported a custom SSL certificate using [mysql.rds\$1import\$1binlog\$1ssl\$1material](#mysql_rds_import_binlog_ssl_material) to enable this option. If you haven't imported an custom SSL certificate, then set this parameter to 0 and use [mysql.rds\$1set\$1binlog\$1source\$1ssl (Aurora MySQL version 3)](#mysql_rds_set_binlog_source_ssl) to enable SSL for binary log replication. The `SOURCE_SSL_VERIFY_SERVER_CERT` option isn't supported. This option is set to 0, which means that the connection is encrypted, but the certificates aren't verified. ### Usage notes The administrative user must run the `mysql.rds_set_external_source` procedure. This procedure must be run on the Aurora MySQL DB instance to be configured as the read replica of a MySQL instance running external to Amazon RDS. Before you run `mysql.rds_set_external_source`, you must configure the instance of MySQL running external to Amazon RDS to be a source database instance. To connect to the MySQL instance running external to Amazon RDS, you must specify `replication_user_name` and `replication_user_password` values that indicate a replication user that has `REPLICATION CLIENT` and `REPLICATION SLAVE` permissions on the external instance of MySQL. **To configure an external instance of MySQL as a source database instance** 1. Using the MySQL client of your choice, connect to the external instance of MySQL and create a user account to be used for replication. The following is an example. ``` CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY 'password'; ``` **Note** Specify a password other than the prompt shown here as a security best practice. 1. On the external instance of MySQL, grant `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges to your replication user. The following example grants `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges on all databases for the 'repl\$1user' user for your domain. ``` GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com'; ``` To use encrypted replication, configure source database instance to use SSL connections. Also, import the certificate authority certificate, client certificate, and client key into the DB instance or DB cluster using the [mysql.rds\$1import\$1binlog\$1ssl\$1material](url-rds-user;mysql_rds_import_binlog_ssl_material.html) procedure. **Note** We offer these stored procedures primarily to enable replication with MySQL instances running external to Amazon RDS. We recommend that you use Aurora Replicas to manage replication within an Aurora MySQL DB cluster when possible. For information about managing replication in Aurora MySQL DB clusters, see [Using Aurora Replicas](AuroraMySQL.Replication.md#AuroraMySQL.Replication.Replicas). After calling `mysql.rds_set_external_source` to configure an Aurora MySQL DB instance as a read replica, you can call [mysql.rds\$1start\$1replication](#mysql_rds_start_replication) on the read replica to start the replication process. You can call [mysql.rds\$1reset\$1external\$1source (Aurora MySQL version 3)](#mysql_rds_reset_external_source) to remove the read replica configuration. When `mysql.rds_set_external_source` is called, Amazon RDS records the time, user, and an action of `set master` in the `mysql.rds_history` and `mysql.rds_replication_status` tables. ### Examples When run on an Aurora MySQL DB instance, the following example configures the DB instance to be a read replica of an instance of MySQL running external to Amazon RDS. ``` call mysql.rds_set_external_source( 'Externaldb.some.com', 3306, 'repl_user', 'password', 'mysql-bin-changelog.0777', 120, 1); ``` ## mysql.rds\$1set\$1external\$1master\$1with\$1auto\$1position (Aurora MySQL version 2) mysql.rds\$1set\$1external\$1master\$1with\$1auto\$1position Configures an Aurora MySQL primary instance to accept incoming replication from an external MySQL instance. This procedure also configures replication based on global transaction identifiers (GTIDs). This procedure doesn't configure delayed replication, because Aurora MySQL doesn't support delayed replication. ### Syntax ``` CALL mysql.rds_set_external_master_with_auto_position ( host_name , host_port , replication_user_name , replication_user_password , ssl_encryption ); ``` ### Parameters *host\$1name* The host name or IP address of the MySQL instance running external to Aurora to become the replication source. *host\$1port* The port used by the MySQL instance running external to Aurora to be configured as the replication source. If your network configuration includes Secure Shell (SSH) port replication that converts the port number, specify the port number that is exposed by SSH. *replication\$1user\$1name* The ID of a user with `REPLICATION CLIENT` and `REPLICATION SLAVE` permissions on the MySQL instance running external to Aurora. We recommend that you provide an account that is used solely for replication with the external instance. *replication\$1user\$1password* The password of the user ID specified in `replication_user_name`. *ssl\$1encryption* This option isn't currently implemented. The default is 0. ### Usage notes For an Aurora MySQL DB cluster, you call this stored procedure while connected to the primary instance. The master user must run the `mysql.rds_set_external_master_with_auto_position` procedure. The master user runs this procedure on the primary instance of an Aurora MySQL DB cluster that acts as a replication target. This can be the replication target of an external MySQL DB instance or an Aurora MySQL DB cluster. This procedure is supported for Aurora MySQL version 2. For Aurora MySQL version 3, use the procedure [mysql.rds\$1set\$1external\$1source\$1with\$1auto\$1position (Aurora MySQL version 3)](#mysql_rds_set_external_source_with_auto_position) instead. Before you run `mysql.rds_set_external_master_with_auto_position`, configure the external MySQL DB instance to be a replication source. To connect to the external MySQL instance, specify values for `replication_user_name` and `replication_user_password`. These values must indicate a replication user that has `REPLICATION CLIENT` and `REPLICATION SLAVE` permissions on the external MySQL instance. **To configure an external MySQL instance as a replication source** 1. Using the MySQL client of your choice, connect to the external MySQL instance and create a user account to be used for replication. The following is an example. ``` CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY 'SomePassW0rd' ``` 1. On the external MySQL instance, grant `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges to your replication user. The following example grants `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges on all databases for the `'repl_user'` user for your domain. ``` GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com' IDENTIFIED BY 'SomePassW0rd' ``` When you call `mysql.rds_set_external_master_with_auto_position`, Amazon RDS records certain information. This information is the time, the user, and an action of `"set master"` in the `mysql.rds_history` and `mysql.rds_replication_status` tables. To skip a specific GTID-based transaction that is known to cause a problem, you can use the [mysql.rds\$1skip\$1transaction\$1with\$1gtid(Aurora MySQL version 2 and 3)](mysql-stored-proc-gtid.md#mysql_rds_skip_transaction_with_gtid) stored procedure. For more information about working with GTID-based replication, see [Using GTID-based replication](mysql-replication-gtid.md). ### Examples When run on an Aurora primary instance, the following example configures the Aurora cluster to act as a read replica of an instance of MySQL running external to Aurora. ``` call mysql.rds_set_external_master_with_auto_position( 'Externaldb.some.com', 3306, 'repl_user'@'mydomain.com', 'SomePassW0rd'); ``` ## mysql.rds\$1set\$1external\$1source\$1with\$1auto\$1position (Aurora MySQL version 3) mysql.rds\$1set\$1external\$1source\$1with\$1auto\$1position Configures an Aurora MySQL primary instance to accept incoming replication from an external MySQL instance. This procedure also configures replication based on global transaction identifiers (GTIDs). ### Syntax ``` CALL mysql.rds_set_external_source_with_auto_position ( host_name , host_port , replication_user_name , replication_user_password , ssl_encryption ); ``` ### Parameters *host\$1name* The host name or IP address of the MySQL instance running external to Aurora to become the replication source. *host\$1port* The port used by the MySQL instance running external to Aurora to be configured as the replication source. If your network configuration includes Secure Shell (SSH) port replication that converts the port number, specify the port number that is exposed by SSH. *replication\$1user\$1name* The ID of a user with `REPLICATION CLIENT` and `REPLICATION SLAVE` permissions on the MySQL instance running external to Aurora. We recommend that you provide an account that is used solely for replication with the external instance. *replication\$1user\$1password* The password of the user ID specified in `replication_user_name`. *ssl\$1encryption* This option isn't currently implemented. The default is 0. Use [mysql.rds\$1set\$1binlog\$1source\$1ssl (Aurora MySQL version 3)](#mysql_rds_set_binlog_source_ssl) to enable SSL for binary log replication. ### Usage notes For an Aurora MySQL DB cluster, you call this stored procedure while connected to the primary instance. The administrative user must run the `mysql.rds_set_external_source_with_auto_position` procedure. The administrative user runs this procedure on the primary instance of an Aurora MySQL DB cluster that acts as a replication target. This can be the replication target of an external MySQL DB instance or an Aurora MySQL DB cluster. This procedure is supported for Aurora MySQL version 3. This procedure doesn't configure delayed replication, because Aurora MySQL doesn't support delayed replication. Before you run `mysql.rds_set_external_source_with_auto_position`, configure the external MySQL DB instance to be a replication source. To connect to the external MySQL instance, specify values for `replication_user_name` and `replication_user_password`. These values must indicate a replication user that has `REPLICATION CLIENT` and `REPLICATION SLAVE` permissions on the external MySQL instance. **To configure an external MySQL instance as a replication source** 1. Using the MySQL client of your choice, connect to the external MySQL instance and create a user account to be used for replication. The following is an example. ``` CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY 'SomePassW0rd' ``` 1. On the external MySQL instance, grant `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges to your replication user. The following example grants `REPLICATION CLIENT` and `REPLICATION SLAVE` privileges on all databases for the `'repl_user'` user for your domain. ``` GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com' IDENTIFIED BY 'SomePassW0rd' ``` When you call `mysql.rds_set_external_source_with_auto_position`, Amazon RDS records certain information. This information is the time, the user, and an action of `"set master"` in the `mysql.rds_history` and `mysql.rds_replication_status` tables. To skip a specific GTID-based transaction that is known to cause a problem, you can use the [mysql.rds\$1skip\$1transaction\$1with\$1gtid(Aurora MySQL version 2 and 3)](mysql-stored-proc-gtid.md#mysql_rds_skip_transaction_with_gtid) stored procedure. For more information about working with GTID-based replication, see [Using GTID-based replication](mysql-replication-gtid.md). ### Examples When run on an Aurora primary instance, the following example configures the Aurora cluster to act as a read replica of an instance of MySQL running external to Aurora. ``` call mysql.rds_set_external_source_with_auto_position( 'Externaldb.some.com', 3306, 'repl_user'@'mydomain.com', 'SomePassW0rd'); ``` ## mysql.rds\$1set\$1master\$1auto\$1position (Aurora MySQL version 2) mysql.rds\$1set\$1master\$1auto\$1position Sets the replication mode to be based on either binary log file positions or on global transaction identifiers (GTIDs). ### Syntax ``` CALL mysql.rds_set_master_auto_position ( auto_position_mode ); ``` ### Parameters *auto\$1position\$1mode* A value that indicates whether to use log file position replication or GTID-based replication: + `0` – Use the replication method based on binary log file position. The default is `0`. + `1` – Use the GTID-based replication method. ### Usage notes The master user must run the `mysql.rds_set_master_auto_position` procedure. This procedure is supported for Aurora MySQL version 2. ## mysql.rds\$1set\$1read\$1only (Aurora MySQL version 3) mysql\$1rds\$1set\$1read\$1only Turns `read_only` mode on or off globally for the DB instance. ### Syntax ``` CALL mysql.rds_set_read_only(mode); ``` ### Parameters *mode* A value that indicates whether `read_only` mode is on or off globally for the DB instance: + `0` – `OFF`. The default is `0`. + `1` – `ON` ### Usage notes The `mysql.rds_set_read_only` stored procedure modifies only the `read_only` parameter. The `innodb_read_only` parameter can't be changed on reader DB instances. The `read_only` parameter change doesn't persist on rebooting. To make permanent changes to `read_only`, you must use the `read_only` DB cluster parameter. This procedure is supported for Aurora MySQL version 3.06 and higher. ## mysql.rds\$1set\$1session\$1binlog\$1format (Aurora MySQL version 2) mysql.rds\$1set\$1session\$1binlog\$1format Sets the binary log format for the current session. ### Syntax ``` CALL mysql.rds_set_session_binlog_format(format); ``` ### Parameters *format* A value that indicates the binary log format for the current session: + `STATEMENT` – The replication source writes events to the binary log based on SQL statements. + `ROW` – The replication source writes events to the binary log that indicate changes to individual table rows. + `MIXED` – Logging is generally based on SQL statements, but switches to rows under certain conditions. For more information, see [Mixed Binary Logging Format](https://dev.mysql.com/doc/refman/8.0/en/binary-log-mixed.html) in the MySQL documentation. ### Usage notes For an Aurora MySQL DB cluster, you call this stored procedure while connected to the primary instance. To use this stored procedure, you must have binary logging configured for the current session. For Aurora, this procedure is supported for Aurora MySQL version 2.12 and higher MySQL 5.7–compatible versions. ## mysql.rds\$1set\$1source\$1auto\$1position (Aurora MySQL version 3) mysql.rds\$1set\$1source\$1auto\$1position Sets the replication mode to be based on either binary log file positions or on global transaction identifiers (GTIDs). ### Syntax ``` CALL mysql.rds_set_source_auto_position (auto_position_mode); ``` ### Parameters *auto\$1position\$1mode* A value that indicates whether to use log file position replication or GTID-based replication: + `0` – Use the replication method based on binary log file position. The default is `0`. + `1` – Use the GTID-based replication method. ### Usage notes For an Aurora MySQL DB cluster, you call this stored procedure while connected to the primary instance. The administrative user must run the `mysql.rds_set_source_auto_position` procedure. ## mysql.rds\$1skip\$1repl\$1error Skips and deletes a replication error on a MySQL DB read replica. ### Syntax ``` CALL mysql.rds_skip_repl_error; ``` ### Usage notes The master user must run the `mysql.rds_skip_repl_error` procedure on a read replica. For more information about this procedure, see [Skipping the current replication error](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html#Appendix.MySQL.CommonDBATasks.SkipError). To determine if there are errors, run the MySQL `SHOW REPLICA STATUS\G` command. If a replication error isn't critical, you can run `mysql.rds_skip_repl_error` to skip the error. If there are multiple errors, `mysql.rds_skip_repl_error` deletes the first error, then warns that others are present. You can then use `SHOW REPLICA STATUS\G` to determine the correct course of action for the next error. For information about the values returned, see [SHOW REPLICA STATUS statement](https://dev.mysql.com/doc/refman/8.0/en/show-replica-status.html) in the MySQL documentation. For more information about addressing replication errors with Aurora MySQL, see [Diagnosing and resolving a MySQL read replication failure](CHAP_Troubleshooting.md#CHAP_Troubleshooting.MySQL.RR). #### Replication stopped error When you call the `mysql.rds_skip_repl_error` procedure, you might receive an error message stating that the replica is down or disabled. This error message appears if you run the procedure on the primary instance instead of the read replica. You must run this procedure on the read replica for the procedure to work. This error message might also appear if you run the procedure on the read replica, but replication can't be restarted successfully. If you need to skip a large number of errors, the replication lag can increase beyond the default retention period for binary log (binlog) files. In this case, you might encounter a fatal error because of binlog files being purged before they have been replayed on the read replica. This purge causes replication to stop, and you can no longer call the `mysql.rds_skip_repl_error` command to skip replication errors. You can mitigate this issue by increasing the number of hours that binlog files are retained on your source database instance. After you have increased the binlog retention time, you can restart replication and call the `mysql.rds_skip_repl_error` command as needed. To set the binlog retention time, use the [mysql.rds\$1set\$1configuration](mysql-stored-proc-configuring.md#mysql_rds_set_configuration) procedure and specify a configuration parameter of `'binlog retention hours'` along with the number of hours to retain binlog files on the DB cluster. The following example sets the retention period for binlog files to 48 hours. ``` CALL mysql.rds_set_configuration('binlog retention hours', 48); ``` ## mysql.rds\$1start\$1replication Initiates replication from an Aurora MySQL DB cluster. **Note** You can use the [mysql.rds\$1start\$1replication\$1until(Aurora MySQL version 3)](#mysql_rds_start_replication_until) or [mysql.rds\$1start\$1replication\$1until\$1gtid(Aurora MySQL version 3)](mysql-stored-proc-gtid.md#mysql_rds_start_replication_until_gtid) stored procedure to initiate replication from an Aurora MySQL DB instance and stop replication at the specified binary log file location. ### Syntax ``` CALL mysql.rds_start_replication; ``` ### Usage notes The master user must run the `mysql.rds_start_replication` procedure. To import data from an instance of MySQL external to Amazon RDS, call `mysql.rds_start_replication` on the read replica to start the replication process after you call [mysql.rds\$1set\$1external\$1master (Aurora MySQL version 2)](#mysql_rds_set_external_master) or [mysql.rds\$1set\$1external\$1source (Aurora MySQL version 3)](#mysql_rds_set_external_source) to build the replication configuration. For more information, see [Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)](AuroraMySQL.Replication.MySQL.md). To export data to an instance of MySQL external to Amazon RDS, call `mysql.rds_start_replication` and `mysql.rds_stop_replication` on the read replica to control some replication actions, such as purging binary logs. For more information, see [Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)](AuroraMySQL.Replication.MySQL.md). You can also call `mysql.rds_start_replication` on the read replica to restart any replication process that you previously stopped by calling `mysql.rds_stop_replication`. For more information, see [Replication stopped error](CHAP_Troubleshooting.md#CHAP_Troubleshooting.MySQL.ReplicationStopped). ## mysql.rds\$1start\$1replication\$1until(Aurora MySQL version 3) mysql.rds\$1start\$1replication\$1until Initiates replication from an Aurora MySQL DB cluster and stops replication at the specified binary log file location. ### Syntax ``` CALL mysql.rds_start_replication_until ( replication_log_file , replication_stop_point ); ``` ### Parameters *replication\$1log\$1file* The name of the binary log on the source database instance that contains the replication information. *replication\$1stop\$1point * The location in the `replication_log_file` binary log at which replication will stop. ### Usage notes The master user must run the `mysql.rds_start_replication_until` procedure. This procedure is supported for Aurora MySQL version 3.04 and higher. The `mysql.rds_start_replication_until` stored procedure isn't supported for managed replication, which includes the following: + [Replicating Amazon Aurora MySQL DB clusters across AWS Regions](AuroraMySQL.Replication.CrossRegion.md) + [Migrating data from an RDS for MySQL DB instance to an Amazon Aurora MySQL DB cluster by using an Aurora read replica](AuroraMySQL.Migrating.RDSMySQL.Replica.md) The file name specified for the `replication_log_file` parameter must match the source database instance binlog file name. When the `replication_stop_point` parameter specifies a stop location that is in the past, replication is stopped immediately. ### Examples The following example initiates replication and replicates changes until it reaches location `120` in the `mysql-bin-changelog.000777` binary log file. ``` call mysql.rds_start_replication_until( 'mysql-bin-changelog.000777', 120); ``` ## mysql.rds\$1stop\$1replication Stops replication from a MySQL DB instance. ### Syntax ``` CALL mysql.rds_stop_replication; ``` ### Usage notes The master user must run the `mysql.rds_stop_replication` procedure. If you are configuring replication to import data from an instance of MySQL running external to Amazon RDS, you call `mysql.rds_stop_replication` on the read replica to stop the replication process after the import has completed. For more information, see [Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)](AuroraMySQL.Replication.MySQL.md). If you are configuring replication to export data to an instance of MySQL external to Amazon RDS, you call `mysql.rds_start_replication` and `mysql.rds_stop_replication` on the read replica to control some replication actions, such as purging binary logs. For more information, see [Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)](AuroraMySQL.Replication.MySQL.md). The `mysql.rds_stop_replication` stored procedure isn't supported for managed replication, which includes the following: + [Replicating Amazon Aurora MySQL DB clusters across AWS Regions](AuroraMySQL.Replication.CrossRegion.md) + [Migrating data from an RDS for MySQL DB instance to an Amazon Aurora MySQL DB cluster by using an Aurora read replica](AuroraMySQL.Migrating.RDSMySQL.Replica.md) # Ending a session or query The following stored procedures end a session or query. **Topics** + [ ## mysql.rds\$1kill ](#mysql_rds_kill) + [ ## mysql.rds\$1kill\$1query ](#mysql_rds_kill_query) ## mysql.rds\$1kill mysql.rds\$1kill Ends a connection to the MySQL server. ### Syntax Syntax ``` CALL mysql.rds_kill(processID); ``` ### Parameters Parameters *processID* The identity of the connection thread to be ended. ### Usage notes Usage notes Each connection to the MySQL server runs in a separate thread. To end a connection, use the `mysql.rds_kill` procedure and pass in the thread ID of that connection. To obtain the thread ID, use the MySQL [SHOW PROCESSLIST](https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html) command. ### Examples Examples The following example ends a connection with a thread ID of 4243: ``` CALL mysql.rds_kill(4243); ``` ## mysql.rds\$1kill\$1query mysql.rds\$1kill\$1query Ends a query running against the MySQL server. ### Syntax Syntax ``` CALL mysql.rds_kill_query(processID); ``` ### Parameters Parameters *processID* The identity of the process or thread that is running the query to be ended. ### Usage notes Usage notes To stop a query running against the MySQL server, use the `mysql_rds_kill_query` procedure and pass in the connection ID of the thread that is running the query. The procedure then terminates the connection. To obtain the ID, query the MySQL [INFORMATION\$1SCHEMA PROCESSLIST table](https://dev.mysql.com/doc/refman/8.0/en/information-schema-processlist-table.html) or use the MySQL [SHOW PROCESSLIST](https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html) command. The value in the ID column from `SHOW PROCESSLIST` or `SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST` is the *processID*. ### Examples Examples The following example stops a query with a query thread ID of 230040: ``` CALL mysql.rds_kill_query(230040); ``` # Replicating transactions using GTIDs The following stored procedures control how transactions are replicated using global transaction identifiers (GTIDs) with Aurora MySQL. To learn how to use replication based on GTIDs with Aurora MySQL, see [Using GTID-based replication](mysql-replication-gtid.md). **Topics** + [ ## mysql.rds\$1assign\$1gtids\$1to\$1anonymous\$1transactions (Aurora MySQL version 3) ](#mysql_assign_gtids_to_anonymous_transactions) + [ ## mysql.rds\$1gtid\$1purged (Aurora MySQL version 3) ](#mysql_rds_gtid_purged) + [ ## mysql.rds\$1skip\$1transaction\$1with\$1gtid(Aurora MySQL version 2 and 3) ](#mysql_rds_skip_transaction_with_gtid) + [ ## mysql.rds\$1start\$1replication\$1until\$1gtid(Aurora MySQL version 3) ](#mysql_rds_start_replication_until_gtid) ## mysql.rds\$1assign\$1gtids\$1to\$1anonymous\$1transactions (Aurora MySQL version 3) mysql.rds\$1assign\$1gtids\$1to\$1anonymous\$1transactions Configures the `ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS` option of the `CHANGE REPLICATION SOURCE TO` statement. It makes the replication channel assign a GTID to replicated transactions that don't have one. That way, you can perform binary log replication from a source that doesn't use GTID-based replication to a replica that does. For more information, see [CHANGE REPLICATION SOURCE TO Statement](https://dev.mysql.com/doc/refman/8.0/en/change-replication-source-to.html) and [Replication From a Source Without GTIDs to a Replica With GTIDs](https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-assign-anon.html) in the *MySQL Reference Manual*. ### Syntax ``` CALL mysql.rds_assign_gtids_to_anonymous_transactions(gtid_option); ``` ### Parameters *gtid\$1option* String value. The allowed values are `OFF`, `LOCAL`, or a specified UUID. ### Usage notes This procedure has the same effect as issuing the statement `CHANGE REPLICATION SOURCE TO ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = gtid_option` in community MySQL. GTID must be turned to `ON` for *gtid\$1option* to be set to `LOCAL` or a specific UUID. The default is `OFF`, meaning that the feature isn't used. `LOCAL` assigns a GTID including the replica's own UUID (the `server_uuid` setting). Passing a parameter that is a UUID assigns a GTID that includes the specified UUID, such as the `server_uuid` setting for the replication source server. ### Examples To turn off this feature: ``` mysql> call mysql.rds_assign_gtids_to_anonymous_transactions('OFF'); +-------------------------------------------------------------+ | Message | +-------------------------------------------------------------+ | ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS has been set to: OFF | +-------------------------------------------------------------+ 1 row in set (0.07 sec) ``` To use the replica's own UUID: ``` mysql> call mysql.rds_assign_gtids_to_anonymous_transactions('LOCAL'); +---------------------------------------------------------------+ | Message | +---------------------------------------------------------------+ | ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS has been set to: LOCAL | +---------------------------------------------------------------+ 1 row in set (0.07 sec) ``` To use a specified UUID: ``` mysql> call mysql.rds_assign_gtids_to_anonymous_transactions('317a4760-f3dd-3b74-8e45-0615ed29de0e'); +----------------------------------------------------------------------------------------------+ | Message | +----------------------------------------------------------------------------------------------+ | ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS has been set to: 317a4760-f3dd-3b74-8e45-0615ed29de0e | +----------------------------------------------------------------------------------------------+ 1 row in set (0.07 sec) ``` ## mysql.rds\$1gtid\$1purged (Aurora MySQL version 3) mysql.rds\$1gtid\$1purged Sets the global value of the system variable `gtid_purged` to a given global transaction identifier (GTID) set. The `gtid_purged` system variable is a GTID set that consists of the GTIDs of all transactions that have been committed on the server, but don't exist in any binary log file on the server. To allow compatibility with MySQL 8.0, there are two ways to set the value of `gtid_purged`: + Replace the value of `gtid_purged` with your specified GTID set. + Append your specified GTID set to the GTID set that `gtid_purged` already contains. ### Syntax Syntax To replace the value of `gtid_purged` with your specified GTID set: ``` CALL mysql.rds_gtid_purged (gtid_set); ``` To append the value of `gtid_purged` to your specified GTID set: ``` CALL mysql.rds_gtid_purged (+gtid_set); ``` ### Parameters *gtid\$1set* The value of *gtid\$1set* must be a superset of the current value of `gtid_purged`, and can't intersect with `gtid_subtract(gtid_executed,gtid_purged)`. That is, the new GTID set must include any GTIDs that were already in `gtid_purged`, and can't include any GTIDs in `gtid_executed` that haven't yet been purged. The *gtid\$1set* parameter also can't include any GTIDs that are in the global `gtid_owned` set, the GTIDs for transactions that are currently being processed on the server. ### Usage notes The master user must run the `mysql.rds_gtid_purged` procedure. This procedure is supported for Aurora MySQL version 3.04 and higher. ### Examples Examples The following example assigns the GTID `3E11FA47-71CA-11E1-9E33-C80AA9429562:23` to the `gtid_purged` global variable. ``` CALL mysql.rds_gtid_purged('3E11FA47-71CA-11E1-9E33-C80AA9429562:23'); ``` ## mysql.rds\$1skip\$1transaction\$1with\$1gtid(Aurora MySQL version 2 and 3) mysql.rds\$1skip\$1transaction\$1with\$1gtid Skips replication of a transaction with the specified global transaction identifier (GTID) on an Aurora primary instance. You can use this procedure for disaster recovery when a specific GTID transaction is known to cause a problem. Use this stored procedure to skip the problematic transaction. Examples of problematic transactions include transactions that disable replication, delete important data, or cause the DB instance to become unavailable. ### Syntax Syntax ``` CALL mysql.rds_skip_transaction_with_gtid ( gtid_to_skip ); ``` ### Parameters Parameters *gtid\$1to\$1skip* The GTID of the replication transaction to skip. ### Usage notes Usage notes The master user must run the `mysql.rds_skip_transaction_with_gtid` procedure. This procedure is supported for Aurora MySQL version 2 and 3. ### Examples Examples The following example skips replication of the transaction with the GTID `3E11FA47-71CA-11E1-9E33-C80AA9429562:23`. ``` CALL mysql.rds_skip_transaction_with_gtid('3E11FA47-71CA-11E1-9E33-C80AA9429562:23'); ``` ## mysql.rds\$1start\$1replication\$1until\$1gtid(Aurora MySQL version 3) mysql.rds\$1start\$1replication\$1until\$1gtid Initiates replication from an Aurora MySQL DB cluster and stops replication immediately after the specified global transaction identifier (GTID). ### Syntax Syntax ``` CALL mysql.rds_start_replication_until_gtid(gtid); ``` ### Parameters Parameters *gtid* The GTID after which replication is to stop. ### Usage notes Usage notes The master user must run the `mysql.rds_start_replication_until_gtid` procedure. This procedure is supported for Aurora MySQL version 3.04 and higher. The `mysql.rds_start_replication_until_gtid` stored procedure isn't supported for managed replication, which includes the following: + [Replicating Amazon Aurora MySQL DB clusters across AWS Regions](AuroraMySQL.Replication.CrossRegion.md) + [Migrating data from an RDS for MySQL DB instance to an Amazon Aurora MySQL DB cluster by using an Aurora read replica](AuroraMySQL.Migrating.RDSMySQL.Replica.md) When the `gtid` parameter specifies a transaction that has already been run by the replica, replication is stopped immediately. ### Examples Examples The following example initiates replication and replicates changes until it reaches GTID `3E11FA47-71CA-11E1-9E33-C80AA9429562:23`. ``` call mysql.rds_start_replication_until_gtid('3E11FA47-71CA-11E1-9E33-C80AA9429562:23'); ``` # Rotating the query logs The following stored procedures rotate MySQL logs to backup tables. For more information, see [AuroraMySQL database log files](USER_LogAccess.Concepts.MySQL.md). **Topics** + [ ## mysql.rds\$1rotate\$1general\$1log ](#mysql_rds_rotate_general_log) + [ ## mysql.rds\$1rotate\$1slow\$1log ](#mysql_rds_rotate_slow_log) ## mysql.rds\$1rotate\$1general\$1log mysql.rds\$1rotate\$1general\$1log Rotates the `mysql.general_log` table to a backup table. ### Syntax Syntax ``` CALL mysql.rds_rotate_general_log; ``` ### Usage notes Usage notes You can rotate the `mysql.general_log` table to a backup table by calling the `mysql.rds_rotate_general_log` procedure. When log tables are rotated, the current log table is copied to a backup log table and the entries in the current log table are removed. If a backup log table already exists, then it is deleted before the current log table is copied to the backup. You can query the backup log table if needed. The backup log table for the `mysql.general_log` table is named `mysql.general_log_backup`. You can run this procedure only when the `log_output` parameter is set to `TABLE`. ## mysql.rds\$1rotate\$1slow\$1log mysql.rds\$1rotate\$1slow\$1log Rotates the `mysql.slow_log` table to a backup table. ### Syntax Syntax ``` CALL mysql.rds_rotate_slow_log; ``` ### Usage notes Usage notes You can rotate the `mysql.slow_log` table to a backup table by calling the `mysql.rds_rotate_slow_log` procedure. When log tables are rotated, the current log table is copied to a backup log table and the entries in the current log table are removed. If a backup log table already exists, then it is deleted before the current log table is copied to the backup. You can query the backup log table if needed. The backup log table for the `mysql.slow_log` table is named `mysql.slow_log_backup`. # Setting and showing binary log configuration The following stored procedures set and show configuration parameters, such as for binary log file retention. **Topics** + [ ## mysql.rds\$1set\$1configuration ](#mysql_rds_set_configuration) + [ ## mysql.rds\$1show\$1configuration ](#mysql_rds_show_configuration) ## mysql.rds\$1set\$1configuration Specifies the number of hours to retain binary logs or the number of seconds to delay replication. ### Syntax Syntax ``` CALL mysql.rds_set_configuration(name,value); ``` ### Parameters Parameters *name* The name of the configuration parameter to set. *value* The value of the configuration parameter. ### Usage notes Usage notes The `mysql.rds_set_configuration` procedure supports the following configuration parameters: + [binlog retention hours](#mysql_rds_set_configuration-usage-notes.binlog-retention-hours) The configuration parameters are stored permanently and survive any DB instance reboot or failover. #### binlog retention hours The `binlog retention hours` parameter is used to specify the number of hours to retain binary log files. Amazon Aurora normally purges a binary log as soon as possible, but the binary log might still be required for replication with a MySQL database external to Aurora. The default value of `binlog retention hours` is `NULL`. For Aurora MySQL, `NULL` means binary logs are cleaned up lazily. Aurora MySQL binary logs might remain in the system for a certain period, which is usually not longer than a day. To specify the number of hours to retain binary logs on a DB cluster, use the `mysql.rds_set_configuration` stored procedure and specify a period with enough time for replication to occur, as shown in the following example. `call mysql.rds_set_configuration('binlog retention hours', 24);` **Note** You can't use the value `0` for `binlog retention hours`. For Aurora MySQL version 2.11.0 and higher and version 3 DB clusters, the maximum `binlog retention hours` value is 2160 (90 days). After you set the retention period, monitor storage usage for the DB instance to make sure that the retained binary logs don't take up too much storage. ## mysql.rds\$1show\$1configuration The number of hours that binary logs are retained. ### Syntax Syntax ``` CALL mysql.rds_show_configuration; ``` ### Usage notes Usage notes To verify the number of hours that Amazon RDS retains binary logs, use the `mysql.rds_show_configuration` stored procedure. ### Examples Examples The following example displays the retention period: ``` call mysql.rds_show_configuration; name value description binlog retention hours 24 binlog retention hours specifies the duration in hours before binary logs are automatically deleted. ``` # Aurora MySQL–specific information\$1schema tables information\$1schema tables Aurora MySQL has certain `information_schema` tables that are specific to Aurora. ## information\$1schema.aurora\$1global\$1db\$1instance\$1status The `information_schema.aurora_global_db_instance_status` table contains information about the status of all DB instances in a global database's primary and secondary DB clusters. The following table shows the columns that you can use. The remaining columns are for Aurora internal use only. **Note** This information schema table is only available with Aurora MySQL version 3.04.0 and higher global databases. | Column | Data type | Description | | --- | --- | --- | | SERVER\$1ID | varchar(100) | The identifier of the DB instance. | | SESSION\$1ID | varchar(100) | A unique identifier for the current session. A value of MASTER\$1SESSION\$1ID identifies the Writer (primary) DB instance. | | AWS\$1REGION | varchar(100) | The AWS Region in which this global database instance runs. For a list of Regions, see [Region availability](Concepts.RegionsAndAvailabilityZones.md#Aurora.Overview.Availability). | | DURABLE\$1LSN | bigint unsigned | The log sequence number (LSN) made durable in storage. A log sequence number (LSN) is a unique sequential number that identifies a record in the database transaction log. LSNs are ordered such that a larger LSN represents a later transaction. | | HIGHEST\$1LSN\$1RCVD | bigint unsigned | The highest LSN received by the DB instance from the writer DB instance. | | OLDEST\$1READ\$1VIEW\$1TRX\$1ID | bigint unsigned | The ID of the oldest transaction that the writer DB instance can purge to. | | OLDEST\$1READ\$1VIEW\$1LSN | bigint unsigned | The oldest LSN used by the DB instance to read from storage. | | VISIBILITY\$1LAG\$1IN\$1MSEC | float(10,0) unsigned | For readers in the primary DB cluster, how far this DB instance is lagging behind the writer DB instance in milliseconds. For readers in a secondary DB cluster, how far this DB instance is lagging behind the secondary volume in milliseconds. | ## information\$1schema.aurora\$1global\$1db\$1status The `information_schema.aurora_global_db_status` table contains information about various aspects of Aurora global database lag, specifically, lag of the underlying Aurora storage (so called durability lag) and lag between the recovery point objective (RPO). The following table shows the columns that you can use. The remaining columns are for Aurora internal use only. **Note** This information schema table is only available with Aurora MySQL version 3.04.0 and higher global databases. | Column | Data type | Description | | --- | --- | --- | | AWS\$1REGION | varchar(100) | The AWS Region in which this global database instance runs. For a list of Regions, see [Region availability](Concepts.RegionsAndAvailabilityZones.md#Aurora.Overview.Availability). | | HIGHEST\$1LSN\$1WRITTEN | bigint unsigned | The highest log sequence number (LSN) that currently exists on this DB cluster. A log sequence number (LSN) is a unique sequential number that identifies a record in the database transaction log. LSNs are ordered such that a larger LSN represents a later transaction. | | DURABILITY\$1LAG\$1IN\$1MILLISECONDS | float(10,0) unsigned | The difference in the timestamp values between the HIGHEST\$1LSN\$1WRITTEN on a secondary DB cluster and the HIGHEST\$1LSN\$1WRITTEN on the primary DB cluster. This value is always 0 on the primary DB cluster of the Aurora global database. | | RPO\$1LAG\$1IN\$1MILLISECONDS | float(10,0) unsigned | The recovery point objective (RPO) lag. The RPO lag is the time it takes for the most recent user transaction COMMIT to be stored on a secondary DB cluster after it's been stored on the primary DB cluster of the Aurora global database. This value is always 0 on the primary DB cluster of the Aurora global database. In simple terms, this metric calculates the recovery point objective for each Aurora MySQL DB cluster in the Aurora global database, that is, how much data might be lost if there were an outage. As with lag, RPO is measured in time. | | LAST\$1LAG\$1CALCULATION\$1TIMESTAMP | datetime | The timestamp that specifies when values were last calculated for DURABILITY\$1LAG\$1IN\$1MILLISECONDS and RPO\$1LAG\$1IN\$1MILLISECONDS. A time value such as 1970-01-01 00:00:00\$100 means this is the primary DB cluster. | | OLDEST\$1READ\$1VIEW\$1TRX\$1ID | bigint unsigned | The ID of the oldest transaction that the writer DB instance can purge to. | ## information\$1schema.replica\$1host\$1status The `information_schema.replica_host_status` table contains replication information. The columns that you can use are shown in the following table. The remaining columns are for Aurora internal use only. | Column | Data type | Description | | --- | --- | --- | | CPU | double | The CPU percentage usage of the replica host. | | IS\$1CURRENT | tinyint | Whether the replica is current. | | LAST\$1UPDATE\$1TIMESTAMP | datetime(6) | The time the last update occurred. Used to determine whether a record is stale. | | REPLICA\$1LAG\$1IN\$1MILLISECONDS | double | The replica lag in milliseconds. | | SERVER\$1ID | varchar(100) | The ID of the database server. | | SESSION\$1ID | varchar(100) | The ID of the database session. Used to determine whether a DB instance is a writer or reader instance. | **Note** When a replica instance falls behind, the information queried from its `information_schema.replica_host_status` table might be outdated. In this situation, we recommend that you query from the writer instance instead. While the `mysql.ro_replica_status` table has similar information, we don't recommend that you use it. ## information\$1schema.aurora\$1forwarding\$1processlist The `information_schema.aurora_forwarding_processlist` table contains information about processes involved in write forwarding. The contents of this table are visible only on the writer DB instance for a DB cluster with global or in-cluster write forwarding turned on. An empty result set is returned on reader DB instances. | Field | Data type | Description | | --- | --- | --- | | ID | bigint | The identifier of the connection on the writer DB instance. This identifier is the same value displayed in the Id column of the SHOW PROCESSLIST statement and returned by the CONNECTION\$1ID() function within the thread. | | USER | varchar(32) | The MySQL user that issued the statement. | | HOST | varchar(255) | The MySQL client that issued the statement. For forwarded statements, this field shows the application client host address that established the connection on the forwarding reader DB instance. | | DB | varchar(64) | The default database for the thread. | | COMMAND | varchar(16) | The type of command the thread is executing on behalf of the client, or Sleep if the session is idle. For descriptions of thread commands, see the MySQL documentation on [Thread Command Values](https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html) in the MySQL documentation. | | TIME | int | The time in seconds that the thread has been in its current state. | | STATE | varchar(64) | An action, event, or state that indicates what the thread is doing. For descriptions of state values, see [General Thread States](https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html) in the MySQL documentation. | | INFO | longtext | The statement that the thread is executing, or NULL if it isn't executing a statement. The statement might be the one sent to the server, or an innermost statement if the statement executes other statements. | | IS\$1FORWARDED | bigint | Indicates whether the thread is forwarded from a reader DB instance. | | REPLICA\$1SESSION\$1ID | bigint | The connection identifier on the Aurora Replica. This identifier is the same value displayed in the Id column of the SHOW PROCESSLIST statement on the forwarding Aurora reader DB instance. | | REPLICA\$1INSTANCE\$1IDENTIFIER | varchar(64) | The DB instance identifier of the forwarding thread. | | REPLICA\$1CLUSTER\$1NAME | varchar(64) | The DB cluster identifier of the forwarding thread. For in-cluster write forwarding, this identifier is the same DB cluster as the writer DB instance. | | REPLICA\$1REGION | varchar(64) | The AWS Region from which the forwarding thread originates. For in-cluster write forwarding, this Region is the same AWS Region as the writer DB instance. | # Database engine updates for Amazon Aurora MySQL Aurora MySQL updates Amazon Aurora releases updates regularly. Updates are applied to Aurora DB clusters during system maintenance windows. The timing when updates are applied depends on the region and maintenance window setting for the DB cluster, as well as the type of update. Amazon Aurora releases are made available to all AWS Regions over the course of multiple days. Some Regions might temporarily show an engine version that isn't available in a different Region yet. Updates are applied to all instances in a DB cluster at the same time. An update requires a database restart on all instances in a DB cluster, so you experience 20 to 30 seconds of downtime, after which you can resume using your DB cluster or clusters. You can view or change your maintenance window settings from the [AWS Management Console](https://console.aws.amazon.com/). For details about the Aurora MySQL versions that are supported by Amazon Aurora, see the [https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/Welcome.html](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/Welcome.html). Following, you can learn how to choose the right Aurora MySQL version for your cluster, how to specify the version when you create or upgrade a cluster, and the procedures to upgrade a cluster from one version to another with minimal interruption. **Topics** + [ # Checking Aurora MySQL version numbers ](AuroraMySQL.Updates.Versions.md) + [ # Long-term support (LTS) and beta releases for Amazon Aurora MySQL ](AuroraMySQL.Update.SpecialVersions.md) + [ # Preparing for Amazon Aurora MySQL-Compatible Edition version 2 end of standard support ](Aurora.MySQL57.EOL.md) + [ # Preparing for Amazon Aurora MySQL-Compatible Edition version 1 end of life ](Aurora.MySQL56.EOL.md) + [ # Upgrading Amazon Aurora MySQL DB clusters ](AuroraMySQL.Updates.Upgrading.md) + [ # Database engine updates and fixes for Amazon Aurora MySQL ](AuroraMySQL.Updates.RN.md) # Checking Aurora MySQL version numbers Checking version numbers Although Aurora MySQL-Compatible Edition is compatible with the MySQL database engines, Aurora MySQL includes features and bug fixes that are specific to particular Aurora MySQL versions. Application developers can check the Aurora MySQL version in their applications by using SQL. Database administrators can check and specify Aurora MySQL versions when creating or upgrading Aurora MySQL DB clusters and DB instances. **Topics** + [ ## Checking or specifying Aurora MySQL engine versions through AWS ](#AuroraMySQL.Updates.EngineVersions) + [ ## Checking Aurora MySQL versions using SQL ](#AuroraMySQL.Updates.DBVersions) ## Checking or specifying Aurora MySQL engine versions through AWS Aurora MySQL engine versions When you perform administrative tasks using the AWS Management Console, AWS CLI, or RDS API, you specify the Aurora MySQL version in a descriptive alphanumeric format. Starting with Aurora MySQL version 2, Aurora engine versions have the following syntax. ``` mysql-major-version.mysql_aurora.aurora-mysql-version ``` The `mysql-major-version-` portion is `5.7` or `8.0`. This value represents the version of the client protocol and general level of MySQL feature support for the corresponding Aurora MySQL version. The `aurora-mysql-version` is a dotted value with three parts: the Aurora MySQL major version, the Aurora MySQL minor version, and the patch level. The major version is `2` or `3`. Those values represent Aurora MySQL compatible with MySQL 5.7 or 8.0, respectively. The minor version represents the feature release within the 2.x or 3.x series. The patch level begins at `0` for each minor version, and represents the set of subsequent bug fixes that apply to the minor version. Occasionally, a new feature is incorporated into a minor version but not made visible immediately. In these cases, the feature undergoes fine-tuning and is made public in a later patch level. All 2.x Aurora MySQL engine versions are wire-compatible with Community MySQL 5.7.12 or higher. All 3.x Aurora MySQL engine versions are wire-compatible with MySQL 8.0.23 or higher. You can refer to release notes of the specific 3.x version to find the corresponding MySQL compatible version. For example, the engine versions for Aurora MySQL 3.04.0 and 2.11.2 are the following. ``` 8.0.mysql_aurora.3.04.0 5.7.mysql_aurora.2.11.2 ``` **Note** There isn't a one-to-one correspondence between community MySQL versions and the Aurora MySQL 2.x versions. For Aurora MySQL version 3, there is a more direct mapping. To check which bug fixes and new features are in a particular Aurora MySQL release, see [ Database engine updates for Amazon Aurora MySQL version 3](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.Updates.30Updates.html) and [ Database engine updates for Amazon Aurora MySQL version 2](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.Updates.20Updates.html) in the *Release Notes for Aurora MySQL*. For a chronological list of new features and releases, see [Document history](WhatsNew.md). To check the minimum version required for a security-related fix, see [ Security vulnerabilities fixed in Aurora MySQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.CVE_list.html)in the *Release Notes for Aurora MySQL*. You specify the Aurora MySQL engine version in some AWS CLI commands and RDS API operations. For example, you specify the `--engine-version` option when you run the AWS CLI commands [create-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-cluster.html) and [modify-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-cluster.html). You specify the `EngineVersion` parameter when you run the RDS API operations [CreateDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBCluster.html) and [ModifyDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBCluster.html). In Aurora MySQL version 2 and higher, the engine version in the AWS Management Console also includes the Aurora version. Upgrading the cluster changes the displayed value. This change helps you to specify and check the precise Aurora MySQL versions, without the need to connect to the cluster or run any SQL commands. **Tip** For Aurora clusters managed through CloudFormation, this change in the `EngineVersion` setting can trigger actions by CloudFormation. For information about how CloudFormation treats changes to the `EngineVersion` setting, see [the CloudFormation documentation](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-rds-dbcluster.html). ## Checking Aurora MySQL versions using SQL The Aurora version numbers that you can retrieve in your application using SQL queries use the format `