Inbound Serial Replication to Distributed Transactions from MariaDB Xpand

Overview

MariaDB SkySQL customers can configure inbound serial replication from MariaDB Xpand to a Distributed Transactions service.

Alternatively, if your Distributed Transactions service uses MariaDB Xpand 6, parallel replication can be used instead of serial replication. For additional information, see "Inbound Parallel Replication to Distributed Transactions from MariaDB Xpand".

For additional information about the stored procedures used to configure replication with Distributed Transactions services, see "SkySQL Replication Helper Procedures for Distributed Transactions".

Use Cases

  • Offsite replica for Disaster Recovery (DR)

  • Dedicated replica for reporting workloads

  • Migration cutover to SkySQL

Compatibility

  • Distributed Transactions

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.

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