Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

MariaDB MaxScale Setup With Binlog Server and SQL Query Routing

DZone's Guide to

MariaDB MaxScale Setup With Binlog Server and SQL Query Routing

See how to improve a MariaDB replication setup with MariaDB MaxScale, combining benefits of replication proxy and query routing scalability.

· Database Zone ·
Free Resource

MariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

Binlog server is a MariaDB MaxScale replication proxy setup that 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.

This 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 more than 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

MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

Topics:
mariadb ,binlog ,sql query ,database ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}