- About Binary Logs
- MySQL 5.7 Replication and GTIDs
- Create Replication User
- Creating a Binary Log File
- Setting Binary Logging Options
- Displaying Binary Log Information
- Trimming a Binary Log
- Backing Up Binary Logs
- Excluding A Session from Binary Logs
- Dropping a Binary Log File
- Global Variables
- Related Links
About Binary Logs
MySQL implements replication by logging queries into a series of binary log files. The files are written to the server's local file system. Slaves connect to the MySQL instance and request these files starting at a specific file and offset.
MySQL's binary logging has the following drawbacks:
- It maintains a single binary log for the entire system.
- Fault tolerance is provided by the underlying filesystem, if at all.
- Unless sync_binlog is enabled, the binary log can become inconsistent with the database as the result of power loss.
- Static configuration is read only when the server starts, so changes to /etc/my.cnf require server restart.
ClustrixDB surpasses MySQL's capabilities by providing the following additional features:
- ClustrixDB 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 ClustrixDB system.
- Writes to the binlog are transactional, consistent and durable (full ACID guarantee).
- Binary logs can be created and dropped online.
To configure a ClustrixDB system with a single binary log, issue the following commands:
To disable binary logging and drop (permanently) an existing binlog:
MySQL 5.7 Replication and GTIDs
Beginning optionally with MySQL 5.6 and required in MySQL 5.7, MySQL produces global transaction identifiers (GTIDs). ClustrixDB does not implement nor support GTIDs. To enable replication between ClustrixDB and a MySQL instance that produces GTIDs, the following settings must be configured, dependent on replication direction.
For ClustrixDB (Master) to MySQL 5.7 (Slave) Replication
- The MySQL slave must have gtid_mode set to OFF, OFF_PERMISSIVE, or ON_PERMISSIVE.
- The MySQL slave should set enforce-gtid-consistency to OFF as described in Startup Options Used with GTID Replication.
For MySQL 5.7 (Master) to ClustrixDB (Slave) Replication
- Run ClustrixDB 7.6 or higher.
- The MySQL master configuration is inconsequential and does not require special changes.
- Further information is available at Using ClustrixDB as a Slave.
Create Replication User
The user name and password used for replication are stored as plain text within the binlogs. As such, Clustrix 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.
Creating a Binary Log File
To create a binary log, issue the following command:
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").
By default, this command creates a binary log file for the entire cluster in statement format, which is the most common format in MySQL environments. Alternatively, you can create binlogs that scope a database or a list of tables. For more information, please see the section on Binlog scope.
For most workloads, row-based replication provides better performance than statement-based replication. If you are unsure what is most appropriate for your environment, contact Clustrix 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.
LOG (db1, db2)
Only log updates to databases db1 and db2
Ignore updates to db3
ADD LOG (db4)
Log updates to db4, in addition to others
ADD IGNORE (db5)
Ignore updates to db5, in addition to others
DROP LOG (db6)
Stop logging to db6
Log updates to all databases (as opposed to specific databases). Does not reset the "ignore" list.)
Disable logging to this binlog
Enable logging to this binlog
Rename specified binlog to "bar"
FORMAT='row' or 'statement'
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:
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.
To display status for all binary logs, issue the following command:
To display detailed information about binary logs, issue the following command:
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
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 prior data after backing up. The precise 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 command:
The system lists the files that compose the binary log. For example:
To display current Slave locations, issue the SHOW SLAVE STATUS command, which displays status as follows:
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 (where ###### indicates the sequence number):
For example, if the clx001.000283 file were timestamped at 7:15 PM on September 28, 2016, the following sample would delete all log files before that time.
Trimming Using the binlog-trim Script
You can find the binlog-trim script in /bin/binlog-trim of your system.
Show this help message and exit
-H HOST, --host=HOST
Specifies the host
-P PORT, --port=PORT
SQL port, default is mysql port: 3306
-u USER, --user=USER
Username, default is root
-p PASSWD, --passwd=PASSWD
Specifies the password
-n NUM_FILES, --num-files=NUM_FILES
Number of files to trim a time
Do not perform any actual trims
-k KEEP_DAYS, --days=KEEP_DAYS
Keep this many days of binlogs
-i INTERVAL, --interval=INTERVAL
Seconds between trims
-b BINLOG_NAME, --binlog_name=BINLOG_NAME
Name of binlog to trim; must specify if multiple binlogs exist
-M MAX_RUN_TIME_MINS, --max-run-time-mins=MAX_RUN_TIME_MINS
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:
Backing Up Binary Logs
Because ClustrixDB binary logs (binlogs) aren't stored as plain files, they cannot be backed up as MySQL binlogs can. For backup purposes, ClustrixDB provides the repclient utility, which copies binlogs from a ClustrixDB or MySQL system as if it were a replication slave. The repclient utility can be run on any ClustrixDB node.
To copy all of the binlogs off a ClustrixDB cluster, perform the following steps:
- To list the most recent binlog, issue the SHOW MASTER STATUS command. The command returns a filename such as "clustrix-bin.001903".
- Create a directory in the /clustrix mount on a node and cd to it.
To retrieve all binlog files up to the most recent, issue the following command:
Valid options for the repclient command are as follows:
Database host (default: 127.0.0.1)
Number of messages to dump
Ending replication log name
Ending replication log position (default: EOF)
List command options
List command options plus debugging output options
Starting replication log name
Starting replication log position (default: 4)
Maximum packet size (default: 16777216)
Maximum retries after an error (default: 3)
Don't decode row values
Database password (default: #undef)
Dump performance statistics
Dump performance statistics interval (default: 30)
Database port (default: 3306)
Timeout in seconds for retries (default: 10)
-set-variable NAME= VALUE
Set a variable to the given value
Slave ID (default: 1)
Test database connection and display status
Truncate any existing files
Database username (default: "root")
Display debugging messages
Excluding A Session from Binary Logs
To prevent a session's statements from being inserted into any binary log, set sql_log_bin to false by issuing the following command:
This variable inherits the value of the identically-named global variable at the start of each session. To replicate from a ClustrixDB instance, set sql_log_bin to true.
Dropping a Binary Log File
To stop logging to the specified binary log and drop it from the system, issue the following command:
The following global and session variables control binary log behavior:
|Name||Description||Default Value||Session Variable|
|binlog_checksum||Always NONE. Clustrix masters do not support generating event checksums.||NONE|
|binlog_commit_table_slices||Number of slices for binlog commit tables or 0 for system selected||0|
|binlog_format||Force all binlogs to log in this format, unless set to 'DEFAULT'.||DEFAULT|
|binlog_read_check||Binlogs recording a statement must log all tables read by the statement, in addition to tables modified by the statement.||true|
|binlog_rotate_threshold||Once the current binlog segment surpasses this size in bytes, a new binlog segment will be created. Setting this to 0 tells the system to select an appropriate value||0|
|binlog_row_delete_limit||Maximum number of rows each BINLOG_DELETE task trims off each time it is run.||5000|
|binlog_statement_table_slices||Number of slices for new binlog statement table segments or 0 for system selected||0|
|binlog_trim||TRUE when binlog-trim is running. Do not change.||false|
|binlog_trx_check||Every statement in a transaction must record to the same set of binlogs.||true|
|gtid_mode||Always OFF. Clustrix masters do not support generating GTID events.||OFF|
|master_status_binlog||Binlog used in SHOW MASTER STATUS when used without specifying a binlog.|
|mysql_binlog_indexer_interval_trxs||Interval (in transactions) between binlog index entries.||500|
|mysql_binlog_indexer_limit_ms||Maximum time (ms) we spend indexing binlogs during one pass. Do not change.||25000|
|mysql_binlog_size_limit_bytes||Maximum size in bytes allowed per binlog file. This variable cannot be modified if any binlogs exist.||104857600|
|rebalancer_rebalance_binlogs||Allow the rebalancer to move active binlogs.||false|
|rebalancer_split_binlogs||Allow splitting of binlog statement segments.||false|
|sync_binlog||Dummy variable for compatibility.||0|
|task_binlog_delete_interval_ms||Milliseconds between runs of periodic task "binlog_delete". Specify 0 to disable periodic task.||2000|
|task_binlog_rotate_interval_ms||Milliseconds between runs of periodic task "binlog_rotate". Specify 0 to disable periodic task.||120000|
|task_mysql_binlog_indexer_interval_ms||Milliseconds between runs of periodic task "mysql_binlog_indexer". Specify 0 to disable periodic task.||1000|
|task_mysql_binlog_index_scrubber_interval_ms||Milliseconds between runs of periodic task "mysql_binlog_index_scrubber". Specify 0 to disable periodic task.||100000|
The following pages describe areas that should be understood when using ClustrixDB as a Replication Master
- Online Schema Changes
- Replicating User Account Management Statements
- Using ClustrixDB with Multiple Slaves