Inbound Serial Replication to Distributed Transactions from MariaDB Xpand
This page is part of MariaDB's Documentation.
The parent of this page is: Replication for Distributed Transactions
Topics on this page:
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