blog
How to Replace an Intermediate MySQL or MariaDB Master with a Binlog Server using MaxScale
Binary logs (binlogs) contain records of all changes to the databases. They are necessary for replication and can also be used to restore data after a backup. A binlog server is basically a binary log repository. You can think of it like a server with a dedicated purpose to retrieve binary logs from a master, while slave servers can connect to it like they would connect to a master server.
Some advantages of having a binlog server over intermediate master to distribute replication workload are:
- You can switch to a new master server without the slaves noticing that the actual master server has changed. This allows for a more highly available replication setup where replication is high-priority.
- Reduce the load on the master by only serving Maxscale’s binlog server instead of all the slaves.
- 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. As such, if group commit is used, this can cause a reduction in the parallelism of the commits and a subsequent reduction in the performance of the slave servers.
- Intermediate slave has to re-execute every SQL statement which potentially adds latency and lags into the replication chain.
In this blog post, we are going to look into how to replace an intermediate master (a slave host relay to other slaves in a replication chain) with a binlog server running on MaxScale for better scalability and performance.
Architecture
We basically have a 4-node MariaDB v10.4 replication setup with one MaxScale v2.3 sitting on top of the replication to distribute incoming queries. Only one slave is connected to a master (intermediate master) and the other slaves replicate from the intermediate master to serve read workloads, as illustrated in the following diagram.
We are going to turn the above topology into this:
Basically, we are going to remove the intermediate master role and replace it with a binlog server running on MaxScale. The intermediate master will be converted to a standard slave, just like other slave hosts. The binlog service will be listening on port 5306 on the MaxScale host. This is the port that all slaves will be connecting to for replication later on.
Configuring MaxScale as a Binlog Server
In this example, we already have a MaxScale sitting on top of our replication cluster acting as a load balancer for our applications. If you don’t have a MaxScale, you can use ClusterControl to deploy simply go to Cluster Actions -> Add Load Balancer -> MaxScale and fill up the necessary information as the following:
Before we get started, let’s export the current MaxScale configuration into a text file for backup. MaxScale has a flag called –export-config for this purpose but it must be executed as maxscale user. Thus, the command to export is:
$ su -s /bin/bash -c '/bin/maxscale --export-config=/tmp/maxscale.cnf' maxscaleOn the MariaDB master, create a replication slave user called ‘maxscale_slave’ to be used by the MaxScale and assign it with the following privileges:
$ mysql -uroot -p -h192.168.0.91 -P3306
MariaDB> CREATE USER 'maxscale_slave'@'%' IDENTIFIED BY 'BtF2d2Kc8H';
MariaDB> GRANT SELECT ON mysql.user TO 'maxscale_slave'@'%';
MariaDB> GRANT SELECT ON mysql.db TO 'maxscale_slave'@'%';
MariaDB> GRANT SELECT ON mysql.tables_priv TO 'maxscale_slave'@'%';
MariaDB> GRANT SELECT ON mysql.roles_mapping TO 'maxscale_slave'@'%';
MariaDB> GRANT SHOW DATABASES ON *.* TO 'maxscale_slave'@'%';
MariaDB> GRANT REPLICATION SLAVE ON *.* TO 'maxscale_slave'@'%';For ClusterControl users, go to Manage -> Schemas and Users to create the necessary privileges.
Before we move further with the configuration, it’s important to review the current state and topology of our backend servers:
$ maxctrl list servers
┌────────┬──────────────┬──────┬─────────────┬──────────────────────────────┬───────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │
├────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼───────────┤
│ DB_757 │ 192.168.0.90 │ 3306 │ 0 │ Master, Running │ 0-38001-8 │
├────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼───────────┤
│ DB_758 │ 192.168.0.91 │ 3306 │ 0 │ Relay Master, Slave, Running │ 0-38001-8 │
├────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼───────────┤
│ DB_759 │ 192.168.0.92 │ 3306 │ 0 │ Slave, Running │ 0-38001-8 │
├────────┼──────────────┼──────┼─────────────┼──────────────────────────────┼───────────┤
│ DB_760 │ 192.168.0.93 │ 3306 │ 0 │ Slave, Running │ 0-38001-8 │
└────────┴──────────────┴──────┴─────────────┴──────────────────────────────┴───────────┘As we can see, the current master is DB_757 (192.168.0.90). Take note of this information as we are going to setup the binlog server to replicate from this master.
Open the MaxScale configuration file at /etc/maxscale.cnf and add the following lines:
[replication-service]
type=service
router=binlogrouter
user=maxscale_slave
password=BtF2d2Kc8H
version_string=10.4.12-MariaDB-log
server_id=9999
master_id=9999
mariadb10_master_gtid=true
filestem=binlog
binlogdir=/var/lib/maxscale/binlogs
semisync=true # if semisync is enabled on the master
[binlog-server-listener]
type=listener
service=replication-service
protocol=MariaDBClient
port=5306
address=0.0.0.0A bit of explanation – We are creating two components – service and listener. Service is where we define the binlog server characteristic and how it should run. Details on every option can be found here. In this example, our replication servers are running with semi-sync replication, thus we have to use semisync=true so it will connect to the master via semi-sync replication method. The listener is where we map the listening port with the binlogrouter service inside MaxScale.
Restart MaxScale to load the changes:
$ systemctl restart maxscaleVerify the binlog service is started via maxctrl (look at the State column):
$ maxctrl show service replication-serviceVerify that MaxScale is now listening to a new port for the binlog service:
$ netstat -tulpn | grep maxscale
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4850/maxscale
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 4850/maxscale
tcp 0 0 0.0.0.0:5306 0.0.0.0:* LISTEN 4850/maxscale
tcp 0 0 127.0.0.1:8989 0.0.0.0:* LISTEN 4850/maxscaleWe are now ready to establish a replication link between MaxScale and the master.
Activating the Binlog Server
Log into the MariaDB master server and retrieve the current binlog file and position:
MariaDB> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000005 | 4204 | | |
+---------------+----------+--------------+------------------+Use BINLOG_GTID_POS function to get the GTID value:
MariaDB> SELECT BINLOG_GTID_POS("binlog.000005", 4204);
+----------------------------------------+
| BINLOG_GTID_POS("binlog.000005", 4204) |
+----------------------------------------+
| 0-38001-31 |
+----------------------------------------+Back to the MaxScale server, install MariaDB client package:
$ yum install -y mysql-client
Connect to the binlog server listener on port 5306 as maxscale_slave user and establish a replication link to the designated master. Use the GTID value retrieved from the master:
(maxscale)$ mysql -u maxscale_slave -p'BtF2d2Kc8H' -h127.0.0.1 -P5306
MariaDB> SET @@global.gtid_slave_pos = '0-38001-31';
MariaDB> CHANGE MASTER TO MASTER_HOST = '192.168.0.90', MASTER_USER = 'maxscale_slave', MASTER_PASSWORD = 'BtF2d2Kc8H', MASTER_PORT=3306, MASTER_USE_GTID = slave_pos;
MariaDB> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Binlog Dump
Master_Host: 192.168.0.90
Master_User: maxscale_slave
Master_Port: 3306
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Server_Id: 38001
Master_Info_File: /var/lib/maxscale/binlogs/master.ini
Slave_SQL_Running_State: Slave running
Gtid_IO_Pos: 0-38001-31Note: The above output has been truncated to show only important lines.
Pointing Slaves to the Binlog Server
Now on mariadb2 and mariadb3 (the end slaves), change the master pointing to the MaxScale binlog server. Since we are running with semi-sync replication enabled, we have to turn them off first:
(mariadb2 & mariadb3)$ mysql -uroot -p
MariaDB> STOP SLAVE;
MariaDB> SET global rpl_semi_sync_master_enabled = 0; -- if semisync is enabled
MariaDB> SET global rpl_semi_sync_slave_enabled = 0; -- if semisync is enabled
MariaDB> CHANGE MASTER TO MASTER_HOST = '192.168.0.95', MASTER_USER = 'maxscale_slave', MASTER_PASSWORD = 'BtF2d2Kc8H', MASTER_PORT=5306, MASTER_USE_GTID = slave_pos;
MariaDB> START SLAVE;
MariaDB> SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.95
Master_User: maxscale_slave
Master_Port: 5306
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Server_Id: 9999
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-38001-32
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itNote: The above output has been truncated to show only important lines.
Inside my.cnf, we have to comment the following lines to disable semi-sync in the future:
#loose_rpl_semi_sync_slave_enabled=ON
#loose_rpl_semi_sync_master_enabled=ONAt this point, the intermediate master (mariadb1) is still replicating from the master (mariadb0) while other slaves have been replicating from the binlog server. Our current topology can be illustrated like the diagram below:
The final part is to change the master pointing of the intermediate master (mariadb1) after all slaves that used to attach to it are no longer there. The steps are basically the same with the other slaves:
(mariadb1)$ mysql -uroot -p
MariaDB> STOP SLAVE;
MariaDB> SET global rpl_semi_sync_master_enabled = 0; -- if semisync is enabled
MariaDB> SET global rpl_semi_sync_slave_enabled = 0; -- if semisync is enabled
MariaDB> CHANGE MASTER TO MASTER_HOST = '192.168.0.95', MASTER_USER = 'maxscale_slave', MASTER_PASSWORD = 'BtF2d2Kc8H', MASTER_PORT=5306, MASTER_USE_GTID = slave_pos;
MariaDB> START SLAVE;
MariaDB> SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.95
Master_User: maxscale_slave
Master_Port: 5306
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Server_Id: 9999
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-38001-32Note: The above output has been truncated to show only important lines.
Don’t forget to disable semi-sync replication in my.cnf as well:
#loose_rpl_semi_sync_slave_enabled=ON
#loose_rpl_semi_sync_master_enabled=ONWe can the verify the binlog router service has more connections now via maxctrl CLI:
$ maxctrl list services
┌─────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Servers │
├─────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────────────┤
│ rw-service │ readwritesplit │ 1 │ 1 │ DB_757, DB_758, DB_759, DB_760 │
├─────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────────────┤
│ rr-service │ readconnroute │ 1 │ 1 │ DB_757, DB_758, DB_759, DB_760 │
├─────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────────────┤
│ replication-service │ binlogrouter │ 4 │ 51 │ binlog_router_master_host, DB_757 │
└─────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────────────┘Also, common replication administration commands can be used inside the MaxScale binlog server, for example, we can verify the connected slave hosts by using this command:
(maxscale)$ mysql -u maxscale_slave -p'BtF2d2Kc8H' -h127.0.0.1 -P5306
MariaDB> SHOW SLAVE HOSTS;
+-----------+--------------+------+-----------+------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+--------------+------+-----------+------------+
| 38003 | 192.168.0.92 | 3306 | 9999 | |
| 38002 | 192.168.0.91 | 3306 | 9999 | |
| 38004 | 192.168.0.93 | 3306 | 9999 | |
+-----------+--------------+------+-----------+------------+At this point, our topology is looking as what we anticipated:
Our migration from intermediate master setup to binlog server setup is now complete.
