Shulou(Shulou.com)06/01 Report--

[MySQL] [highly available] background of the purge_relay_logs tool:

In the step of ​ switching in the MHA high availability architecture, it mainly depends on comparing the old and new relay log files of each instance, and selecting the latest relay log to append the data in the group, so as to ensure the security of data as much as possible and reduce the number of data loss. This requires that relay log can not be automatically cleared by the system, but should be retained for a period of time to prevent the main database from hanging up and complete the data.

​ but this will give rise to a problem. When the business continues to be busy, the slave library may accumulate a lot of relay log, which will cause disaster sooner or later if it is not cleared in time.

Introduction: tools: purge_relay_logs function: clear relay log, clear relay log installation as much as possible: included in the node module of mha, do not need separate installation parameters:

​-- user/password/host/port specifies the user name / password / host / port. It is important to note that although the parameters of the host are provided, only 127.0.0.1 or localhost are allowed, that is, you can only log in locally.

​-- workdir specifies the location where the hard link is created. Purge_relay_logs uses the hard link to delete the relay log. It should be noted that in order to ensure the success of the hard link, it is recommended that the folder of the hard link and the directory of the relay log are in the same partition. [version 5.7 does not seem to be used]

​-- disable_relay_log_purge disables the automatic clearing relay log setting of MySQL. By default, MySQL automatically clears relay log, which is reflected in the parameter: relay_log_purge=1. If MySQL automatically clears relay log, the tool automatically ends after startup and does not erase any files. When this parameter is specified, the tool disables the automatic cleanup relay log setting for MySQL.

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 that 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: $. / vc-mysql-sniffer-- binding "[:]: 3110" in All relay log purging operations succeeded.#MySQL | 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',' 200') 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

​ parsing: 1. First check to see if automatic cleaning relay log is enabled

​ 2. Get the file name, location, number in use of relay log from MySQL, and create a hard link.

​ 3. Enable automatic cleaning, refresh relay log, generate a new set of relay log, turn off automatic cleaning, this step cleans up the relay log to the maximum extent.

Automated cleanup:

​ cleans up regularly through the shell script and adding it to crontab.

Cat > / etc/auto_clean_relay_log.sh $log_dir/purge_relay_logs.log 2 > & 1

​ crontab-e adds the following line:

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

Attached:

Reference article: brother Guoping: https://blog.51cto.com/arthur376/1812640

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.