Outbound Replication from Distributed Transactions to MySQL Server
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 outbound replication from a Distributed Transactions service to MySQL 5.7.
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
Compatibility
Distributed Transactions
Requirements
To configure outbound replication from your Distributed Transactions service in SkySQL to an external replica server using MySQL 5.7, the following requirements must be met:
GTID (Global Transaction ID) is not supported. Outbound replication must be configured using the binary log file and position. On the external replica server, the
gtid_modesystem variable must be set toOFF,OFF_PERMISSIVE, orON_PERMISSIVE. Theenforce_gtid_consistencysystem variable must be set toOFF.The external replica server must use the same character set and collation as the SkySQL service.
Configure SELinux
On the external replica server, configure SELinux to allow MySQL 5.7 to connect to the SkySQL service if SELinux is in enforcing mode.
You can check if SELinux is in enforcing mode by executing the getenforce command:
$ sudo getenforce
Enforcing
When SELinux is set to enforcing mode, SELinux can block MySQL 5.7 from connecting to SkySQL. When SELinux blocks the connection, it writes the following message to the SELinux audit log:
type=AVC msg=audit(1624056805.982:587): avc: denied { name_connect } for pid=9482 comm="mysqld" dest=5001 scontext=system_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:commplex_link_port_t:s0 tclass=tcp_socket permissive=0
There are multiple solutions for this problem:
If you want to keep SELinux in enforcing mode, you can configure SELinux to recognize the port used by the SkySQL service as an additional MySQL port by executing the
semanage portcommand:$ sudo semanage port --modify \ --type mysqld_port_t \ --proto tcp 5001
If you don't want to keep SELinux in enforcing mode, you can set SELinux to permissive mode by executing the
setenforcecommand:$ sudo setenforce 0
Create User Account for Replication
On the 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 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 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'@'%' |
+-----------------------------------------------------------------------+
Add External Replica to Allowlist
On the SkySQL Customer Portal, add the IP address of the external replica server to the SkySQL service's allowlist.
Create Binary Log
On the 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 mariadb:
CALL sky.create_binlog('mariadb', 'mysql-bin');
Obtain Binary Log File and Position
On the SkySQL service, obtain the binary log file and position.
The binary log file and position can be obtained by calling the sky.binlog_status() stored procedure:
CALL sky.binlog_status();
+------------------+----------+--------------+----------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+----------------------------------------------------+
| mysql-bin.000001 | 4 | NULL | system, _replication, clustrix_statd, clustrix_dbi |
+------------------+----------+--------------+----------------------------------------------------+
Download SkySQL CA Chain
On the external replica server, download the SkySQL CA chain.
Configure Replication
On the external replica server, configure replication using the connection parameters for your MariaDB SkySQL service.
Replication can be configured using the CHANGE MASTER TO statement:
CHANGE MASTER TO
MASTER_HOST='FULLY_QUALIFIED_DOMAIN_NAME',
MASTER_PORT=TCP_PORT,
MASTER_USER='skysql_replication',
MASTER_PASSWORD='my_password',
MASTER_SSL=1,
MASTER_SSL_CA='~/PATH_TO_PEM_FILE',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;
Replace
FULLY_QUALIFIED_DOMAIN_NAMEwith the Fully Qualified Domain Name of your serviceReplace
TCP_PORTwith the read-write or read-only port of your serviceReplace
~/PATH_TO_PEM_FILEwith the path to the certificate authority chain (.pem) file
Start Replication
On the external replica server, start replication.
Replication can be started using the START SLAVE statement:
START SLAVE;
Check Replication Status
On the external replica server, check replication status.
Replication status can be checked using the SHOW SLAVE STATUS statement:
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: my-service.mdb0002147.db.skysql.net
Master_User: skysql_replication
Master_Port: 5001
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
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: 4
Relay_Log_Space: 256
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /var/lib/mysql/skysql_chain.pem
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: 2135026113
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 210618 01:22:12
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version: