python mysql备份脚本
#!/usr/bin/env python # encoding: utf-8 #@author: 东哥加油! #@file: pyinnobackup.py #@time: 2018/12/11 11:34 import datetime import os import pymysql import subprocess import re import sys #从库备份 innobackupex = '/usr/bin/innobackupex' mysql_user = 'root' mysql_password = 'mysqlpassword' defaults_file='/usr/local/mysql/mysql3316.cnf' mysql_host = '127.0.0.1' mysql_port = 3316 fullback_dir = '/data/bktest/full' increback_dir = '/data/bktest/incre' log_text = '/data/bktest/backup.'+datetime.datetime.now().strftime('%Y%m%d%H%M%S')+'.txt' logger = open(log_text, 'a+') #环境检查: def check_env(): print('日志文件:',log_text) chk_1 = not os.path.exists(innobackupex) chk_2 = not os.path.exists(fullback_dir) chk_3 = not os.path.exists(increback_dir) if chk_1: logger.write(get_now_time()+':'+innobackupex+'文件不存在'+'\n') if chk_2: logger.write(get_now_time()+':'+fullback_dir+'全量备份目录不存在'+'\n') if chk_3: logger.write(get_now_time()+':'+increback_dir+'增量备份目录不存在'+'\n') conn = None chk_4 = False try: conn = pymysql.connect( host=mysql_host, port=mysql_port, user=mysql_user, passwd=mysql_password, charset="utf8", ) except Exception as err: logger.write(get_now_time()+':'+str(err)+'\n') chk_4 = True if(chk_1 or chk_2 or chk_3 or chk_4): #检查命令,目录是否存在,检查Mysql是否可以连接,如果否终止备份程序 print(get_now_time()+':'+'请配置相关环境') logger.write(get_now_time()+':'+'请配置相关环境'+'\n') exit() def get_now_time(): str_now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') return str_now_time; #执行全量备份 def fullbackup(): full_backup = innobackupex+''' --no-lock --safe-slave-backup --parallel=4 --safe-slave-backup --slave-info --defaults-file=%s --user=%s --password='%s' %s >> %s 2>&1 '''%(defaults_file,mysql_user,mysql_password,fullback_dir,log_text) logger.write('全量备份使用命令:'+full_backup+'\n') logger.write('################################全量备份开始################################\n') logger.write('全量备份开始时间:' + get_now_time() + '\n') logger.flush() subprocess.call(full_backup,shell=True) logger.write("################################全量备份结束################################\n") logger.write('全量备份结束时间:' + get_now_time()+'\n') logger.close() #检查备份是否生效 def check_bakcup_success(): file = open(log_text, 'r', encoding='UTF-8') succ = False for (num, line) in enumerate(file): if (re.search(r'innobackupex: completed OK!', line)): succ = True file.close() if succ: print('备份成功') else: print('备份失败') return succ #执行增量备份,需要检查最近的全量备份 def incre_backup(): fullback_dir='/data/bktest/full' sonfiles = os.listdir(fullback_dir) date_strs = [] for i in sonfiles: child = os.path.join(fullback_dir, i) if os.path.isdir(child ): if(re.search(r'[2][0][1-9][0-9]\-[0-1][0-9]\-[0-3][0-9]\_[0-2][0-9]\-[0-5][0-9]\-[0-5][0-9]', str(child))): date_strs.append(i) if len(date_strs) == 0: print('没有全量备份') exit() date_str = max(date_strs) incremental_basedir = os.path.join(fullback_dir,date_str) increback_dir_1 = os.path.join(increback_dir,date_str) if not os.path.exists(increback_dir_1): os.makedirs(increback_dir_1) full_backup = innobackupex+''' --no-lock --safe-slave-backup --parallel=4 --safe-slave-backup --slave-info '''\ '''--defaults-file=%s --user=%s --password='%s' --incremental-basedir=%s --incremental %s >> %s 2>&1 '''%(defaults_file,mysql_user,mysql_password,incremental_basedir,increback_dir_1,log_text) logger.write('增量备份使用命令:'+full_backup+'\n') logger.write('################################增量备份开始################################\n') logger.write('增量备份开始时间:' + get_now_time() + '\n') logger.flush() subprocess.call(full_backup,shell=True) logger.write("################################增量备份结束################################\n") logger.write('增量备份结束时间:' + get_now_time()+'\n') logger.close() def del_expire_bk(): print('删除过期备份') if __name__ == '__main__': if len(sys.argv) == 1: print('请输入参数 full or incr') exit() if sys.argv[1] == 'full': check_env() fullbackup() check_bakcup_success() del_expire_bk() elif sys.argv[1] == 'incr': incre_backup() check_bakcup_success() del_expire_bk() else: print('请输入参数 full or incr')