SkySQL Replication Operations - Transactions

MariaDB Platform for Transactions supports inbound and outbound replication in the HA (Primary/Replica) and Galera topologies.

Supported Versions for Replication

The supported versions for replication depend on:

  • The MariaDB Enterprise Server version used by the SkySQL service

  • The direction of replication (inbound or outbound)

Enterprise Server 10.5

When the SkySQL service uses ES 10.5 and replication is inbound, the following versions are supported for the external primary server:

  • MariaDB Server 10.2

  • MariaDB Server 10.3

  • MariaDB Server 10.4

  • MariaDB Server 10.5

  • MySQL 5.7

When the SkySQL service uses ES 10.5 and replication is outbound, the following versions are supported for the external replica server:

  • MariaDB Server 10.5

Enterprise Server 10.4

When the SkySQL service uses ES 10.4 and replication is inbound, the following versions are supported for the external primary server:

  • MariaDB Server 10.2

  • MariaDB Server 10.3

  • MariaDB Server 10.4

  • MySQL 5.7

When the SkySQL service uses ES 10.4 and replication is outbound, the following versions are supported for the external replica server:

  • MariaDB Server 10.4

  • MariaDB Server 10.5

Configuration

To configure inbound replication, choose the type of external primary server:

To configure outbound replication with MariaDB Server as the external replica server:

For help with a more complex replication configuration, create a support case.

Configure Inbound Replication from MariaDB Server

To configure inbound replication from an external primary server using MariaDB Server to your SkySQL service, follow the procedure below.

Requirements

The external primary server must use a supported version of MariaDB Server.

When the SkySQL service uses ES 10.5, the following versions are supported for the external primary server:

  • MariaDB Server 10.2

  • MariaDB Server 10.3

  • MariaDB Server 10.4

  • MariaDB Server 10.5

When the SkySQL service uses ES 10.4, the following versions are supported for the external primary server:

  • MariaDB Server 10.2

  • MariaDB Server 10.3

  • MariaDB Server 10.4

Obtain GTID Position

On the external primary server, obtain the GTID position from which to start replication.

When you want to start replication from the most recent transaction, the current GTID position can be obtained by querying the value of the gtid_current_pos system variable with the SHOW GLOBAL VARIABLES statement:

SHOW GLOBAL VARIABLES
   LIKE 'gtid_current_pos';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| gtid_current_pos | 0-100-1 |
+------------------+---------+

Configure GTID Position

On the SkySQL service, configure the GTID position from which to start replication.

The GTID position can be configured using the sky.change_external_primary_gtid() stored procedure:

CALL sky.change_external_primary_gtid('mariadb1.example.com', 3306, '0-100-1', false);
+--------------------------------------------------------------------------------------------------------------+
| Run_this_grant_on_your_external_primary                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%' IDENTIFIED BY '<password_hash>';                  |
+--------------------------------------------------------------------------------------------------------------+

The stored procedure returns a GRANT statement that is used in the next step.

Grant Replication Privileges

On the external primary server, execute the GRANT statement returned by the last step:

GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%' IDENTIFIED BY '<password_hash>';

Start Replication

On the SkySQL service, start replication.

Replication can be started using the sky.start_replication() stored procedure:

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

Check Replication Status

On the SkySQL service, check replication status.

Replication status can be checked using the sky.replication_status() stored procedure:

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

Check GTID Status

On the SkySQL service, check GTID status.

GTID status can be checked using the sky.gtid_status() stored procedure:

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                   |
+-------------------+---------------------------+

Configure Inbound Replication from MySQL 5.7

To configure inbound replication from an external primary server using MySQL 5.7 to your SkySQL service, follow the procedure below.

Requirements

GTID (Global Transaction ID) is not supported. Inbound replication must be configured using the binary log file and position.

Obtain Binary Log File and Position

On the external primary server, obtain the binary log file and position from which to start replication.

When you want to start replication from the most recent transaction, the current binary log file position can be obtained by executing the SHOW MASTER STATUS statement:

SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

Configure Binary Log File and Position

On the SkySQL service, configure the binary log file and position from which to start replication.

The binary log file and position can be configured using the sky.change_external_primary() stored procedure:

CALL sky.change_external_primary('mysql1.example.com', 3306, 'mysql-bin.000001', 154, false);
+--------------------------------------------------------------------------------------------------------------+
| Run_this_grant_on_your_external_primary                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%' IDENTIFIED BY '<password_hash>';                  |
+--------------------------------------------------------------------------------------------------------------+

The stored procedure returns a GRANT statement that is used in the next step.

Grant Replication Privileges

On the external primary server, execute the GRANT statement returned by the last step:

GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%' IDENTIFIED BY '<password_hash>';

Start Replication

On the SkySQL service, start replication.

Replication can be started using the sky.start_replication() stored procedure:

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

Check Replication Status

On the SkySQL service, check replication status.

Replication status can be checked using the sky.replication_status() stored procedure:

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: mysql-bin.000001
           Read_Master_Log_Pos: 462
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 665
         Relay_Master_Log_File: mysql-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: 462
               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: 200
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       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

Configure Outbound Replication from MariaDB Server

To configure outbound replication from your SkySQL service to an external replica server using MariaDB Server, follow the procedure below.

Requirements

The external replica server must use a supported version of MariaDB Server.

When the SkySQL service uses ES 10.5, the following versions are supported for the external replica server:

  • MariaDB Server 10.5

When the SkySQL service uses ES 10.4, the following versions are supported for the external replica server:

  • MariaDB Server 10.4

  • MariaDB Server 10.5

Request User for Outbound Replication

On the SkySQL Customer Portal, create a support case to request a user account with sufficient privileges for outbound replication to the external replica server.

SkySQL customers do not have sufficient privileges to create a user account that supports replication, so the user account must be created by SkySQL Support.

Check User Account

On the SkySQL service, confirm that the new user has sufficient privileges by executing SHOW GRANTS:

SHOW GRANTS FOR 'external_replication'@'%';
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for external_replication@%                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, SLAVE MONITOR ON *.* TO `external_replication`@`%` IDENTIFIED BY PASSWORD '*CCD3A959D6A004B9C3807B728BC2E55B67E10518' |
+------------------------------------------------------------------------------------------------------------------------------------------------+

Add External Replica to Whitelist

On the SkySQL Customer Portal, add the IP address of the external replica server to the SkySQL service's whitelist.

Obtain GTID Position

On the SkySQL service, obtain the GTID position from which to start replication.

When you want to start replication from the most recent transaction, the current GTID position can be obtained by querying the value of the gtid_current_pos system variable with the SHOW GLOBAL VARIABLES statement:

SHOW GLOBAL VARIABLES
   LIKE 'gtid_current_pos';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| gtid_current_pos | 435700-435700-124 |
+------------------+-------------------+

Configure GTID Position

On the external replica server, configure the GTID position from which to start replication.

The GTID position can be configured by setting the gtid_slave_pos system variable with the SET GLOBAL statement:

SET GLOBAL gtid_slave_pos='435700-435700-124';

Download SkySQL CA Chain

On the external replica server, download the SkySQL CA chain.

Click the "Download" link to download skysql_chain.pem (Download).

Configure Replication

On the external replica server, configure replication.

Replication can be configured using the CHANGE MASTER TO statement:

CHANGE MASTER TO
   MASTER_HOST='my-service.mdb0002147.db.skysql.net',
   MASTER_PORT=5003,
   MASTER_USER='external_replication',
   MASTER_PASSWORD='my_password',
   MASTER_SSL=1,
   MASTER_SSL_CA='/path/to/skysql_chain.pem',
   MASTER_USE_GTID=slave_pos;

Start Replication

On the external replica server, start replication.

Replication can be started using the START REPLICA statement:

START REPLICA;

Check Replication Status

On the external replica server, check replication status.

Replication status can be checked using the SHOW REPLICA STATUS statement:

SHOW REPLICA STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: my-service.mdb0002147.db.skysql.net
                   Master_User: external_replication
                   Master_Port: 5003
                 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: Yes
            Master_SSL_CA_File: /var/lib/mysql/skysql_chain.pem
            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: 435701
                Master_SSL_Crl: /var/lib/mysql/skysql_chain.pem
            Master_SSL_Crlpath:
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 435700-435700-127
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: optimistic
                     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

Replication Stored Procedures

Several replication-related stored procedures are available in the sky database. The stored procedures are described below.

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 |
+------------------------+

skip_repl_error

Sets the sql_slave_skip_counter system variable to skip a single replication error. 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 |
+---------------------------------+