Skip to content

Latest commit

 

History

History
566 lines (455 loc) · 23.8 KB

Replication-Proxy-Binlog-Router-Tutorial.md

File metadata and controls

566 lines (455 loc) · 23.8 KB

MariaDB MaxScale as a Binlog Server

Table of Contents

Introduction

MariaDB MaxScale is a dynamic data routing platform that sits between a database layer and the clients of that database. However, the binlog router described here is somewhat different to that original concept, moving MariaDB MaxScale down to play a role within the database layer itself.

In a traditional MariaDB replication setup a single master server is created and a set of slaves MariaDB instances are configured to pull the binlog files from that master to the slaves. There are some problems, however, in this setup; when the number of slaves grows, an increasing load caused by the serving of binlogs to each slave, is placed on the master. When the master server fails, some action must be performed on every slave server before a new server can become the master server.

Introducing a proxy layer between the master server and the slave servers can improve the situation, by reducing the load on the master to simply serving the proxy layer rather than all of the slaves. The slaves only need to be aware of the proxy layer and not of the real master server. Removing the need for the slaves to have knowledge of the actual master, greatly simplifies the process of replacing a failed master within a replication environment.

MariaDB as a Binlog Server

The most obvious solution to the requirement for a proxy layer within a replication environment is to use a MariaDB or MySQL database instance. The database server is designed to allow this, since a slave server is able to be configured such that it will produce binary logs for updates it has itself received via replication from the master server. This is done with the log_slave_updates configuration option of the server. In this case the server is known as an intermediate master, it is simultaneously a slave to the real master and a master to the other slaves in the configuration.

Using an intermediate master does not, however, solve all the problems and introduces some new ones, due to the way replication is implemented. A slave server reads the binary log data and creates a relay log from that binary log. This log provides a source of SQL statements, which are executed within the slave in order to make the same changes to the databases on the slaves as were made on the master. If the log_slave_updates option has been enabled, new binary log entries are created for the statements executed from the relay log.

The above means that the data in the binary log of the intermediate master is not a direct copy of the data that was received from the binary log of the real master. The resultant changes to the database will be the same, provided no updates have been performed on the intermediate master that did not originate on the real master, but the steps to achieve those changes may be different. In particular, if group commit functionality is used, to allow multiple transactions to commit in parallel, these may well be different on the intermediate master. This can cause a reduction in the parallelism of the commits and a subsequent reduction in the performance of the slave servers.

This re-execution of the SQL statements also adds latency to the intermediate master solution, since the full process of parsing, optimization and execution must occur for every statement that is replicated from the master to the slaves must be performed in the intermediate master. This latency introduces lag in the replication chain, with a greater delay being introduced from the time a transaction is committed on the master until the data is available on the slaves.

Use of an intermediate master does improve the process of failover of the master server, since the slaves are only aware of the intermediate master the process of promoting one of the existing slaves to become the new master only involves that slave and the intermediate master. A slave can become the new master as soon as all the changes from the intermediate master have been processed. The intermediate master then needs to be reset to the correct point in the binary log of the new master and replication can continue.

An added complexity that needs to be dealt with is the failure of the intermediate master itself. If this occurs then the same problem as described earlier exists, all slaves must be updated when a new intermediate master is created. If multiple intermediate masters are used, there is also a restriction that slaves can not be moved from the failed intermediate master to another intermediate master due to the fact that the binlog on the different intermediate nodes are not guaranteed to be the same.

MariaDB MaxScale's approach

MariaDB MaxScale takes a much simpler approach to the process of being a Binlog Server. It acts as a slave to the real master and as a master to the slaves, in the same way as an intermediate master does. However, it does not implement any re-execution of the statements within the binary log. MariaDB MaxScale creates a local cache of the binary logs it receives from the master and will serve binary log events to the slaves from this cache of the master's binary log. This means that the slaves will always get binary log events that have a one-to-one correlation to those written by the master. Parallelism in the binary log events of the master is maintained in the events that are observed by the slaves.

In the MariaDB MaxScale approach, the latency that is introduced is mostly the added network latency associated with adding the extra network hop. There is no appreciable processing performed at the MariaDB MaxScale level, other than for managing the local cache of the binlog files.

In addition, every MariaDB 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 MariaDB MaxScale server or to the real master without a need to perform any special processing. The result is much simpler behavior for failure recovery and the ability to have a very simple, redundant proxy layer with slaves free to both between the proxies.

Configuring MariaDB MaxScale as a Binlog Server

Using MariaDB MaxScale as a Binlog Server is much the same as using MariaDB 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 MariaDB MaxScale.

Service Configuration

As with any MariaDB 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 proxies this router name is binlogrouter.

The minimum set of router options that must be given in the configuration are are server_id and binlogdir, default values may be used for all other options.

All configuration prameters can be found in the Binlog Router Documentation.

A minimal example of a service entry for a binlog router service that is used with MariaDB 10 would be as follows.

[Replication]
type=service
router=binlogrouter
user=maxscale
password=maxpwd
server_id=1
mariadb10-compatibility=1
binlogdir=/var/lib/maxscale/

Listener Configuration

As per any service in MariaDB MaxScale, a listener section is required to define the address, port and protocol that is used to listen for incoming connections. Those incoming connections will originate from either slave servers or from a MySQL client. The binlogrouter is administered and configured via SQL commands on the listener.

[Replication-Listener]
type=listener
service=Replication
protocol=MariaDBClient
port=3306

The protocol used by slaves for connection to MariaDB MaxScale is the same MariaDBClient protocol that is used for client applications to connect to databases, therefore the same MariaDB MaxScale protocol module can be used.

It's also possible to enable client side SSL by adding the required SSL options in the listener:

[Replication-SSL-Listener]
type=listener
service=Replication
protocol=MariaDBClient
port=3306
ssl=required
ssl_key=/path/to/key.pem
ssl_cert=/path/to/cert.pem
ssl_ca_cert=/path/to/ca-cert.pem

Refer to the Configuration-Guide for more details about the SSL configuration in MaxScale.

Configuring Replication

When the binlogrouter is started for the first time, it needs to be configured to replicate from a master. To do this, connect to the binlogrouter listener that was defined before and execute a normal CHANGE MASTER TO command. Use the credentials defined in maxscale.cnf when you connect to MaxScale. Finally, execute a START SLAVE command to start the replication.

Here is an example SQL command that configures the binlogrouter to replicate from a MariaDB server and starts replication:

CHANGE MASTER TO MASTER_HOST='master.example.com',
                 MASTER_PORT=3306,
                 MASTER_USER='maxuser',
                 MASTER_PASSWORD='maxpwd',
                 MASTER_LOG_FILE='mysql-bin.000001',
                 MASTER_LOG_POS=4;

START SLAVE;

Both the MASTER_LOG_FILE and MASTER_LOG_POS must be defined and the value of MASTER_LOG_POS must be 4.

Note: Legacy versions defined the server by configuring a separate server object in maxscale.cnf.

Stopping and Starting the Replication

When router is configured and it is properly working it is possible to stop the replication with STOP SLAVE and to resume it with START SLAVE. In addition to this, the SHOW SLAVE STATUS command can be used to display information about the replication configuration.

Slave connections are not affected by the STOP SLAVE and START SLAVE commands. They only control the connection to the master server.

Change the Master server configuration

When router is configured and it is properly working it is possible to change the master parameters. First step is stop the replication from the master.

STOP SLAVE;

Next step is the master configuration

CHANGE MASTER TO ...

A successful configuration change results in master.ini being updated. Any error is reported in the MySQL and in log files.

The supported CHAGE MASTER TO options are:

  • MASTER_HOST
  • MASTER_PORT
  • MASTER_USER
  • MASTER_PASSWORD
  • MASTER_LOG_FILE
  • MASTER_LOG_POS
  • MASTER_SSL
  • MASTER_SSL_CERT (path to certificate file)
  • MASTER_SSL_KEY (path to key file)
  • MASTER_SSL_CA (path to CA cerificate file)
  • MASTER_TLS_VERSION (TLS/SSL version)

Further details about level of encryption or certificates could be found in the Configuration Guide

Slave servers setup

Examples of CHANGE MASTER TO command issued on a slave server that wants to gets replication events from MariaDB MaxScale binlog router:

CHANGE MASTER TO MASTER_HOST=‘$maxscale_IP’, MASTER_PORT=5308, MASTER_USER='repl', MASTER_PASSWORD=‘somepasswd’,
MASTER_LOG_FILE=‘mysql-bin.000001'

CHANGE MASTER TO MASTER_HOST=‘$maxscale_IP’, MASTER_PORT=5308, MASTER_USER='repl', MASTER_PASSWORD=‘somepasswd’,
MASTER_LOG_FILE=‘mysql-bin.000159', MASTER_LOG_POS=245

The latter example specifies a MASTER_LOG_POS for the selected MASTER_LOG_FILE

Note:

  • MASTER_LOG_FILE must be set to one of existing binlog files in MariaDB MaxScale binlogdir

  • If MASTER_LOG_POS is not set with CHANGE MASTER TO it defaults to 4

  • Latest binlog file name and pos in MariaDB MaxScale can be found by executing SHOW MASTER STATUS on MaxScale.

Controlling the Binlogrouter

There are some constraints related to MASTER_LOG_FILE and MASTER_LOG_POS. MASTER_LOG_FILE can be changed to next binlog in sequence with MASTER_LOG_POS=4 or to current one at current position.

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 should be checked that binlog file and position are valid: in case of any error replication stops and errors are reported via SHOW SLAVE STATUS and in error logs.

  1. Current binlog file is ‘mysql-bin.000099', position 1234

    MariaDB> CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000100',MASTER_LOG_POS=4

This could be applied with current master_host/port or a new one If transaction safety option is on and the current binlog file contains an incomplete transaction it will be truncated to the position where transaction started. In such situation a proper message is reported in MySQL connection and with next START SLAVE binlog file truncation will occur and MariaDB MaxScale will request events from the master using the next binlog file at position 4.

The above scenario might refer to a master crash/failure: the new server that has just been promoted as master doesn't have last transaction events but it should have the new binlog file (the next in sequence). Truncating the previous MariaDB MaxScale binlog is safe as that incomplete transaction is lost. It should be checked that current master or new one has the new binlog file, in case of any error replication stops and errors are reported via SHOW SLAVE STATUS and in error logs.

MariaDB> START SLAVE;

Check for any error in log files with:

MariaDB> SHOW SLAVE STATUS;

In some situations replication state could be STOPPED and proper messages are displayed in error logs and in SHOW SLAVE STATUS. In order to resolve any mistake done with CHANGE MASTER TO MASTER_LOG_FILE / MASTER_LOG_POS, another administrative command can be helpful.

MariaDB> RESET SLAVE;

This command removes master.ini file, blanks all master configuration in memory and 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.

Examples with SSL options:

MySQL [(none)]> CHANGE MASTER TO MASTER_SSL = 1, MASTER_SSL_CERT='/home/maxscale/packages/certificates/client/client-cert.pem', MASTER_SSL_CA='/home/maxscale/packages/certificates/client/ca.pem', MASTER_SSL_KEY='/home/maxscale/packages/certificates/client/client-key.pem', MASTER_TLS_VERSION='TLSv12';

MySQL [(none)]> CHANGE MASTER TO MASTER_TLS_VERSION='TLSv12';

MySQL [(none)]> CHANGE MASTER TO MASTER_SSL = 0;

SSL Limitations

  • In order to enable/re-enable Master SSL comunication the MASTER_SSL=1 option is required and all certificate options must be explicitey set in the same CHANGE MASTER TO command.

  • New certificate options changes take effect after maxScale restart or after MASTER_SSL=1 with the new options.

  • SHOW SLAVE STATUS displays all the options but MASTER_TLS_VERSION value.

  • Maxadmin, 'show services' or 'show service $binlog_service' displays all the options when SSL is on.

  • STOP SLAVE is required for CHANGE MASTER TO command (any option)

  • START SLAVE will use new SSL options for Master SSL communication setup.

Binlog Router Compatibility

Binlog Router Plugin is compatible with MariaDB 5.5, 10.0, 10.1 and 10.2 as well as MySQL 5.6 and 5.7.

Note: When using MariaDB 10.2 or MySQL 5.7 the send_slave_heartbeat option must be set to On as the slave servers request the hearbeat to MaxScale. As an alternative use CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD=0 in the slave server in order to disable the heartbeat request.

Enabling MariaDB 10 compatibility

MariaDB 10 has different slave registration phase so an extra option is required:

mariadb10-compatibility=1

version_string can be modified in order to present MariaDB 10 version when MariaDB MaxScale sends server handshake packet.

version_string=10.0.17-log

MySQL Limitations

In order to use it with MySQL 5.6/5.7, the GTID_MODE setting must be OFF and connecting slaves must not use MASTER_AUTO_POSITION = 1 option. Additionally with MySQL 5.7 slaves the send_slave_heartbeat option must be set to on.

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

MariaDB Limitations

Starting from version 10.2 there are new replication events related to binlog event compression: these new events are not supported yet. Be sure that log_bin_compress is not set in any MariaDB 10.2 server.

MariaDB MaxScale Replication Diagnostics

The binlog router module of MariaDB MaxScale produces diagnostic output that can be viewed via the maxadmin client application. Running the maxadmin command and issuing a show service command will produce output that will show both the master connection status and statistics and also a block for each of the slaves currently connected.

-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/maxscale/binlogs
        Heartbeat period (seconds):                 200
        Number of master connects:                  1
        Number of delayed reconnects:               0
        Current binlog file:                        mybin.000061
        Current binlog position:                    120
        Number of slave servers:                    0
        No. of binlog events received this session: 1002705
        Total no. of binlog events received:        2005410
        No. of bad CRC received from master:        0
        Number of binlog events per minute
        Current        5        10       15       30 Min Avg
              4       4.0      4.0      4.0      4.0
        Number of fake binlog events:           0
        Number of artificial binlog events:     61
        Number of binlog events in error:       0
        Number of binlog rotate events:         60
        Number of heartbeat events:             69
        Number of packets received:             599
        Number of residual data packets:        379
        Average events per packet               3347.9
        Last event from master at:              Thu Jan 29 16:41:53 2015 (10 seconds ago)
        Last event from master:                 0x1b (Heartbeat Event)
        Events received:
            Invalid                                  0
            Start Event V3                           0
            Query Event                              703307
            Stop Event                               55
            Rotate Event                             65
            Integer Session Variable                 0
            Load Event                               0
            Slave Event                              0
            Create File Event                        0
            Append Block Event                       0
            Exec Load Event                          0
            Delete File Event                        0
            New Load Event                           0
            Rand Event                               0
            User Variable Event                      0
            Format Description Event                 61
            Transaction ID Event (2 Phase Commit)    299148
            Begin Load Query Event                   0
            Execute Load Query Event                 0
            Table Map Event                          0
            Write Rows Event (v0)                    0
            Update Rows Event (v0)                   0
            Delete Rows Event (v0)                   0
            Write Rows Event (v1)                    0
            Update Rows Event (v1)                   0
            Delete Rows Event (v1)                   0
            Incident Event                           0
            Heartbeat Event                          69
            Ignorable Event                          0
            Rows Query Event                         0
            Write Rows Event (v2)                    0
            Update Rows Event (v2)                   0
            Delete Rows Event (v2)                   0
            GTID Event                               0
            Anonymous GTID Event                     0
            Previous GTIDS Event                     0
        Started:                Thu Jan 29 16:06:11 2015
        Root user access:           Disabled
        Backend databases
            178.62.50.70:3306  Protocol: MariaDBBackend
        Users data:                     0x156c030
        Total connections:              2
        Currently connected:            2

If a slave is connected to MaxScale with SSL, an entry will be present in the Slave report:

    Slaves:
        Server-id:                               106
        Hostname:                                SBslave6
        Slave UUID:                              00019686-7777-7777-7777-777777777777
        Slave_host_port:                         188.165.213.5:40365
        Username:                                massi
        Slave DCB:                               0x7fc01be3ba88
        Slave connected with SSL:                Established

The SHOW SLAVE STATUS command provides diagnostic information about the replication state.

MySQL [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Binlog Dump
                  Master_Host: 88.26.197.94
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.003140
          Read_Master_Log_Pos: 16682679
               Relay_Log_File: mysql-bin.003140
                Relay_Log_Pos: 16682679
        Relay_Master_Log_File: mysql-bin.003140
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 16682679
              Relay_Log_Space: 16682679
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /home/maxscale/packages/certificates/client/ca.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /home/maxscale/packages/certificates/client/client-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /home/maxscale/packages/certificates/client/client-key.pem
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1111
                  Master_UUID: 6aae714e-b975-11e3-bc33-0401152c3d01
             Master_Info_File: /home/maxscale/binlog/first/binlogs/master.ini

MariaDB 10 masters display some extra events.

MariaDB 10 Annotate Rows Event 0
MariaDB 10 Binlog Checkpoint Event 0
MariaDB 10 GTID Event 0
MariaDB 10 GTID List Event 0