Amazon Relational Database Service
User Guide (API Version 2014-10-31)

MySQL on Amazon RDS

Amazon RDS supports DB instances running several versions of MySQL. You first use the Amazon RDS management tools or interfaces to create an Amazon RDS MySQL DB instance. You can then use the resizing the DB instance, authorizing connections to the DB instance, creating and restoring from backups or snapshots, creating Multi-AZ secondaries, creating Read Replicas, and monitoring the performance of the DB instance. You use standard MySQL utilities and applications to store and access the data in the DB instance.

Amazon RDS for MySQL is compliant with many industry standards. For example, you can use Amazon RDS for MySQL databases to build HIPAA-compliant applications and to store healthcare related information, including protected health information (PHI) under an executed Business Associate Agreement (BAA) with AWS. Amazon RDS for MySQL also meets Federal Risk and Authorization Management Program (FedRAMP) security requirements and has received a FedRAMP Joint Authorization Board (JAB) Provisional Authority to Operate (P-ATO) at the FedRAMP HIGH Baseline within the AWS GovCloud (US) region. For more information on supported compliance standards, see AWS Cloud Compliance.

The following are common management tasks that you can perform with an Amazon RDS MySQL DB instance, with links to relevant documentation for each task.

Task Area Relevant Documentation

Understanding Amazon Relational Database Service (Amazon RDS)

Understand key Amazon RDS components, including DB instances, regions, Availability Zones, security groups, parameter groups, and option groups.

What Is Amazon Relational Database Service (Amazon RDS)?

Planning a new RDS MySQL DB instance

Follow best practices to plan a new RDS MySQL DB instance, including its MySQL version upgrades, storage engines, security, and other features supported in Amazon RDS.

MySQL on Amazon RDS Planning Information

Setting up Amazon RDS for first time use

Set up Amazon RDS so that you can create MySQL DB instances in Amazon Web Services (AWS).

Setting Up for Amazon RDS

Understanding Amazon RDS DB instances

Create virtual MySQL server instances that run in AWS. Because DB instances are the building blocks of Amazon RDS, we recommend that you understand their principles.

Amazon RDS DB Instances

Creating a DB instance for production

Create a DB instance for production purposes. Creating an instance includes choosing a DB instance class with appropriate processing power and memory capacity and choosing a storage type that supports the way you expect to use your database.

DB Instance Class

Amazon RDS Storage Types

Creating a DB Instance Running the MySQL Database Engine

Managing security for your DB instance

By default, DB instances are created with a firewall that prevents access to them. You must create a security group with the correct IP addresses and network configuration to access the DB instance. You can also use AWS Identity and Access Management (IAM) policies to assign permissions that determine who is allowed to manage RDS resources.

Security in Amazon RDS

Overview of Managing Access Permissions to Your Amazon RDS Resources

Amazon RDS Security Groups

Determining Whether You Are Using the EC2-VPC or EC2-Classic Platform

Connecting to your DB instance

Connect to your DB instance using a standard SQL client application such as the MySQL command line utility or MySQL Workbench.

Connecting to a DB Instance Running the MySQL Database Engine

Configuring high availability for a production DB instance

Provide high availability with synchronous standby replication in a different Availability Zone, automatic failover, fault tolerance for DB instances using Multi-AZ deployments, and Read Replicas.

High Availability (Multi-AZ)

Configuring a DB instance in an Amazon Virtual Private Cloud

Configure a virtual private cloud (VPC) in the Amazon VPC service. An Amazon VPC is a virtual network logically isolated from other virtual networks in AWS.

Determining Whether You Are Using the EC2-VPC or EC2-Classic Platform

Working with an Amazon RDS DB Instance in a VPC

Configuring specific MySQL database parameters and features

Configure specific MySQL database parameters with a parameter group that can be associated with many DB instances. You can also configure specific MySQL database features with an option group that can be associated with many DB instances.

Working with DB Parameter Groups

Working with Option Groups

Appendix: Options for MySQL Database Engine

Modifying a DB instance running the MySQL database engine

Change the settings of a DB instance to accomplish tasks such as adding additional storage or changing the DB instance class.

Modifying a DB Instance Running the MySQL Database Engine

Modifying an Amazon RDS DB Instance and Using the Apply Immediately Parameter

Configuring database backup and restore

Configure your DB instance to take automated backups. You can also back up and restore your databases manually by using full backup files.

Working With Backups

Backing Up and Restoring Amazon RDS DB Instances

Importing and exporting data

Import data from other RDS MySQL DB instances, MySQL instances running external to Amazon RDS, and other types of data sources, and export data to MySQL instances running external to Amazon RDS.

Importing and Exporting Data From a MySQL DB Instance

Monitoring a MySQL DB instance

Monitor your RDS MySQL DB instance by using Amazon CloudWatch RDS metrics, events, and Enhanced Monitoring. View log files for your RDS MySQL DB instance.

Monitoring Amazon RDS

Viewing DB Instance Metrics

Viewing Amazon RDS Events

Amazon RDS Database Log Files

MySQL Database Log Files

Replicating your data

Create a MySQL Read Replica—optionally, in a different AWS Region—for load balancing, disaster recovery, and processing read-heavy database workloads, such as for analysis and reporting.

Working with PostgreSQL, MySQL, and MariaDB Read Replicas

Replication with a MySQL or MariaDB Instance Running External to Amazon RDS

There are also several appendices with useful information about working with Amazon RDS MySQL DB instances:

MySQL on Amazon RDS Planning Information

MySQL on Amazon RDS Versions

For MySQL, version numbers are organized as version = X.Y.Z. In Amazon RDS terminology, X.Y denotes the major version, and Z is the minor version number. For Amazon RDS implementations, a version change is considered major if the major version number changes—for example, going from version 5.6 to 5.7. A version change is considered minor if only the minor version number changes—for example, going from version 5.6.22 to 5.6.23.

Amazon RDS currently supports MySQL major versions 5.5, 5.6, and 5.7. MySQL minor version support varies by AWS Region. Use the following table to see what MySQL minor versions are supported in each AWS Region.

AWS Region Supported MySQL 5.5 Minor Versions Supported MySQL 5.6 Minor Versions Supported MySQL 5.7 Minor Versions
US East (N. Virginia) Region (us-east-1) 5.5.46, 5.5.53 5.6.19a, 5.6.19b, 5.6.21, 5.6.21b, 5.6.22, 5.6.23, 5.6.27, 5.6.29, 5.6.34 5.7.10, 5.7.11, 5.7.16
US East (Ohio) Region (us-east-2) 5.5.46, 5.5.53 5.6.29, 5.6.34 5.7.11, 5.7.16
US West (N. California) Region (us-west-1) 5.5.46, 5.5.53 5.6.19a, 5.6.19b, 5.6.21, 5.6.21b, 5.6.22, 5.6.23, 5.6.27, 5.6.29, 5.6.34 5.7.10, 5.7.11, 5.7.16
US West (Oregon) Region (us-west-2) 5.5.46, 5.5.53 5.6.19a, 5.6.19b, 5.6.21, 5.6.21b, 5.6.22, 5.6.23, 5.6.27, 5.6.29, 5.6.34 5.7.10, 5.7.11, 5.7.16
Asia Pacific (Mumbai) Region (ap-south-1) 5.5.46, 5.5.53 5.6.27, 5.6.29, 5.6.34 5.7.10, 5.7.11, 5.7.16
Asia Pacific (Seoul) Region (ap-northeast-2) 5.5.46, 5.5.53 5.6.23, 5.6.27, 5.6.29, 5.6.34 5.7.10, 5.7.11, 5.7.16
Asia Pacific (Singapore) Region (ap-southeast-1) 5.5.46, 5.5.53 5.6.19a, 5.6.19b, 5.6.21, 5.6.21b, 5.6.22, 5.6.23, 5.6.27, 5.6.29, 5.6.34 5.7.10, 5.7.11, 5.7.16
Asia Pacific (Sydney) Region (ap-southeast-2) 5.5.46, 5.5.53 5.6.19a, 5.6.19b, 5.6.21, 5.6.21b, 5.6.22, 5.6.23, 5.6.27, 5.6.29, 5.6.34 5.7.10, 5.7.11, 5.7.16
Asia Pacific (Tokyo) Region (ap-northeast-1) 5.5.46, 5.5.53 5.6.19a, 5.6.19b, 5.6.21, 5.6.21b, 5.6.22, 5.6.23, 5.6.27, 5.6.29, 5.6.34 5.7.10, 5.7.11, 5.7.16
Canada (Central) Region (ca-central-1) 5.5.46, 5.5.53 5.6.29, 5.6.34 5.7.11, 5.7.16
EU (Frankfurt) Region (eu-central-1) 5.5.46, 5.5.53 5.6.19a, 5.6.19b, 5.6.21, 5.6.21b, 5.6.22, 5.6.23, 5.6.27, 5.6.29, 5.6.34 5.7.10, 5.7.11, 5.7.16
EU (Ireland) Region (eu-west-1) 5.5.46, 5.5.53 5.6.19a, 5.6.19b, 5.6.21, 5.6.21b, 5.6.22, 5.6.23, 5.6.27, 5.6.29, 5.6.34 5.7.10, 5.7.11, 5.7.16
EU (London) Region (eu-west-2) 5.5.46, 5.5.53 5.6.29, 5.6.34 5.7.11, 5.7.16
South America (São Paulo) Region (sa-east-1) 5.5.46, 5.5.53 5.6.19a, 5.6.19b, 5.6.21, 5.6.21b, 5.6.22, 5.6.23, 5.6.27, 5.6.29, 5.6.34 5.7.10, 5.7.11, 5.7.16

Over time, we plan to support additional MySQL versions for Amazon RDS. The number of new version releases supported in a given year will vary based on the frequency and content of the MySQL version releases and the outcome of a thorough vetting of the release by our database engineering team. However, as a general guidance, we aim to support new MySQL versions within 3 to 5 months of their General Availability release.

You can specify any currently supported MySQL version when creating a new DB instance. You can specify the MySQL 5.7, 5.6, or 5.5 major versions, and any supported minor version for the specified major version. If no version is specified, Amazon RDS will default to a supported version, typically the most recent version. If a major version (for example, MySQL 5.7) is specified but a minor version is not, Amazon RDS will default to a recent release of the major version you have specified. To see a list of supported versions, as well as defaults for newly created DB instances, use the DescribeDBEngineVersions API action.

With Amazon RDS, you control when to upgrade your MySQL instance to a new version supported by Amazon RDS. You can maintain compatibility with specific MySQL versions, test new versions with your application before deploying in production, and perform version upgrades at times that best fit your schedule.

Unless you specify otherwise, your DB instance will automatically be upgraded to new MySQL minor versions as they are supported by Amazon RDS. This patching will occur during your scheduled maintenance window, and it will be announced on the Amazon RDS Community Forum in advance. To turn off automatic version upgrades, set the AutoMinorVersionUpgrade parameter to “false.”

If you opt out of automatically scheduled upgrades, you can manually upgrade to a supported minor version release by following the same procedure as you would for a major version update. For information, see DB Instance and DB Cluster Maintenance and Upgrades.

Amazon RDS currently supports the major version upgrades from MySQL version 5.5 to version 5.6 and MySQL version 5.6 to version 5.7. Because major version upgrades involve some compatibility risk, they do not occur automatically; you must make a request to modify the DB instance. You should thoroughly test any upgrade before upgrading your production instances. For information about upgrading a DB instance, see DB Instance and DB Cluster Maintenance and Upgrades.

You can test a DB instance against a new version before upgrading by creating a DB snapshot of your existing DB instance, restoring from the DB snapshot to create a new DB instance, and then initiating a version upgrade for the new DB instance. You can then experiment safely on the upgraded clone of your DB instance before deciding whether or not to upgrade your original DB instance.

The Amazon RDS deprecation policy for MySQL includes the following:

  • We intend to support major MySQL version releases, including MySQL 5.5, for 3 years after they are initially supported by Amazon RDS.

  • We intend to support minor MySQL version releases (for example, MySQL 5.5.46) for at least 1 year after they are initially supported by Amazon RDS.

  • After a MySQL major or minor version has been “deprecated,” we expect to provide a three month grace period for you to initiate an upgrade to a supported version prior to an automatic upgrade being applied during your scheduled maintenance window.

Using the memcached Option with MySQL

Most Amazon RDS DB engines support option groups that allow you to select additional features for your DB instance. DB instances on MySQL version 5.6 and later support the memcached option, a simple, key-based cache. For more information about the memcached option, see Appendix: Options for MySQL Database Engine. For more information about working with option groups, see Working with Option Groups.

Amazon RDS Supported Storage Engines

While MySQL supports multiple storage engines with varying capabilities, not all of them are optimized for recovery and data durability. Amazon RDS fully supports the InnoDB storage engine for MySQL DB instances. Amazon RDS features such as Point-In-Time restore and snapshot restore require a recoverable storage engine and are supported for the InnoDB storage engine only. You must be running an instance of MySQL 5.6 or later to use the InnoDB memcached interface. For more information, see MySQL memcached Support.

The Federated Storage Engine is currently not supported by Amazon RDS for MySQL.

The MyISAM storage engine does not support reliable recovery and can result in lost or corrupt data when MySQL is restarted after a recovery, preventing Point-In-Time restore or snapshot restore from working as intended. However, if you still choose to use MyISAM with Amazon RDS, snapshots can be helpful under some conditions.

If you want to convert existing MyISAM tables to InnoDB tables, you can use the alter table command (for example, alter table TABLE_NAME engine=innodb;). Bear in mind that MyISAM and InnoDB have different strengths and weaknesses, so you should fully evaluate the impact of making this switch on your applications before doing so.

MySQL 5.1 is no longer supported in Amazon RDS. However, you can restore existing MySQL 5.1 snapshots. When you restore a MySQL 5.1 snapshot, the instance is automatically upgraded to MySQL 5.5.

Amazon RDS and MySQL Security

Security for Amazon RDS MySQL DB instances is managed at three levels:

  • AWS Identity and Access Management controls who can perform Amazon RDS management actions on DB instances. When you connect to AWS using IAM credentials, your IAM account must have IAM policies that grant the permissions required to perform Amazon RDS management operations. For more information, see Authentication and Access Control for Amazon RDS.

  • When you create a DB instance, you use either a VPC security group or a DB security group to control which devices and Amazon EC2 instances can open connections to the endpoint and port of the DB instance. These connections can be made using SSL. In addition, firewall rules at your company can control whether devices running at your company can open connections to the DB instance.

  • To authenticate login and permissions for a MySQL DB instance, you can take either of the following approaches, or a combination of them.

    You can take the same approach as with a stand-alone instance of MySQL. Commands such as CREATE USER, RENAME USER, GRANT, REVOKE, and SET PASSWORD work just as they do in on-premises databases, as does directly modifying database schema tables. For information, see MySQL User Account Management in the MySQL documentation.

    You can also use IAM database authentication. With IAM database authentication, you authenticate to your DB instance by using an IAM user or IAM role and an authentication token. An authentication token is a unique value that is generated using the Signature Version 4 signing process. By using IAM database authentication, you can use the same credentials to control access to your AWS resources and your databases. For more information, see IAM Database Authentication for MySQL and Amazon Aurora.

When you create an Amazon RDS DB instance, the master user has the following default privileges:

  • alter

  • alter routine

  • create

  • create routine

  • create temporary tables

  • create user

  • create view

  • delete

  • drop

  • event

  • execute

  • grant option

  • index

  • insert

  • lock tables

  • process

  • references

  • replication client

  • replication slave (MySQL 5.6 and later)

  • select

  • show databases

  • show view

  • trigger

  • update


Although it is possible to delete the master user on the DB instance, it is not recommended. To recreate the master user, use the ModifyDBInstance RDS API action or the modify-db-instance AWS CLI tool and specify a new master user password with the appropriate parameter. If the master user does not exist in the instance, the master user will be created with the specified password.

To provide management services for each DB instance, the rdsadmin user is created when the DB instance is created. Attempting to drop, rename, change the password, or change privileges for the rdsadmin account will result in an error.

To allow management of the DB instance, the standard kill and kill_query commands have been restricted. The Amazon RDS commands rds_kill and rds_kill_query are provided to allow you to terminate user sessions or queries on DB instances.

Using SSL with a MySQL DB Instance

Amazon RDS supports SSL connections with DB instances running the MySQL database engine.


Amazon Aurora is compatible with MySQL. However, you use a different SSL certificate to connect to an Amazon Aurora DB cluster. For information on connecting to Amazon Aurora using SSL, see Securing Aurora Data with SSL.

Amazon RDS creates an SSL certificate and installs the certificate on the DB instance when Amazon RDS provisions the instance. These certificates are signed by a certificate authority. The SSL certificate includes the DB instance endpoint as the Common Name (CN) for the SSL certificate to guard against spoofing attacks. The public key is stored at

An SSL certificate created by Amazon RDS is the trusted root entity and should work in most cases but might fail if your application does not accept certificate chains. If your application does not accept certificate chains, you might need to use an intermediate certificate to connect to your region. For example, you must use an intermediate certificate to connect to the AWS GovCloud (US) region using SSL. For a list of regional intermediate certificates that you can download, see Intermediate Certificates.

To encrypt connections using the default mysql client, launch the mysql client using the --ssl-ca parameter to reference the public key, for example:

mysql -h --ssl-ca=[full path]rds-combined-ca-bundle.pem --ssl-verify-server-cert

You can use the GRANT statement to require SSL connections for specific users accounts. For example, you can use the following statement to require SSL connections on the user account encrypted_user:

GRANT USAGE ON *.* TO 'encrypted_user'@'%' REQUIRE SSL


For more information on SSL connections with MySQL, go to the MySQL documentation.

Local Time Zone for MySQL DB Instances

By default, the time zone for an RDS MySQL DB instance is Universal Time Coordinated (UTC). You can set the time zone for your DB instance to the local time zone for your application instead.

To set the local time zone for a DB instance, set the time_zone parameter in the parameter group for your DB instance to one of the supported values listed later in this section. When you set the time_zone parameter for a parameter group, all DB instances and Read Replicas that are using that parameter group change to use the new local time zone. For information on setting parameters in a parameter group, see Working with DB Parameter Groups.

After you set the local time zone, all new connections to the database reflect the change. If you have any open connections to your database when you change the local time zone, you won't see the local time zone update until after you close the connection and open a new connection.

You can set a different local time zone for a DB instance and one or more of its Read Replicas. To do this, use a different parameter group for the DB instance and the replica or replicas and set the time_zone parameter in each parameter group to a different local time zone.

If you are replicating across regions, then the replication master DB instance and the Read Replica use different parameter groups (parameter groups are unique to a region). To use the same local time zone for each instance, you must set the time_zone parameter in the instance's and Read Replica's parameter groups.

When you restore a DB instance from a DB snapshot, the local time zone is set to UTC. You can update the time zone to your local time zone after the restore is complete. If you restore a DB instance to a point in time, then the local time zone for the restored DB instance is the time zone setting from the parameter group of the restored DB instance.

Local time zone is supported for MySQL versions 5.5, 5.6, and 5.7 only.

You can set your local time zone to one of the following values.





















































































InnoDB Cache Warming

InnoDB cache warming can provide performance gains for your MySQL DB instance by saving the current state of the buffer pool when the DB instance is shut down, and then reloading the buffer pool from the saved information when the DB instance starts up. This bypasses the need for the buffer pool to "warm up" from normal database use and instead preloads the buffer pool with the pages for known common queries. The file that stores the saved buffer pool information only stores metadata for the pages that are in the buffer pool, and not the pages themselves. As a result, the file does not require much storage space. The file size is about 0.2 percent of the cache size. For example, for a 64 GB cache, the cache warming file size is 128 MB. For more information on InnoDB cache warming, go to Saving and Restoring the Buffer Pool State in the MySQL documentation.

MySQL on Amazon RDS supports InnoDB cache warming for MySQL version 5.6 and later. To enable InnoDB cache warming, set the innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup parameters to 1 in the parameter group for your DB instance. Changing these parameter values in a parameter group will affect all MySQL DB instances that use that parameter group. To enable InnoDB cache warming for specific MySQL DB instances, you might need to create a new parameter group for those instances. For information on parameter groups, see Working with DB Parameter Groups.

InnoDB cache warming primarily provides a performance benefit for DB instances that use standard storage. If you use PIOPS storage, you do not commonly see a significant performance benefit.


If your MySQL DB instance does not shut down normally, such as during a failover, then the buffer pool state will not be saved to disk. In this case, MySQL loads whatever buffer pool file is available when the DB instance is restarted. No harm is done, but the restored buffer pool might not reflect the most recent state of the buffer pool prior to the restart. To ensure that you have a recent state of the buffer pool available to warm the InnoDB cache on startup, we recommend that you periodically dump the buffer pool "on demand." You can dump or load the buffer pool on demand if your DB instance is running MySQL version 5.6.19 or later.

You can create an event to dump the buffer pool automatically and on a regular interval. For example, the following statement creates an event named periodic_buffer_pool_dump that dumps the buffer pool every hour.

CREATE EVENT periodic_buffer_pool_dump ON SCHEDULE EVERY 1 HOUR DO CALL mysql.rds_innodb_buffer_pool_dump_now();
For more information on MySQL events, see Event Syntax in the MySQL documentation.

Dumping and Loading the Buffer Pool on Demand

For MySQL version 5.6.19 and later, you can save and load the InnoDB cache "on demand."

MySQL Features Not Supported By Amazon RDS

Amazon RDS currently does not support the following MySQL features:

  • Global Transaction IDs

  • Transportable Table Space

  • Authentication Plugin

  • Password Strength Plugin

  • Replication Filters

  • Semi-synchronous Replication

In order to deliver a managed service experience, Amazon RDS does not provide shell access to DB instances, and it restricts access to certain system procedures and tables that require advanced privileges. Amazon RDS supports access to databases on a DB instance using any standard SQL client application. Amazon RDS does not allow direct host access to a DB instance via Telnet, Secure Shell (SSH), or Windows Remote Desktop Connection. When you create a DB instance, you are assigned to the db_owner role for all databases on that instance, and you will have all database-level permissions except for those used for backups (Amazon RDS manages backups for you).

Known Issues and Limitations

Known issues and limitations are as follows.

Inconsistent InnoDB Buffer Pool Size

For MySQL 5.7, there is currently a bug in the way that the InnoDB buffer pool size is managed. MySQL 5.7 might adjust the value of the innodb_buffer_pool_size parameter to a large value that can result in the InnoDB buffer pool growing too large and using up too much memory. This effect can cause the MySQL database engine to stop running or can prevent the MySQL database engine from starting. This issue is more common for DB instance classes that have less memory available.

To resolve this issue, set the value of the innodb_buffer_pool_size parameter to a multiple of the product of the innodb_buffer_pool_instances parameter value and the innodb_buffer_pool_chunk_size parameter value. For example, you might set the innodb_buffer_pool_size parameter value to a multiple of eight times the product of the innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size parameter values, as shown in the following example.

innodb_buffer_pool_chunk_size = 536870912 innodb_buffer_pool_instances = 4 innodb_buffer_pool_size = (536870912 * 4) * 8 = 17179869184

For details on this MySQL 5.7 bug, go to in the MySQL documentation.

Memcached Recommended MySQL Version

We recommend that you only use the memcached interface with MySQL version 5.6.21b or later. We do so because there are a number of bug fixes related to the memcached interface that are included in the MySQL engine starting with version 5.6.21b. For more information, go to Changes in MySQL 5.6.20 (2014-07-31) and Changes in MySQL 5.6.21 (2014-09-23) in the MySQL documentation.

For more information on using memcached with MySQL on Amazon RDS, see MySQL memcached Support.

MySQL Version 5.5.40 Asynchronous I/O Is Disabled

You might observe reduced I/O performance if you have a MySQL DB instance that was created before April 23, 2014, and then upgraded to MySQL version 5.5.40 after October 17, 2014. This reduced performance can be caused by an error that disables the innodb_use_native_aio parameter even if the corresponding DB parameter group enables the innodb_use_native_aio parameter.

To resolve this error, we recommend that you upgrade your MySQL DB instance running version 5.5.40 to version 5.5.40a, which corrects this behavior. For information on minor version upgrades, see Upgrading the MySQL DB Engine.

For more information on MySQL asynchronous I/O, go to Asynchronous I/O on Linux in the MySQL documentation.

Index Merge Optimization Returns Wrong Results

Queries that use index merge optimization might return wrong results due to a bug in the MySQL query optimizer that was introduced in MySQL 5.5.37. When you issue a query against a table with multiple indexes the optimizer scans ranges of rows based on the multiple indexes, but does not merge the results together correctly. For more information on the query optimizer bug, go to and in the MySQL bug database.

For example, consider a query on a table with two indexes where the search arguments reference the indexed columns.

SELECT * FROM table1 WHERE indexed_col1 = 'value1' AND indexed_col2 = 'value2';

In this case, the search engine will search both indexes. However, due to the bug, the merged results will be incorrect.

To resolve this issue, you can do one of the following:

  • Set the optimizer_switch parameter to index_merge=off in the DB parameter group for your MySQL DB instance. For information on setting DB parameter group parameters, see Working with DB Parameter Groups.

  • Upgrade your MySQL DB instance to MySQL version 5.6.19a. For information on major version upgrades, see DB Instance and DB Cluster Maintenance and Upgrades.

  • If you cannot upgrade your instance or change the optimizer_switch parameter, you can work around the bug by explicitly identifying an index for the query, for example:

    SELECT * FROM table1 USE INDEX covering_index WHERE indexed_col1 = 'value1' AND indexed_col2 = 'value2';

For more information, go to Index Merge Optimization.

Replication Fails After Upgrading to MySQL Version 5.6.21

If you have a DB instance that runs a version prior to version 5.6.4, or if the DB instance was upgraded from a version prior to version 5.6.4, you can receive the following error if you have a Read Replica that runs MySQL version 5.6.21.

mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail.

MySQL version 5.6.4 introduced a new date and time format for datetime, time, and timestamp columns that allows fractional components in date and time values. The error is caused by a mismatch in date and time formats between the master and the replica, and results in a failure when row-based logging attempts to replay an operation from the master DB instance to the replica DB instance. You might also see a number of related row-based logging messages in your MySQL error log, for example: Relay_log_info, Rows_log_event, and so on. For information on the new date and time format for MySQL, go to Upgrading from MySQL 5.5 to 5.6 in the MySQL documentation.

To resolve the error, you can do either of the following:

  • Upgrade your Read Replica to MySQL version 5.6.23 or later. For information on upgrading a MySQL DB instance on Amazon RDS to version 5.6, see Upgrading Database Engine Versions.

  • Upgrade your master DB instance to MySQL version 5.6.12 or later and update the format of the affected date and time columns. For information on upgrading a MySQL DB instance on Amazon RDS to version 5.6, see Upgrading Database Engine Versions.

    To upgrade your date and time columns to the new format on your master DB instance, you must issue the ALTER TABLE <table_name> FORCE; command.


    Because altering a table locks the table as read-only, we recommend that you perform this update during a maintenance window.

    You can run the following query to find all of the tables in your database that have columns of type datetime, time, or timestamp and create an ALTER TABLE <table_name> FORCE; command for each table.

    SELECT DISTINCT CONCAT('ALTER TABLE `', REPLACE(is_tables.TABLE_SCHEMA, '`', '``'), '`.`', REPLACE(is_tables.TABLE_NAME, '`', '``'), '` FORCE;') FROM information_schema.TABLES is_tables INNER JOIN information_schema.COLUMNS col ON col.TABLE_SCHEMA = is_tables.TABLE_SCHEMA AND col.TABLE_NAME = is_tables.TABLE_NAME LEFT OUTER JOIN information_schema.INNODB_SYS_TABLES systables ON SUBSTRING_INDEX(systables.NAME, '#', 1) = CONCAT(is_tables.TABLE_SCHEMA,'/',is_tables.TABLE_NAME) LEFT OUTER JOIN information_schema.INNODB_SYS_COLUMNS syscolumns ON syscolumns.TABLE_ID = systables.TABLE_ID AND syscolumns.NAME = col.COLUMN_NAME WHERE col.COLUMN_TYPE IN ('time','timestamp','datetime') AND is_tables.TABLE_TYPE = 'BASE TABLE' AND is_tables.TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema') AND (is_tables.ENGINE = 'InnoDB' AND syscolumns.MTYPE = 6);

Log File Size

For MySQL version 5.6.20 and later, there is a size limit on BLOBs written to the redo log. To account for this limit, ensure that the innodb_log_file_size parameter for your MySQL DB instance is 10 times larger than the largest BLOB data size found in your tables, plus the length of other variable length fields (VARCHAR, VARBINARY, TEXT) in the same tables. For information on how to set parameter values, see Working with DB Parameter Groups. For information on the redo log BLOB size limit, go to Changes in MySQL 5.6.20.

MySQL Parameter Exceptions for Amazon RDS DB Instances

Some MySQL parameters require special considerations when used with an Amazon RDS DB instance.


Because Amazon RDS uses a case-sensitive file system, setting the value of the lower_case_table_names server parameter to 2 ("names stored as given but compared in lowercase") is not supported. Supported values for Amazon RDS DB instances are 0 ("names stored as given and comparisons are case-sensitive"), which is the default, or 1 ("names stored in lowercase and comparisons are not case-sensitive").

The lower_case_table_names parameter should be set as part of a custom DB parameter group before creating a DB instance. You should avoid changing the lower_case_table_names parameter for existing database instances because doing so could cause inconsistencies with point-in-time recovery backups and Read Replica DB instances.

Read Replicas should always use the same lower_case_table_names parameter value as the master DB instance.


You can set the long_query_time parameter to a floating point value which allows you to log slow queries to the MySQL slow query log with microsecond resolution. You can set a value such as 0.1 seconds, which would be 100 milliseconds, to help when debugging slow transactions that take less than one second.

MySQL File Size Limits

For Amazon RDS MySQL DB instances, the maximum provisioned storage limit constrains the size of a table to a maximum size of 6 TB when using InnoDB file-per-table tablespaces. This limit also constrains the system tablespace to a maximum size of 6 TB. InnoDB file-per-table tablespaces (with tables each in their own tablespace) is set by default for Amazon RDS MySQL DB instances.


MySQL DB instances created prior to April 2014 have a file and table size limit of 2 TB. This 2 TB file size limit also applies to DB instances or Read Replicas created from DB snapshots taken prior to April 2014, regardless of when the DB instance was created.

There are advantages and disadvantages to using InnoDB file-per-table tablespaces, depending on your application. To determine the best approach for your application, go to InnoDB File-Per-Table Mode in the MySQL documentation.

We don't recommend allowing tables to grow to the maximum file size. In general, a better practice is to partition data into smaller tables, which can improve performance and recovery times.

One option that you can use for breaking a large table up into smaller tables is partitioning. Partitioning distributes portions of your large table into separate files based on rules that you specify. For example, if you store transactions by date, you can create partitioning rules that distribute older transactions into separate files using partitioning. Then periodically, you can archive the historical transaction data that doesn't need to be readily available to your application. For more information, go to in the MySQL documentation.

To determine the file size of a table

Use the following SQL command to determine if any of your tables are too large and are candidates for partitioning.

SELECT TABLE_SCHEMA, TABLE_NAME, round(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) As "Approximate size (MB)" FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema');

To enable InnoDB file-per-table tablespaces

  • To enable InnoDB file-per-table tablespaces, set the innodb_file_per_table parameter to 1 in the parameter group for the DB instance.

To disable InnoDB file-per-table tablespaces

  • To disable InnoDB file-per-table tablespaces, set the innodb_file_per_table parameter to 0 in the parameter group for the DB instance.

For information on updating a parameter group, see Working with DB Parameter Groups.

When you have enabled or disabled InnoDB file-per-table tablespaces, you can issue an ALTER TABLE command to move a table from the global tablespace to its own tablespace, or from its own tablespace to the global tablespace as shown in the following example: