Inbound Replication to Distributed Transactions from MySQL Server

Overview

MariaDB SkySQL customers can configure inbound replication from MySQL 5.7 to a Distributed Transactions service.

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 MySQL 5.7 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.

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