def backup(exclude_ports: list):
# 检查二进制文件
if not mydumper.exists():
sender.send_all("mysql_sqldump.py, mydumper binary file is not exist. err: {}".format(mydumper))
return
# 检查备份目录是否可写
if not writable(backup_base):
sender.send_all("mysql_sqldump.py, err: {} not writable".format(backup_base))
return
# 判断是否存在备份
if not backup_base.exists():
backup_base.mkdir(parents=True)
# 判断是否存在备份目录
if not backup_today.exists():
backup_today.mkdir(parents=True)
running_ports = get_mysql_running_ports()
for port in running_ports:
# 排除的端口不做备份
if port in exclude_ports:
continue
# 如果没有端口的目录说明没有进行过备份
# 这里需要做一次备份
base_port_dir = backup_today / "{}_{}".format(hostname, port)
if not base_port_dir.exists():
sqldump(port)
# 金融库进行额外的备份
extra_backup(running_ports)
def rsync_binlog(binlog, ports):
dir = archive_today_dir / "{}".format(ports)
cmd1 = "rsync -avzP --bwlimit=5000 {} {}:{}".format(binlog, NFSHOST, dir)
rc, stdout, stderr = exec_cmd(cmd1)
if rc != 0:
sender.send_all("rsync binlog is failed.cmd:{}.stder:{}".format(
cmd1, stderr))
def exec_bash_cmd(cmd):
rc, stdout, stderr = exec_cmd(cmd)
if rc != 0:
sender.send_all("exec_bash_cmd is failed.cmd:{}.stder:{}".format(
cmd, stderr))
result = stdout.strip()
return result
def check_threads(ins: Instance, threshold: int):
sql = 'select count(*) from information_schema.processlist ' \
'where command != "sleep" and command !="Connect" and time > 60;'
count = ins.query(sql)[0][0]
if count > threshold:
sender.send_all(
"check_non_sleep_thread.py, port {} non_sleep threads {} more than {} ."
.format(ins.port, count, threshold))
def sqldump(port):
base_port_dir = backup_today / "{}_{}".format(hostname, port)
cmd = "{} -u root -S /tmp/mysql_{}.sock " \
"--regex '^(?!(mysql|test|information_schema|performance_schema|sys))' " \
"-o {} --triggers --events --routines -c".\
format(mydumper, port, base_port_dir)
rc, stdout, stderr = exec_cmd(cmd)
if rc != 0:
sender.send_all("mysql_sqldump.py, sqldump is failed. port {} . error: {}".format(port, stderr))
def arch_dump():
cmd = "ssh {}".format(NFSHOST) + ' "/bin/mkdir -p {}" '.format(arch_dir)
rc, stdout, stderr = exec_cmd(cmd)
if rc != 0:
sender.send_all("mkdir {} failed . stderr : {}".format(arch_dir, stderr))
return
cmd = "rsync -avzP --bwlimit=5000 {} {}:{}".format(local_dir, NFSHOST, arch_dir)
rc, stdout, stderr = exec_cmd(cmd)
if rc != 0:
sender.send_all("{} rsync is failed .stderr : {} ".format(local_dir, stderr))
return
shutil.rmtree(local_dir)
def mdl_lock(ins: Instance):
# 查询存在lock的线程id以及SQL语句
sql = "select c.THREAD_ID,SQL_TEXT,b.PROCESSLIST_STATE " \
"from performance_schema.events_statements_current c " \
"join (select t.THREAD_ID,t.PROCESSLIST_STATE from information_schema.processlist p " \
"join performance_schema.threads t " \
"on p.id = t.PROCESSLIST_ID where state like 'Waiting for % lock') b " \
"on c.THREAD_ID=b.THREAD_ID"
result = ins.query(sql)
# 查询结果为空，无锁则返回，不继续执行命令
if len(result) == 0:
return
# 循环返回的结果，按照指定的格式打印
empty = []
for i in result:
t_id, sql, lock = i
message = "thread_id:{}. sql:{}. lock:{} .".format(t_id, sql, lock)
empty.append(message)
empty = ','.join(empty)
message = "mysql_monitor_lock.py.port:{},{}".format(ins.port, empty)
sender.send_all(message)
from mtk.mysql.runtime import get_mysql_running_ports, get_mysql_config_files
from mtk.alert import sender
def check(c_ports, r_ports):
not_running_port = []
for port in c_ports:
if port not in r_ports:
not_running_port.append(port)
return not_running_port
running_ports = get_mysql_running_ports()
config_ports = get_mysql_config_files()
if sorted(running_ports) != sorted(config_ports):
not_running_ports = check(config_ports, running_ports)
sender.send_all(
"mysql_instance_status_check.py.mysql port {} is not running".format(
not_running_ports))
return mess_dict
if __name__ == '__main__':
for port, instance in get_mysql_runtime_instances().items():
# dict变量返回是字典，如果为None，说明数据库中不存在长事务
dict = long_trx(instance)
if dict is None:
continue
else:
# 如果存在长事务，需要将其序列化到列表，下次执行脚本时，判断dict中的key值是否有存在于序列化列表中，存在则报警
dir_file = Path("/root/dict_long_file")
if not dir_file.exists():
dictfile = open("/root/dict_long_file", 'wb')
pickle.dump(dict, dictfile)
dictfile.close()
dictfile = open("/root/dict_long_file", 'rb')
readdict = pickle.load(dictfile)
for k in dict.keys():
if k in readdict:
sql = readdict["{}".format(k)]
sender.send_all(
"mysql_long_trx_monitor.py.Monitor for long transactions,port:{}. trx_id:{}. sql:{}.".format(
port, k, sql))
dictfile = open("/root/dict_long_file", 'wb')
pickle.dump(dict, dictfile)
dictfile.close()
def bak_bin():
# 获取归档机器中，最大日期的备份目录
cmd1 = "ssh {}".format(NFSHOST) + ' "/bin/ls {}"'.format(archive_base_dir)
list_date = exec_bash_cmd(cmd1).split("\n")
max_date = max(list_date)
# 在归档机器上创建今天的备份目录
for port in running_ports():
cmd_mkdir_date = "ssh {}".format(
NFSHOST) + ' "/bin/mkdir -p {}/{}" '.format(
archive_today_dir, port)
rc, stdout, stderr = exec_cmd(cmd_mkdir_date)
if rc != 0:
sender.send_all(
"mkdir archive date dir is failed.cmd:{}.stder:{}".format(
cmd_mkdir_date, stderr))
# 判断上一次的备份端口数和金融端口实例数是否一致（为了防止新添加金融实例，新实例需要备份所有binlog）
archive_max_dir = archive_base_dir / "{}".format(max_date)
cmd1 = "ssh {}".format(NFSHOST) + ' "/bin/ls {}"'.format(archive_max_dir)
list_port = exec_bash_cmd(cmd1).split("\n")
if len(list_port) != len(financial_ports):
for p in running_ports():
# 获得binlog文件的前缀、datadir、以及binlog.index
log_bin = dir_bin_all_binlog(p)[1]
datadir = dir_bin_all_binlog(p)[0]
index = log_bin + ".index"
binlog_index = datadir / "{}".format(index)
# 如果不存在于list_port中，证明上次备份没有备份该文件，需要获取所有的binlog
if str(p) not in list_port:
all_binlog = dir_bin_all_binlog(p)[2]
cmd = "/usr/local/mysql/bin/mysql -uroot -S /tmp/mysql_{}.sock -e 'flush logs;'".format(
p)
rc, stdout, stderr = exec_cmd(cmd)
if rc != 0:
sender.send_all(
"flush log is failed.cmd:{}.stder:{}".format(
cmd, stderr))
# 上一步执行刷新日志命令，因此最新的日志不需要拷贝，需要增加判断
tail_bin = tail_n(binlog_index, 1).lstrip("./")
for binlog in all_binlog:
if str(binlog) != str(datadir / "{}".format(tail_bin)):
rsync_binlog(binlog, p)
else:
# 如果存在于list_port中，证明没有新添加金融端口的数据库，拷贝最后一次的binlog文件即可
tail_bin = tail_n(binlog_index, 1).lstrip("./")
cmd = "/usr/local/mysql/bin/mysql -uroot -S /tmp/mysql_{}.sock -e 'flush logs;'".format(
p)
rc, stdout, stderr = exec_cmd(cmd)
if rc != 0:
sender.send_all(
"flush log is failed.cmd:{}.stder:{}".format(
cmd, stderr))
tail_binlog = datadir / "{}".format(tail_bin)
rsync_binlog(tail_binlog, p)
rsync_binlog(binlog_index, p)