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.
mydbops.wordpress.com 
One thought on “MySQL Binlog server with Maxscale”