Best practices for configuring parameters for Amazon RDS for MySQL, part 3: Parameters related to security, operational manageability, and connectivity timeout

This blog post was last reviewed or updated January, 2023.

In the previous blog post of this series, I discuss MySQL parameters used to optimize replication in Amazon Relational Database Service (Amazon RDS) for MySQL and best practices related to them. In today’s post, I discuss the most important and commonly used MySQL parameters for implementing various security features in the RDS MySQL environment. I also discuss some of the parameters that help in managing an RDS DB instance’s operation and troubleshooting issues. In addition, I discuss some useful parameters related to collation and character sets.

Note: The default values mentioned in this post apply to Amazon RDS for MySQL 5.7. You can find the default values for Amazon RDS for MySQL 8.0 in the corresponding MySQL documentation or in the RDS for MySQL 8.0 default parameter group which you can access using the AWS CLI or the Amazon RDS console.

Parameters related to security

Following, I list parameters related to security, with best practice suggestions for configuring each.

init_connect

This parameter defines a string for the server to run for each client that connects. There is no default value for this parameter. The string consists of one or more SQL statements, separated by semicolons.

For example, you can use this parameter to create a simple audit of which database users have successfully connected to the database. To do so, first you create an audit table and trigger. Then you set init_connect value to the trigger name. If you do, every time a client connects the parameter triggers the connection details to be written to the audit table.

You can also use this parameter to disable autocommit for specific user accounts, as in the following example.

init_connect = 'set autocommit=case current_user() when 'test@localhost' then 0 else 1 end’. 

old_passwords

This parameter controls the password hashing method used by the PASSWORD function. It also influences password hashing performed by CREATE USER and GRANT statements that specify a password using an IDENTIFIED BY clause.

We strongly recommend that you do not enable this parameter. If you do, it causes the PASSWORD function to use an insecure password hash. The default value for this parameter is 0, which uses MySQL version 4.1 native hashing.

default_password_lifetime

This parameter is available from MySQL version 5.7. It’s used to define the global automatic password expiration policy. If the value is set to N, it means that the permitted password lifetime is N days and each password must be changed every N days. The default value is 0, which disables automatic password expiration. The allowed values are in the range 0–65,535.

Set this parameter to meet your security standard’s requirements and your password policy. You can also set the value explicitly for an account by using CREATE USER or ALTER USER, which overrides the default parameter setting, for example:

mysql> ALTER USER ‘test’@‘localhost' PASSWORD EXPIRE INTERVAL 30 DAY;

max_user_connections

You can use this parameter to restrict client use of MySQL server resources by setting it to a nonzero value. This parameter limits the number of simultaneous connections that the specified account can make. If the number of connections reaches this value, an attempt to create a new connection receives an error like “Warning: mysql_connect(): User xxxxxxxx already has more than ‘max_user_connections’ active connections”. The default value is 0, which doesn’t limit simultaneous connections for the specified account. 

Parameters related to managing operations

Following, I list parameters related to managing operations, with best practice suggestions for configuring each.

log_warnings

To log aborted connections and access-denied errors for new connection attempts, set the value of log_warnings to greater than 1. Typically, it’s set to 2 to help debug issues related to connectivity and to collect data in MySQL error logs. The default value is 1.

This parameter enters warnings about disconnects and other minor issues in the error log. For details, see the AWS Support article How do I log failed attempts to log in to my Amazon RDS instance running MySQL? and Server System Variables in the MySQL documentation. 

performance_schema

This parameter is used to enable or disable performance schema. This parameter can have only two values: 0 (disable the performance schema) and 1 (enable the performance schema). In MySQL 5.6, this parameter’s default value is 1, which enables the performance schema. In 5.7, this parameter’s default value is 0, which disables the performance schema. Set this parameter depending on whether you want to use the performance schema.

Event_scheduler

This commonly used parameter turns the event scheduler ON or OFF. The default value is OFF, and available values are ON or OFF. The value OFF stops the event scheduler and ON starts the event scheduler.

How you set this parameter’s value depends on your usage requirements. However, the event scheduler doesn’t have any protection on multiple concurrent executions of an event. For example, suppose that an event that is scheduled to run every 2 minutes takes more than 2 minutes. The next scheduled event can cause the event’s executions to pile up. Such a pileup can cause deadlock. The best practice to avoid this is use the GET_LOCK function to communicate between threads.

Following is an example that shows how to do this.

CREATE EVENT test_event ON SCHEDULE EVERY 10 SECOND DO
BEGIN
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 BEGIN
   DO RELEASE_LOCK('test_event');
 END;
 IF GET_LOCK('testlock_event', 0) THEN
   -- add some   logic/DML operation here as per your requirement :
  END IF;
  DO RELEASE_LOCK('testlock_event');
END;

You can also query the information schema for event information, for example:

> select * from information_schema.EVENTS\G 

If you need to troubleshoot issues related to events, the error log usually contains useful information. To show the current event thread status, use the command show full processlist. For more information, see Event Scheduler Thread States in the MySQL documentation.

In a replication scenario, enabling the event scheduler on both master and replica can cause incorrect results. These occur because the event on the master generates the desired record and the record is replicated to the replica. Enabling the event scheduler on the replica means that the event is executed again. In the usual course of replication, the process replicates modification to events (such as CREATE, ALTER, DROP, and so on) to the replica, so that the master and replica have identical event configuration. Thus, the event status should be SLAVESIDE_DISABLED so that events on the replica aren’t enabled unless and until the replica is promoted.

After promoting a replica to the master, to start running the events from the new master do the following:

• Disable the event scheduler (if it’s enabled).
• Set the status of all the replicated events from the original master to ENABLED using the update statements listed following.
• Turn on the event scheduler.

To see what events have the status SLAVESIDE_DISABLED, use the following query.

SELECT EVENT_SCHEMA, EVENT_NAME
  FROM INFORMATION_SCHEMA.EVENTS
  WHERE STATUS = 'SLAVESIDE_DISABLED';

To update all the events’ status as enabled, use the following statement.

UPDATE mysql.event SET status='ENABLED';

If you have multiple masters (for example, an external master) for an RDS replica, you can enable events from a particular master using the originator column as shown following.

UPDATE mysql.event SET status='ENABLED' WHERE originator=817021841; 

Here, 817021841 is the server ID from the originating master. You can find this ID using SELECT @@server_id.

explicit_defaults_for_timestamp

This parameter determines how MySQL behaves for default values and NULL values for columns with the TIMESTAMP data type. The default value is 1, which enables the parameter, and allowed values are 0 and 1.

We recommend that you keep this value enabled to avoid nonstandard behaviors for the TIMESTAMP columns with NULL and default values. For more details, you can see the MySQL 5.6 documentation and MySQL 5.7 documentation. After you enable this parameter, the server disables the nonstandard behavior, as described in the MySQL 5.6 documentation and MySQL 5.7 documentation.

However, if this parameter is enabled, you can get errors like the following:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'column_name' cannot be null' error when not specifying a value. 

These appear because in RDS MySQL, the default value for the explicit_defaults_for_timestamp parameter is 1 (enabled). This setting is different from the MySQL default of 0 (disabled). This discrepancy causes there to be no default setting for a column when a value isn’t explicitly provided. To avoid this error, modify your parameter group to set the value of explicit_defaults_for_timestamp to 0. This parameter is static, so the change requires a reboot.

However, explicit_defaults_for_timestamp is deprecated because its sole purpose is to control the nonstandard behavior of MySQL TIMESTAMP columns, which will be removed in future MySQL releases. After these nonstandard behaviors are removed, this parameter will also have no use and will be removed, according to the MySQL documentation. Thus, we recommend that you plan a code change to fully specify values for your TIMESTAMP columns to set the current timestamp value. You can use CURRENT_TIMESTAMP or a synonym such as NOW().

Parameters related to character sets

A character set is a set of symbols and encoding that is typically derived from the type of collation. You use character sets to encode characters into bits. You can set the collation and character set at the instance level, the database level, the table level, or the column level. RDS MySQL uses Latin1 encoding for its database by default. For details of the character sets in use, see the information_schema.CHARACTER_SETS table in the MySQL documentation.

Following are the parameters available that are related to character sets in RDS MySQL.

Any column-specific character set overrides the default character set at the table level. Similarly, any table-specific character set overrides the default character set at the database level. Also, the database-specific character set overrides the default character set at the instance level.

To check character set parameters at the instance level, use the following query.

MySQL [(none)]> show variables like '%character%';
+--------------------------+------------------------------------------------+
| Variable_name            | Value                                          |
+--------------------------+------------------------------------------------+
| character_set_client     | utf8                                           |
| character_set_connection | utf8                                           |
| character_set_database   | latin1                                         |
| character_set_filesystem | binary                                         |
| character_set_results    | utf8                                           |
| character_set_server     | latin1                                         |
| character_set_system     | utf8                                           |
| character_sets_dir       | /rdsdbbin/oscar-5.6.10a.31.101/share/charsets/ |
+--------------------------+------------------------------------------------+
8 rows in set (0.00 sec)

To check the default character set for a particular database, use a query similar to the following query. This example uses the database name ‘TEST’.

MySQL [(none)]> SELECT SCHEMA_NAME,
    -> DEFAULT_CHARACTER_SET_NAME
    -> FROM INFORMATION_SCHEMA.SCHEMATA
    -> WHERE SCHEMA_NAME='TEST';
+-------------+----------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME |
+-------------+----------------------------+
| TEST        | utf8                       |
+-------------+----------------------------+
1 row in set (0.00 sec)

To check the table-level and column-level character set, you can use the command show create table <table_name>.

To change the parameters at the instance level, set the values in your parameter group. To do so, take the following steps:

1. Create a custom parameter group.
2. Modify the parameters to the required values.
3. Log in to the database and view the values of the parameters by using the following command:

> show variables like '%character%';

If the instance is already using a custom parameter group, you can modify the parameters there. For more information, see Working with DB Parameter Groups in the RDS documentation.

However, the parameters character_set_client, character_set_connection, and character_set_results don’t take the character set shown in the parameter group when you query using > show variables like '%character%';. As the MySQL documentation says, “The session value of these variables is set using the character set requested by the client when the client connects to the server.” In other words, you must specify the character set explicitly during each connection.

You can do this using session-level parameters with the SET NAMES statement. The SQL statement SET NAMES 'char_set_name' is equivalent to these three SQL statements:

> set character_set_client = char_set_name;
> set character_set_connection = char_set_name;
> set character_set_results = char_set_name;

For example, as shown following, you can use the SQL statement SET NAMES utf8mb4 to set all the required parameters (character_set_client, character_set_connection, character_set_results) to utf8mb4.

MySQL [(none)]> SET NAMES utf8mb4
    -> ;
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]>  show variables like '%character%';
+--------------------------+------------------------------------------------+
| Variable_name            | Value                                          |
+--------------------------+------------------------------------------------+
| character_set_client     | utf8mb4                                        |
| character_set_connection | utf8mb4                                        |
| character_set_database   | latin1                                         |
| character_set_filesystem | binary                                         |
| character_set_results    | utf8mb4                                        |
| character_set_server     | latin1                                         |
| character_set_system     | utf8                                           |
| character_sets_dir       | /rdsdbbin/oscar-5.6.10a.31.101/share/charsets/ |
+--------------------------+------------------------------------------------+
8 rows in set (0.00 sec)

To avoid running the SET NAMES command for every connection from your application, you can use the init_connect parameter. Following is an example of using init_connect to set these parameters’ value to utf8mb4.

-- setting the 'init_connect' to 'SET NAMES utf8mb4' via parameter group
mysql> show variables like '%init_connect%'; +---------------+-------------------+ 
| Variable_name | Value             | 
+---------------+-------------------+ 
| init_connect  | SET NAMES utf8mb4 | 
+---------------+-------------------+

Alternatively, as mentioned in the MySQL documentation, you can modify the following parameters in the parameter group as shown. These settings ignore the client information and use the default server character set:

character-set-client-handshake=0 
skip-character-set-client-handshake=1

Changing the character set parameters doesn’t modify the character set of existing tables. However, all the tables created after the parameter change have the new character set. To change the character set for an existing table, you need to use the ALTER TABLE statement, for example:

ALTER TABLE <table_name>
CHARACTER SET <desired_character_set>;

Similarly, to change database-specific and column-specific settings, use ALTER DATABASE and ALTER TABLE statements as shown following.

ALTER DATABASE <database_name> CHARACTER SET = utf8mb4  ;
ALTER TABLE <table_name> CHANGE column_name <column_name> <datatype> CHARACTER SET <desired_character_set>;

The utf8 character set in MySQL uses at most three bytes per character, where the utf8mb4 character set uses four bytes per character. The utf8 character set can store various multilingual characters, such as Chinese, Japanese, and Korean characters. However, there are certain characters it can’t store. For example, various emojis such as the Unicode character snowman (U+2603) ☃ can’t be stored in utf8 but can be stored in utf8mb4. We recommend that you use utf8mb4 because it can store all the characters in the utf8 character set and more.

Parameters related to collation

Collation defines a set of rules to compare characters of a character set. Each character set has one or more collations and has at least one default collation.

You can use the SHOW CHARACTER SET command to get the default collation for a particular character set. You can also find the default collation in the table information_schema.collations in the MySQL documentation.

There are four parts in each collation name. In order, these are the following:

1. The character set name.
2. Which language it is for, for example ja for Japanese. A special “language” is binary, which means each byte is compared directly one by one.
3. Whether it is a UCA 9.0.0 based collation. The UCA 9.0.0 based collations have 0900 in the name.
4. Up to three modifiers depending on whether the character has an accent, whether it is case-sensitive, or both.

For example, the collation utf8mb4_ja_0900_as_cs shows utf8mb4 as the supported character set, Japanese as the language, 900 meaning that it is based on a UCA 9.0.0 based collation, and cs for case-sensitive. There are a total of five accent and case modifiers:

• ai: accent insensitive, ä is equal to a.
• as: accent sensitive, ä is not equal to a.
• ci: case insensitive, a is equal to A.
• cs: case sensitive, a is not equal to A.
• ks: kana sensitive (only for Japanese).

Following are the available parameters related to collation in RDS MySQL.

As with the character set, you can set the collation at the instance, database, table and column level. Any column-specific collation overrides the default collation at the table level. Similarly, any table-specific collation overrides the default collation at the database level. Also, the database-specific collation overrides the default collation at the instance level.

To check collation parameters at instance level, you can use the following query.

MySQL [(none)]> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

To check the default collation for a particular database, you can use the following query. In this example, the database name is test.

MySQL [(none)]> SELECT SCHEMA_NAME,
    -> DEFAULT_COLLATION_NAME
    -> FROM INFORMATION_SCHEMA.SCHEMATA
    -> WHERE SCHEMA_NAME = 'test';
+-------------+------------------------+
| SCHEMA_NAME | DEFAULT_COLLATION_NAME |
+-------------+------------------------+
| test        | utf8_general_ci        |
+-------------+------------------------+
1 row in set (0.00 sec)

To check the table-level and column-level collation value, you can use show create table <table_name>.

To change the parameters at instance level, set the values in your parameter group. To do so, take the following steps:

1. Create a custom parameter group.
2. Modify the parameters to the required values.
3. Log in to the database and view the values of the parameters using the following command:

> show variables like ‘%collation%’;

If the instance is already using a custom parameter group, you can modify the parameters there. For more information, see Working with DB Parameter Groups in the RDS documentation.

Changing the collation parameters doesn’t modify the collation of existing tables. However, all the tables created after the parameter change have the new collation. To change the collation for an existing table, you need to use the ALTER TABLE statement, for example:

ALTER TABLE <table_name> COLLATE  <desired_collation>;

Similarly, to change database-specific and column-specific settings, use ALTER DATABASE and ALTER TABLE statements as shown following.

ALTER DATABASE <database_name>  COLLATE  <desired_collation> ;
ALTER TABLE <table_name> CHANGE column_name <column_name> <datatype> COLLATE  <desired_collation>;

slow_query_log

This parameter is very helpful in identifying slow queries. The value can be 0 (or OFF) to disable the slow query log or 1 (or ON) to enable the log. The default value is 0, which disables this feature.

The threshold to determine if a query is slow is defined by another parameter, long_query_time. The long_query_time parameter defines the query execution time in seconds. You can capture slow queries in either a table or a file. Which you do depends on the value of another parameter, log_output, which can equal TABLE or FILE. The default is table. For more details, see this YouTube video.

log_queries_not_using_indexes

This is another parameter frequently used to identify problematic queries. When this parameter is set to 1 (enabled), it logs all the queries not using the index to the slow query log. By default, this parameter is not enabled and the value is 0.

innodb_file_per_table

When innodb_file_per_table is enabled (by setting the value to 1, the default), InnoDB stores the data and indexes for each newly created table in a separate .ibd file instead of the system tablespace. By default, RDS enables this parameter, which means that data for each tablespace is stored in its own .ibd file. If innodb_file_per_table is disabled by setting it to 0, all tables are also allocated to the system tablespace. In that case, dropping tables or indexes or deleting or truncating data from tables allocated in the system tablespace marks the space previously occupied as reusable. However, this command doesn’t free up any space to the file system.

This being the case, we recommend that you keep innodb_file_per_table set to 1 so that you can drop individual InnoDB tables and reclaim storage used by those tables for the DB instance. In most use cases, setting the innodb_file_per_table parameter to 1 is the best practice.

However, you might want to take a different approach if the number of tables in the database is very high. An example is over 1,000 tables when you use standard (magnetic) or general purpose SSD storage or over 10,000 tables when you use Provisioned IOPS storage. In this case, this value should be set to 0 to improve the time it takes for database crash recovery. This improvement occurs because if you set this parameter to 0, individual tablespaces aren’t created.

Parameters related to connectivity timeouts

Following are some of the parameters related to connectivity timeouts. These parameters often need to be adjusted to eliminate timeout-related errors in the MySQL error logs.

net_write_timeout

This parameter indicates the number of seconds to wait for a block to be written to a connection before aborting the write. The recommended value is 120 seconds. This might needed to be tuned to your requirements (the default is 60 seconds). Usually, ‘Got timeout’ messages in the error log are caused by the value set in mysqld and ‘Got an error’ messages in the error log are caused by abnormally terminated connections.

max_allowed_packet

This parameter indicates the maximum size of one packet or any generated or intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function. The default setting in RDS for MySQL is 16 MB. We recommend a value of 64 MB, which is the new default value starting in RDS for MySQL 8.x, and you might need to tune the parameter to your requirements. If a row has more data than the max_allowed_packet value for the client, then errors are reported. You must increase this value if you are using large BLOB columns or long strings. Set it to a value as large as the largest BLOB that you want to use. The protocol limit for max_allowed_packet is 1 GB.

wait_timeout

This parameter indicates the number of seconds the server waits for activity on a noninteractive connection before closing it (non-interactive timeout). The default value is 28,800. If a client is doing nothing for wait_timeout seconds, the MySQL server terminates the connection. The proper setting for this variable depends on the particular environment.

A low wait_timeout can break point-in-time restore in RDS if you replay large transactions (for example, a large row-based bulk update, insert, or delete) and the replay takes longer than wait_timeout. You can set wait_time to 600 (10 minutes) or more to avoid this issue. The wait_timeout value is really only going to be a factor when there are other “bad practices” at play. For example, not including a unique key on an InnoDB table means that a row-based logged bulk update takes much longer on replay than it did initially on the source instance.

interactive_timeout

According to the MySQL manual, this parameter is the number of seconds that the server waits for activity on an interactive connection before closing it. The default value is 28,800. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to perform mysql_real_connect (). For this parameter, the proper setting depends on the particular environment. If you encounter errors, the value might need to be gradually increased until the errors go away.

skip_name_resolve

This parameter is set from the value of the --skip-name-resolve option. If it is OFF (0 in case of RDS), mysqld resolves host names when checking client connections. If it is ON (1 in case of RDS), mysqld uses only IP numbers. In this case, all Host column values in the grant tables must be IP addresses or localhost. Thus, this parameter is used to avoid DNS lookup on connection. By default, it’s turned off (set to 0).

If this parameter is turned off, you might find the following warning in the error log:

2017-03-03 13:10:00          [-]2017-03-03 13:07:22 3396 [Warning] IP address ‘<ip-address-rep-instance>’ could not be resolved: Temporary failure in name resolution                

innodb_print_all_deadlocks

When this parameter is enabled (set to 1), it logs the deadlocks related to InnoDB user transactions to MySQL error logs. Otherwise, this information is only available for the last deadlock by using the SHOW ENGINE INNODB STATUS command. You can use this option to troubleshoot why application logic doesn’t have proper error handling to detect deadlocks and retry operations. By default this parameter is disabled, that is set to 0.

max_connections

This parameter is used to limit the maximum number of concurrent connections that MySQL allows. If no more connections are available, the following error is returned when connection attempts are made:

ERROR 1040 (HY000): Too many connections

The current value for max_connections can be found by using this command:

mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'max_connections';
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| MAX_CONNECTIONS | 500            |
+-----------------+----------------+
1 row in set (0.00 sec)

By default, this parameter is based on a formula that is calculated from the DBInstanceClassMemory value. This parameter is tuned automatically based on the total available memory of the RDS instances. The formula is as follows:

max_connections = DBInstanceClassMemory/12582880

We don’t recommend that you change these values. If you need more connections, we encourage you to upgrade to a larger RDS instance size.

When you decide how many connections to allow and tune the optimum value for max_used_connection, we recommend that you watch for the max_used_connections status value. The max_used_connections server status variable reports the maximum number of connections that have been in use simultaneously since the server started. Check the value using the following command:

mysql> show global status like '%Conn%';

max_connect_errors

This parameter indicates how many connection errors are possible before the server blocks a host. If more than max_connect_errors successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. The default value is 100 and can be tuned to your security requirements and environment.

For example, if max_connect_errors=5000, after 5,000 connection requests from Host X are interrupted you get an error like the following:

Host X is blocked because of many connection errors ().

Unblock the host using the following command:

mysql> flush hosts;
mysql> show global variables like 'max_connect_errors';

+| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 5000|
+--------------------+-------+

1 row in set (0.00 sec)

Conclusion

In this blog post series, we provide best practices for the most commonly customized parameters in RDS for MySQL. Before you go live with RDS MySQL, we recommend that you review your custom parameters values to avoid any potential operational issues.

About the author

Saikat Banerjee is a cloud support engineer with Amazon Web Services.