Parallel Replication from Distributed Transactions to Distributed Transactions

Use Cases

• Replica in another region for Disaster Recovery (DR)

• Dedicated replica for reporting workloads

Compatibility

• Distributed Transactions, with MariaDB Xpand 6

Requirements

To configure parallel replication from your Distributed Transactions service in SkySQL to another Distributed Transactions service in SkySQL, the following requirements must be met:

• Both Distributed Transactions services must use the same character set and collation.

• Both Distributed Transactions services must use MariaDB Xpand 6.

Create User Account for Replication

On the primary 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 primary 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 primary 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'@'%'            |
+-----------------------------------------------------------------------+

Obtain Outbound IP for Replica Service

On the SkySQL Customer Portal, obtain the outbound IP address for the replica SkySQL service.

On the "Service Details" view, the "Outbound IP" field contains the address.

Add External Replica to Allowlist

On the SkySQL Customer Portal, add the outbound IP address of the replica SkySQL service to the primary SkySQL service's allowlist.

Create Binary Log

On the primary 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 primary SkySQL service, obtain the binary log file and position.

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

CALL sky.binlog_status_parallel();

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

Configure Replication

On the replica SkySQL service, configure replication using the connection parameters for your primary SkySQL service.

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

CALL sky.create_replication_slave_parallel('FULLY_QUALIFIED_DOMAIN_NAME', TCP_PORT, 'skysql_replication', '<password>', 'xpand-bin', 0, true, 6, 5000);

• Replace FULLY_QUALIFIED_DOMAIN_NAME with the Fully Qualified Domain Name of your primary SkySQL service

• Replace TCP_PORT with the read-write or read-only port of your service

Start Replication

On the replica 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 replica 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: 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