SkySQL Replication Operations - Distributed SQL

Overview

MariaDB Platform for Distributed SQL supports inbound and outbound replication in the Distributed Transactions topology.

Supported Versions for Replication

Replication is supported with:

  • MariaDB Server 10.3

  • MariaDB Server 10.4

  • MariaDB Server 10.5

  • MariaDB Xpand 5.3

  • MySQL 5.7

Configuration

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

To configure outbound replication, choose the type of 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

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

The external primary server must use the same character set and collation as the SkySQL service.

The external primary server must use a supported version of MariaDB Server. The following versions are supported for the external primary server:

  • MariaDB Server 10.3

  • MariaDB Server 10.4

  • MariaDB Server 10.5

Configure Binary Log Filters

On the external primary server, configure the binlog_ignore_db option, so that changes affecting the following databases are not written to the binary log:

  • mysql

  • performance_schema

  • information_schema

[mariadb]
server_id=100
log_bin=mariadb-bin
binlog_ignore_db=mysql
binlog_ignore_db=performance_schema
binlog_ignore_db=information_schema

Restart the Server

On the external primary server, restart MariaDB Server, so that it reloads the configuration:

sudo systemctl restart mariadb

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                            |
+--------------------+----------+--------------+---------------------------------------------+
| mariadb-bin.000001 |      330 |              | mysql,performance_schema,information_schema |
+--------------------+----------+--------------+---------------------------------------------+

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('mariadb1.example.com', 3306, 'mariadb-bin.000001', 330, 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_Name: default
           Slave_Status: Running
            Master_Host: mariadb1.example.com
            Master_Port: 3306
            Master_User: skysql_replication
        Master_Log_File: mariadb-bin
          Slave_Enabled: Enabled
           Log_File_Seq: 1
           Log_File_Pos: 569
             Last_Error: no error
      Connection_Status: Connected
   Relay_Log_Bytes_Read: 0
Relay_Log_Current_Bytes: 0
  Seconds_Behind_Master: 0

Configure Inbound Replication from MariaDB Xpand

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

Requirements

The external primary server must use the same character set and collation as the SkySQL service.

Create Binary Log

On the external primary server, create a binary log.

A binary log can be created by executing the CREATE BINLOG statement:

CREATE BINLOG 'xpand-bin' FORMAT='ROW';

Obtain Binary Log File and Position

On the external primary server, obtain the binary log file and position.

The binary log file and position can be obtained by executing the SHOW MASTER STATUS statement. If more than one binary log is configured, you will have to set the master_status_binlog system variable to the specified binary log file using the SET GLOBAL statement:

SET GLOBAL master_status_binlog='xpand-bin';
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| xpand-bin.000001 |        4 | NULL         | NULL             |
+------------------+----------+--------------+------------------+

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('xpand1.example.com', 3306, 'xpand-bin.000001', 4, 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_Name: default
           Slave_Status: Running
            Master_Host: xpand1.example.com
            Master_Port: 3306
            Master_User: skysql_replication
        Master_Log_File: xpand-bin
          Slave_Enabled: Enabled
           Log_File_Seq: 1
           Log_File_Pos: 353
             Last_Error: no error
      Connection_Status: Connected
   Relay_Log_Bytes_Read: 0
Relay_Log_Current_Bytes: 0
  Seconds_Behind_Master: 0

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.

The external primary server must use the same character set and collation as the SkySQL service.

Configure Binary Log Filters

On the external primary server, configure the binlog_ignore_db option, so that changes affecting the following databases are not written to the binary log:

  • mysql

  • performance_schema

  • information_schema

[mysqld]
server_id=200
log_bin=mysql-bin
binlog_ignore_db=mysql
binlog_ignore_db=performance_schema
binlog_ignore_db=information_schema

Restart the Server

On the external primary server, restart MySQL, so that it reloads the configuration:

$ sudo systemctl restart mysqld

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 |              | mysql,performance_schema,information_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+

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_Name: default
           Slave_Status: Running
            Master_Host: mysql1.example.com
            Master_Port: 3306
            Master_User: skysql_replication
        Master_Log_File: mysql-bin
          Slave_Enabled: Enabled
           Log_File_Seq: 2
           Log_File_Pos: 462
             Last_Error: no error
      Connection_Status: Connected
   Relay_Log_Bytes_Read: 0
Relay_Log_Current_Bytes: 0
  Seconds_Behind_Master: 0

Configure Outbound Replication to MariaDB Server

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

Requirements

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

The external replica server must use the same character set and collation as the SkySQL service.

The external replica server must use a supported version of MariaDB Server. The following versions are supported for the external primary server:

  • MariaDB Server 10.3

  • MariaDB Server 10.4

  • MariaDB Server 10.5

Create User Account for Replication

On the SkySQL service, create a user account to use for replication.

A replication user account can be created by calling the sky.install_repl_users() stored procedure:

CALL sky.install_repl_users();

Get User Account Credentials for Replication

On the SkySQL service, get the credentials for the user account created in the last step.

The credentials can be obtained by calling the sky.get_replica_creds() stored procedure:

CALL sky.get_replica_creds();
+-------------------------------+----------------------------------+
| PRIMARY_USER                  | PRIMARY_PASSWORD                 |
+-------------------------------+----------------------------------+
| skysql_replication            | <password>                       |
+-------------------------------+----------------------------------+

Check User Account

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

SHOW GRANTS FOR 'skysql_replication'@'%';
+-----------------------------------------------------------------------+
| Grants for skysql_replication@%                                       |
+-----------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%'            |
+-----------------------------------------------------------------------+

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.

Create Binary Log

On the SkySQL service, create a binary log.

A binary log can be created by calling the sky.create_binlog() stored procedure. The stype parameter should be set to mariadb:

CALL sky.create_binlog('mariadb', 'mariadb-bin');

Obtain Binary Log File and Position

On the SkySQL service, obtain the binary log file and position.

The binary log file and position can be obtained by calling the sky.binlog_status() stored procedure:

CALL sky.binlog_status();
+--------------------+----------+--------------+----------------------------------------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB                                   |
+--------------------+----------+--------------+----------------------------------------------------+
| mariadb-bin.000001 |        4 | NULL         | system, _replication, clustrix_statd, clustrix_dbi |
+--------------------+----------+--------------+----------------------------------------------------+

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=5001,
   MASTER_USER='skysql_replication',
   MASTER_PASSWORD='my_password',
   MASTER_SSL=1,
   MASTER_SSL_CA='/path/to/skysql_chain.pem',
   MASTER_LOG_FILE='mariadb-bin.000001',
   MASTER_LOG_POS=4;

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: skysql_replication
                   Master_Port: 5001
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 4
                Relay_Log_File: mariadb-relay-bin.000001
                 Relay_Log_Pos: 4
         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: 4
               Relay_Log_Space: 256
               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: 2135026113
                Master_SSL_Crl: /var/lib/mysql/skysql_chain.pem
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       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

Configure Outbound Replication to MariaDB Xpand

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

Requirements

The external replica server must use the same character set and collation as the SkySQL service.

Create User Account for Replication

On the SkySQL service, create a user account to use for replication.

A replication user account can be created by calling the sky.install_repl_users() stored procedure:

CALL sky.install_repl_users();

Get User Account Credentials for Replication

On the SkySQL service, get the credentials for the user account created in the last step.

The credentials can be obtained by calling the sky.get_replica_creds() stored procedure:

CALL sky.get_replica_creds();
+-------------------------------+----------------------------------+
| PRIMARY_USER                  | PRIMARY_PASSWORD                 |
+-------------------------------+----------------------------------+
| skysql_replication            | <password>                       |
+-------------------------------+----------------------------------+

Check User Account

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

SHOW GRANTS FOR 'skysql_replication'@'%';
+-----------------------------------------------------------------------+
| Grants for skysql_replication@%                                       |
+-----------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%'            |
+-----------------------------------------------------------------------+

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.

Create Binary Log

On the SkySQL service, create a binary log.

A binary log can be created by calling the sky.create_binlog() stored procedure. The stype parameter should be set to xpand:

CALL sky.create_binlog('xpand', 'xpand-bin');

Obtain Binary Log File and Position

On the SkySQL service, obtain the binary log file and position.

The binary log file and position can be obtained by calling the sky.binlog_status() stored procedure:

CALL sky.binlog_status();
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| xpand-bin.000001 |        4 | NULL         | NULL             |
+------------------+----------+--------------+------------------+

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=5001,
   MASTER_USER='skysql_replication',
   MASTER_PASSWORD='my_password',
   MASTER_SSL=1,
   MASTER_LOG_FILE='xpand-bin.000001',
   MASTER_LOG_POS=4;

Start Replication

On the external replica server, start replication.

Replication can be started using the START SLAVE statement:

START SLAVE;

Check Replication Status

On the external replica server, check replication status.

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

SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_Name: default
           Slave_Status: Running
            Master_Host: my-service.mdb0002147.db.skysql.net
            Master_Port: 5001
            Master_User: skysql_replication
        Master_Log_File: xpand-bin
          Slave_Enabled: Enabled
           Log_File_Seq: 1
           Log_File_Pos: 4
             Last_Error: no error
      Connection_Status: Connected
   Relay_Log_Bytes_Read: 0
Relay_Log_Current_Bytes: 0
  Seconds_Behind_Master: NULL

Configure Outbound Replication to MySQL 5.7

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

Requirements

GTID (Global Transaction ID) is not supported. Outbound replication must be configured using the binary log file and position. On the external replica server, the gtid_mode system variable must be set to OFF, OFF_PERMISSIVE, or ON_PERMISSIVE. The enforce_gtid_consistency system variable must be set to OFF.

The external replica server must use the same character set and collation as the SkySQL service.

Configure SELinux

On the external replica server, configure SELinux to allow MySQL 5.7 to connect to the SkySQL service if SELinux is in enforcing mode.

You can check if SELinux is in enforcing mode by executing the getenforce command:

$ sudo getenforce
Enforcing

When SELinux is set to enforcing mode, SELinux can block MySQL 5.7 from connecting to SkySQL. When SELinux blocks the connection, it writes the following message to the SELinux audit log:

type=AVC msg=audit(1624056805.982:587): avc:  denied  { name_connect } for  pid=9482 comm="mysqld" dest=5001 scontext=system_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:commplex_link_port_t:s0 tclass=tcp_socket permissive=0

There are multiple solutions for this problem:

  • If you want to keep SELinux in enforcing mode, you can configure SELinux to recognize the port used by the SkySQL service as an additional MySQL port by executing the semanage port command:

    $ sudo semanage port --modify \
       --type mysqld_port_t \
       --proto tcp 5001
    
  • If you don't want to keep SELinux in enforcing mode, you can set SELinux to permissive mode by executing the setenforce command:

    $ sudo setenforce 0
    

Create User Account for Replication

On the SkySQL service, create a user account to use for replication.

A replication user account can be created by calling the sky.install_repl_users() stored procedure:

CALL sky.install_repl_users();

Get User Account Credentials for Replication

On the SkySQL service, get the credentials for the user account created in the last step.

The credentials can be obtained by calling the sky.get_replica_creds() stored procedure:

CALL sky.get_replica_creds();
+-------------------------------+----------------------------------+
| PRIMARY_USER                  | PRIMARY_PASSWORD                 |
+-------------------------------+----------------------------------+
| skysql_replication            | <password>                       |
+-------------------------------+----------------------------------+

Check User Account

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

SHOW GRANTS FOR 'skysql_replication'@'%';
+-----------------------------------------------------------------------+
| Grants for skysql_replication@%                                       |
+-----------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%'            |
+-----------------------------------------------------------------------+

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.

Create Binary Log

On the SkySQL service, create a binary log.

A binary log can be created by calling the sky.create_binlog() stored procedure. The stype parameter should be set to mariadb:

CALL sky.create_binlog('mariadb', 'mysql-bin');

Obtain Binary Log File and Position

On the SkySQL service, obtain the binary log file and position.

The binary log file and position can be obtained by calling the sky.binlog_status() stored procedure:

CALL sky.binlog_status();
+------------------+----------+--------------+----------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                   |
+------------------+----------+--------------+----------------------------------------------------+
| mysql-bin.000001 |        4 | NULL         | system, _replication, clustrix_statd, clustrix_dbi |
+------------------+----------+--------------+----------------------------------------------------+

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=5001,
   MASTER_USER='skysql_replication',
   MASTER_PASSWORD='my_password',
   MASTER_SSL=1,
   MASTER_SSL_CA='/path/to/skysql_chain.pem',
   MASTER_LOG_FILE='mysql-bin.000001',
   MASTER_LOG_POS=4;

Start Replication

On the external replica server, start replication.

Replication can be started using the START SLAVE statement:

START SLAVE;

Check Replication Status

On the external replica server, check replication status.

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

SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: my-service.mdb0002147.db.skysql.net
                   Master_User: skysql_replication
                   Master_Port: 5001
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000001
           Read_Master_Log_Pos: 4
                Relay_Log_File: mariadb-relay-bin.000001
                 Relay_Log_Pos: 4
         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: 4
               Relay_Log_Space: 256
               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: 2135026113
                  Master_UUID:
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 210618 01:22:12
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

Replication Stored Procedures

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

binlog_status

Executes the SHOW MASTER STATUS statement to obtain the binary log file and position when configuring outbound replication.

CALL sky.binlog_status();
+--------------------+----------+--------------+----------------------------------------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB                                   |
+--------------------+----------+--------------+----------------------------------------------------+
| mariadb-bin.000001 |        4 | NULL         | system, _replication, clustrix_statd, clustrix_dbi |
+--------------------+----------+--------------+----------------------------------------------------+

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

create_binlog

Executes the CREATE BINLOG statement to create a binary log file. The stype parameter should be set to either 'mariadb' or 'xpand', depending on whether the external replica server runs MariaDB Server or MariaDB Xpand.

CALL sky.create_binlog(
   stype varchar(15),
   name varchar(255)
);

drop_binlog

Executes the DROP BINLOG statement to drop a binary log file.

CALL sky.drop_binlog(
   name varchar(255)
);

drop_repl_users

Drops the user account created by sky.install_repl_users().

CALL sky.drop_repl_users();

get_replica_creds

Prints the user account credentials created by sky.install_repl_users().

CALL sky.get_replica_creds();
+-------------------------------+----------------------------------+
| PRIMARY_USER                  | PRIMARY_PASSWORD                 |
+-------------------------------+----------------------------------+
| skysql_replication            | <password>                       |
+-------------------------------+----------------------------------+

install_repl_users

Creates a user account that can be used to configure outbound replication.

CALL sky.install_repl_users();

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 SLAVE STATUS statement to obtain the status of inbound replication.

CALL sky.replication_status();
*************************** 1. row ***************************
             Slave_Name: default
           Slave_Status: Running
            Master_Host: mariadb1.example.com
            Master_Port: 3306
            Master_User: skysql_replication
        Master_Log_File: mariadb-bin
          Slave_Enabled: Enabled
           Log_File_Seq: 1
           Log_File_Pos: 569
             Last_Error: no error
      Connection_Status: Connected
   Relay_Log_Bytes_Read: 0
Relay_Log_Current_Bytes: 0
  Seconds_Behind_Master: 0

reset_replication

Executes the STOP SLAVE and START SLAVE statements to restart replication.

CALL sky.reset_replication();

skip_repl_error

Sets the sql_slave_skip_counter system variable to skip a single replication error.

CALL sky.skip_repl_error();

start_replication

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

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

stop_replication

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

CALL sky.stop_replication();