python mysql备份脚本

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147

#!/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')