The binlogrouter is a router that acts as a replication proxy for MariaDB
primary-replica replication. The router connects to a primary, retrieves the binary
logs and stores them locally. Replica servers can connect to MaxScale like they
would connect to a normal primary server. If the primary server goes down,
replication between MaxScale and the replicas can still continue up to the latest
point to which the binlogrouter replicated to. The primary can be changed without
disconnecting the replicas and without them noticing that the primary server has
changed. This allows for a more highly available replication setup.
In addition to the high availability benefits, the binlogrouter creates only one
connection to the primary whereas with normal replication each individual replica
will create a separate connection. This reduces the amount of work the primary
database has to do which can be significant if there are a large number of
replicating replicas.
Binlog purge, archive and compress
File purge and archive are mutually exclusive. Archiving simply means that
a binlog is moved to another directory. That directory can be mounted to another
file system for backups or, for example, a locally mounted S3 bucket.
If archiving is started from a primary that still has all its history intact, a
full copy of the primary can be archived.
File compression preserves disk space and makes archiving faster. All
binlogs except the very last one, which is the one being logged to, can be
compressed. The overhead of reading from a compressed binlog is small, and
is typically only needed when a replica goes down, reconnects and is far
enough behind the current GTID that an older file needs to be opened.
There is no automated way as of yet for the binlogrouter to use archived files,
but should the need arise files can be copied from the archive to the binlog
directory. See .
The related configuration options, which are explained in more detail in the
configuration section are: Select purge or archive. Directory where binlog files are stored (the default is usually fine). Directory to which files are archived. This directory
&#xNAN;must exist when MaxScale is started. The minimum number of
binlogs to keep before purge or archive is allowed. Duration from the last file
&#xNAN;modification until the binlog is eligible for purge or archive. Select a compression algorithm
or none for no compression. Currently only zstandard is supported.
The minimum number of
binlogs not to compress.
Following are example settings where it is expected that a
replica is down for no more than 24 hours.
Modifying binlog files manually
There is usually no reason to modify the contents of the binlog directory.
Changing the contents can cause failures if not done correctly. Never make
any changes if running a version prior to 23.08, except when a is needed.
A binlog file has the name .<sequence_number>. The basename is decided
by the primary server. The sequence number increases by one for each file and is six
digits long. The first file has the name .000001
The file binlog.index contains the view of the current state of the binlogs
as a list of file names ordered by the file sequence number. binlog.index is
automatically generated any time the contents of datadir changes.
Older files can be manually deleted and should be deleted in the order they
were created, lowest to highest sequence number. Prefer to use purge configuration.
Archived files can be copied back to datadir, but care should be taken to copy
them back in the reverse order they were created, highest to lowest sequence number.
The copied over files will be re-archived once expire_log_duration time has
passed.
Never leave a gap in the sequence numbers, and always preserve the name of a
binlog file if copied. Do not copy binlog files on top of existing binlog files.
As of version 24.02 any binlog except the latest one can be manually compressed, .e.g:
Supported SQL Commands
The binlogrouter supports a subset of the SQL constructs that the MariaDB server
supports. The following commands are supported:
The binlogrouter supports the same syntax as the MariaDB server but only the
following values are allowed:
NOTE: MASTER_LOG_FILE and MASTER_LOG_POS are not supported
as binlogrouter only supports GTID based replication.
Stops replication, same as MariaDB.
Semi-sync replication
If the server from which the binlogrouter replicates from is using semi-sync
replication, the binlogrouter will acknowledge the replicated events.
Configuration Parameters
The binlogrouter is configured similarly to how normal routers are configured in
MaxScale. It requires at least one listener where clients can connect to and one
server from which the database user information can be retrieved. An example
configuration can be found in the section of this document.
Default: /var/lib/maxscale/binlogs
Directory where binary log files are stored.
Mandatory if expiration_mode=archive
The directory to where files are archived. This is presumably a directory
mounted to a remote file system or an S3 bucket. Ensure that the user running
MaxScale (typically "maxscale") has sufficient privileges on the archive directory.
S3 buckets mounted with s3fs may require setting permissions manually:
The directory must exist when MaxScale starts.
The server ID that MaxScale uses when connecting to the primary and when serving
binary logs to the replicas.
Network connection and read timeout for the connection to the primary.
Automatically select the primary server to replicate from.
When this feature is enabled, the primary which binlogrouter will replicate
from will be selected from the servers defined by a monitor cluster=TheMonitor.
Alternatively servers can be listed in servers. The servers should be monitored
by a monitor. Only servers with the Master status are used. If multiple primary
servers are available, the first available primary server will be used.
If a CHANGE MASTER TO command is received while select_master is on, the
command will be honored and select_master turned off until the next reboot.
This allows the Monitor to perform failover, and more importantly, switchover.
It also allows the user to manually redirect the Binlogrouter. The current
primary is "sticky", meaning that the same primary will be chosen on reboot.
NOTE: Do not use the mariadbmon parameter if the monitor is
monitoring a binlogrouter. The binlogrouter does not support all the SQL
commands that the monitor will send and the rejoin will fail. This restriction
will be lifted in a future version.
The GTID the replication will start from, will be based on the latest replicated
GTID. If no GTID has been replicated, the router will start replication from the
start. Manual configuration of the GTID can be done by first configuring the
replication manually with CHANGE MASTER TO.
expiration_mode
Choose whether expired logs should be purged or archived.
expire_log_duration
Duration after which a binary log file expires, i.e. becomes eligible for purge or archive.
This is similar to the server system variable.
A value of 0s turns off purging.
.
The duration is measured from the last modification of the log file. Files are
purged in the order they were created. The automatic purge works in a similar
manner to PURGE BINARY LOGS TO <filename> in that it will stop the purge if
an eligible file is in active use, i.e. being read by a replica.
expire_log_minimum_files
The minimum number of log files the automatic purge keeps. At least one file
is always kept.
compression_algorithm
number_of_noncompressed_files
The minimum number of log files that are not compressed. At least one file
is not compressed.
When enabled, only DDL events are written to the binary logs. This means thatCREATE, ALTER and DROP events are written but INSERT, UPDATE andDELETE events are not.
This mode can be used to keep a record of all the schema changes that occur on a
database. As only the DDL events are stored, it becomes very easy to set up an
empty server with no data in it by simply pointing it at a binlogrouter instance
that has ddl_only enabled.
encryption_key_id
Encryption key ID used to encrypt the binary logs. If configured, an
must also be configured and it must contain the key with the given ID. If the
encryption key manager supports versioning, new binary logs will be encrypted
using the latest encryption key. Old binlogs will remain encrypted with older
key versions and remain readable as long as the key versions used to encrypt
them are available.
Once binary log encryption has been enabled, the encryption key ID cannot be
changed and the key must remain available to MaxScale in order for replication
to work. If an encryption key is not available or the key manager fails to
retrieve it, the replication from the currently selected primary server will
stop. If the replication is restarted manually, the encryption key retrieval is
attempted again.
Re-encryption of binlogs using another encryption key is not possible. However,
this is possible if the data is replicated to a second MaxScale server that uses
a different encryption key. The same approach can also be used to decrypt
binlogs.
encryption_cipher
The encryption cipher to use. The encryption key size also affects which mode is
used: only 128, 192 and 256 bit encryption keys are currently supported.
Possible values are:
rpl_semi_sync_slave_enabled
Enable
replication when replicating from a MariaDB server. If enabled, the binlogrouter
will send acknowledgment for each received event. Note that the
parameter must be enabled in the MariaDB server where the replication is done
from for the semi-synchronous replication to take place.
New installation
Configure and start MaxScale.
If you have not configured select_master=true (automatic
primary selection), issue a CHANGE MASTER TO command to binlogrouter.
Redirect each replica to replicate from Binlogrouter
Upgrading from legacy versions
Binlogrouter does not read any of the data that a version prior to 2.5
has saved. By default binlogrouter will request the replication stream
from the blank state (from the start of time), which is basically meant
for new systems. If a system is live, the entire replication data probably
does not exist, and if it does, it is not necessary for binlogrouter to read
and store all the data.
Before you start
Binlogrouter uses which has three kernel limits.
While Binlogrouter uses a modest number of inotify instances, the limit max_user_instances applies to the total
number of instances for the user and has a low default value on many systems. A double or triple of the
default value should suffice. The other two limits, max_queued_events and max_user_watches are usually high
enough, but it is sensible to double (triple) them if max_user_instances was doubled (tripled).
Note that binlogrouter only supports GTID based replication.
The method described here inflicts the least downtime. Assuming you have
configured MaxScale version 2.5 or newer, and it is ready to go:
Redirect each replica that replicates from Binlogrouter to replicate from the
primary.
Stop the old version of MaxScale, and start the new one.
Verify routing functionality.
Issue a CHANGE MASTER TO command, or use .
Run maxctrl list servers. Make sure all your servers are accounted for.
Pick the lowest gtid state (e.g. 0-1000-1234,1-1001-5678) on display and
issue this command to Binlogrouter:
NOTE: Even with select_master=true you have to set @@global.gtid_slave_pos
if any binlog files have been purged on the primary. The server will only stream
from the start of time if the first binlog file is present.
See .
Redirect each replica to replicate from Binlogrouter.
Bootstrap binlogrouter
If for any reason you need to "bootstrap" the binlogrouter you can change
the datadir or delete the entire binglog directory (datadir) when
MaxScale is NOT running. This could be necessary if files are accidentally
deleted or the file system becomes corrupt.
No changes are required to the attached replicas.
if is set to true and the primary contains
the entire binlog history, a simple restart of MaxScale sufficies.
In the normal case, the primary does not have the entire history and
you will need to set the GTID position to a starting value, usually the
earliest gtid state of all replicas. Once MaxScale has been restarted
connect to the binlogrouter from the command line.
If is set to true issue:
else
When replicating from a Galera cluster, must be
set to true, and the servers must be monitored by the .
Configuring binlogrouter is the same as described above.
The Galera cluster must be configured to use .
The MariaDB version must be 10.5.1 or higher.
The required GTID related server settings for MariaDB/Galera to work with
Binlogrouter are listed here:
The following is a small configuration file with automatic primary selection.
With it, the service will accept connections on port 3306.
Old-style replication with binlog name and file offset is not supported
and the replication must be started by setting up the GTID to replicate
from.
Only replication from MariaDB servers (including Galera) is supported.
Old encrypted binary logs are not re-encrypted with newer key versions ()
This page is licensed: CC BY-SA / Gnu FDL