MHA clears relay log (purge_relay_logs) and relaylogpurge
By default, the slave database's relay logs in the MySQL database will be automatically deleted after the SQL thread is executed. However, in the MHA scenario, the recovery of some lagging slave databases depends on the relay logs of other slave databases. Therefore, disable the automatic deletion function and regularly clean up the database. Pay attention to the overhead of replication delayed resources when clearing excessive relay logs. MHA can use the purge_relay_logs script and cronjob to complete this task. The details are as follows.
1. Features of purge_relay_logs
A. Create hard links for relay logs (to minimize performance problems caused by batch deletion of large files)
B. SET GLOBAL relay_log_purge = 1; FLUSH LOGS; SET GLOBAL relay_log_purge = 0;
C. Delete relay log (rm-f/path/to/archive_dir /*)
2. purge_relay_logs usage and Related Parameters
### Usage
# Purge_relay_logs -- help
Usage:
Purge_relay_logs -- user = root -- password = rootpass -- host = 127.0.0.1
### Parameter description
-- User mysql user name. The default value is root.
-- Password mysql password
-- Port number
-- Host name. The default value is 127.0.0.1.
-- Workdir specifies the location where the hard link of the relay log is created. The default value is/var/tmp. After the script is executed successfully, the relay log file of the hard link is deleted.
Since hard-link files cannot be created in different partitions of the system, you need to execute the specific location of the hard-link. We recommend that you specify the same partition as the relay log.
-- Disable_relay_log_purge: by default, the relay_log_purge parameter is set to 1. The script is automatically exited without any processing.
Set this parameter. The script sets relay_log_purge to 0. After clearing the relay log, the script sets the parameter to OFF (0)
3. Customize and clear relay log cronjob
The pureg_relay_logs script automatically clears the relay log without blocking the SQL thread. This script is directly deployed to crontab for continuously generated relay logs for regular cleaning by day or by hour.
$ Crontab-l
# Purge relay logs at 5 am
0 5 *** app/usr/bin/purge_relay_logs -- user = root -- password = PASSWORD -- disable_relay_log_purge>/var/log/masterha/purge_relay_logs.log 2> & 1
4. Manual cleanup example
# Purge_relay_logs -- user = mha -- password = mha -- disable_relay_log_purge
2015-04-23 14: 33: 20: purge_relay_logs script started.
Relay_log_purge is enabled. Disabling ..
Found relay_log.info:/data/mysqldata/relay-log.info
Opening/data/mysqldata/vdbsrv3-relay-bin.000001 ..
Opening/data/mysqldata/vdbsrv3-relay-bin.000002 ..
Executing set global relay_log_purge = 1; flush logs; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up );
Set global relay_log_purge = 0;... OK.
2015-04-23 14: 33: 23: All relay log purging operations succeeded.