MYSQL-MHA Pre-release environment architecture share (eight) relay log cleanup

(1) Setting the Relay Log Purge method (on each slave node): mysql-e ' Set global relay_log_purge=0 '

Attention:

MHA in the process of switching, from the library during the recovery process depends on the relay log information, so here to relay log automatic cleanup set to off, with manual removal relay log. By default, the relay log from the server is automatically deleted when the SQL thread finishes executing. However, in a MHA environment, these relay logs may be used when restoring other slave servers, so you need to disable the automatic deletion of the trunk log. Periodic cleanup of the relay log requires consideration of replication latency issues. In ext3 file system, deleting large files takes a certain amount of time and can cause serious replication delays. To avoid replication delays, you need to temporarily create a hard link for the trunk log because it is fast to delete large files on a Linux system with hard links. (in MySQL database, when you delete a large table, you usually use a hard-link method)

The MHA node contains the pure_relay_logs command tool, which creates a hard link for the trunk log, executes the set global relay_log_purge=1, waits a few seconds for the SQL thread to switch to the new trunk log, and then executes the set global Relay_log_purge=0.

pure_relay_logs script parameters are as follows:

--user mysql user name--password mysql password--port port number--workdir Specifies the location of the hard link that creates the relay log, by default,/var/tmp, because a hard-link file will fail due to a different partition of the system, so you need to perform a hard-link specific location, and after the script is successfully executed, the hard-linked relay log file is deleted--disable_rela Y_log_purge by default, if the relay_log_purge=1, the script will not clean up, automatically exit, by setting this parameter, when relay_log_purge=1 the case will be relay_log_purge set to 0. After cleaning up the relay log, set the parameter to off finally.

(2) Set up regular cleanup relay scripts (per slave server)

Cat purge_relay_log.sh #!/bin/bashuser=rootpasswd=123456port=3306log_dir= '/data/masterha/log ' work_dir= '/data ' Purge= '/usr/local/bin/purge_relay_logs ' if [!-D $log _dir]then mkdir $log _dir-pfi$purge--user= $user--password= $PASSW D--disable_relay_log_purge--port= $port--workdir= $work _dir >> $log _dir/purge_relay_logs.log 2>&1

(3) add to crontab periodic execution

Crontab-l0 4 * * */bin/bash/root/purge_relay_log.sh

The purge_relay_logs script removes the trunk log without blocking the SQL thread. Let's take a look at what happens manually.

[[email protected] ~]# purge_relay_logs --user=root --password=123456 --port= 3306 -disable_relay_log_purge --workdir=/data/2014-04-20 15:47:24: purge_relay_logs  Script started. found relay_log.info: /data/mysql/relay-log.info removing hard  linked relay log files server03-relay-bin* under /data/.  done. Current relay log file: /data/mysql/server03-relay-bin.000002  archiving unused relay log files  (up to /data/mysql/ SERVER03-RELAY-BIN.000001)  ... creating hard link for /data/mysql/ SERVER03-RELAY-BIN.000001 UNDER /DATA//SERVER03-RELAY-BIN.000001&NBSP, ....  ok. Creating hard links for unused relay log files  Completed. 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. Removing hard linked relay log files server03-relay-bin*  under /data/.  done.2014-04-20 15:47:27: all relay log purging operations succeeded.

This article is from "Allen Lee Oracle" blog, please make sure to keep this source http://lipengfei666666.blog.51cto.com/6384154/1613965

MYSQL-MHA Pre-release environment architecture share (eight) relay log cleanup