Creating a Simple MySQL Binlog Server

Jul 29, 2020

Mins to Read

All

In my previous Blog i have explained how we can integrate MySQL tools ( Percona Tool kit ) with systemd service, At Mydbops works with multiple clients ,we get various requirements from customers a few may be a complex ones ,one of the requirement was to setup a binlog server and copy the Production binlogs instantly .

To satisfy customer request i have made a simple shell script and integrated in systemd service which does a similar job as of Maxscale Binlog Server ( Pervious blog )

In this blog i will explain how to setup a simple binlog server.

Pre-requites for setting up the Binlog server.

A standalone remote server with required disk.
The mysqlclient to be installed on remote server(where the Binlogs will be stored).
Mysql user with REPLICATION SLAVE and REPLICATION CLIENT privilege.

The script basically pulls the binlogs from production sever using mysqlbinlog (–read-from-remote-server) utility .

My Test environment

 
Source = 192.168.33.11 (vm1 - Mysql Server)
Destination = 192.168.33.13 (vm3 - Remote Server)

Lets create a user in source with required privileges

 
mysql> create user 'binlog_server'@'192.%' identified by '3!nl0g@321';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave,replication client on *.* to 'binlog_server'@'192.%';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'binlog_server'@'192.%';
+-------------------------------------------------------------------------------+
| Grants for binlog_server@192.% |
+-------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO 'binlog_server'@'192.%' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Setting up script in remote server

 
[root@vm3 vagrant]# vi /home/vagrant/bin_server.sh

 
#!/bin/bash
###loop###
s3_upload()
{
for ((i=1;i<="$binlog_count";i++));do trans_binlog=$(echo "$binlog_details" | awk '{print $1}' | head -n $i | tail -n 1) echo "aws s3 cp \"$log_bin_dir\"/\"$trans_binlog\" \"$s3_bucket\" " if [[ $i -eq $binlog_count ]];then echo "$trans_binlog" > $last_binlog_file
fi
done
}
mysql_remote()
{
for ((i=1;i<="$binlog_count";i++));do trans_binlog=$(echo "$binlog_details" | awk '{print $1}' | head -n $i | tail -n 1) /bin/mysqlbinlog --read-from-remote-server --host=$mysql_host --user=$mysql_user --password=$mysql_pass $trans_binlog > $local_dir/$trans_binlog
if [[ $i -eq $binlog_count ]];then
echo "$trans_binlog" > $last_binlog_file
fi
done
}
log_bin_dir="/var/lib/mysql"
local_dir="/home/vagrant/binlog"
mysql_user="binlog_server"
mysql_pass='3!nl0g@321'
mysql_host='192.168.33.11'
#s3_bucket=""
last_binlog_file="/tmp/last_binlog_file"
last_binlog=$(cat "$last_binlog_file")
binlog_details=$(mysql -sN --user="$mysql_user" --password="$mysql_pass" --host=$mysql_host -e "show binary logs" | sed '$ d')
binlog_count=$(echo "$binlog_details" | awk '{print $1}' | wc -l)
if [[ -z "$last_binlog" ]];then
mysql_remote
else
binlog_details=$(echo "$binlog_details" | awk '{print $1}' | awk "f;/$last_binlog/{f=1}")
binlog_count=$(echo "$binlog_details" | awk '{print $1}' | wc -l)
if [[ -z "$binlog_details" ]];then
echo "$last_binlog" > $last_binlog_file
else
mysql_remote
fi
fi

 
log_bin_dir - Binlog directory path in prod server 
local_dir   - Path where Binlogs to be stored (remote server)
mysql_host  - production master IP / Endpoint

The script is designed in such a way that , it pulls the binlog from master, it excludes active binlog file ( recent one) to to avoid unusual load on source ( master ) server.

Note : There is a difference in mysqlbinlog option to read binary log remotely , please modify the option according to your environment needs.

 
--read-from-remote-server - Read the binary log from a MySQL master rather than reading a local log file

--read-from-remote-master - Read binary log from MySQL server rather than local log file

Scheduling script in systemd service

 
[root@vm3 vagrant]# vi /etc/systemd/system/binlog-server.service

 
##binlog copy systemd service file
[Unit]
Description="binlog server"
Wants=binlog-server.timer

[Install]
WantedBy=timers.target

[Service]
Type=forking
ExecStart=/bin/bash /home/vagrant/bin_server.sh
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=binlog-server
Restart=always

 
[root@vm3 vagrant]# systemctl daemon-reload

[root@vm3 vagrant]# systemctl start binlog-server

 
[root@vm3 vagrant]# systemctl status binlog-server
● binlog-server.service - "binlog server"
   Loaded: loaded (/etc/systemd/system/binlog-server.service; disabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-07-26 11:04:13 UTC; 17s ago
 Main PID: 25421 (bash)
    Tasks: 2
   Memory: 665.5M
   CGroup: /system.slice/binlog-server.service
           ├─25421 /bin/bash /home/vagrant/bin_server.sh
           └─25447 /bin/mysqlbinlog --read-from-remote-server --host=192.168.33.11 --user=binlog_server --password=x xxxxxxxxx mysql-bin...
Jul 26 13:04:13 vm3 systemd[1]: Started "binlog server".

After starting the service , the script will make initial a copy of binlogs till the last writing binlog from master server and exits.

Consider we have 6 binlogs in master server , once the service is initialised the script will copy binlogs from mysql-bin.000171 to mysql-bin.000175 and exits as mysql-bin.000176 will be the current writing binlog in master server.

 
mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000171 |        154 |
| mysql-bin.000172 |        769 |
| mysql-bin.000173 | 1073742038 |
| mysql-bin.000174 |  754325685 |
| mysql-bin.000175 |        154 |
| mysql-bin.000176 |        201 |
+------------------+------------+
6 rows in set (0.00 sec)

List binlogs copied to remote server

 
[root@vm3 binlog]# ls -lrth
total 3.4G
-rw-r--r--. 1 root root  834 Jul 26 13:04 mysql-bin.000171
-rw-r--r--. 1 root root 2.3K Jul 26 13:04 mysql-bin.000172
-rw-r--r--. 1 root root 1.9G Jul 26 13:05 mysql-bin.000173
-rw-r--r--. 1 root root 1.6G Jul 26 13:06 mysql-bin.000174
-rw-r--r--. 1 root root  834 Jul 26 13:06 mysql-bin.000175

 
[root@vm3 vagrant]# systemctl status binlog-server
● binlog-server.service - "binlog server"
   Loaded: loaded (/etc/systemd/system/binlog-server.service; disabled; vendor preset: disabled)
   Active: inactive (dead) since Sun 2020-07-26 13:06:18 UTC; 2min 58s ago
  Process: 25421 ExecStart=/bin/bash /home/vagrant/bin_server.sh (code=exited, status=0/SUCCESS)
 Main PID: 25421 (code=exited, status=0/SUCCESS)

In case if we want to re-initialise the copy from initial binlogs , we have to truncate /tmp/last_binlog_file and start back the systemd service.

To make a incremental copy of binlogs ,we have to make a cron in systemd like binlog-server.timer

 
[root@vm3 vagrant]# vi /etc/systemd/system/binlog-server.timer

 
##/etc/systemd/system/binlog-server.timer
[Unit]
Description="copy binlog files for every 5 minute"
Requires=binlog-server.service

[Timer]
OnUnitInactiveSec=5m

[Install]
WantedBy=timers.target

OnUnitInactiveSec – Schedule the task relatively to the last time the service unit was inactive.

 
[root@vm3 vagrant]# systemctl daemon-reload
[root@vm3 vagrant]# systemctl enable binlog-server.timer

binlog-server.timer will invoke binlog-server.service for every 5 minutes to copy the latest incremental binlogs excluding active binlog file.

After initial copy of binlogs , there are 5 more new binlogs generated in master which will copied when binlog-server.timer invokes binlog-server.service for every 5mins.

 
mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000171 |        154 |
| mysql-bin.000172 |        769 |
| mysql-bin.000173 | 1073742038 |
| mysql-bin.000174 |  754325685 |
| mysql-bin.000175 |        154 |
| mysql-bin.000176 |        201 |
| mysql-bin.000177 |        201 |
| mysql-bin.000178 |  205265844 |
| mysql-bin.000179 |        177 |
| mysql-bin.000180 |       3635 |
+------------------+------------+
10 rows in set (0.00 sec)

Binlogs list

 
[root@vm3 binlog]# ls -lrth
total 3.7G
-rw-r--r--. 1 root root  834 Jul 26 13:19 mysql-bin.000171
-rw-r--r--. 1 root root 2.3K Jul 26 13:19 mysql-bin.000172
-rw-r--r--. 1 root root 1.9G Jul 26 13:20 mysql-bin.000173
-rw-r--r--. 1 root root 1.6G Jul 26 13:21 mysql-bin.000174
-rw-r--r--. 1 root root  834 Jul 26 13:21 mysql-bin.000175
-rw-r--r--. 1 root root  943 Jul 26 13:21 mysql-bin.000176
-rw-r--r--. 1 root root  917 Jul 26 13:21 mysql-bin.000177
-rw-r--r--. 1 root root 268M Jul 26 13:21 mysql-bin.000178
-rw-r--r--. 1 root root  913 Jul 26 13:21 mysql-bin.000179

We can verify the timer list

 
[root@vm3 vagrant]# systemctl list-timers 
NEXT                         LEFT          LAST                         PASSED  UNIT                         ACTIVATES
Sun 2020-07-26 12:49:52 UTC  4min 28s left Sun 2020-07-26 12:44:52 UTC  31s ago binlog-server.timer          binlog-server.service

The Source code is available in our Git repo ( https://github.com/mydbops/Mydbops_Binlog_Server )

Note : As per my testing environment i have set 5mins timer to sync the incremental binlogs, the timer has to be set according to your convenient time , like for every 6hrs or 12hrs .

Finally, from the above simple steps we can setup a binlog server without a need of external binlog streaming tools.

Mydbops offers a comprehensive suite of open-source database management tools for MySQL, including utilities for binlog management and replication.