Outbound Replication from Distributed Transactions to MariaDB Server

Overview

MariaDB SkySQL customers can configure outbound replication from a Distributed Transactions service to MariaDB Server.

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 MariaDB Server, 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.

  • The external replica server must use the same character set and collation as the SkySQL service.

  • The external replica server must use a supported version of MariaDB Server. The following versions are supported for the external replica server:

    • MariaDB Server 10.3

    • MariaDB Server 10.4

    • MariaDB Server 10.5

    • MariaDB Server 10.6

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', 'mariadb-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                                   |
+--------------------+----------+--------------+----------------------------------------------------+
| mariadb-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='mariadb-bin.000001',
   MASTER_LOG_POS=4;
  • Replace FULLY_QUALIFIED_DOMAIN_NAME with the Fully Qualified Domain Name of your service

  • Replace TCP_PORT with the read-write or read-only port of your service

  • Replace ~/PATH_TO_PEM_FILE with 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 REPLICA statement:

START REPLICA;

Check Replication Status

On the external replica server, check replication status.

Replication status can be checked using the SHOW REPLICA STATUS statement:

SHOW REPLICA 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_SSL_Crl: /var/lib/mysql/skysql_chain.pem
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: optimistic
                     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