mysql 二进制日志备份_MySQL按天备份二进制日志

#!/usr/bin/env python

# -*- coding:utf-8 -*-

# Author:guozhen.zhang

import MySQLdb

import time

import os

# 创建备份binlog目录

project_path = '/data/binlog_back' # 定义备份日志的目录

binlog_file = "/data/binlog_back/binlog_file" # 定义获取日志的存放文件

last_binlog_file = "/data/binlog_back/last_binlog_file" # 获取binlog日志的最后一个日志文件

dir_time = time.strftime('%Y%m%d-%H%M', time.localtime(time.time())) # 返回当前时间的年月日作为目录名称

isExists = os.path.exists(project_path + '/' + dir_time) # 判断该目录是否存在

if not isExists:

os.makedirs(project_path + '/' + dir_time)

print(project_path + '/' + dir_time + "目录创建成功")

# 定义执行备份脚本

def back_binlog():

# 建立MySQL连接

conn = MySQLdb.connect(host='192.168.1.20', port=3306, user='root', passwd='123a456b')

# 刷新master的二进制日志

cursor = conn.cursor()

cursor.execute("flush logs;")

# 获取binlog的存放路径

cursor1 = conn.cursor()

cursor1.execute("show variables like 'log_bin_basename'")

row1 = cursor1.fetchone()[1]

# 获取masterbinlog日志的最后一个日志文件

cmd = 'find %s* -mtime 0 -exec ls {} \;|grep -v index|tail -1 > %s' % (row1, last_binlog_file)

os.popen(cmd).read()

# 获取master前一天的二进制日志

cmd = 'find %s* -mtime 0 -exec ls {} \;|grep -v `cat %s` |grep -v index> %s' % (

row1, last_binlog_file, binlog_file)

os.popen(cmd)

f = open(binlog_file, mode="r")

lines = f.readlines()

for line in lines:

fname = line.strip()

cmd = 'cp ' + fname + ' ' + (project_path + '/' + dir_time)

os.system(cmd)

f.close()

# 关闭数据库连接

conn.close()

# 备份二进制文件存在就执行备份，否则退出

if os.path.exists(binlog_file):

back_binlog()

print("backup success!")

else:

print("binlog file not found")

exit()