Use of the "MySQL" "High Availability" purge_relay_logs tool

Background of the "MySQL" "High Availability" purge_relay_logs tool:

? In the MHA high-availability architecture to switch the steps, mainly by the individual instances of the relay log file compared to the old and new, select the latest relay log for the data in the group to append, as far as possible to ensure the security of the data, reduce the number of data loss. This requires relay log can not be automatically purged by the system, to retain a period of time, to prevent the main library hanging off, data completion.

? But this will create a problem, when the business continues to be busy, from the library may accumulate a lot of relay log, if not clear sooner or later will brew a plague.

Brief introduction:

Tools:	Purge_relay_logs
Role:	Clear relay log and clear relay log as much as possible
Installation:	Included in the MHA node module, no separate installation required

Parameters:

? --user/password/host/port Specify username/password/HOST/port, there is a need to pay attention to the place, although the parameters of the host, but only allow 127.0.0.1 or localhost two values, that is, only from the local login.

? --workdir Specify the location of the hard link creation, purge_relay_logs delete the relay log using a hard link, it is important to note that in order to ensure the success of the hard link, it is recommended that the hard-linked folder and the relay log directory be in the same partition. [5.7 version does not seem to be used]

? --disable_relay_log_purge Disable automatic removal of MySQL relay log settings, by default, MySQL automatically clears the relay log, which is reflected on the parameter: relay_log_purge=1. If MySQL automatically clears the relay log, the tool will end automatically when it is started and does not erase any files. When this parameter is specified, the tool disables the automatic removal of MySQL relay log setting.

Use:

? purge_relay_logs --user=sniffer --password=123456 --port=3110 --host=127.0.0.1 --disable_relay_log_purge --workdir=/data/mha/

? The output is as follows:

2018-02-02 16:40:01:purge_relay_logs script started. opening/data/mysql/3110/data/relay-bin.000012. opening/data/mysql/3110/data/relay-bin.000013. Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; Sleeping a few seconds so the SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay_log_purge=0;. ok.2018-02-02 16:40:04:all Relay Log purging operations succeeded. #MySQL中 $./vc-mysql-sniffer--binding "[::]:3110" | Grep-v "#" use '; SHOW SLAVE STATUS; SELECT @ @global. Relay_log_purge as Value; SELECT VERSION () as Value; SELECT @ @global. relay_log_info_repository as Value; SELECT relay_log_name from Mysql.slave_relay_log_info; SELECT @ @global. DataDir as Value; SELECT get_lock (' Mha_master_high_availability_failover ', ' $ ') as Value; SELECT VERSION () as Value; SET GLOBAL relay_log_purge=1; FLUSH no_write_to_binlog/*!50501 RELAY */LOGS; SET GLOBAL relay_log_purge=0; SELECT release_lock (' Mha_master_high_availability_failover ') as Value;  

? Resolution: 1. First check whether automatic cleanup relay log is turned on

? 2. Get relay log file name, location, number in use, create hard link from MySQL.

? 3. Turn on automatic cleanup, refresh relay log, generate a new set of relay log, turn off automatic cleanup, this step cleans up the relay log to the greatest extent.

Automated Cleanup:

? Timed cleanup through shell scripts and added to Crontab.

cat >/etc/auto_clean_relay_log.sh<<EOF#!/bin/shUSER=proxysqlPASSWORD=123456PORT=3110log_dir=‘/data/mha‘work_dir=‘/data/mha‘purge=‘/usr/local/bin/purge_relay_logs‘if [ ! -d $log_dir ]then   mkdir $log_dir -pfi$purge --user=$user --password=$passwd --disable_relay_log_purge --port=$port  >> $log_dir/purge_relay_logs.log 2>&1

? crontab -eAdd the following line:

0 0 */3 * * sh /etc/auto_clean_relay_log.sh

Report:

Reference article: Guo Ping Senior: http://blog.51cto.com/arthur376/1812640

Use of the "MySQL" "High Availability" purge_relay_logs tool