# Options for MySQL DB instances
Following, you can find a description of 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](USER_WorkingWithOptionGroups.md).
Amazon RDS supports the following options for MySQL:
****
| Option | Option ID | Engine versions |
| --- | --- | --- |
| [MariaDB Audit Plugin support for MySQL](Appendix.MySQL.Options.AuditPlugin.md) | `MARIADB_AUDIT_PLUGIN` | All MySQL 8.4 versionsMySQL 8.0.28 and higher 8.0 versionsAll MySQL 5.7 versions |
| [MySQL memcached support](Appendix.MySQL.Options.memcached.md) | `MEMCACHED` | All MySQL 5.7 and 8.0 versions |
# MariaDB Audit Plugin support for MySQL
Amazon RDS offers an audit plugin for MySQL database instances based on the open source MariaDB Audit Plugin. For more information, see the [Audit Plugin for MySQL Server GitHub repository](https://github.com/aws/audit-plugin-for-mysql).
**Note**
The audit plugin for MySQL is based on the MariaDB Audit Plugin. Throughout this article, we refer to it as MariaDB Audit Plugin.
The MariaDB Audit Plugin records database activity, including users logging on to the database and queries run against the database. 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 setting | Valid values | Default value | Description |
| --- | --- | --- | --- |
| `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](USER_LogAccess.Procedural.Viewing.md) and [MySQL database log files](USER_LogAccess.Concepts.MySQL.md). |
| `SERVER_AUDIT_FILE_ROTATE_SIZE` | 1–1000000000 | 1000000 | The size in bytes that when reached, causes the file to rotate. For more information, see [Overview of RDS for MySQL database logs](USER_LogAccess.MySQL.LogFileSize.md). |
| `SERVER_AUDIT_FILE_ROTATIONS` | 0–100 | 9 | The number of log rotations to save when `server_audit_output_type=file`. If set to 0, then the log file never rotates. For more information, see [Overview of RDS for MySQL database logs](USER_LogAccess.MySQL.LogFileSize.md) and [Downloading a database log file](USER_LogAccess.Procedural.Downloading.md). |
| `SERVER_AUDIT_EVENTS` | `CONNECT`, `QUERY`, `QUERY_DDL`, `QUERY_DML`, `QUERY_DML_NO_SELECT`, `QUERY_DCL` | `CONNECT`, `QUERY` | The types of activity to record in the log. Installing the MariaDB Audit Plugin is itself logged. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.Options.AuditPlugin.html) For MySQL, `TABLE` is not supported. |
| `SERVER_AUDIT_INCL_USERS` | Multiple comma-separated values | None | Include only activity from the specified users. By default, activity is recorded for all users. `SERVER_AUDIT_INCL_USERS` and `SERVER_AUDIT_EXCL_USERS` are mutually exclusive. If you add values to `SERVER_AUDIT_INCL_USERS`, make sure no values are added to `SERVER_AUDIT_EXCL_USERS`. |
| `SERVER_AUDIT_EXCL_USERS` | Multiple comma-separated values | None | Exclude activity from the specified users. By default, activity is recorded for all users. `SERVER_AUDIT_INCL_USERS` and `SERVER_AUDIT_EXCL_USERS` are mutually exclusive. If you add values to `SERVER_AUDIT_EXCL_USERS`, make sure no values are added to `SERVER_AUDIT_INCL_USERS`. 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. `CONNECT` activity is always recorded for all users, even if the user is specified for this option setting. |
| `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](#Appendix.MySQL.Options.AuditPlugin.Remove). |
| `SERVER_AUDIT_QUERY_LOG_LIMIT` | 0–2147483647 | 1024 | The limit on the length of the query string in a record. |
## 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.
**Important**
Adding the MariaDB Audit Plugin to a DB instance might cause an outage. We recommend adding the MariaDB Audit Plugin during a maintenance window or during a time of low database workload.
**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.7**, **8.0**, or **8.4** for **Major engine version**. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create).
1. Add the **MARIADB\$1AUDIT\$1PLUGIN** option to the option group, and configure the option settings. For more information about adding options, see [Adding an option to an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption). For more information about each setting, see [Audit Plugin option settings](#Appendix.MySQL.Options.AuditPlugin.Options).
1. Apply the option group to a new or existing DB instance.
+ For a new DB instance, you apply the option group when you launch the instance. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md).
+ For an existing DB instance, you apply the option group by modifying the instance and attaching the new option group. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
## Audit log format
Log files are represented as comma-separated variable (CSV) files in UTF-8 format.
**Tip**
Log file entries are not in sequential order. To order the entries, use the timestamp value. To see the latest events, you might have to review all log files. For more flexibility in sorting and searching the log data, turn on the setting to upload the audit logs to CloudWatch and view them using the CloudWatch interface.
To view audit data with more types of fields and with output in JSON format, you can also use the Database Activity Streams feature. For more information, see [Monitoring Amazon RDS with Database Activity Streams](DBActivityStreams.md).
The audit log files include the following comma-delimited information in rows, in the specified order:
| Field | Description |
| --- | --- |
| timestamp | The `YYYYMMDD` followed by the `HH:MI:SS` (24-hour clock) for the logged event. |
| serverhost | The name of the instance that the event is logged for. |
| username | The connected user name of the user. |
| host | The host that the user connected from. |
| connectionid | The connection ID number for the logged operation. |
| queryid | The query ID number, which can be used for finding the relational table events and related queries. For `TABLE` events, multiple lines are added. |
| operation | The recorded action type. Possible values are: `CONNECT`, `QUERY`, `READ`, `WRITE`, `CREATE`, `ALTER`, `RENAME`, and `DROP`. |
| database | The active database, as set by the `USE` command. |
| object | For `QUERY` events, this value indicates the query that the database performed. For `TABLE` events, it indicates the table name. |
| retcode | The return code of the logged operation. |
| connection\$1type | The security state of the connection to the server. Possible values are: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.Options.AuditPlugin.html) |
## 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](USER_LogAccess.Procedural.Viewing.md). For information about downloading the log file, see [Downloading a database log file](USER_LogAccess.Procedural.Downloading.md).
## 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](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.ModifyOption). For more information about each setting, see [Audit Plugin option settings](#Appendix.MySQL.Options.AuditPlugin.Options).
## 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. When you remove the MariaDB Audit Plugin, the DB instance is restarted automatically 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](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.RemoveOption)
+ 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 an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
# 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.
**Note**
The memcached interface is no longer available in MySQL 8.4. When you upgrade your DB instances to MySQL 8.4, you must disable `memcached` in existing option groups.
The `memcached` interface 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, see [InnoDB integration with memcached](https://dev.mysql.com/doc/refman/8.0/en/innodb-memcached.html).
**To enable memcached support for an RDS for MySQL DB instance**
1. Determine 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 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 [Controlling access with security groups](Overview.RDSSecurityGroups.md)
1. Create a custom DB option group, selecting MySQL as the engine type and version. For more information about creating an option group, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create).
1. Add 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](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.AddOption).
1. Modify the option settings to configure the `memcached` parameters, if necessary. For more information about how to modify option settings, see [Modifying an option setting](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.ModifyOption).
1. Apply the option group to an instance. Amazon RDS enables `memcached` support for that instance when the option group is applied:
+ You enable `memcached` support for a new instance by specifying the custom option group when you launch the instance. For more information about launching a MySQL instance, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md).
+ You enable `memcached` support for an existing instance by specifying the custom option group when you modify the instance. For more information about modifying a DB instance, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
1. Specify 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, see [InnoDB memcached plugin internals](https://dev.mysql.com/doc/refman/8.0/en/innodb-memcached-internals.html). For an example of mapping an InnoDB table and accessing it through `memcached`, see [Writing applications for the InnoDB memcached plugin](https://dev.mysql.com/doc/refman/8.0/en/innodb-memcached-developing.html).
1. 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 security group associated with the MySQL instance. For more information about managing security groups, see [Controlling access with security groups](Overview.RDSSecurityGroups.md).
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 DB instance, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md).
## MySQL memcached security considerations
The `memcached` protocol does not support user authentication. For more information about MySQL `memcached` security considerations, see [Security Considerations for the InnoDB memcached Plugin](https://dev.mysql.com/doc/refman/8.0/en/innodb-memcached-security.html) in the MySQL documentation.
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 a VPC security group that limits access to known, trusted client addresses and EC2 instances. For more information about managing security groups, see [Controlling access with security groups](Overview.RDSSecurityGroups.md).
## 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:
```
1. connect('my-cache-instance.cg034hpkmmjt.region.rds.amazonaws.com',11212);
5. ?>
```
**To find the DNS name and memcached port of a MySQL DB instance**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the top right corner of the AWS Management Console, select the region that contains the DB instance.
1. In the navigation pane, choose **Databases**.
1. Choose the MySQL DB instance name to display its details.
1. In the **Connect** section, note the value of the **Endpoint** field. The DNS name is the same as the endpoint. Also, note that the port in the **Connect** section is not used to access the `memcached` interface.
1. In the **Details** section, note the name listed in the **Option Group** field.
1. In the navigation pane, choose **Option groups**.
1. Choose the name of the option group used by the MySQL DB instance to show the option group details. In the **Options** section, note the value of the **Port** setting for 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, and 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\$1memcached\$1options 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.
+ `CHUNK_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 `CHUNK_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 that when set to 1 (true) 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` – a 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 running 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_MEMCACHED_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`.