Configure MariaDB Xpand as a Replication Master
When outbound replication is used with MariaDB Xpand, the master Xpand cluster must be configured to maintain binary logs.
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';
When running MySQL database as a slave to a Xpand master, Xpand does not support the variable binlog_
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:
CREATE BINLOG Syntax
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 (
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.
If a database is set to both
IGNORE, Xpand will
IGNORE. This deviates from MySQL, which will log and not ignore.
Setting Binary Logging Options
To configure binary logging options, issue the
ALTER BINLOG command. Options for the
ALTER BINLOG logfile command are as follows.
Only log updates to databases db1 and db2
Ignore updates to db3
Log updates to db4, in addition to others
Ignore updates to db5, in addition to others
Stop logging to db6
Log updates to all databases, as opposed to specific databases. Does not reset the
Disable logging to this binlog
Enable logging to this binlog
Rename specified binlog to "bar"
Configure log format (row-based or statement-based)
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_
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:
Trimming using the TRIM BINLOG Command
Back up your database regularly using the
mysqldump --master-data command, which records the binary log filename at the start of the dump. To keep the size of the binary log under control, use this value to trim older data after it is backed up. The extent to which you trim is a matter of policy: you can choose to retain a week's history, or you might prefer to minimize disk consumption as much as possible by trimming all but the current file. To minimize the amount of space being used by your binary log, trim according to the Slave that is farthest behind in replication.
To list the files that compose the binary log, issue the following:
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 | ...
To display current Slave locations, issue the
SHOW SLAVE STATUS command, which displays status as follows:
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)
The filename argument is the filename returned by the
SHOW MASTER STATUS command. The sequence number (
Log_File_Seq) indicates the binary log file currently in use (it's the numeric portion of the file name). To delete old binary data without dropping the entire log, issue the following command (nnnnn represents the sequence number):
master> TRIM BINLOG 'binlog_name' BEFORE FILE 'binlog_name.nnnnn';
For example, if the
clx001.000283 file is timestamped at 7:15 PM on September 28, 2016, the following sample would delete all log files before that time.
master> TRIM BINLOG 'clx001' BEFORE FILE 'clx001.000283';
Trimming Using the binlog-trim Script
You can find the
binlog-trim script in
/opt/clustrix/bin/binlog-trim of your system.
Show this help message and exit
Specifies the host
SQL port, default is mysql port:
Username, default is
Specifies the password
Number of files to trim a time
Do not perform any actual trims
Keep this many days of binlogs
Seconds between trims
Name of binlog to trim; must specify if multiple binlogs exist
Maximum time (minutes) script may run
Indicates the version
binlog-trim is generally deployed as a
cron job on one of the nodes. An example entry to run once a day at 5:35UTC, with a retention policy of 7 days, trimming no more than 50 files at a time, with a minimum 60-second pause between trims, and operating on the binlog called clustrix-bin:
35 5 * * * root /bin/binlog-trim -H localhost -i 60 -k 7 -n 50 -b clustrix-bin 2>&1 >> /var/log/binlog-trim.log
INTERVAL is a minimum wait between trims; there is additional logic in the script to prevent the trims from building up too much cleanup work (the logs will indicate this with 'waiting for bigc to pass trim').
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:
To list the most recent binlog, issue the
SHOW MASTER STATUScommand. The command returns a filename such as
Create a directory in the
/clustrixmount on a node and
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
By default, the tool outputs decoded binlog messages to
stdout. To specify an output file, specify the
-dumpbinlog option. If you intend to archive the binlogs, omit
-logpos, which can create gaps in the resulting binlog. By default, the utility stays connected to the master. To specify when it is to disconnect, include the
Valid options for the
repclient command are as follows:
Database host (default:
Number of messages to dump
Ending replication log name
Ending replication log position (default:
List command options
List command options plus debugging output options
Starting replication log name
Starting replication log position (default:
Maximum packet size (default:
Maximum retries after an error (default:
Don't decode row values
Database password (default:
Dump performance statistics
Dump performance statistics interval (default:
Database port (default:
Timeout in seconds for retries (default:
Set a variable to the given value
Slave ID (default:
Test database connection and display status
Truncate any existing files
Database username (default:
Display debugging messages
Excluding A Session from Binary Logs
To prevent a session's statements from being written to any binary log, set
false by executing
sql_log_bin is also available as a global variable.
Be careful using sql_
In MariaDB Xpand 6, you can also exclude a session from writing to specific binlogs using the
sql_log_skip_binlogs system variable.
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
binlogB, set the system variable by executing
To re-enable writes to all binlogs for the current session, set the system variable back to
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;
You cannot recover a binary log after dropping it.
The following global and session variables control binary log behavior:
Always NONE. Xpand masters do not support generating event checksums.
Force all binlogs to log in this format, unless set to 'DEFAULT'.
Always OFF. Xpand masters do not support generating GTID events.
Dummy variable for compatibility. (Xpand does not support replication with Global Transaction Identifiers.)
Binlog used in SHOW MASTER STATUS when used without specifying a binlog.
Log statements to binary logs. This variable can be set to FALSE on a per-session basis.
Dummy variable for compatibility.
Exercise extreme care when changing these settings. The defaults may not be ideal for your system, but they should be reasonable. The product will not warn you if you configure inadvisable settings.
The following pages describe areas that should be understood when using Xpand as a Replication Master: