Inbound Parallel Replication to Distributed Transactions from MariaDB Xpand

Use Cases

• Offsite replica for Disaster Recovery (DR)

• Dedicated replica for reporting workloads

• Migration cutover to SkySQL

Compatibility

• Distributed Transactions, with MariaDB Xpand 6

Requirements

To configure inbound replication from an external primary server using MariaDB Xpand to your Distributed Transactions service in SkySQL, the following requirements must be met:

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

• The external primary server must use MariaDB Xpand 6.

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 PARALLEL 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 PARALLEL;

+-----------+----------+--------------+------------------+
| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------+----------+--------------+------------------+
| xpand-bin |        0 |              |                  |
+-----------+----------+--------------+------------------+

Obtain Credentials for Replication User

On the SkySQL service, obtain the credentials for the replication database user account to create on the external primary server.

The credentials can be obtained using the sky.replication_grants() stored procedure:

CALL sky.replication_grants();

+--------------------------------------------------------------------------------------------------------------+
| Run_this_grant_on_your_external_primary                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%' IDENTIFIED BY '<password>';                       |
+--------------------------------------------------------------------------------------------------------------+

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

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.create_replication_slave_parallel() stored procedure:

CALL sky.create_replication_slave_parallel('xpand1.example.com', 3306, 'skysql_replication', '<password>', 'xpand-bin', 0, false, 6, 5000);

Start Replication

On the SkySQL service, start replication.

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

CALL sky.start_replication();

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