def xtrabackup_install(os_args, version):
if version == '8.0':
soft_name = 'xtrabackup\percona-xtrabackup-8.0.tar.gz'
else:
soft_name = 'xtrabackup\percona-xtrabackup-2.4.tar.gz'
ssh_input_noprint(os_args, 'rm -rf /usr/local/xtrabackup')
ssh_input_noprint(os_args, 'mkdir -p /usr/local/xtrabackup')
print(f"\nINFO: {os_args[0]} 开始上传xtrabackup介质文件")
ssh_ftp(os_args, '/usr/local/xtrabackup/percona-xtrabackup.tar.gz',
soft_name, 'put')
print(f"\nINFO: {os_args[0]} xtrabackup介质文件上传完成")
print(f"\nINFO: {os_args[0]} 开始解压配置xtrabackup介质包")
ssh_input_noprint(
os_args,
'cd /usr/local/xtrabackup/\ntar -zxf percona-xtrabackup.tar.gz')
ssh_input_noprint(
os_args,
'cd /usr/local/xtrabackup/percona-xtrabackup*/\nmv * /usr/local/xtrabackup'
)
print(f"\nINFO: {os_args[0]} xtrabackup介质包解压配置完成")
check_res = ''.join(
ssh_input_noprint(os_args, '/usr/local/xtrabackup/bin/xtrabackup -v'))
if 'version' in check_res:
print(f"\nINFO: {os_args[0]} xtrabackup软件可用")
return 'xb install s'
else:
print(f"\nWARNING: {os_args[0]} xtrabackup软件不可用")
return 'xb install f'
def err_log(mysql_args,os_args):
log_dir = get_all(mysql_args,"show global variables where variable_name in ('datadir','log_error');")
if './' in log_dir[1][1]:
errlog = (log_dir[0][1]+log_dir[1][1]).replace('./','/').replace('//','/')
else:
errlog = log_dir[1][1]
errlog_size = round(int(ssh_input_noprint(os_args,f"du -sk {errlog}|awk '{{print $1}}'")[0].replace('\n',''))/1024,3)
if errlog_size > 2048:
advice = "Need clean error log"
else:
advice = "Do not need clean error log"
print("\nINFO:\n目前MySQL错误日志大小如下：")
title = ['Error_log_path','Size (MB)','Advice']
before_err_res = [[errlog,errlog_size,advice]]
before_errlog_table = res_table(before_err_res,title)
print(before_errlog_table)
if advice == "Need clean error log":
print("\nINFO:\n清理MySQL错误日志：")
clean_log_file(os_args,errlog)
run_noprint(mysql_args,"flush logs")
print("\nINFO:\nMySQL错误日志清理完毕.")
err_log(mysql_args,os_args)
return before_err_res
def xtrabackup(src_args, src_os_args, tag_os_args, xtra_args, obj, tag_args):
source_socket, source_dmp_dir, source_cnf_dir, target_data_dir, target_dmp_dir, target_base_dir = xtra_args
print("\nINFO:源端开始配置xtrabackup软件")
version = get_version(src_args)
install_res = xtrabackup_install(src_os_args, version)
if install_res == "xb install s":
print("\nINFO:目标端开始配置xtrabackup软件")
t_install_res = xtrabackup_install(tag_os_args, version)
if t_install_res == "xb install s":
xtrabackup_cmd = get_xtrabackup_cmd(src_args, xtra_args, obj)
xtrabackup_res = ''.join(
ssh_input_noprint(src_os_args, xtrabackup_cmd))
if 'completed OK' in xtrabackup_res:
print("\nINFO:源端xtrabackup备份完成")
print("\nINFO:开始往目标端传输物理备份文件")
ssh_scp(src_os_args, tag_os_args,
f"{source_dmp_dir}/xtrabackup.xbstream",
f"{target_dmp_dir}/xtrabackup.xbstream")
target_xtra_res = target_xtra(tag_os_args, xtra_args, tag_args)
return target_xtra_res
else:
print("\nINFO:源端xtrabackup备份失败，详情请查看ops_mysql.log")
return 'xtrabackup f'
else:
return t_install_res
else:
return t_install_res
def mysqldump(src_args, tag_args, tag_os_args, obj):
run_res = ''
if '.' not in obj:
size = int(get_size(src_args, obj))
data_dir_free = int(get_datadir_free(tag_args, tag_os_args))
if size < data_dir_free * 0.8:
print(f"\nINFO:对象大小{obj}约为{size}MB,目标环境磁盘空间充足，开始执行mysqldump")
mysqldump_cmd = get_mysqldump_cmd(src_args, tag_args, tag_os_args,
obj)
imp_res = ssh_input_noprint(tag_os_args, mysqldump_cmd)
if 'ERROR' in ''.join(imp_res):
print("\nERROR:数据导入出错!")
print(''.join(imp_res))
run_res = 'mysqldump f'
else:
print(f"\nINFO:{obj} 数据导入完成.")
run_res = 'mysqldump s'
else:
print(f"\nINFO:对象大小{obj}约为{size}MB,目标环境磁盘空间不足")
run_res = 'no free'
else:
obj_list = obj.split(',')
for obj in obj_list:
size = int(get_size(src_args, obj))
data_dir_free = int(get_datadir_free(tag_args, tag_os_args))
if size < data_dir_free * 0.8:
check_table(tag_args, tag_os_args, obj)
print(f"\nINFO:对象{obj} 大小约为{size}MB,目标环境磁盘空间充足，开始执行mysqldump")
mysqldump_cmd = get_mysqldump_cmd(src_args, tag_args,
tag_os_args, obj)
imp_res = ssh_input_noprint(tag_os_args, mysqldump_cmd)
if 'ERROR' in ''.join(imp_res):
print("\nERROR:数据导入出错!")
print(''.join(imp_res))
run_res = 'mysqldump f'
else:
print(f"\nINFO:{obj} 数据导入完成.")
run_res = 'mysqldump s'
else:
print(f"\nINFO:对象大小{obj}约为{size}MB,目标环境磁盘空间不足")
run_res = 'no free'
return run_res
def os_dir_use(os_args):
fs_set = ssh_input_noprint(os_args,"df -hP")
space_param = []
for space in fs_set[1:]:
space_tmp = space.split(' ')
space = [x.replace('\n','') for x in space_tmp if x != ''][0:6]
if space!=[]:
space_param.append(space)
print("\nINFO:\n操作系统目录使用情况如下:\n")
dir_title = ["Filesystem","Size" ,"Used" ,"Avail" ,"Use%" ,"Mounted on"]
dir_table = res_table(space_param,dir_title)
print(dir_table)
return space_param
def mysql_dir_use(mysql_args,os_args):
dirs = get_all(mysql_args,"show global variables where variable_name in ('basedir','datadir')")
get_dirs = []
for dir in dirs:
dir = list(dir)
size = ''
if dir[1]!='':
size = ssh_input_noprint(os_args,"du -sh %s|awk '{print $1}'"%dir[1])[0].replace('\n','')
else:
size = 0
dir.append(size)
get_dirs.append(dir)
print("\nINFO:\nMySQL软件及数据目录使用情况如下:\n")
dir_title = ["Directory_name","Directory_path","Size"]
dir_table = res_table(get_dirs,dir_title)
print(dir_table)
return get_dirs
def check_table(tag_args, tag_os_args, obj):
dbname, tbname = obj.split('.')
t_ip, t_db_user, t_db_port, t_db_pwd = tag_args
t_db_pwd = t_db_pwd.replace('!', '\\!')
bin_dir = get_bin_dir(tag_args)
check_tb_cmd = f"{bin_dir}/mysql -e 'show tables' -u{t_db_user} -p{t_db_pwd} -h{t_ip} -P{t_db_port} -D{dbname} |grep -w {tbname}"
res = ssh_input_noprint(tag_os_args, check_tb_cmd)
if res == []:
return 'tb no exist'
else:
rename_yn = input(f'\nINFO:对象{obj} 在目标库已存在，是否重命名？ Y/N ').upper()
if rename_yn == 'Y':
rename_time = time.strftime("%Y%m%d%H%M%S", time.localtime())
rename_tnb = f"{obj}_old_{rename_time}"
rename_sql = f"rename table {obj} to {rename_tnb}"
run_noprint(tag_args, rename_sql)
print(f"\nINFO:目标库对象{obj} 已重命名为{rename_tnb}")
else:
print(f"\nINFO:目标库已存在对象{obj} 将被覆盖.")
return 'tb exist'
def bin_log(mysql_args,os_args):
logbin = get_all(mysql_args,"show global variables where Variable_name in ('log_bin')")[0][1]
db_version = get_version(mysql_args)
if logbin == 'ON':
binlog = get_all(mysql_args,"show global variables where \
variable_name in ('log_bin','LOG_BIN_BASENAME','expire_logs_days','binlog_format','sync_binlog',\
'log_bin_index','binlog_cache_size','max_binlog_cache_size','max_binlog_size','Binlog_cache_disk_use','Binlog_cache_use')")
if db_version != '5.5':
bin_dir = get_all(mysql_args,"show global variables where\
variable_name in ('log_bin_basename')")
cmd = "ls -lhs --time-style '+%%Y/%%m/%%d %%H:%%M:%%S' %s*|awk '{print $9,$7,$8,$1}'"%bin_dir[0][1]
bin_size = ssh_input_noprint(os_args,cmd)
bin_set = []
for space in bin_size:
space = [x for x in space.split(' ') if x != '']
if space!=[]:
bin_set.append(space)
else:
bin_set = [('None', 'None')]
bin_size = [('None', 'None')]
else:
binlog = [('log_bin', 'OFF')]
bin_dir = [('None', 'None')]
bin_size = [('None', 'None')]
bin_set = [('None', 'None')]
print("\nINFO:\nMySQL Binlog日志参数如下：")
binlog_title = ["Variable_name","Value"]
binlog_table = res_table(binlog,binlog_title)
print(binlog_table)
if binlog[2][1] == '0' and logbin =='ON':
print("\n小结:\nLOG_BIN参数值为ON，且expire_logs_days参数值为0,建议设置保留时间.")
if bin_set!=[('None', 'None')]:
print("\nINFO:\nMySQL Binlog空间使用情况如下")
binlog_dir_title = ["Binlog_path","Use_date","Use_time","Size"]
binlog_dir_table = res_table(bin_set,binlog_dir_title)
print(binlog_dir_table)
return binlog,bin_set,bin_size
def target_xtra(tag_os_args, xtra_args, tag_args):
source_socket, source_dmp_dir, source_cnf_dir, target_data_dir, target_dmp_dir, target_base_dir = xtra_args
host, db_username, db_port, db_password = tag_args
check_dir = ''.join(ssh_input_noprint(tag_os_args,
f'ls {target_data_dir}'))
if 'No such file or directory' in check_dir:
ssh_input_noprint(tag_os_args, f'mkdir -p {target_data_dir}')
else:
print(f"\nINFO:目标端{target_data_dir} 已存在文件,将其备份后重新创建")
rename_time = time.strftime("%Y%m%d%H%M%S", time.localtime())
ssh_input_noprint(
tag_os_args,
f'mv {target_data_dir} {target_data_dir}.bak_{rename_time}\nmkdir -p {target_data_dir}'
)
print("\nINFO:目标端开始解压备份流文件")
ssh_input_noprint(
tag_os_args,
f'cd {target_dmp_dir}\n/usr/local/xtrabackup/bin/xbstream -x < xtrabackup.xbstream -C {target_data_dir}'
)
print("\nINFO:目标端解压备份流文件完成")
print("\nINFO:目标端开始应用备份流文件日志")
apply_res = ''.join(
ssh_input_noprint(
tag_os_args,
f'cd {target_dmp_dir}\n/usr/local/xtrabackup/bin/xtrabackup --prepare --target-dir={target_data_dir}'
))
if 'completed OK' in apply_res:
print("\nINFO:目标端应用备份流文件日志完成")
ssh_input_noprint(tag_os_args,
f"chown -R mysql:mysql {target_data_dir}")
cnf_str = '''
[mysqld]
basedir=%s
datadir=%s
port=%s
socket=%s/mysql_%s.socket
''' % (target_base_dir, target_data_dir, db_port, target_data_dir,
db_port)
ssh_input_noprint(tag_os_args,
f"echo '{cnf_str}'>/etc/my_xtrabak_{db_port}.cnf")
print("\nINFO:目标端生成mysql配置文件完成")
print("\nINFO:目标端开始启动数据库")
ssh_input_noprint(
tag_os_args,
f"nohup {target_base_dir}/bin/mysqld_safe --defaults-file=/etc/my_xtrabak_{db_port}.cnf --user=mysql > /dev/null 2>&1 >>/tmp/startmysql{tag_os_args[0]}_xtra.log &"
)
time.sleep(10)
start_res = ''.join(
ssh_input_noprint(
tag_os_args, f'cat /tmp/startmysql{tag_os_args[0]}_xtra.log '))
if ' ended ' not in start_res or start_res == '':
print(f"\nNFO:目标端数据库启动完成.")
ssh_input_noprint(
tag_os_args, f'rm -f /tmp/startmysql{tag_os_args[0]}_xtra.log')
return 'start success'
else:
print(f"\nWARNING:目标端数据库启动失败，详情请查看ops_mysql.log")
ssh_input_noprint(
tag_os_args, f'rm -f /tmp/startmysql{tag_os_args[0]}_xtra.log')
return 'start failed'
else:
print("\nWARNING:目标端应用备份流文件日志失败，详情请查看ops_mysql.log")
return 'apply f'
def clean_log_file(os_args,log_file_path):
clean_time = time.strftime("%Y%m%d%H%M%S",time.localtime())
ssh_input_noprint(os_args,f"tail -10000 {log_file_path} >>{log_file_path}_bak_{clean_time}")
ssh_input_noprint(os_args,f"rm -f {log_file_path}")
return 'cleanned'
def slow_log(mysql_args,os_args):
slowlog = get_all(mysql_args,"show global variables where variable_name in ('slow_query_log','slow_query_log_file','log_output')")
data_dir = get_all(mysql_args,"show global variables like 'datadir'")
log_param,log_on,log_path = slowlog
print("\nINFO:\nMySQL慢日志参数如下：")
title = ['Variable_name','Value']
if log_on[1] == 'OFF':
slowlog_table = res_table(slowlog,title)
print (slowlog_table)
print("\nINFO:\n该MySQL数据库未开启慢日志，无需清理.")
else:
if 'NONE' in log_param[1]:
slowlog_table = res_table(slowlog,title)
print (slowlog_table)
print("\nINFO:\n该MySQL数据库输出存在NONE，无需清理.")
elif log_param[1] == 'FILE':
log_size = round(int(ssh_input_noprint(os_args,f"du -sk {log_path[1]}|awk '{{print $1}}'")[0].replace('\n',''))/1024,3)
slowlog.append(('Size of file(MB)',log_size))
slowlog_table = res_table(slowlog,title)
print (slowlog_table)
if log_size > 1024:
print("\nINFO:\n清理MySQL慢日志：")
clean_log_file(os_args,log_path[1])
run_noprint(mysql_args,"flush slow logs")
print("\nINFO:\nMySQL慢日志清理完毕.")
slow_log(mysql_args,os_args)
elif log_param[1] == 'TABLE':
table_file_path = f"{data_dir[0][1]}/mysql/slow_log.CSV".replace('//','/')
table_size = round(int(ssh_input_noprint(os_args,f"ls -l {table_file_path}|awk '{{print $5}}'")[0].replace('\n',''))/1024/1024,3)
slowlog.append(('Size of table(MB)',table_size))
slowlog_table = res_table(slowlog,title)
print (slowlog_table)
if table_size > 1024:
print("\nINFO:\n清理MySQL慢日志表：")
more_sql(mysql_args,['drop table IF EXISTS mysql.mc_slow_log_old','set global slow_query_log=off','rename table mysql.slow_log to mysql.mc_slow_log_old',
'create table mysql.slow_log like mysql.mc_slow_log_old','set global slow_query_log=on','drop table IF EXISTS mysql.mc_slow_log_old'])
print("\nINFO:\nMySQL慢日志表清理完毕.")
slow_log(mysql_args,os_args)
elif 'FILE' in log_param[1] and 'TABLE' in log_param[1]:
log_size = round(int(ssh_input_noprint(os_args,f"du -sk {log_path[1]}|awk '{{print $1}}'")[0].replace('\n',''))/1024,3)
slowlog.append(('Size of file(MB)',log_size))
table_file_path = f"{data_dir[0][1]}/mysql/slow_log.CSV".replace('//','/')
table_size = round(int(ssh_input_noprint(os_args,f"ls -l {table_file_path}|awk '{{print $5}}'")[0].replace('\n',''))/1024/1024,3)
slowlog.append(('Size of table(MB)',table_size))
slowlog_table = res_table(slowlog,title)
print (slowlog_table)
if log_size > 1024:
print("\nINFO:\n清理MySQL慢日志：")
clean_log_file(os_args,log_path[1])
run_noprint(mysql_args,"flush slow logs")
print("\nINFO:\nMySQL慢日志清理完毕.")
if table_size > 1024:
print("\nINFO:\n清理MySQL慢日志表：")
more_sql(mysql_args,['drop table IF EXISTS mysql.mc_slow_log_old','set global slow_query_log=off','rename table mysql.slow_log to mysql.mc_slow_log_old',
'create table mysql.slow_log like mysql.mc_slow_log_old','set global slow_query_log=on','drop table IF EXISTS mysql.mc_slow_log_old'])
print("\nINFO:\nMySQL慢日志表清理完毕.")
if table_size > 1024 or log_size > 1024:
print ("\nINFO:\nMySQL慢日志参数如下:")
slowlog_aft = get_all(mysql_args,"show global variables where variable_name in ('slow_query_log','slow_query_log_file','log_output')")
log_size_aft = round(int(ssh_input_noprint(os_args,f"du -sk {log_path[1]}|awk '{{print $1}}'")[0].replace('\n',''))/1024,3)
slowlog_aft.append(('Size of file(MB)',log_size_aft))
table_size_aft = round(int(ssh_input_noprint(os_args,f"ls -l {table_file_path}|awk '{{print $5}}'")[0].replace('\n',''))/1024/1024,3)
slowlog_aft.append(('Size of table(MB)',table_size_aft))
slowlog_table_aft = res_table(slowlog_aft,title)
print (slowlog_table_aft)
return "clean slow log ok"
def get_datadir_free(db_args, os_args):
data_dir = get_all(db_args, " show variables like 'datadir'")[0][1]
datadir_free = ssh_input_noprint(
os_args, f"df -m {data_dir}|awk '{{ print $4}}'")[-1]
return datadir_free
