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

Appendix: Options for MySQL Database Engine

This appendix describes options, or additional features, that are available for Amazon RDS instances running the MySQL DB engine. To enable these options, you can add them to a custom option group, and then associate the option group with your DB instance. For more information about working with option groups, see Working with Option Groups.

Amazon RDS supports the following options for MySQL:

Option IDEngine Versions

MEMCACHED

MySQL 5.6 and later

MARIADB_AUDIT_PLUGIN

MySQL 5.6.29 and later

MySQL 5.7.11 and later

MySQL memcached Support

Amazon RDS supports using the memcached interface to InnoDB tables that was introduced in MySQL 5.6. The memcached API enables applications to use InnoDB tables in a manner similar to NoSQL key-value data stores.

Important

We recommend that you only use the memcached interface with MySQL version 5.6.21b or later. This is because there are a number of bug fixes related to the memcached interface which 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.

memcached is a simple, key-based cache. Applications use memcached to insert, manipulate, and retrieve key-value data pairs from the cache. MySQL 5.6 introduced a plugin that implements a daemon service that exposes data from InnoDB tables through the memcached protocol. For more information about the MySQL memcached plugin, go to InnoDB Integration with memcached.

You enable memcached support for an Amazon RDS MySQL 5.6 or later instance by:

  1. Determining the security group to use for controlling access to the memcached interface. If the set of applications already using the SQL interface are the same set that will access the memcached interface, you can use the existing VPC or DB security group used by the SQL interface. If a different set of applications will access the memcached interface, define a new VPC or DB security group. For more information about managing security groups, see Amazon RDS Security Groups

  2. Creating a custom DB option group, selecting MySQL as the engine type and a 5.6 or later version. For more information about creating an option group, see Creating an Option Group.

  3. Adding the MEMCACHED option to the option group. Specify the port that the memcached interface will use, and the security group to use in controlling access to the interface. For more information about adding options, see Adding an Option to an Option Group.

  4. Modifying the option settings to configure the memcached parameters, if necessary. For more information about how to modify option settings, see Modifying an Option Setting.

  5. Applying the option group to an instance. Amazon RDS enables memcached support for that instance when the option group is applied:

  6. Specifying which columns in your MySQL tables can be accessed through the memcached interface. The memcached plug-in creates a catalog table named containers in a dedicated database named innodb_memcache. You insert a row into the containers table to map an InnoDB table for access through memcached. You specify a column in the InnoDB table that is used to store the memcached key values, and one or more columns that are used to store the data values associated with the key. You also specify a name that a memcached application uses to refer to that set of columns. For details on inserting rows in the containers table, go to Internals of the InnoDB memcached Plugin. For an example of mapping an InnoDB table and accessing it through memcached, go to Specifying the Table and Column Mappings for an InnoDB + memcached Application.

  7. If the applications accessing the memcached interface are on different computers or EC2 instances than the applications using the SQL interface, add the connection information for those computers to the VPC or DB security group associated with the MySQL instance. For more information about managing security groups, see Amazon RDS Security Groups.

You turn off the memcached support for an instance by modifying the instance and specifying the default option group for your MySQL version. For more information about modifying a MySQL instance, see Modifying a DB Instance Running the MySQL Database Engine.

MySQL memcached Security Considerations

The memcached protocol does not support user authentication. For more information about MySQL memcached security considerations, go to memcached Deployment and Using memcached as a MySQL Caching Layer.

You can take the following actions to help increase the security of the memcached interface:

  • Specify a different port than the default of 11211 when adding the MEMCACHED option to the option group.

  • Ensure that you associate the memcached interface with either a VPC or DB security group that limits access to known, trusted client addresses or EC2 instances. For more information about managing security groups, see Amazon RDS Security Groups.

MySQL memcached Connection Information

To access the memcached interface, an application must specify both the DNS name of the Amazon RDS instance and the memcached port number. For example, if an instance has a DNS name of my-cache-instance.cg034hpkmmjt.region.rds.amazonaws.com and the memcached interface is using port 11212, the connection information specified in PHP would be:

<?php

$cache = new Memcache;
$cache->connect('my-cache-instance.cg034hpkmmjt.region.rds.amazonaws.com',11212);
?> 

To find the DNS name and memcached port of an Amazon RDS MySQL instance

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the top right corner of the AWS Management Console, select the region that contains the DB instance.

  3. In the navigation pane, click Instances.

  4. Select the arrow to the left of name of the DB Instance running the MySQL database engine. In the description display, note the value of the endpoint field. The DNS name is the part of the endpoint up to the semicolon (:). Ignore the semicolon and the port number after the semicolon, that port is not used to access the memcached interface.

  5. Note the name listed in the Option Group(s) field.

  6. In the navigation pane, click Option Groups.

  7. Select the arrow to the left of the name of the option group used by the MySQL DB instance. In the description display, note the value of the port setting in the MEMCACHED option.

MySQL memcached Option Settings

Amazon RDS exposes the MySQL memcached parameters as option settings in the Amazon RDS MEMCACHED option.

MySQL memcached Parameters

  • DAEMON_MEMCACHED_R_BATCH_SIZE - an integer that specifies how many memcached read operations (get) to perform before doing a COMMIT to start a new transaction. The allowed values are 1 to 4294967295, the default is 1. The option does not take effect until the instance is restarted.

  • DAEMON_MEMCACHED_W_BATCH_SIZE - an integer that specifies how many memcached write operations, such as add, set, or incr, to perform before doing a COMMIT to start a new transaction. The allowed values are 1 to 4294967295, the default is 1. The option does not take effect until the instance is restarted.

  • INNODB_API_BK_COMMIT_INTERVAL - an integer that specifies how often to auto-commit idle connections that use the InnoDB memcached interface. The allowed values are 1 to 1073741824, the default is 5. The option takes effect immediately, without requiring that you restart the instance.

  • INNODB_API_DISABLE_ROWLOCK - a Boolean that disables (1 (true)) or enables (0 (false)) the use of row locks when using the InnoDB memcached interface. The default is 0 (false). The option does not take effect until the instance is restarted.

  • INNODB_API_ENABLE_MDL - a Boolean that when set to 0 (false) locks the table used by the InnoDB memcached plugin, so that it cannot be dropped or altered by DDL through the SQL interface. The default is 0 (false). The option does not take effect until the instance is restarted.

  • INNODB_API_TRX_LEVEL - an integer that specifies the transaction isolation level for queries processed by the memcached interface. The allowed values are 0 to 3. The default is 0. The option does not take effect until the instance is restarted.

Amazon RDS configures these MySQL memcached parameters, they cannot be modified: DAEMON_MEMCACHED_LIB_NAME, DAEMON_MEMCACHED_LIB_PATH, and INNODB_API_ENABLE_BINLOG. The parameters that MySQL administrators set by using daemon_memcached_options are available as individual MEMCACHED option settings in Amazon RDS.

MySQL daemon_memcached_options Parameters

  • BINDING_PROTOCOL - a string that specifies the binding protocol to use. The allowed values are auto, ascii, or binary. The default is auto, which means the server automatically negotiates the protocol with the client. The option does not take effect until the instance is restarted.

  • BACKLOG_QUEUE_LIMIT - an integer that specifies how many network connections can be waiting to be processed by memcached. Increasing this limit may reduce errors received by a client that is not able to connect to the memcached instance, but does not improve the performance of the server. The allowed values are 1 to 2048, the default is 1024. The option does not take effect until the instance is restarted.

  • CAS_DISABLED - a Boolean that enables (1 (true)) or disables (0 (false)) the use of compare and swap (CAS), which reduces the per-item size by 8 bytes. The default is 0 (false). The option does not take effect until the instance is restarted.

  • CHUNK_SIZE - an integer that specifies the minimum chunk size, in bytes, to allocate for the smallest item's key, value, and flags. The allowed values are 1 to 48. The default is 48 and you can significantly improve memory efficiency with a lower value. The option does not take effect until the instance is restarted.

  • CHUNCK_SIZE_GROWTH_FACTOR - a float that controls the size of new chunks. The size of a new chunk is the size of the previous chunk times CHUNCK_SIZE_GROWTH_FACTOR. The allowed values are 1 to 2, the default is 1.25. The option does not take effect until the instance is restarted.

  • ERROR_ON_MEMORY_EXHAUSTED - a Boolean, when set to 1 (true) it specifies that memcached will return an error rather than evicting items when there is no more memory to store items. If set to 0 (false), memcached will evict items if there is no more memory. The default is 0 (false). The option does not take effect until the instance is restarted.

  • MAX_SIMULTANEOUS_CONNECTIONS - an integer that specifies the maximum number of concurrent connections. Setting this value to anything under 10 prevents MySQL from starting. The allowed values are 10 to 1024, the default is 1024. The option does not take effect until the instance is restarted.

  • VERBOSITY - an string that specifies the level of information logged in the MySQL error log by the memcached service. The default is v. The option does not take effect until the instance is restarted. The allowed values are:

    • v - Logs errors and warnings while executing the main event loop.

    • vv - In addition to the information logged by v, also logs each client command and the response.

    • vvv - In addition to the information logged by vv, also logs internal state transitions.

Amazon RDS configures these MySQL DAEMON_MEMCAHCED_OPTIONS parameters, they cannot be modified: DAEMON_PROCESS, LARGE_MEMORY_PAGES, MAXIMUM_CORE_FILE_LIMIT, MAX_ITEM_SIZE, LOCK_DOWN_PAGE_MEMORY, MASK, IDFILE, REQUESTS_PER_EVENT, SOCKET, and USER.

MariaDB Audit Plugin Support

Amazon RDS supports using the MariaDB Audit Plugin on MySQL database instances. The MariaDB Audit Plugin records database activity such as users logging on to the database, queries run against the database, and more. The record of database activity is stored in a log file.

Audit Plugin Option Settings

Amazon RDS supports the following settings for the MariaDB Audit Plugin option.

Option SettingValid ValuesDefault ValueDescription

SERVER_AUDIT_FILE_PATH

/rdsdbdata/log/audit/

/rdsdbdata/log/audit/

The location of the log file. The log file contains the record of the activity specified in SERVER_AUDIT_EVENTS. For more information, see Viewing and Listing Database Log Files and MySQL Database Log Files.

SERVER_AUDIT_FILE_SIZE

1–1000000000

None

The size in bytes that when reached, causes the file to rotate. For more information, see Log File Size.

SERVER_AUDIT_FILE_ROTATION

0–100

None

The number of log rotations to save. For more information, see Log File Size and Downloading a Database Log File.

SERVER_AUDIT_EVENTS

CONNECT, QUERY, TABLE

CONNECT, QUERY

The types of activity to record in the log. Installing the MariaDB Audit Plugin is itself logged.

  • CONNECT: Log successful and unsuccessful connections to the database, and disconnections from the database.

  • QUERY: Log the text of all queries run against the database.

  • TABLE: Log tables affected by queries when the queries are run against the database.

SERVER_AUDIT_INCL_USERS

Multiple comma-separated values

None

Include only activity from the specified users. By default, activity is recorded for all users. If a user is specified in both SERVER_AUDIT_EXCL_USERS and SERVER_AUDIT_INCL_USERS, then activity is recorded for the user.

SERVER_AUDIT_EXCL_USERS

Multiple comma-separated values

None

Exclude activity from the specified users. By default, activity is recorded for all users. If a user is specified in both SERVER_AUDIT_EXCL_USERS and SERVER_AUDIT_INCL_USERS, then activity is recorded for the user.

The rdsadmin user queries the database every second to check the health of the database. Depending on your other settings, this activity can possibly cause the size of your log file to grow very large, very quickly. If you don't need to record this activity, add the rdsadmin user to the SERVER_AUDIT_EXCL_USERS list.

SERVER_AUDIT_LOGGING

ON

ON

Logging is active. The only valid value is ON. Amazon RDS does not support deactivating logging. If you want to deactivate logging, remove the MariaDB Audit Plugin. For more information, see Removing the MariaDB Audit Plugin.

Adding the MariaDB Audit Plugin

The general process for adding the MariaDB Audit Plugin to a DB instance is the following:

  • Create a new option group, or copy or modify an existing option group

  • Add the option to the option group

  • Associate the option group with the DB instance

After you add the MariaDB Audit Plugin, you don't need to restart your DB instance. As soon as the option group is active, auditing begins immediately.

To add the MariaDB Audit Plugin

  1. Determine the option group you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group. Choose mysql for Engine, and choose 5.6, 5.7, or later for Major Engine Version. For more information, see Creating an Option Group.

  2. Add the MARIADB_AUDIT_PLUGIN option to the option group, and configure the option settings. For more information about adding options, see Adding an Option to an Option Group. For more information about each setting, see Audit Plugin Option Settings.

  3. Apply the option group to a new or existing DB instance.

Viewing and Downloading the MariaDB Audit Plugin Log

After you enable the MariaDB Audit Plugin, you access the results in the log files the same way you access any other text-based log files. The audit log files are located at /rdsdbdata/log/audit/. For information about viewing the log file in the console, see Viewing and Listing Database Log Files. For information about downloading the log file, see Downloading a Database Log File.

Modifying MariaDB Audit Plugin Settings

After you enable the MariaDB Audit Plugin, you can modify the settings. For more information about how to modify option settings, see Modifying an Option Setting. For more information about each setting, see Audit Plugin Option Settings.

Removing the MariaDB Audit Plugin

Amazon RDS doesn't support turning off logging in the MariaDB Audit Plugin. However, you can remove the plugin from a DB instance. After you remove the MariaDB Audit Plugin, you need to restart your DB instance to stop auditing.

To remove the MariaDB Audit Plugin from a DB instance, do one of the following:

  • Remove the MariaDB Audit Plugin option from the option group it belongs to. This change affects all DB instances that use the option group. For more information, see Removing an Option from an Option Group

  • Modify the DB instance and specify a different option group that doesn't include the plugin. This change affects a single DB instance. You can specify the default (empty) option group, or a different custom option group. For more information, see Modifying a DB Instance Running the MySQL Database Engine.