MySQL Binlog server with Maxscale

Nov 2, 2019

Mins to Read

All

Master servers under heavy replication load can become bottlenecks. This blog explores how Maxscale's binlog server functionality can alleviate this issue. By establishing Maxscale as a dedicated binlog server, you can significantly reduce the load on your master server and streamline replication processes.

This is the our next blog in Maxscale series, Below is a list of our previous blogs, which provides deep insight for Maxscale and its use cases for different architectures.

Scale with Maxscale part – 1 (Intro & Galera Cluster)
Scale with Maxscale part – 2 (Administration, Installation & operation)
Scale with Maxscale part – 3 (Replication M-S)
Scale with Maxscale part – 4 (Amazon Aurora)
Scale with Maxscale part – 5 (Multi-Master)
Other Maxscale blogs (Column masking, Load balancer Comparison)

In this blog, we are going to discuss about Maxscale binlog server and the configuration.

In general, MySQL replication setup can be a single master server and with a set of slaves servers are configured to pull the binlog files from the master. It will create a lot of load on the master while all the slaves are trying to access the binlog files.

Generic Master Slave Replication:

Introducing a replication 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.

Master Slave replication with binlog server:

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.

Binlog server has a number of advantages:

Reduces load on master caused by Binlog reads..
Ease the master failover and re-align replicas to another master.Because the Binlog server abstracts the dependency between master and replica.
Avoids differential lag on slaves when master goes down.
Better binary logs retentions

Installation of Maxscale Binlog server:

In this example we have used Debian 9 OS.

Using Mariadb Repo:

 
[root@binlogserver ~]# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
[root@binlogserver ~]# apt install maxscale-2.1.17

Direct installation:

 
[root@binlogserver ~]# wget https://dlm.mariadb.com/501/MaxScale/2.1.17/debian/dists/stretch/main/binary-amd64/maxscale-2.1.17-1.debian.stretch.x86_64.deb
[root@binlogserver ~]# dpkg -i maxscale-2.1.17-1.debian.stretch.x86_64.deb

Creating Maxscale user for getting the binary logs from the master server.

 
(master) MySQL [(none)]> CREATE USER 'maxscale'@'%' IDENTIFIED BY 'yPE#w73Bb8';
(master) MySQL [(none)]> GRANT SELECT ON mysql.user TO 'maxscale'@'%';
(master) MySQL [(none)]> GRANT SELECT ON mysql.db TO 'maxscale'@'%';
(master) MySQL [(none)]> GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
(master) MySQL [(none)]> GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
(master) MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'maxscale'@'%';

After creating user in the master DB we need to add these details in maxscale configuration file.

/etc/maxscale.cnf file looks like:

 
[maxscale]
threads=6

[Binlog_Service]
type=service
router=binlogrouter
servers=master
user=maxscale
passwd=yPE#w73Bb8
version_string=5.7.27-30
router_options=server-id=172321110,user=maxscale,password=yPE#w73Bb8,mariadb10-compatibility=0,filestem=mysql-172321110-binary,binlogdir=/var/lib/maxscale/binlogs

[master]
type=server
address=172.32.11.10
port=3306
protocol=MySQLBackend

[Binlog Listener]
type=listener
service=Binlog_Service
protocol=MySQLClient
port=3306

[Debug Service]
type=service
router=debugcli

[Debug Listener]
type=listener
service=Debug Service
protocol=telnetd
address=localhost
port=4442

[CLI Service]
type=service
router=cli

[CLI Listener]
type=listener
service=CLI Service
protocol=maxscaled
address=localhost
socket=default

Key parameters for binlog configuration:

 
[Binlog_Service]
type=service
router=binlogrouter
servers=master
user=maxscale
passwd=yPE#w73Bb8
version_string=5.7.27-30
router_options=server-id=172321110,user=maxscale,password=yPE#w73Bb8,mariadb10-compatibility=0,filestem=mysql-172321110-binary,binlogdir=/var/lib/maxscale/binlogs

[Binlog Listener]
type=listener
service=Binlog_Service
protocol=MySQLClient
port=3306

 
version_string: = 5.7.27-30

This parameter sets a custom version string that is sent in the MySQL Handshake from Maxscale to other MySQL clients.

router_options:

 
server-id: = 172321110

Master’s server id.

 
user=maxscale
password=yPE#w73Bb8

For connecting the master server using this credentials to fetch the binary logs

 
mariadb10-compatibility=0

For ignoring same GTID format as in MariaDB 10.0 or later.

 
filesytem=mysql-172321110-binary

The prefix of the binary logs from the master server.

 
binlogdir=/var/lib/maxscale/binlogs

Path for saving the binary logs in the binlog server.

Everything looks good at configuration level, Let’s start Maxscale service

 
[root@binlogserver ~]# service maxscale start

Command to connect maxscale’s MySQL terminal

 
[root@binlogserver ~]# mysql -h127.0.0.1 -umaxscale -pyPE#w73Bb8 --port=3306

To start fetching the binary logs from the master server we need to run the CHANGE MASTER command like below:

 
(binlogserver) MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST = '172.32.11.10', MASTER_PORT = 3306, MASTER_USER = maxscale, MASTER_PASSWORD = 'yPE#w73Bb8', MASTER_LOG_FILE = 'mysql-172321110-binary.002207', MASTER_LOG_POS = 4;

(binlogserver) MariaDB [(none)]> start slave;
(binlogserver) MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Binlog Dump
                  Master_Host: 172.32.11.10
                  Master_User: maxscale
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-172321110-binary.002222
          Read_Master_Log_Pos: 385867709
               Relay_Log_File: mysql-172321110-binary.002222
                Relay_Log_Pos: 385867709
        Relay_Master_Log_File: mysql-172321110-binary.002222
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 385867709
              Relay_Log_Space: 385867709
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
             Master_Server_Id: 172321110
                  Master_UUID: 12fe70e7-b52b-11e9-8039-b8599f33fbbe
             Master_Info_File: /var/lib/maxscale/binlogs/master.ini
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave running
           Master_Retry_Count: 1000

Let us check the binlog directory:

 
[root@binlogserver ~]# ls -lrth /var/lib/maxscale/binlogs/
-rw------- 1 maxscale maxscale  147 Oct 8 18:16 master.ini
drwx------ 2 maxscale maxscale  224 Oct 8 18:16 cache
-rw-r--r-- 1 maxscale maxscale 1.1G Oct  8 18:18 mysql-172321110-binary.002207
-rw-r--r-- 1 maxscale maxscale 1.1G Oct  8 18:39 mysql-172321110-binary.002208
-rw-r--r-- 1 maxscale maxscale 1.1G Oct  8 19:02 mysql-172321110-binary.002209
-rw-r--r-- 1 maxscale maxscale 1.1G Oct  8 19:27 mysql-172321110-binary.002210
-rw-r--r-- 1 maxscale maxscale 1.1G Oct  8 19:50 mysql-172321110-binary.002211
-rw-r--r-- 1 maxscale maxscale 212M Oct  8 19:55 mysql-172321110-binary.002212

We can get some good information from show services command from maxadmin terminal.

 
MaxScale> show services
Service:                             Binlog_Service
Router:                              binlogrouter
State:                               Started
Master connection DCB:               0x55dd3b832560
Master connection state:                     Binlog Dump
Binlog directory:                            /var/lib/maxscale/binlogs
Heartbeat period (seconds):                  300
Number of master connects:                   1
Number of delayed reconnects:                0
Current binlog file:                         mysql-172311110-binary.002209
Current binlog position:                     716927578
Number of slave servers:                     1
No. of binlog events received this session:  74032736
Total no. of binlog events received:         74032736
No. of bad CRC received from master:         0
Number of binlog events per minute
Current        5        10       15       30 Min Avg
630664  514283.8 492402.4 473213.8 459310.4
Number of fake binlog events:                1
Number of artificial binlog events:          8
Number of binlog events in error:            0
Number of binlog rotate events:              7
Number of heartbeat events:                  0
Number of packets received:                  2115352
Number of residual data packets:             0

Adding replicas under the binlog server:

The steps to add the replicas under the binlog server is same as getting the server under the master server. Only the master IP needs to be changed as binlog server.

We can take mysqldump / percona xtrabackup from the master server for creating replicas. Replace the binlog server IP instead of master server IP while configuring the replication.

Limitations:

Binlogs won’t get auto purged by maxscale server. We need to have a cron job to purge it from the binlog dir.
It won’t support MySQL GTID based replication.
Binlog Router is compatible with MySQL 5.6+, 5.7+ and MariaDB 5.5, the current default.
It’s also works with a MariaDB 10.0 setup (master and slaves) but slave connection must not include any GTID feature.
There is no support for MySQL 5.5 due to missing binlog_checksum variable.

Ready to optimize your MySQL replication setup with Maxscale? Explore Mydbops' Maxscale services or contact us for expert guidance! Contact Mydbops Today !

No items found.