|« PreviousNext »|
|Did this page help you? Yes | No | Tell us about it...|
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 Amazon RDS tools to perform management actions for the DB instance, such as reconfiguring or 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.
These are the common management tasks you perform with an Amazon RDS MySQL DB instance, with links to information about each task:
For planning information, such as MySQL versions, storage engines, security, and features supported in Amazon RDS, see Amazon RDS MySQL Planning Information.
You can create an Amazon RDS MySQL DB instance after you have met prerequisites, such as creating security groups, DB parameter groups, or DB option groups. For information, see Creating a DB Instance Running the MySQL Database Engine.
After creating the security group and DB instance, you can connect to the DB instance from MySQL applications and utilities. For information, see Connecting to a DB Instance Running the MySQL Database Engine.
A newly created Amazon RDS DB instance has one empty database with the name you specified when you created the DB instance, and one masteruser account with the name and password you specified. You must use a MySQL tool or utility to log in as the masteruser, and then use MySQL commands and SQL statements to add all of the users and elements required for your applications to store and retrieve data in the DB instance, such as:
Create all user IDs and grant them the appropriate permissions. For information, go to MySQL User Account Management in the MySQL documentation.
Create any required databases and objects such as tables and views. For information, go to Data Definition Statements in the MySQL documentation.
Establish procedures for importing or exporting data. For information on some recommended procedures, see Importing and Exporting Data From a MySQL DB Instance.
You may need to periodically change your DB instance, such as to resize or reconfigure the DB instance. For information, see Modifying a DB Instance Running the MySQL Database Engine. For additional information on specific tasks, see:
You can configure your DB instance to take automated backups, or take manual snapshots, and then restore instances from the backups or snapshots. For information, see Back Up and Restore.
You can monitor an instance through actions such as viewing the MySQL logs, CloudWatch Amazon RDS metrics, and events. For information, see Monitoring .
You can offload read traffic from your primary MySQL DB instance by creating read replicas. For information, see Working with Read Replicas.
There are several Amazon RDS features you can use with MySQL DB instances that are common across the Amazon RDS database engines. For information, see:
There are also several appendices with useful information about working with Amazon RDS MySQL DB instances:
Amazon RDS currently supports MySQL versions 5.6, 5.5, and 5.1. 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-5 months of their General Availability release.
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 would be considered major if the major version number changes; for example, going from version 5.1.71 to 5.5.33. A version change would be considered minor if only the minor version number changes - for example, going from version 5.5.31 to 5.5.33.
You can specify any currently supported MySQL version when creating a new DB Instance. You can specify the MySQL 5.6, 5.5, or 5.1 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 (e.g. MySQL 5.6) 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.
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 Upgrading a DB Instance.
Amazon RDS currently supports the major version upgrades from MySQL version 5.1 to version 5.5 and from MySQL version 5.5 to version 5.6. Because major version upgrades involve some compatibility risk, they will 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 Upgrading a DB Instance.
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.1, for 3 years after they are initially supported by Amazon RDS.
We intend to support minor MySQL version releases (e.g. MySQL 5.1.45) 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.
Most Amazon RDS DB engines support option groups that allow you to select
additional features for your DB instance. MySQL 5.6 DB instances support the
memcached option, a simple, key-based cache. For more
information about the
memcached option, see Appendix: Options for MySQL DB Engine.
For more information about working with option groups, see Working with Option Groups.
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 to use the InnoDB
memcached interface. For more information, see MySQL 5.6 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 may 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 may be helpful under some conditions. For more information on MyISAM restrictions, see Automated Backups with Unsupported MySQL Storage Engines.
If you would like to convert existing MyISAM tables to InnoDB tables, you can use the alter table command (e.g., alter table TABLE_NAME engine=innodb;). Please 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.
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 Using AWS Identity and Access Management (IAM) to Manage Access to Amazon RDS Resources.
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.
Once a connection has been opened to a MySQL DB instance, authentication
of the login and permissions are applied the same way as in a stand-alone
instance of MySQL. Commands such as
SET PASSWORD work
just as they do in stand-alone databases, as does directly modifying
database schema tables. For information, go to MySQL User Account Management in the MySQL
When you create an Amazon RDS DB instance, the master user has the following default privileges:
create temporary tables
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 API or the
rds-modify-db-instance command line 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
To provide management services for each DB instance, the
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_query commands have been restricted. The Amazon RDS
provided to allow you to terminate user sessions or queries on DB instances.
Amazon RDS supports SSL connections with DB instances running the MySQL database engine.
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 https://rds.amazonaws.com/doc/mysql-ssl-ca-cert.pem.
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 myinstance.c9akciq32.rds-us-east-1.amazonaws.com
Prior to August 5, 2014, SSL certificate verification was not available and SSL certificates for MySQL DB instances did not use the DB instance endpoint as the CN for the SSL certificate for the DB instance. If you have a MySQL DB instance that was created before August 5, 2014, and you want to ensure that the instance endpoint is included as the CN for the SSL certificate for that DB instance, then rename the DB instance. When you rename a DB instance, a new certificate is deployed for the DB instance and the instance is rebooted to enable the new certificate.
The SSL certificate verification
connection string parameter is not valid for connections prior to August 5,
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.
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 Preloading the InnoDB Buffer Pool for Faster Restart in the MySQL documentation.
MySQL on Amazon RDS supports InnoDB cache warming for MySQL version 5.6 and later. To enable InnoDB cache warming,
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.
For MySQL version 5.6.19 and later, you can save and load the InnoDB cache "on demand."
To dump the current state of the buffer pool to disk, call the mysql.rds_innodb_buffer_pool_dump_now stored procedure.
To load the saved state of the buffer pool from disk, call the mysql.rds_innodb_buffer_pool_load_now stored procedure.
To cancel a load operation in progress, call the mysql.rds_innodb_buffer_pool_load_abort stored procedure.
Amazon RDS currently does not support the following MySQL features:
Global Transaction IDs
Transportable Table Space
Password Strength Plugin
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).
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
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:
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.
To resolve the error, your master DB instance must be running MySQL version 5.6.4 or later and you must update the format of the date and time columns in the replicated tables on your master DB instance. For information on upgrading a MySQL DB instance on Amazon RDS to version 5.6, see Upgrading from MySQL 5.1 to MySQL 5.6.
To upgrade your date and time columns to the new format on your master DB
instance, you must issue the
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
timestamp and create an
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 systables.NAME = 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 IN (3, 6));
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
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
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.
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").
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.
Amazon RDS instances can support files with a maximum size of 2 TB due to underlying file system constraints. For MySQL, this file size limit constrains each table to a maximum size of 2 TB when using InnoDB file-per-table. This also constrains the system tablespace to a maximum size of 2 TB.
InnoDB file per table is enabled by default in MySQL version 5.6.6 and later, but
must be enabled for MySQL versions 5.1 and 5.5. To enable InnoDB file per table, set
innodb_file_per_table parameter to 1 in the parameter group for the
DB instance. For information on updating a parameter group, see Working with DB Parameter Groups.
Once you have enabled InnoDB file per table, you can then issue an
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
-- Move table from system tablespace to its own tablespace. SET GLOBAL innodb_file_per_table=1; ALTER TABLE table_name ENGINE=InnoDB; -- Move table from its own tablespace to system tablespace. SET GLOBAL innodb_file_per_table=0; ALTER TABLE table_name ENGINE=InnoDB;
We do not recommend allowing tables to grow to 2TiB. A better practice, in general, is to partition data into smaller tables, which can improve performance and recovery times. There are advantages and disadvantages to each approach depending on your application. For more information, see InnoDB File-Per-Table Mode.