Inbound Replication to Replicated Transactions from MariaDB Server
This page is part of MariaDB's Documentation.
The parent of this page is: Replication for Replicated Transactions
Topics on this page:
Overview
MariaDB SkySQL customers can configure inbound replication from MariaDB Server to a Replicated Transactions service.
For additional information about the stored procedures used to configure replication with Replicated Transactions services, see "SkySQL Replication Helper Procedures for Replicated Transactions".
Use Cases
Offsite replica for Disaster Recovery (DR)
Dedicated replica for reporting workloads
Migration cutover to SkySQL, such as Lift and Shift
Compatibility
Replicated Transactions
Requirements
To configure inbound replication from an external primary server using MariaDB Server to your Replicated Transactions service in SkySQL, the following requirements must be met:
The external primary server must use a supported version of MariaDB Server, and the external primary server must use a version in the same or older release series as the version used by the SkySQL service.
When the SkySQL service uses ES 10.6, the following versions are supported for the external primary server:
MariaDB Server 10.2
MariaDB Server 10.3
MariaDB Server 10.4
MariaDB Server 10.5
MariaDB Server 10.6
When the SkySQL service uses ES 10.5, the following versions are supported for the external primary server:
MariaDB Server 10.2
MariaDB Server 10.3
MariaDB Server 10.4
MariaDB Server 10.5
When the SkySQL service uses ES 10.4, the following versions are supported for the external primary server:
MariaDB Server 10.2
MariaDB Server 10.3
MariaDB Server 10.4
Obtain GTID Position
On the external primary server, obtain the GTID position from which to start replication.
When you want to start replication from the most recent transaction, the current GTID position can be obtained by querying the value of the gtid_current_pos
system variable with the SHOW GLOBAL VARIABLES
statement:
SHOW GLOBAL VARIABLES
LIKE 'gtid_current_pos';
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| gtid_current_pos | 0-100-1 |
+------------------+---------+
Configure GTID Position
On the SkySQL service, configure the GTID position from which to start replication.
The GTID position can be configured using the sky.change_external_primary_gtid()
stored procedure:
CALL sky.change_external_primary_gtid('mariadb1.example.com', 3306, '0-100-1', 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_IO_State: Waiting for master to send event
Master_Host: mariadb1.example.com
Master_User: skysql_replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 558
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 674
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 558
Relay_Log_Space: 985
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-100-1
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
Check GTID Status
On the SkySQL service, check GTID status.
GTID status can be checked using the sky.gtid_status()
stored procedure:
CALL sky.gtid_status();
+-------------------+---------------------------+
| Variable_name | Value |
+-------------------+---------------------------+
| gtid_binlog_pos | 435700-435700-122 |
| gtid_binlog_state | 435700-435700-122 |
| gtid_current_pos | 0-100-1,435700-435700-122 |
| gtid_slave_pos | 0-100-1 |
+-------------------+---------------------------+