SkySQL Replication Helper Procedures for Replicated Transactions

Overview

MariaDB SkySQL provides stored procedures in the sky database to configure inbound and outbound replication with a Replicated Transactions service.

Compatibility

change_external_primary

Executes the CHANGE MASTER TO statement to configures inbound replication from an external primary server based on binary log file and position.

CALL sky.change_external_primary(
   host VARCHAR(255),
   port INT,
   logfile TEXT,
   logpos LONG ,
   use_ssl_encryption BOOLEAN
);
+--------------------------------------------------------------------------------------------------------------+
| Run_this_grant_on_your_external_primary                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%' IDENTIFIED BY '<password_hash>';                  |
+--------------------------------------------------------------------------------------------------------------+

change_external_primary_gtid

Executes the CHANGE MASTER TO statement to configures inbound replication from an external primary server based on the provided GTID.

CALL sky.change_external_primary_gtid(
   host VARCHAR(255),
   port INT,
   gtid VARCHAR(60),
   use_ssl_encryption BOOLEAN
);
+--------------------------------------------------------------------------------------------------------------+
| Run_this_grant_on_your_external_primary                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%' IDENTIFIED BY '<password_hash>';                  |
+--------------------------------------------------------------------------------------------------------------+

gtid_status

Provides a list of GTID-related system variables.

CALL sky.gtid_status();
+-------------------+---------------------------+
| Variable_name     | Value                     |
+-------------------+---------------------------+
| gtid_binlog_pos   | 435700-435700-122         |
| gtid_binlog_state | 435700-435700-122         |
| gtid_current_pos  | 0-100-1,435700-435700-122 |
| gtid_slave_pos    | 0-100-1                   |
+-------------------+---------------------------+

kill_session

Kills any non-root or non-SkySQL threads, similar to the KILL statement.

CALL sky.kill_session(IN thread BIGINT);

replication_grants

Provides a GRANT statement to run on an external primary server when configuring inbound replication.

CALL sky.replication_grants();
+--------------------------------------------------------------------------------------------------------------+
| Run_this_grant_on_your_external_primary                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%' IDENTIFIED BY '<password_hash>';                  |
+--------------------------------------------------------------------------------------------------------------+

replication_status

Executes the SHOW REPLICA STATUS statement to obtain the status of inbound replication.

CALL sky.replication_status()\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: mariadb1.example.com
                   Master_User: skysql_replication
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 558
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 674
         Relay_Master_Log_File: mariadb-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 558
               Relay_Log_Space: 985
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 100
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 0-100-1
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

reset_replication

Executes the RESET REPLICA statement to clear inbound replication configuration.

CALL sky.reset_replication();
+------------------------+
| Message                |
+------------------------+
| Replica has been reset |
+------------------------+

set_master_ssl

Toggles the MASTER_SSL replication option using the CHANGE MASTER TO statement.

CALL sky.set_master_ssl();

skip_repl_error

This stored procedure can be used to ignore a transaction that is causing a replication error.

Executes the STOP REPLICA statement, then sets the sql_slave_skip_counter system variable, and then executes the START REPLICA statement to skip a single transaction. Does not currently work with GTID.

CALL sky.skip_repl_error();

start_replication

Executes the START REPLICA statement to start inbound replication from an external primary.

CALL sky.start_replication();
+----------------------------------------+
| Message                                |
+----------------------------------------+
| External replication running normally. |
+----------------------------------------+

stop_replication

Executes the STOP REPLICA statement to stop inbound replication from an external primary.

CALL sky.stop_replication();
+---------------------------------+
| Message                         |
+---------------------------------+
| Replication is down or disabled |
+---------------------------------+