December 4, 2017

MariaDB MaxScale Setup with Binlog Server and SQL Query Routing

Binlog server is a MariaDB MaxScale replication proxy setup which involves one Master server and several slave servers using MariaDB replication protocol.

Up to MariaDB MaxScale version 2.1, due to the lack of some SQL variables needed by the monitor for MariaDB instances, it’s not possible to use it in conjunction with SQL routing operations, such as Read/Write split routing.

With MariaDB MaxScale 2.2 (currently in beta) this is no longer a limitation as the monitor can detect a Binlog server setup and SQL statements can be properly routed among Master and Slave servers.

Depending on the configuration value of the optional variable “master_id”, the binlog server can be seen as a ‘Relay Master’ with its own slaves or just a ‘Running’ server, without its slaves being listed.

MariaDB MaxScale configuration:

# binlog server details
[binlog_server]
type=server
address=127.0.0.1
port=8808
protocol=MySQLBackend

# Mysql monitor
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,...,binlog_server
user=mon_user
passwd=some_pass
monitor_interval=10000
detect_replication_lag=true

# R/W split service
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,...

# Binlog server configuration
[Replication_Service]
type=service
router=binlogrouter
version_string=10.1.17-log
router_options=server_id=93

# Binlog server listener
[BinlogServer_Listener]
type=listener
service=Replication_Service
protocol=MySQLClient
port=8808
address=0.0.0.0


Note: the ‘binlog_server’ is not needed in the server list of R/W split service; if set it doesn’t harm MariaDB MaxScale as it doesn’t have Slave or Master states.

Binlog Server identity post reminds which parameters affect the way MaxScale is seen from Slave servers and MaxScale monitor.


Scenario A: only server_id is given in configuration.

MySQL [(none)]> select @@server_id; // The server_id of master, query from slaves.
+-------------+
| @@server_id |
+-------------+
|       10124 |
+-------------+

MySQL [(none)]> select @@server_id, @@read_only; // Maxscale server_id, query from MySQL monitor only.

+-------------+-------------+
| @@server_id | @@read_only |
+-------------+-------------+
|          93 |           0 |
+-------------+-------------+

MySQL [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
              Slave_IO_State: Binlog Dump
                 Master_Host: 192.168.100.11  // Master server IP
                 Master_User: repo
                 Master_Port: 3306
                 ...
            Master_Server_Id: 10124 // Master Server_ID
                 ...

MaxAdmin> show servers
Server 0x1f353b0 (server1)
    Server:                              127.0.0.1
    Status:                              Slave, Running
    Protocol:                            MySQLBackend
    Port:                                25231
    Server Version:                      10.0.21-MariaDB-log
    Node Id:                             101
    Master Id:                           10124
    Slave Ids:                           
    Repl Depth:                          1
    ...

Server 0x1f31af0 (server2)
    Server:                              192.168.122.1
    Status:                              Master, Running
    Protocol:                            MySQLBackend
    Port:                                10124
    Server Version:                      10.1.24-MariaDB
    Node Id:                             10124
    Master Id:                           -1
    Slave Ids:                           101, 93
    Repl Depth:                          0
    ...

Server 0x1f32d90 (binlog_server)
    Server:                              127.0.0.1
    Status:                              Running
    Protocol:                            MySQLBackend
    Port:                                8808
    Server Version:                      10.1.17-log
    Node Id:                             93
    Master Id:                           10124
    Slave Ids:                           
    Repl Depth:                          1
    ...

Scenario B: server_id and common_identity (master_id)

[BinlogServer]
type=service
router=binlogrouter
version_string=10.1.17-log
router_options=server-id=93, master_id=1111

MySQL [(none)]> select @@server_id; // Maxscale common identity
+-------------+
| @@server_id |
+-------------+
|        1111 |
+-------------+
1 row in set (0.00 sec)

MySQL [(none)]> select @@server_id, @@read_only; // Maxscale common identity
+-------------+-------------+
| @@server_id | @@read_only |
+-------------+-------------+
|        1111 |           0 |
+-------------+-------------+
1 row in set (0.00 sec)

MySQL [(none)]> show slave status\G
*************************** 1. row ***************************
              Slave_IO_State: Binlog Dump
                 Master_Host: 192.168.100.11  // Master server IP
                 Master_User: repl
                 Master_Port: 3306
                 ...
            Master_Server_Id: 10124 // Master Server_ID
                 ...

MaxAdmin> show servers
Server 0x24103b0 (server1)
    Server:                              127.0.0.1
    Status:                              Slave, Running
    Protocol:                            MySQLBackend
    Port:                                25231
    Server Version:                      10.0.21-MariaDB-log
    Node Id:                             101
    Master Id:                           1111
    Slave Ids:                           
    Repl Depth:                          2
    ...
Server 0x240dd90 (binlog_server)
    Server:                              127.0.0.1
    Status:                              Relay Master, Running
    Protocol:                            MySQLBackend
    Port:                                8808
    Server Version:                      10.1.17-log
    Node Id:                             1111
    Master Id:                           10124
    Slave Ids:                           101
    Repl Depth:                          1
    ...

Server 0x240caf0 (server2)
    Server:                              192.168.122.1
    Status:                              Master, Running
    Protocol:                            MySQLBackend
    Port:                                10124
    Server Version:                      10.1.24-MariaDB
    Node Id:                             10124
    Master Id:                           -1
    Slave Ids:                           1111
    Repl Depth:                          0
    ...

The latter configuration with the extra master_id option is clearly then one which well represents the setup with Binlog server as a replication proxy: the user can immediately see that.

The picture shows the setup and makes it clear MariaDB MaxScale handles both replication protocol between Master and Slaves and also routes Read and Write application traffic.

complete_setup.jpg

Conclusion

This post shows how it's easy for any user to improve a MariaDB replication setup with MariaDB MaxScale combining benefits of replication proxy and query routing scalability.

MariaDB MaxScale 2.2 is in beta and we do not recommend using it in production environments. However, we do encourage you to download, test it and share your successes!

 

Additional Resources

About Massimiliano Pinto

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.

Read all posts by Massimiliano Pinto