Inbound Replication to Distributed Transactions from MariaDB Server

Use Cases

• Offsite replica for Disaster Recovery (DR)

• Dedicated replica for reporting workloads

• Migration cutover to SkySQL, such as Lift and Shift

Compatibility

• Distributed Transactions

Requirements

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

• 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

  • MariaDB Server 10.6

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