# MariaDB on Amazon RDS SQL reference
Following, you can find descriptions of system stored procedures that are available for Amazon RDS instances running the MariaDB DB engine.
You can use the system stored procedures that are available for MySQL DB instances and MariaDB DB instances. These stored procedures are documented at [RDS for MySQL stored procedure reference](Appendix.MySQL.SQLRef.md). MariaDB DB instances support all of the stored procedures, except for `mysql.rds_start_replication_until` and `mysql.rds_start_replication_until_gtid`.
Additionally, the following system stored procedures are supported only for Amazon RDS DB instances running MariaDB:
+ [mysql.rds\$1replica\$1status](mysql_rds_replica_status.md)
+ [mysql.rds\$1set\$1external\$1master\$1gtid](mysql_rds_set_external_master_gtid.md)
+ [mysql.rds\$1kill\$1query\$1id](mysql_rds_kill_query_id.md)
+ [mysql.rds\$1execute\$1operation](mysql_rds_execute_operation.md)
# mysql.rds\$1replica\$1status
Shows the replication status of a MariaDB read replica.
Call this procedure on the read replica to show status information on essential parameters of the replica threads.
## Syntax
```
CALL mysql.rds_replica_status;
```
## Usage notes
This procedure is only supported for MariaDB DB instances running MariaDB version 10.5 and higher.
This procedure is the equivalent of the `SHOW REPLICA STATUS` command. This command isn't supported for MariaDB version 10.5 and higher DB instances.
In prior versions of MariaDB, the equivalent `SHOW SLAVE STATUS` command required the `REPLICATION SLAVE` privilege. In MariaDB version 10.5 and higher, it requires the `REPLICATION REPLICA ADMIN` privilege. To protect the RDS management of MariaDB 10.5 and higher DB instances, this new privilege isn't granted to the RDS master user.
## Examples
The following example shows the status of a MariaDB read replica:
```
call mysql.rds_replica_status;
```
The response is similar to the following:
```
*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: XX.XX.XX.XXX
Source_User: rdsrepladmin
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin-changelog.003988
Read_Source_Log_Pos: 405
Relay_Log_File: relaylog.011024
Relay_Log_Pos: 657
Relay_Source_Log_File: mysql-bin-changelog.003988
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.rds_sysinfo,mysql.rds_history,mysql.rds_replication_status
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 405
Relay_Log_Space: 1016
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Master: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 807509301
Source_SSL_Crl:
Source_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-807509301-3980
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Reading event from the relay log
Replica_DDL_Groups: 15
Replica_Non_Transactional_Groups: 0
Replica_Transactional_Groups: 3658
1 row in set (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
```
# mysql.rds\$1set\$1external\$1master\$1gtid
Configures GTID-based replication from a MariaDB instance running external to Amazon RDS to a MariaDB DB instance. This stored procedure is supported only where the external MariaDB instance is version 10.0.24 or higher. When setting up replication where one or both instances do not support MariaDB global transaction identifiers (GTIDs), use [mysql.rds\$1set\$1external\$1master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)](mysql-stored-proc-replicating.md#mysql_rds_set_external_master).
Using GTIDs for replication provides crash-safety features not offered by binary log replication, so we recommend it in cases where the replicating instances support it.
## Syntax
```
CALL mysql.rds_set_external_master_gtid(
host_name
, host_port
, replication_user_name
, replication_user_password
, gtid
, ssl_encryption
);
```
## Parameters
*host\$1name*
String. The host name or IP address of the MariaDB instance running external to Amazon RDS that will become the source instance.
*host\$1port*
Integer. The port used by the MariaDB instance running external to Amazon RDS to be configured as the source instance. If your network configuration includes SSH port replication that converts the port number, specify the port number that is exposed by SSH.
*replication\$1user\$1name*
String. The ID of a user with `REPLICATION SLAVE` permissions in the MariaDB DB instance to be configured as the read replica.
*replication\$1user\$1password*
String. The password of the user ID specified in `replication_user_name`.
*gtid*
String. The global transaction ID on the source instance that replication should start from.
You can use `@@gtid_current_pos` to get the current GTID if the source instance has been locked while you are configuring replication, so the binary log doesn't change between the points when you get the GTID and when replication starts.
Otherwise, if you are using `mysqldump` version 10.0.13 or greater to populate the replica instance prior to starting replication, you can get the GTID position in the output by using the `--master-data` or `--dump-slave` options. If you are not using `mysqldump` version 10.0.13 or greater, you can run the `SHOW MASTER STATUS` or use those same `mysqldump` options to get the binary log file name and position, then convert them to a GTID by running `BINLOG_GTID_POS` on the external MariaDB instance:
```
SELECT BINLOG_GTID_POS('', );
```
For more information about the MariaDB implementation of GTIDs, go to [Global transaction ID](http://mariadb.com/kb/en/mariadb/global-transaction-id/) in the MariaDB documentation.
*ssl\$1encryption*
A value that specifies whether Secure Socket Layer (SSL) encryption is used on the replication connection. 1 specifies to use SSL encryption, 0 specifies to not use encryption. The default is 0.
The `MASTER_SSL_VERIFY_SERVER_CERT` option isn't supported. This option is set to 0, which means that the connection is encrypted, but the certificates aren't verified.
## Usage notes
The `mysql.rds_set_external_master_gtid` procedure must be run by the master user. It must be run on the MariaDB DB instance that you are configuring as the replica of a MariaDB instance running external to Amazon RDS. Before running `mysql.rds_set_external_master_gtid`, you must have configured the instance of MariaDB running external to Amazon RDS as a source instance. For more information, see [Importing data into an Amazon RDS for MariaDB DB instance](MariaDB.Procedural.Importing.md).
**Warning**
Do not use `mysql.rds_set_external_master_gtid` to manage replication between two Amazon RDS DB instances. Use it only when replicating with a MariaDB instance running external to RDS. For information about managing replication between Amazon RDS DB instances, see [Working with DB instance read replicas](USER_ReadRepl.md).
After calling `mysql.rds_set_external_master_gtid` to configure an Amazon RDS DB instance as a read replica, you can call [mysql.rds\$1start\$1replication](mysql-stored-proc-replicating.md#mysql_rds_start_replication) on the replica to start the replication process. You can call [mysql.rds\$1reset\$1external\$1master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)](mysql-stored-proc-replicating.md#mysql_rds_reset_external_master) to remove the read replica configuration.
When `mysql.rds_set_external_master_gtid` is called, Amazon RDS records the time, user, and an action of "set master" in the `mysql.rds_history` and `mysql.rds_replication_status` tables.
## Examples
When run on a MariaDB DB instance, the following example configures it as the replica of an instance of MariaDB running external to Amazon RDS.
```
call mysql.rds_set_external_master_gtid ('Sourcedb.some.com',3306,'ReplicationUser','SomePassW0rd','0-123-456',0);
```
# mysql.rds\$1kill\$1query\$1id
Ends a query running against the MariaDB server in order to terminate long-running or problematic queries. You can identify the query ID and effectively stop a specific query to address performance issues and maintain optimal database operation.
## Syntax
```
CALL mysql.rds_kill_query_id(queryID);
```
## Parameters
*queryID*
Integer. The identity of the query to be ended.
## Usage notes
To stop a query running against the MariaDB server, use the `mysql.rds_kill_query_id` procedure and pass in the ID of that query. To obtain the query ID, query the MariaDB [Information schema PROCESSLIST table](http://mariadb.com/kb/en/mariadb/information-schema-processlist-table/), as shown following:
```
SELECT USER, HOST, COMMAND, TIME, STATE, INFO, QUERY_ID FROM
INFORMATION_SCHEMA.PROCESSLIST WHERE USER = '';
```
The connection to the MariaDB server is retained.
## Examples
The following example ends a query with a query ID of 230040:
```
call mysql.rds_kill_query_id(230040);
```
# mysql.rds\$1execute\$1operation
Executes InnoDB operations to manage buffer pool states and temporary tablespace. This procedure can be used to dynamically control InnoDB operations such as dumping and loading buffer pool states or truncating temporary tablespace.
## Syntax
```
CALL mysql.rds_execute_operation(operation);
```
## Parameters
*operation*
String. The InnoDB operations to execute. Valid values are:
+ *innodb\$1buffer\$1pool\$1dump\$1now* - Operation that dumps the current state of the buffer pool.
+ *innodb\$1buffer\$1pool\$1load\$1now* - Operation that loads the saved buffer pool state.
+ *innodb\$1buffer\$1pool\$1load\$1abort* - Operation that aborts a buffer pool load operation.
+ *innodb\$1truncate\$1temporary\$1tablespace\$1now* - Operation that truncates the temporary tablespace.
## Usage notes
This procedure is only supported for MariaDB DB instances running MariaDB version 11.8 and higher.
During execution, binary logging is temporarily disabled to prevent replication of these administrative commands.
The procedure maintains an audit trail by logging all operations in the [https://mariadb.com/docs/server/security/securing-mariadb/securing-mariadb-encryption/data-in-transit-encryption/securing-connections-for-client-and-server#requiring-tls](https://mariadb.com/docs/server/security/securing-mariadb/securing-mariadb-encryption/data-in-transit-encryption/securing-connections-for-client-and-server#requiring-tls) table.
## Examples
The following example demonstrates temporary tablespace shrinking using `mysql.rds_execute_operation`:
To check current temporary tablespace size, run the following query:
```
SELECT FILE_SIZE FROM information_schema.innodb_sys_tablespaces WHERE name LIKE 'innodb_temporary';
+------------+
| FILE_SIZE |
+------------+
| 6723469312 | -- 6.3 GB
+------------+
```
When you drop temporary tables, it doesn't reduce storage usage in the global tablespace. To reduce the size of the global tablespace, run the `mysql.rds_execute_operation` command to shrink the temporary tablespace.
```
CALL mysql.rds_execute_operation('innodb_truncate_temporary_tablespace_now');
Query OK, 2 rows affected (0.004 sec)
```
After you run the procedure, verify that the space was reclaimed.
```
SELECT FILE_SIZE FROM information_schema.innodb_sys_tablespaces WHERE name LIKE 'innodb_temporary';
+-----------+
| FILE_SIZE |
+-----------+
| 12582912 | -- 12 MB
+-----------+
```
**Note**
The shrink operation might take time, depending on the temporary tablespace size and current workload.
**Important**
The temporary tablespace shrinks only when all temporary tables that contributed to its size are no longer in use. We recommend that you run this procedure when there are no active temporary tablespaces on the instance.