SkySQL Replication Helper Procedures for Distributed Transactions

Overview

MariaDB SkySQL provides stored procedures in the sky database to configure inbound and outbound replication with a Distributed Transactions service.

Compatibility

add_binlog_scope_ignore

Executes the ALTER BINLOG .. ADD IGNORE(..) statement to add a binary log filter that ignores the specified scope.

CALL sky.add_binlog_scope_ignore(
   binlog varchar(255),
   db_table_entry varchar(255)
);

add_binlog_scope_log

Executes the ALTER BINLOG .. ADD LOG(..) statement to add a binary log filter that logs the specified scope.

CALL sky.add_binlog_scope_log(
   binlog varchar(255),
   db_table_entry varchar(255)
);

binlog_scope_ignore_all

Executes the ALTER BINLOG .. IGNORE ALL statement to reset the current binary log filters and ignore everything.

CALL sky.binlog_scope_ignore_all(
   binlog varchar(255),
   db_table_entry varchar(255)
);

binlog_scope_log_all

Executes the ALTER BINLOG .. LOG ALL statement to reset the binary log filters and log everything.

CALL sky.binlog_scope_log_all(
   binlog varchar(255),
   db_table_entry varchar(255)
);

binlog_status

Executes the SHOW MASTER STATUS statement to obtain the binary log file and position when configuring outbound serial replication.

CALL sky.binlog_status();

+--------------------+----------+--------------+----------------------------------------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB                                   |
+--------------------+----------+--------------+----------------------------------------------------+
| mariadb-bin.000001 |        4 | NULL         | system, _replication, clustrix_statd, clustrix_dbi |
+--------------------+----------+--------------+----------------------------------------------------+

binlog_status_parallel

Executes the SHOW MASTER STATUS PARALLEL statement to obtain the binary log file and position when configuring outbound parallel replication from an external replica server running MariaDB Xpand 6.

CALL sky.binlog_status_parallel();

+-----------+----------+--------------+------------------+
| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------+----------+--------------+------------------+
| xpand-bin |        0 |              |                  |
+-----------+----------+--------------+------------------+

change_external_primary

Executes the CHANGE MASTER TO statement to configure inbound replication from an external primary server based on binary log file and position.

CALL sky.change_external_primary(
   host varchar(255),
   port int(11),
   logfile text,
   logpos bigint(20),
   use_ssl_encryption tinyint(1)
);

+--------------------------------------------------------------------------------------------------------------+
| Run_this_grant_on_your_external_primary                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%' IDENTIFIED BY '<password>';                       |
+--------------------------------------------------------------------------------------------------------------+

create_binlog

Executes the CREATE BINLOG statement to create a binary log file. The stype parameter should be set to either 'mariadb' or 'xpand', depending on whether the external replica server runs MariaDB Server or MariaDB Xpand.

CALL sky.create_binlog(
   stype varchar(15),
   name varchar(255)
);

create_replication_slave_parallel

Executes the CREATE SLAVE statement to configure a parallel replication slave named default that performs inbound parallel replication from an external primary server running MariaDB Xpand 6.

CALL sky.create_replication_slave_parallel(
   rhost varchar(255),
   rport int(11),
   ruser varchar(255),
   rpass varchar(255),
   logfile text,
   logpos bigint(20),
   use_ssl_encryption tinyint(1),
   slices int(11),
   batch_size_ms int(11)
);

drop_binlog

Executes the DROP BINLOG statement to drop a binary log file.

CALL sky.drop_binlog(
   name varchar(255)
);

drop_binlog_scope_ignore

Executes the ALTER BINLOG .. DROP IGNORE(..) statement to remove a binary log filter that ignores the specified scope.

CALL sky.drop_binlog_scope_ignore(
   binlog varchar(255),
   db_table_entry varchar(255)
);

drop_binlog_scope_log

Executes the ALTER BINLOG .. DROP LOG(..) statement to remove a binary log filter that logs the specified scope.

CALL sky.drop_binlog_scope_log(
   binlog varchar(255),
   db_table_entry varchar(255)
);

drop_replication_slave_parallel

Executes the STOP SLAVE and DROP SLAVE statements to stop and drop the parallel replication slave named default.

CALL sky.drop_replication_slave_parallel();

drop_repl_users

Drops the database user account created by sky.install_repl_users().

CALL sky.drop_repl_users();

get_replica_creds

Prints the database user account credentials created by sky.install_repl_users().

CALL sky.get_replica_creds();

+-------------------------------+----------------------------------+
| PRIMARY_USER                  | PRIMARY_PASSWORD                 |
+-------------------------------+----------------------------------+
| skysql_replication            | <password>                       |
+-------------------------------+----------------------------------+

install_repl_users

Creates a database user account that can be used to configure outbound replication. The credentials can be retrieved with sky.get_replica_creds(). The database user account can be dropped with sky.drop_repl_users().

CALL sky.install_repl_users();

replication_grants

Provides a GRANT statement to run on an external primary server when configuring inbound replication.

CALL sky.replication_grants();

+--------------------------------------------------------------------------------------------------------------+
| Run_this_grant_on_your_external_primary                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication'@'%' IDENTIFIED BY '<password>';                       |
+--------------------------------------------------------------------------------------------------------------+

replication_status

Executes the SHOW SLAVE STATUS statement to obtain the status of inbound replication.

CALL sky.replication_status();

*************************** 1. row ***************************
             Slave_Name: default
           Slave_Status: Running
            Master_Host: mariadb1.example.com
            Master_Port: 3306
            Master_User: skysql_replication
        Master_Log_File: mariadb-bin
          Slave_Enabled: Enabled
           Log_File_Seq: 1
           Log_File_Pos: 569
             Last_Error: no error
      Connection_Status: Connected
   Relay_Log_Bytes_Read: 0
Relay_Log_Current_Bytes: 0
  Seconds_Behind_Master: 0

reset_replication

Executes the STOP SLAVE and START SLAVE statements to restart the replication slave named default.

CALL sky.reset_replication();

skip_repl_error

This stored procedure can be used to ignore a transaction that is causing a replication error.

Executes the STOP SLAVE statement, then sets the sql_slave_skip_counter system variable, and then executes the START SLAVE statements to skip a single transaction for the replication slave named default.

CALL sky.skip_repl_error();

start_replication

Executes the START SLAVE statement to start the replication slave named default that performs inbound replication from an external primary.

CALL sky.start_replication();

+----------------------------------------+
| Message                                |
+----------------------------------------+
| External replication running normally. |
+----------------------------------------+

stop_replication

Executes the STOP SLAVE statement to stop the replication slave named default that performs inbound replication from an external primary.

CALL sky.stop_replication();