Configure MariaDB Xpand as a Replication Master

Compatibility

• MariaDB Xpand 5.3

• MariaDB Xpand 6.0

• MariaDB Xpand 6.1

About Binary Logs

Xpand implements MySQL compatible binlogs that with the following additional features:

• Xpand can maintain multiple independent binary logs (for example, binlogs per table, per database, or for a set of tables and databases).

• The binary logs are fault-tolerant, with the same guarantees as the rest of the Xpand system.

• Writes to the binlog are transactional, consistent, and durable (full ACID guarantee).

• Binary logs can be created and dropped online.

To configure a Xpand system with a single row-based binary log, issue the following commands:

master> CREATE BINLOG 'clustrix-bin' FORMAT='ROW';
master> SHOW MASTER STATUS;

To disable binary logging and drop (permanently) an existing binlog:

master> DROP BINLOG 'clustrix-bin';

Create Replication User

The user name and password used for replication are stored as plain text within the binlogs. As such, Xpand recommends establishing a separate account for exclusive use with replication to prevent compromising the security of regularly used accounts.

Follow this sample to create an account that will be used when setting up a slave. You must have privileges to CREATE USER and GRANT to perform this step.

master> CREATE USER 'replication'@'%' IDENTIFIED BY 'clustrix';
master> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

Creating a Binary Log File

To create a binary log, issue the following command:

master> CREATE BINLOG 'binlog_name' [LOG (target1, target2, ...),] [IGNORE (target3, target4, ...),] [FORMAT='STATEMENT'|'ROW']

master> CREATE BINLOG 'mybinlog' FORMAT='ROW';

Optional attributes are:

• LOG: A list of specific tables or databases to log

• IGNORE: A list of specific tables or databases to ignore

• FORMAT: Format specifier (STATEMENT or ROW default is ROW).

By default, this command creates a binary log file for the entire cluster in ROW format. Alternatively, you can create binlogs that scope a database or a list of tables. For more information, please see the section on Configure Binlog Scope with MariaDB Xpand.

For most workloads, row-based replication (FORMAT ='ROW') provides better performance than statement-based replication (FORMAT ='STATEMENT'). If you are unsure what is most appropriate for your environment, contact MariaDB Support.

Setting Binary Logging Options

To configure binary logging options, issue the ALTER BINLOG command. Options for the ALTER BINLOG logfile command are as follows.

Displaying Binary Log Information

If only one binary log exists, you can display its filename, segment number and position by issuing the following command:

master> SHOW MASTER STATUS;

If more than one binary log exists, the log configured by the global variable master_status_binlog is displayed. If master_status_binlog is unset, an error is returned. This behavior is compatible with behavior of the MySQL mysqldump --master-data command.

master> SET GLOBAL master_status_binlog = 'foo';
master> SHOW MASTER STATUS;

To display status for all binary logs, issue the following command:

master> SHOW ALL MASTER STATUS;

To display detailed information about binary logs, issue the following command:

master> SHOW BINLOGS;

Most of this information is not directly useful, though log size can help you decide whether to trim the log.

Trimming a Binary Log

You can trim a binary log using either of the following methods:

• TRIM BINLOG command

• trim-binlog script

master> SHOW BINLOG FILES;
+-----------------+-----------+-----------------------+
| File            | Size      | First Event Timestamp |
+-----------------+-----------+-----------------------+
| eukanuba.000001 | 104857600 | 2016-01-09 19:51:08   |
| eukanuba.000002 | 104857600 | 2016-01-09 20:02:09   |
| eukanuba.000003 | 104857600 | 2016-01-09 22:22:27   |
| eukanuba.000004 | 104857600 | 2016-01-09 22:30:37   |
| eukanuba.000005 | 104857600 | 2016-01-09 22:38:11   |
| eukanuba.000006 | 104857600 | 2016-01-09 22:45:44   |
| eukanuba.000007 | 104857600 | 2016-01-09 22:53:03   |
| eukanuba.000008 | 104857600 | 2016-01-09 23:00:44   |
| eukanuba.000009 | 104857600 | 2016-01-09 23:07:46   |
| eukanuba.000010 | 104857600 | 2016-01-09 23:15:00   |
...

master> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
            Slave_Name: default
          Slave_Status: Running
           Master_Host: alpo
           Master_Port: 3306
           Master_User: root
       Master_Log_File: alpo
         Slave_Enabled: Enabled
          Log_File_Seq: 3383
          Log_File_Pos: 58790712
            Last_Error: no error
     Connection_Status: Connected
  Relay_Log_Bytes_Read: 0
Relay_Log_Current_Size: 0
 Seconds_Behind_Master: 0
1 row in set (0.00 sec)

master> TRIM BINLOG 'binlog_name' BEFORE FILE 'binlog_name.nnnnn';

master> TRIM BINLOG 'clx001' BEFORE FILE 'clx001.000283';

35 5 * * * root /bin/binlog-trim -H localhost -i 60 -k 7 -n 50 -b clustrix-bin 2>&1 >> /var/log/binlog-trim.log

Backing Up Binary Logs

Because Xpand binary logs (binlogs) aren't stored as plain files, they cannot be backed up as MySQL binlogs can. For backup purposes, Xpand provides the repclient utility, which copies binlogs from a Xpand or MySQL system as if it were a replication slave. The repclient utility can be run on any Xpand node.

To copy all of the binlogs off a Xpand cluster, perform the following steps:

1. To list the most recent binlog, issue the SHOW MASTER STATUS command. The command returns a filename such as clustrix-bin.001903.

2. Create a directory in the /clustrix mount on a node and cd to it.

3. To retrieve all binlog files up to the most recent, issue the following command:

   shell> node# repclient -addr 10.52.2.20 -dumpbinlog -logname clustrix-bin.000001 -end_logname clustrix-bin.001903
   

Valid options for the repclient command are as follows:

Excluding A Session from Binary Logs

To prevent a session's statements from being written to any binary log, set sql_log_bin to false by executing SET:

SET sql_log_bin=false;

sql_log_bin is also available as a global variable.

In MariaDB Xpand 6, you can also exclude a session from writing to specific binlogs using the sql_log_skip_binlogs system variable.

The sql_log_skip_binlogs system variable can be set at the session-level and accepts a comma-separated list of binlog names.

To exclude the current session's statements from binlogA and binlogB, set the system variable by executing SET:

SET sql_log_skip_binlogs='binlogA,binlogB';

To re-enable writes to all binlogs for the current session, set the system variable back to DEFAULT:

SET sql_log_skip_binlogs=DEFAULT;

Dropping a Binary Log File

To stop logging to the specified binary log and drop it from the system, issue the following command:

master> DROP BINLOG binlog_name;

Global Variables

The following global and session variables control binary log behavior:

Related Links

The following pages describe areas that should be understood when using Xpand as a Replication Master:

1. Online Schema Changes with MariaDB Xpand

2. Replication and User Account Management Statements with MariaDB Xpand

3. Multiple Replication Slaves with MariaDB Xpand