crt 执行mysql脚本_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')