Creating a Simple MySQL Binlog Server

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s