Saturday, March 26, 2016

The Binlog Server [feedly]

The Binlog Server
// MariaDB blogs

Mon, 2016-03-07 07:49

MaxScale as a replication proxy, aka the Binlog Server

In database setups with a large number of users reading data, the Binlog Server can be used to offload traffic to the Master, make Master failover easier to handle and in general simplify replication. In this blog I will describe the benefits of Binlog Server and how to set up MaxScale as a Binlog Server.

In a traditional MariaDB/MySQL replication setup a single master server is created and a set of slaves of MariaDB/MySQL servers are configured to pull the binlog files from the master, putting a lot of load on the master. Introducing a layer between the master server and the slave servers can reduce the load on the master by only serving MaxScale's Binlog Server instead of all the slaves. The slaves will only need to be aware of the Binlog Server and not the real master server. Removing the requirement for the slaves to have knowledge of the master also simplifies the process of replacing a failed master within a replication environment.

MaxScale, with Binlog Server, can act as a slave to the real master and as a master to the slaves in the same way as an intermediate MySQL master does, however it does not implement any re-execution of the statements within the binary log. The latency that is introduced is mostly added network latency associated with adding the extra network hop. There is no appreciable processing performed at the MaxScale level, other than for managing the local cache of the binlog files.

In addition every MaxScale that is acting as a proxy of the master will have exactly the same binlog events as the master itself. This means that a slave can be moved between any of the MaxScale servers or to the real master without the need to perform any special processing. The result is much simpler behavior for failure recovery and the ability to have a very simple and redundant proxy layer for the slave servers.

The Binlog Server's main features are:

  • The Binlog Server requests and receives binlog records from the master server autonomously of any slave activity.
  • Stored binlogs are identical to the one stored in the Master server.
  • Binlog records received from the master must be relayed to the slaves that are able to accept them.
  • The slave servers must be able to request historical binlog records without sending any additional traffic to the master server.

How to setup MaxScale as a Binlog Server

MaxScale Binlog ServerUsing MaxScale as a replication proxy is much the same as using MaxScale as a proxy between the clients and the database servers. In this case the master server should be considered as the database backend and the slave servers as the clients of MaxScale.

The basic scenario includes:

  • 1 Master server
  • 2 or more slave servers
  • 1 MaxScale server configured with binlog router.

1- Service Configuration

As with any MaxScale configuration a good starting point is with the service definition with the maxscale.cnf file. The service requires a name which is the section name in the ini file, a type parameter with a value of service and the name of the router plugin that should be loaded. In the case of replication proxy this router name is binlogrouter.

Other standard service parameters need to be given in the configuration section that are used to retrieve the set of users from the backend (master) database, also a version string can be given such that the MaxScale instance will report this version string to the slave servers that connect to MaxScale.

[Replication]  type=service  router=binlogrouter  version_string=5.6.17-log  user=maxscale  passwd=Mhu87p2D

The master database server details are provided by a master.ini file located in binlog directory and changes could be also applied via CHANGE MASTER TO command issued via MySQL connection to MaxScale.

The user and passwd entries in the above example are used in order for MaxScale to populate the credential information that is required to allow the slaves to connect to MaxScale. This user should be configured in exactly the same way a for any other MaxScale service.

The final configuration requirement is the router specific options. The binlog router requires a set of parameters to be passed, these are passed in the router_options parameter of the service definition as a comma separated list of name value pairs.


This parameter allows the location that MaxScale uses to store binlog files to be set. If this parameter is not set to a directory name then MaxScale will store the binlog files in the directory /var/cache/maxscale/

The binlogdir is also the location for master.ini file, that contains all the details for the replication from Master server.

This is used to set the unique uuid that the router uses when it connects to the master server. It is a requirement of replication that each slave have a unique UUID value. The MaxScale router will identify itself to the slaves using the uuid of the real master and not this uuid. If no explicit value is given for the uuid in the configuration file then a uuid will be generated.


As with uuid, MaxScale must have a unique server-id for the connection it makes to the master, this parameter provides the value of server-id that MaxScale will use when connecting to the master.


The server-id value that MaxScale should use to report to the slaves that connect to MaxScale. This may either be the same as the server-id of the real master or can be chosen to be different if the slaves need to be aware of the proxy layer. The real master server-id will be used if the option is not set.


This is the user name that MaxScale uses when it connects to the master.If the user parameter is not given in the router options then the user in the service entry is used.

The user for replication, either defined using the user option in the router options or using the one defined of the service must be granted replication privileges on the database server as with any other user that a slave uses for replication purposes

MariaDB> CREATE USER 'repl'@'maxscalehost' IDENTIFIED by 'password';  MariaDB> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'maxscalehost';


The password of the above user. If the password is not explicitly given then the password in the service entry will be used.

A complete example of a service entry for a binlog router service would be as follows.

[Replication]  type=service  router=binlogrouter  version_string=5.6.17-log  router_options=server-id=3,user=repl,password=slavepass,dir=/var/binlogs  user=maxscale  passwd=Mhu87p2D

Note: the mandatory router option that must be given in the configuration is the 'server-id', default values may be used for all other options.

2 - Listener Section

A listener section is also required to define the address, port and protocol that is used to listen for incoming connections. In this case those incoming connections will originate from the slave servers.

[Replication Listener]  type=listener  service=Replication  protocol=MySQLClient  port=5308

3 - Master database connection setup

The master server setup is currently managed via CHANGE MASTER TO command issued in MySQL client connection to MaxScale or by providing a proper master.ini file in the binlogdir.

If MaxScale starts without master.ini there is no replication configured to any master and slaves cannot register to the router: the binlog router could be later configured via CHANGE MASTER TO and the master.ini file will be written.

master.ini file example:  [binlog_configuration]  master_host=  master_port=3308  master_user=repl  master_password=somepass  filestem=repl-bin

Enabling, for the first time, the replication from a master server requires this command via MySQL to MaxScale binlog server:

# mysql -h -P 5306 -u maxscale -p  MariaDB> CHANGE MASTER TO MASTER_HOST='', MASTER_PORT=3308, MASTER_USER='repl', MASTER_PASSWORD='somepasswd', MASTER_LOG_FILE='repl-bin.000159', MASTER_LOG_POS=4;

It's possible to specify the desired MASTER_LOG_FILE but position must be 4: this is the first position in the binlog file before any event. After successful configuration it's possible to start replication:


Check for any error in MaxScale error log and then take a look at the Binlog Server replication status:


Similar to START SLAVE, the STOP SLAVE can be used to stop replication from the Master:

# mysql -h -P 5306 -u maxscale -p  MariaDB> STOP SLAVE;

The STOP/START commands only control the the connection to the master and the binlog events receiving, connected slaves or new ones are not affected by these commands.


  • if MaxScale is restarted the replication from Master will automatically start, given that master.ini is present and properly configured.
  • the initfile and filestem router options are no longer available, they are automatically set by parsing MASTER_LOG_FILE.

4 - Connect slave servers to Binlog Server

Slaves can connect to MaxScale same way they connect to a MySQL/MariaDB master however the binlog filename must be explicitly provided, some examples of CHANGE MASTER TO command:

MariaDB> CHANGE MASTER TO MASTER_HOST='$maxscale_IP', MASTER_PORT=5308, MASTER_USER='repl', MASTER_PASSWORD='somepasswd',MASTER_LOG_FILE='mysql-bin.000001'
MariaDB> CHANGE MASTER TO MASTER_HOST='$maxscale_IP', MASTER_PORT=5308, MASTER_USER='repl', MASTER_PASSWORD='somepasswd',MASTER_LOG_FILE='mysql-bin.000181', MASTER_LOG_POS=2569

The examples show MASTER_LOG_FILE and MASTER_LOG_POS

  • MASTER_LOG_FILE must be set to one of existing binlog files in MaxScale binlogdir
  • If MASTER_LOG_POS is not set with CHANGE MASTER TO it defaults to 4 (the slave server does it)
  • Latest binlog file name and pos in MaxScale binlog server could be found via maxadmin show services or from mysql client connected to MaxScale via SHOW MASTER STATUS:
-bash-4.1$ mysql -h -P 5308 -u$user -p$pass        MariaDB> show master status\G      *************************** 1. row ***************************               File: mysql-bin.000181               Position: 2569

5 - The Binlog Server is now running …

The MaxScale's binlog router module produces diagnostic output that can be viewed:

  • Running the maxadmin command and issuing a show service command will produce a considerable amount of output that will show both the master connection status and statistics and also a block for each of the slaves currently connected.
  • It's also possible to issue SHOW SLAVE STATUS, SHOW MASTER STATUS, SHOW SLAVE HOST via mysql connection to Binlog Server port
  • Some examples:

-bash-4.1$ mysql -h $maxscale_ip -P $maxscale_port -u $user -p $passwd  MariaDB> SHOW SLAVE HOSTS;  +-----------+------------+-------+-----------+--------------------------------------+  | Server_id | Host       | Port  | Master_id | Slave_UUID                           |  +-----------+------------+-------+-----------+--------------------------------------+  | 201       | SBslave1_2 | 29681 | 313       | 20019681-2222-2222-2222-222222222222 |  | 202       | SBslave2_2 | 29682 | 313       | 20019682-3333-3333-3333-333333333333 |  | 204       | SBslave4_2 | 29684 | 313       | 20019684-5555-5555-5555-555555555555 |  | 205       | SBslave5_2 | 29685 | 313       | 20019685-6666-6666-6666-666666666666 |  | 203       | SBslave3_2 | 29683 | 313       | 20019683-4444-4444-4444-444444444444 |  | 206       | SBslave6_2 | 29686 | 313       | 20029686-7777-7777-7777-777777777777 |  +-----------+------------+-------+-----------+--------------------------------------+  -bash-4.1$ maxadmin show service Replication  Service 0x1567ef0      Service:                		Replication      Router:                 		binlogrouter (0x7f4ceb96a820)      State:                  		Started      Master connection DCB:              0x15693c0      Master connection state:            Binlog Dump      Binlog directory:                   /var/binlogs      Number of master connects:          1      Number of delayed reconnects:       0      Current binlog file:                mysql-bin.000084      Current binlog position:            120  ...  Backend databases  Protocol: MySQLBackend  --+------------+-------+-----------+--------------------------------------+

6 - Change the master to replicate from

Whenever there is need to modify any master server details such as:

  • replication user/password
  • binlog name or filestem
  • name/IP and port as well

there is no need to restart MaxScale at all and such changes could be made using MySQL client connection to Binlog Server issuing administrative commands such as:


This will make administration much simpler, without downtime and, as it is similar to the one in slave servers, it could be even automated via any software that uses SQL language.

First step: stop the binlog server replication from the master.


Second step: the new/modified master configuration

  • A successful configuration change results in master.ini being updated.
  • Any error is reported in the MySQL client interface and in MaxScale log files.
  • The supported options are:
  • There are some constraints related to MASTER_LOG_FILE and MASTER_LOG_POS:
  • MASTER_LOG_FILE could be changed to next binlog in sequence with MASTER_LOG_POS=4 or to current one at current position.

    Some examples:

    (1) Current binlog file is 'mysql-bin.000003', position 88888

    MariaDB> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=8888

    This could be applied to current master_host/port or a new one.

    If there is a master server maintenance and a slave is being promoted as master it must be checked that binlog file and position are valid: in case of any error the replication stops and errors are reported via SHOW SLAVE STATUS and in error logs.

    (2) Current binlog file is 'mysql-bin.000099', position 1234


    This could be applied with current master_host/port or specifying a new one.

    It must be checked that current master or new one has the new binlog file, in case of any error the replication stops and errors are reported via SHOW SLAVE STATUS and in error logs.

    Final step: start replication and check status


    Check for any error in log files and with:


    In some situations replication state could be STOPPED and proper messages are displayed in error logs and in SHOW SLAVE STATUS,

    7 - Replication reset (configuration only)

    In order to resolve any mistake done with CHANGE MASTER TO MASTER_LOG_FILE / MASTER_LOG_POS, another administrative command is helpful:


    This command, issued via MySQL client connection to Binlog Server:

    • removes the master.ini file
    • blanks all master configuration in memory
    • sets binlog router in unconfigured state: a CHANGE MASTER TO command should be issued for the new configuration.

    Note: existing binlog files are not touched by this command.

    Additional information on Binlog Server

    Binlog Server compatibility

    Binlog Router Plugin is compatible with MySQL 5.7, MySQL 5.6, MariaDB 5.5, the current default.

    In order to use it with MySQL 5.6/7, the GTID_MODE setting must be OFF in the Master server and all connecting slaves mustn't use MASTER_AUTO_POSITION = 1 option.

    It's also works with a MariaDB 10.0/1setup (master and slaves) but slave connection must not include any GTID feature.

    For MariaDB10 slaves: CHANGE MASTER TO master_use_gtid = no

    On the Master side, given that MariaDB 10 has different slave registration phase, an option is required to replicate from a MariaDB 10 Master:

    router_options=...., mariadb10-compatibility=1

    The version_string parameter could also be modified in order to present MariaDB 10 version when MaxScale sends server handshake packet to clients.


    Note: Binlog Router currently does not work for MySQL 5.5 due to missing @@global.binlog_checksum var.

    Informations sent to slaves that need to be aware of the proxy layer

    The new options: master_uuid, master_version, master_hostname can bet set in order to send some details to connected slaves.

    These options define the so called Binlog Server Identity which can provide a common identity to all the slaves connected to the Binlog Server proxy layer: a follow up blog post will show all details.

    New features

    A new router option, send_slave_heartbeat, allows to set whether (on | off) MaxScale should send to the slave the heartbeat packet when there are binlog events to send: default value if 'off', and no heartbeat event is sent to slave server.

    If value is 'on' the interval value (requested by the slave during registration) is reported in the diagnostic output and the packet is send after the time interval.



    About the Author

    massimiliano_pinto_g's picture

    Massimiliano is a Senior Software Solutions Engineer working mainly on MaxScale. Massimiliano has worked for almost 15 years in Web Companies playing the roles of Technical Leader and Software Engineer. Prior to joining MariaDB he worked at Banzai Group and Matrix S.p.A, big players in the Italy Web Industry. He is still a guy who likes too much the terminal window on his Mac. Apache modules and PHP extensions skills are included as well.


    Shared via my feedly reader

    Sent from my iPhone