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)