# Common DBA tasks for MySQL DB instances
In the following content, you can find descriptions of the Amazon RDS-specific implementations of some common DBA tasks for DB instances running the MySQL database engine. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances. Also, it restricts access to certain system procedures and tables that require advanced privileges.
For information about working with MySQL log files on Amazon RDS, see [MySQL database log files](USER_LogAccess.Concepts.MySQL.md).
## Understanding predefined users
Amazon RDS automatically creates several predefined users with new RDS for MySQL DB instances. Predefined users and their privileges can't be changed. You can't drop, rename, or modify privileges for these predefined users. Attempting to do so results in an error.
+ **rdsadmin** – A user that's created to handle many of the management tasks that the administrator with `superuser` privileges would perform on a standalone MySQL database. This user is used internally by RDS for MySQL for many management tasks.
+ **rdsrepladmin** – A user that's used internally by Amazon RDS to support replication activities on RDS for MySQL DB instances and clusters.
For information about other common DBA tasks, see the following topics.
**Topics**
+ [
## Understanding predefined users
](#Appendix.MySQL.CommonDBATasks.users)
+ [
# Role-based privilege model for RDS for MySQL
](Appendix.MySQL.CommonDBATasks.privilege-model.md)
+ [
# Dynamic privileges for RDS for MySQL
](Appendix.MySQL.CommonDBATasks.dynamic-privileges.md)
+ [
# Ending a session or query for RDS for MySQL
](Appendix.MySQL.CommonDBATasks.End.md)
+ [
# Skipping the current replication error for RDS for MySQL
](Appendix.MySQL.CommonDBATasks.SkipError.md)
+ [
# Working with InnoDB tablespaces to improve crash recovery times for RDS for MySQL
](Appendix.MySQL.CommonDBATasks.Tables.md)
+ [
# Managing the Global Status History for RDS for MySQL
](Appendix.MySQL.CommonDBATasks.GoSH.md)
+ [
# Configuring buffer pool size and redo log capacity in MySQL 8.4
](Appendix.MySQL.CommonDBATasks.Config.Size.8.4.md)
# Role-based privilege model for RDS for MySQL
Starting with RDS for MySQL version 8.0.36, you can't modify the tables in the `mysql` database directly. In particular, you can't create database users by performing data manipulation language (DML) operations on the `grant` tables. Instead, you use MySQL account-management statements such as `CREATE USER`, `GRANT`, and `REVOKE` to grant role-based privileges to users. You also can't create other kinds of objects such as stored procedures in the `mysql` database. You can still query the `mysql` tables. If you use binary log replication, changes made directly to the `mysql` tables on the source DB instance aren't replicated to the target cluster.
In some cases, your application might use shortcuts to create users or other objects by inserting into the `mysql` tables. If so, change your application code to use the corresponding statements such as `CREATE USER`.
To export metadata for database users during the migration from an external MySQL database, use one of the following methods:
+ Use MySQL Shell's instance dump utility with a filter to exclude users, roles, and grants. The following example shows you the command syntax to use. Make sure that `outputUrl` is empty.
```
mysqlsh user@host -- util.dumpInstance(outputUrl,{excludeSchemas:['mysql'],users: true})
```
For more information, see [ Instance Dump Utility, Schema Dump Utility, and Table Dump Utility](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html) in the MySQL Reference Manual.
+ Use the `mysqlpump` client utility. This example includes all tables except for tables in the `mysql` system database. It also includes `CREATE USER` and `GRANT` statements to reproduce all MySQL users in the migrated database.
```
mysqlpump --exclude-databases=mysql --users
```
The `mysqlpump` client utility is no longer available with MySQL 8.4. Instead, use `mysqldump`.
To simplify managing permissions for many users or applications, you can use the `CREATE ROLE` statement to create a role that has a set of permissions. Then you can use the `GRANT` and `SET ROLE` statements and the `current_role` function to assign roles to users or applications, switch the current role, and check which roles are in effect. For more information on the role-based permission system in MySQL 8.0, see [Using Roles](https://dev.mysql.com/doc/refman/8.0/en/roles.html) in the MySQL Reference Manual.
**Important**
We strongly recommend that you do not use the master user directly in your applications. Instead, adhere to the best practice of using a database user created with the minimal privileges required for your application.
Starting with version 8.0.36, RDS for MySQL includes a special role that has all of the following privileges. This role is named `rds_superuser_role`. The primary administrative user for each DB instance already has this role granted. The `rds_superuser_role` role includes the following privileges for all database objects:
+ `ALTER`
+ `APPLICATION_PASSWORD_ADMIN`
+ `ALTER ROUTINE`
+ `CREATE`
+ `CREATE ROLE`
+ `CREATE ROUTINE`
+ `CREATE TEMPORARY TABLES`
+ `CREATE USER`
+ `CREATE VIEW`
+ `DELETE`
+ `DROP`
+ `DROP ROLE`
+ `EVENT`
+ `EXECUTE`
+ `INDEX`
+ `INSERT`
+ `LOCK TABLES`
+ `PROCESS`
+ `REFERENCES`
+ `RELOAD`
+ `REPLICATION CLIENT`
+ `REPLICATION SLAVE`
+ `ROLE_ADMIN`
+ `SET_USER_ID`
+ `SELECT`
+ `SHOW DATABASES`
+ `SHOW VIEW`
+ `TRIGGER`
+ `UPDATE`
+ `XA_RECOVER_ADMIN`
The role definition also includes `WITH GRANT OPTION` so that an administrative user can grant that role to other users. In particular, the administrator must grant any privileges needed to perform binary log replication with the MySQL cluster as the target.
**Tip**
To see the full details of the permissions, use the following statement.
```
SHOW GRANTS FOR rds_superuser_role@'%';
```
When you grant access by using roles in RDS for MySQL version 8.0.36 and higher, you also activate the role by using the `SET ROLE role_name` or `SET ROLE ALL` statement. The following example shows how. Substitute the appropriate role name for `CUSTOM_ROLE`.
```
# Grant role to user
mysql> GRANT CUSTOM_ROLE TO 'user'@'domain-or-ip-address'
# Check the current roles for your user. In this case, the CUSTOM_ROLE role has not been activated.
# Only the rds_superuser_role is currently in effect.
mysql> SELECT CURRENT_ROLE();
+--------------------------+
| CURRENT_ROLE() |
+--------------------------+
| `rds_superuser_role`@`%` |
+--------------------------+
1 row in set (0.00 sec)
# Activate all roles associated with this user using SET ROLE.
# You can activate specific roles or all roles.
# In this case, the user only has 2 roles, so we specify ALL.
mysql> SET ROLE ALL;
Query OK, 0 rows affected (0.00 sec)
# Verify role is now active
mysql> SELECT CURRENT_ROLE();
+--------------------------------------------------+
| CURRENT_ROLE() |
+--------------------------------------------------+
| `CUSTOM_ROLE`@`%`,`rds_superuser_role`@`%` |
+--------------------------------------------------+
```
# Dynamic privileges for RDS for MySQL
Dynamic privileges are MySQL privileges that you can explicitly grant by using the `GRANT` statement. Depending on your version of RDS for MySQL, RDS allows you to grant only specific dynamic privileges. RDS disallows some of these privileges because they can interfere with the specific database operations, such as replication and backup.
The following table shows which of these privileges you can grant for different MySQL versions. If you are upgrading from a MySQL version lower than 8.0.36 to version 8.0.36 or higher, you might have to update your application code if granting a particular privilege is no longer allowed.
| Privilege | MySQL 8.0.35 and lower | MySQL 8.0.36 and higher minor versions | MySQL 8.4.3 and higher |
| --- | --- | --- | --- |
| [ALLOW\$1NONEXISTENT\$1DEFINER](https://dev.mysql.com/doc/refman/8.4/en/privileges-provided.html#priv_allow-nonexistent-definer) | Not available | Not available | Disallowed |
| [APPLICATION\$1PASSWORD\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_application-password-admin) | Allowed | Allowed | Allowed |
| [AUDIT\$1ABORT\$1EXEMPT](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_audit-abort-exempt) | Allowed | Disallowed | Disallowed |
| [AUDIT\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_audit-admin) | Disallowed | Disallowed | Disallowed |
| [AUTHENTICATION\$1POLICY\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_authentication-policy-admin) | Allowed | Disallowed | Disallowed |
| [BACKUP\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_backup-admin) | Allowed | Disallowed | Disallowed |
| [BINLOG\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_binlog-admin) | Allowed | Disallowed | Disallowed |
| [BINLOG\$1ENCRYPTION\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_binlog-encryption-admin) | Disallowed | Disallowed | Disallowed |
| [CLONE\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_clone-admin) | Disallowed | Disallowed | Disallowed |
| [CONNECTION\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_connection-admin) | Allowed | Disallowed | Disallowed |
| [ENCRYPTION\$1KEY\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_encryption-key-admin) | Disallowed | Disallowed | Disallowed |
| [FIREWALL\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_firewall-admin) | Disallowed | Disallowed | Disallowed |
| [FIREWALL\$1EXEMPT](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_firewall-exempt) | Allowed | Disallowed | Disallowed |
| [FIREWALL\$1USER](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_firewall-user) | Disallowed | Disallowed | Disallowed |
| [FLUSH\$1OPTIMIZER\$1COSTS](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_flush-optimizer-costs) | Allowed | Allowed | Allowed |
| [FLUSH\$1PRIVILEGES](https://dev.mysql.com/doc/refman/8.4/en/privileges-provided.html#priv_flush-privileges) | Not available | Not available | Allowed |
| [FLUSH\$1STATUS](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_flush-status) | Allowed | Allowed | Allowed |
| [FLUSH\$1TABLES](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_flush-tables) | Allowed | Allowed | Allowed |
| [FLUSH\$1USER\$1RESOURCES](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_flush-user-resources) | Allowed | Allowed | Allowed |
| [GROUP\$1REPLICATION\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_group-replication-admin) | Disallowed | Disallowed | Disallowed |
| [GROUP\$1REPLICATION\$1STREAM](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_group-replication-stream) | Disallowed | Disallowed | Disallowed |
| [INNODB\$1REDO\$1LOG\$1ARCHIVE](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_innodb-redo-log-archive) | Disallowed | Disallowed | Disallowed |
| [INNODB\$1REDO\$1LOG\$1ENABLE](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_innodb-redo-log-enable) | Disallowed | Disallowed | Disallowed |
| [MASKING\$1DICTIONARIES\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_masking-dictionaries-admin) | Disallowed | Disallowed | Disallowed |
| [NDB\$1STORED\$1USER](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_ndb-stored-user) | Disallowed | Disallowed | Disallowed |
| [OPTIMIZE\$1LOCAL\$1TABLE](https://dev.mysql.com/doc/refman/8.4/en/privileges-provided.html#priv_optimize-local-table) | Not available | Not available | Disallowed |
| [PASSWORDLESS\$1USER\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_passwordless-user-admin) | Disallowed | Disallowed | Disallowed |
| [PERSIST\$1RO\$1VARIABLES\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_persist-ro-variables-admin) | Disallowed | Disallowed | Disallowed |
| [REPLICATION\$1APPLIER](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-applier) | Allowed | Disallowed | Disallowed |
| [REPLICATION\$1SLAVE\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave-admin) | Disallowed | Disallowed | Disallowed |
| [RESOURCE\$1GROUP\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_resource-group-admin) | Allowed | Disallowed | Disallowed |
| [RESOURCE\$1GROUP\$1USER](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_resource-group-user) | Allowed | Disallowed | Disallowed |
| [ROLE\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_role-admin) | Allowed | Allowed | Allowed |
| [SENSITIVE\$1VARIABLES\$1OBSERVER](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_sensitive-variables-observer) | Allowed | Allowed | Allowed |
| [SERVICE\$1CONNECTION\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_service-connection-admin) | Allowed | Disallowed | Disallowed |
| [SESSION\$1VARIABLES\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_session-variables-admin) | Allowed | Allowed | Allowed |
| [SET\$1ANY\$1DEFINER](https://dev.mysql.com/doc/refman/8.4/en/privileges-provided.html#priv_set-any-definer) | Not available | Not available | Allowed |
| [SET\$1USER\$1ID](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_set-user-id) | Allowed | Allowed | Not available |
| [SHOW\$1ROUTINE](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_show-routine) | Allowed | Allowed | Allowed |
| [SKIP\$1QUERY\$1REWRITE](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_skip-query-rewrite) | Disallowed | Disallowed | Disallowed |
| [SYSTEM\$1USER](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_system-user) | Disallowed | Disallowed | Disallowed |
| [SYSTEM\$1VARIABLES\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_system-variables-admin) | Disallowed | Disallowed | Disallowed |
| [TABLE\$1ENCRYPTION\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_table-encryption-admin) | Disallowed | Disallowed | Disallowed |
| [TELEMETRY\$1LOG\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_telemetry-log-admin) | Allowed | Disallowed | Disallowed |
| [TP\$1CONNECTION\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_tp-connection-admin) | Disallowed | Disallowed | Disallowed |
| [TRANSACTION\$1GTID\$1TAG](https://dev.mysql.com/doc/refman/8.4/en/privileges-provided.html#priv_transaction-gtid-tag) | Not available | Not available | Disallowed |
| [VERSION\$1TOKEN\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_version-token-admin) | Disallowed | Disallowed | Disallowed |
| [XA\$1RECOVER\$1ADMIN](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_xa-recover-admin) | Allowed | Allowed | Allowed |
# Ending a session or query for RDS for MySQL
You can end user sessions or queries on DB instances by using the `rds_kill` and `rds_kill_query` commands. First connect to your MySQL DB instance, then issue the appropriate command as shown following. For more information, see [Connecting to your MySQL DB instance](USER_ConnectToInstance.md).
```
CALL mysql.rds_kill(thread-ID)
CALL mysql.rds_kill_query(thread-ID)
```
For example, to end the session that is running on thread 99, you would type the following:
```
CALL mysql.rds_kill(99);
```
To end the query that is running on thread 99, you would type the following:
```
CALL mysql.rds_kill_query(99);
```
# Skipping the current replication error for RDS for MySQL
You can skip an error on your read replica if the error is causing your read replica to stop responding and the error doesn't affect the integrity of your data.
**Note**
First verify that the error in question can be safely skipped. In a MySQL utility, connect to the read replica and run the following MySQL command.
```
SHOW REPLICA STATUS\G
```
For information about the values returned, see [the MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/show-replica-status.html).
Previous versions of and MySQL used `SHOW SLAVE STATUS` instead of `SHOW REPLICA STATUS`. If you are using a MySQL version before 8.0.23, then use `SHOW SLAVE STATUS`.
You can skip an error on your read replica in the following ways.
**Topics**
+ [
## Calling the mysql.rds\$1skip\$1repl\$1error procedure
](#Appendix.MySQL.CommonDBATasks.SkipError.procedure)
+ [
## Setting the slave\$1skip\$1errors parameter
](#Appendix.MySQL.CommonDBATasks.SkipError.parameter)
## Calling the mysql.rds\$1skip\$1repl\$1error procedure
Amazon RDS provides a stored procedure that you can call to skip an error on your read replicas. First connect to your read replica, then issue the appropriate commands as shown following. For more information, see [Connecting to your MySQL DB instance](USER_ConnectToInstance.md).
To skip the error, issue the following command.
```
CALL mysql.rds_skip_repl_error;
```
This command has no effect if you run it on the source DB instance, or on a read replica that hasn't encountered a replication error.
For more information, such as the versions of MySQL that support `mysql.rds_skip_repl_error`, see [mysql.rds\$1skip\$1repl\$1error](mysql-stored-proc-replicating.md#mysql_rds_skip_repl_error).
**Important**
If you attempt to call `mysql.rds_skip_repl_error` and encounter the following error: `ERROR 1305 (42000): PROCEDURE mysql.rds_skip_repl_error does not exist`, then upgrade your MySQL DB instance to the latest minor version or one of the minimum minor versions listed in [mysql.rds\$1skip\$1repl\$1error](mysql-stored-proc-replicating.md#mysql_rds_skip_repl_error).
## Setting the slave\$1skip\$1errors parameter
To skip one or more errors, you can set the `slave_skip_errors` static parameter on the read replica. You can set this parameter to skip one or more specific replication error codes. Currently, you can set this parameter only for RDS for MySQL 5.7 DB instances. After you change the setting for this parameter, make sure to reboot your DB instance for the new setting to take effect. For information about setting this parameter, see the [MySQL documentation](https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html#sysvar_slave_skip_errors).
We recommend setting this parameter in a separate DB parameter group. You can associate this DB parameter group only with the read replicas that need to skip errors. Following this best practice reduces the potential impact on other DB instances and read replicas.
**Important**
Setting a nondefault value for this parameter can lead to replication inconsistency. Only set this parameter to a nondefault value if you have exhausted other options to resolve the problem and you are sure of the potential impact on your read replica's data.
# Working with InnoDB tablespaces to improve crash recovery times for RDS for MySQL
Every table in MySQL consists of a table definition, data, and indexes. The MySQL storage engine InnoDB stores table data and indexes in a *tablespace*. InnoDB creates a global shared tablespace that contains a data dictionary and other relevant metadata, and it can contain table data and indexes. InnoDB can also create separate tablespaces for each table and partition. These separate tablespaces are stored in files with a .ibd extension and the header of each tablespace contains a number that uniquely identifies it.
Amazon RDS provides a parameter in a MySQL parameter group called `innodb_file_per_table`. This parameters controls whether InnoDB adds new table data and indexes to the shared tablespace (by setting the parameter value to 0) or to individual tablespaces (by setting the parameter value to 1). Amazon RDS sets the default value for `innodb_file_per_table` parameter to 1, which allows you to 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 recommended setting.
You should set the `innodb_file_per_table` parameter to 0 when you have a large number of tables, such as over 1000 tables when you use standard (magnetic) or general purpose SSD storage or over 10,000 tables when you use Provisioned IOPS storage. When you set this parameter to 0, individual tablespaces are not created and this can improve the time it takes for database crash recovery.
MySQL processes each metadata file, which includes tablespaces, during the crash recovery cycle. The time it takes MySQL to process the metadata information in the shared tablespace is negligible compared to the time it takes to process thousands of tablespace files when there are multiple tablespaces. Because the tablespace number is stored within the header of each file, the aggregate time to read all the tablespace files can take up to several hours. For example, a million InnoDB tablespaces on standard storage can take from five to eight hours to process during a crash recovery cycle. In some cases, InnoDB can determine that it needs additional cleanup after a crash recovery cycle so it will begin another crash recovery cycle, which will extend the recovery time. Keep in mind that a crash recovery cycle also entails rolling-back transactions, fixing broken pages, and other operations in addition to the processing of tablespace information.
Since the `innodb_file_per_table` parameter resides in a parameter group, you can change the parameter value by editing the parameter group used by your DB instance without having to reboot the DB instance. After the setting is changed, for example, from 1 (create individual tables) to 0 (use shared tablespace), new InnoDB tables will be added to the shared tablespace while existing tables continue to have individual tablespaces. To move an InnoDB table to the shared tablespace, you must use the `ALTER TABLE` command.
## Migrating multiple tablespaces to the shared tablespace
You can move an InnoDB table's metadata from its own tablespace to the shared tablespace, which will rebuild the table metadata according to the `innodb_file_per_table` parameter setting. First connect to your MySQL DB instance, then issue the appropriate commands as shown following. For more information, see [Connecting to your MySQL DB instance](USER_ConnectToInstance.md).
```
ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY;
```
For example, the following query returns an `ALTER TABLE` statement for every InnoDB table that is not in the shared tablespace.
**For MySQL 5.7 DB instances:**
```
SELECT CONCAT('ALTER TABLE `',
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`',
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');
```
**For MySQL 8.4 and 8.0 DB instances:**
```
SELECT CONCAT('ALTER TABLE `',
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`',
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');
```
Rebuilding a MySQL table to move the table's metadata to the shared tablespace requires additional storage space temporarily to rebuild the table, so the DB instance must have storage space available. During rebuilding, the table is locked and inaccessible to queries. For small tables or tables not frequently accessed, this might not be an issue. For large tables or tables frequently accessed in a heavily concurrent environment, you can rebuild tables on a read replica.
You can create a read replica and migrate table metadata to the shared tablespace on the read replica. While the ALTER TABLE statement blocks access on the read replica, the source DB instance is not affected. The source DB instance will continue to generate its binary logs while the read replica lags during the table rebuilding process. Because the rebuilding requires additional storage space and the replay log file can become large, you should create a read replica with storage allocated that is larger than the source DB instance.
To create a read replica and rebuild InnoDB tables to use the shared tablespace, take the following steps:
1. Make sure that backup retention is enabled on the source DB instance so that binary logging is enabled.
1. Use the AWS Management Console or AWS CLI to create a read replica for the source DB instance. Because the creation of a read replica involves many of the same processes as crash recovery, the creation process can take some time if there is a large number of InnoDB tablespaces. Allocate more storage space on the read replica than is currently used on the source DB instance.
1. When the read replica has been created, create a parameter group with the parameter settings `read_only = 0` and `innodb_file_per_table = 0`. Then associate the parameter group with the read replica.
1. Issue the following SQL statement for all tables that you want migrated on the replica:
```
ALTER TABLE name ENGINE = InnoDB
```
1. When all of your `ALTER TABLE` statements have completed on the read replica, verify that the read replica is connected to the source DB instance and that the two instances are in sync.
1. Use the console or CLI to promote the read replica to be the instance. Make sure that the parameter group used for the new standalone DB instance has the `innodb_file_per_table` parameter set to 0. Change the name of the new standalone DB instance, and point any applications to the new standalone DB instance.
# Managing the Global Status History for RDS for MySQL
**Tip**
To analyze database performance, you can also use Performance Insights on Amazon RDS. For more information, see [Monitoring DB load with Performance Insights on Amazon RDS](USER_PerfInsights.md).
MySQL maintains many status variables that provide information about its operation. Their value can help you detect locking or memory issues on a DB instance. The values of these status variables are cumulative since last time the DB instance was started. You can reset most status variables to 0 by using the `FLUSH STATUS` command.
To allow for monitoring of these values over time, Amazon RDS provides a set of procedures that will snapshot the values of these status variables over time and write them to a table, along with any changes since the last snapshot. This infrastructure, called Global Status History (GoSH), is installed on all MySQL DB instances starting with versions 5.5.23. GoSH is disabled by default.
To enable GoSH, you first enable the event scheduler from a DB parameter group by setting the parameter `event_scheduler` to `ON`. For MySQL DB instances running MySQL 5.7, also set the parameter `show_compatibility_56` to `1`. For information about creating and modifying a DB parameter group, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md). For information about the side effects of enabling this parameter, see [show\$1compatibility\$156](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_show_compatibility_56) in the *MySQL 5.7 Reference Manual*.
You can then use the procedures in the following table to enable and configure GoSH. First connect to your MySQL DB instance, then issue the appropriate commands as shown following. For more information, see [Connecting to your MySQL DB instance](USER_ConnectToInstance.md). For each procedure, run the following command and replace **procedure-name**:
```
CALL procedure-name;
```
The following table lists all of the procedures that you can use for **procedure-name** in the previous command.
| Procedure | Description |
| --- | --- |
| `mysql.rds_enable_gsh_collector` | Enables GoSH to take default snapshots at intervals specified by `rds_set_gsh_collector`. |
| `mysql.rds_set_gsh_collector` | Specifies the interval, in minutes, between snapshots. Default value is 5. |
| `mysql.rds_disable_gsh_collector` | Disables snapshots. |
| `mysql.rds_collect_global_status_history` | Takes a snapshot on demand. |
| `mysql.rds_enable_gsh_rotation` | Enables rotation of the contents of the `mysql.rds_global_status_history` table to `mysql.rds_global_status_history_old` at intervals specified by `rds_set_gsh_rotation`. |
| `mysql.rds_set_gsh_rotation` | Specifies the interval, in days, between table rotations. Default value is 7. |
| `mysql.rds_disable_gsh_rotation` | Disables table rotation. |
| `mysql.rds_rotate_global_status_history` | Rotates the contents of the `mysql.rds_global_status_history` table to `mysql.rds_global_status_history_old` on demand. |
When GoSH is running, you can query the tables that it writes to. For example, to query the hit ratio of the Innodb buffer pool, you would issue the following query:
```
select a.collection_end, a.collection_start, (( a.variable_Delta-b.variable_delta)/a.variable_delta)*100 as "HitRatio"
from mysql.rds_global_status_history as a join mysql.rds_global_status_history as b on a.collection_end = b.collection_end
where a. variable_name = 'Innodb_buffer_pool_read_requests' and b.variable_name = 'Innodb_buffer_pool_reads'
```
# Configuring buffer pool size and redo log capacity in MySQL 8.4
In MySQL 8.4, Amazon RDS enables the `innodb_dedicated_server` parameter by default. With the `innodb_dedicated_server` parameter, the database engine calculates the `innodb_buffer_pool_size` and `innodb_redo_log_capacity` parameters. For information about how these parameters are calculated, see [Configuring InnoDB Buffer Pool Size](https://dev.mysql.com/doc/refman/8.4/en/innodb-buffer-pool-resize.html) and [Redo Log](https://dev.mysql.com/doc/refman/8.4/en/innodb-redo-log.html) in the MySQL documentation.
With `innodb_dedicated_server` enabled, the `innodb_buffer_pool_size` parameter is calculated based on the DB instance class memory. The following table shows the detected server memory and the corresponding buffer pool size.
| Detected server memory | Buffer pool size |
| --- | --- |
| < 1 GB | Default value of 128 MB |
| 1 GB to 4 GB | *Detected server memory* \$1 0.5 |
| > 4 GB | *Detected server memory* \$1 0.75 |
The `innodb_redo_log_capacity` parameter automatically scales with the instance class to (number of vCPUs / 2) GB up to a maximum of 16 GB. Larger instance classes have a larger redo log capacity, which can improve performance and resilience for write-intensive workloads.
Before upgrading from MySQL 8.0 to MySQL 8.4, be sure to increase your storage space to accommodate a potential increase in the size of the redo logs that might occur after the upgrade completes. For more information, see [Increasing DB instance storage capacity](USER_PIOPS.ModifyingExisting.md).
If you don't want the `innodb_dedicated_server` parameter to calculate the values for the `innodb_buffer_pool_size` and `innodb_redo_log_capacity` parameters, you can override these values by setting specific values for them in a custom parameter group. Alternatively, you can disable the `innodb_dedicated_server` parameter and set values for the `innodb_buffer_pool_size` and `innodb_redo_log_capacity` parameters in a custom parameter group. For more information, see [Default and custom parameter groups](parameter-groups-overview.md#parameter-groups-overview.custom).
If you disable the `innodb_dedicated_server` parameter by setting it to `0` and don't set values for the `innodb_buffer_pool_size` and `innodb_redo_log_capacity` parameters, then Amazon RDS sets the latter two parameters to 128 MB and 100 MB, respectively. These defaults result in poor performance on larger instance classes.