通过 purge_relay_logs 自动清理relaylog

使用背景

线上物理备份任务是在从库上进行的，xtrabackup会在备份binlog的时候执行flush logs，relay-log会rotate到新的一个文件号，导致sql thread线程应用完之后无法自动删除

这时候就可以引入purge_relay_logs做成执行计划自动清理relay log

安装

yum install -y  mha4mysql-node-0.56-0.el6.noarch

需要的权限

RELOAD, SUPER

使用

#Ansible:  purge mysql relay log from mha node

* */ * * * /usr/bin/purge_relay_logs --disable_relay_log_purge --user=purge_relay --password='' --port= --host=127.0.0.1 &>/tmp/mysql_purge_relay.log

可以通过ansible批量对已有的从库加一下

思路就是先获取所有的从库元数据到列表对象中，然后遍历更新inventory及调用写好的playbook，主脚本如

cat mysql_purge_relay.py

# -*_ coding: utf-8 -*-

import json

import os

f = 'port_ip_role'

inf = open(f, 'r')

port_ip_role=json.load(inf)

#port_ip_role={"6666,10.1.1.1": "Slave","6667,10.1.1.2": "Slave"}

for k,v in port_ip_role.items():

    if v=='Slave':

        port_ip=k.split(',')

        port,ip =port_ip[0],port_ip[1]

        os.system('echo "[slave_host]\n{0}">inventory.cfg'.format(ip))

        ansible_cmd='ansible-playbook main.yaml --extra-vars="port={0} slave_host={1}"'.format(port,ip)

        print ansible_cmd

        os.system(ansible_cmd)

main.yaml

- hosts: slave_host

  remote_user: root

  vars:

  vars_files:

    - vars.yaml

  roles:

    - { role: purge_relay, tags: purge_relay }

purge_relay/tasks/main.yaml

- name: check if install

  shell: which purge_relay_logs

  register: status

  ignore_errors: yes

- name: check if exists /usr/bin/purge_relay_logs

  shell: ls /usr/bin/purge_relay_logs

  register: pstatus

  ignore_errors: yes

- name: check if cron on port

  shell: crontab -l|grep purge_relay_logs|grep {{port}}

  register: cronstatus

  ignore_errors: yes

- name: make soft link if not exists /usr/bin/purge_relay_logs

  shell: ln -s `which purge_relay_logs` /usr/bin/purge_relay_logs

  when: "{{ status.rc }} == 0 and {{ pstatus.rc }} !=0"

- name: install mha4mysql-node-0.56 远程yum安装

  yum: name=https://raw.githubusercontent.com/yyueshui/mysql-master-ha-rpm/master/mha4mysql-node-0.56-0.el6.noarch.rpm

  when: "{{ status.rc }} != 0"

- name: purge mysql relay log cronjob

  cron: hour="*/2"

        name="{{port}} purge mysql relay log from mha node"

        user="root"

        job="/usr/bin/purge_relay_logs --disable_relay_log_purge --user={{purge_relay_user}} --password='{{purge_relay_password}}' --port={{port}} --host=127.0.0.1 &>/tmp/mysql_purge_relay.log"

  #when: "{{ cronstatus.rc }} != 0"

#- name: Removes job from crontab

#  cron:

#       name: "purge mysql relay log from mha node"

#       state: absent

#  when: "{{ status.rc }} == 0"

补充

如果在运行purge_relay_logs 报错为

-- ::: purge_relay_logs script started.

install_driver(mysql) failed: Can't load '/usr/local/lib64/perl5/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.18: cannot open shared object file: No such file or directory at /usr/lib64/perl5/DynaLoader.pm line 200.

 at (eval ) line

Compilation failed in require at (eval ) line .

Perhaps a required shared library or dll isn't installed where expected

 at /usr/bin/purge_relay_logs line 

解决方法为

ln -s /home/mysql/mysql/lib/libmysqlclient.so. /usr/lib64/

注：由于Mariadb 和mysql的参数有些出入，所以在Mariadb上执行会报参数不存在的错误，可以自行修改perl脚本修正