python写数据库脚本_Mysql备份脚本python编写

#!/usr/bin/env python

#-*- coding: UTF-8 -*-

##############################################################

#

# Date: 2017/09/22

# Filename: BackupMySQL.py

# Description: backup mysql files,base percona xtrabackup

#

# 备份mysql数据库数据，在主库进行数据同步备份:10.99.10.22

# 备份的数据存储目录: /data/backup/mysqlbak/

# 备份策略是每天备份一次，以当天日期命名的目录，如:20170922

# 保留最近7天的备份数据，可配置

##############################################################

# Import required python libraries

import os

import sys

import time

import logging

import datetime

import subprocess

logging.basicConfig(level=logging.DEBUG,

format='[%(asctime)s] [%(levelname)s] %(message)s',

datefmt='%Y-%m-%d %H:%M:%S',

filename='/software/scrpits/backupMysql/backupMysql.log',

filemode='a')

# 配置数据库连接信息

DB_HOST = '10.99.10.22'

DB_USER = 'bakuser'

DB_USER_PASS = 'xxxxxxxx'

# 配置本地保留多少天的数据备份,默认保留7天

DataSave = 7

# 配置备份的基础目录

BackupPath = '/data/backup/mysqlbak/'

DayTime = time.strftime('%Y%m%d')

TodayBackupPath = BackupPath + DayTime

def Check():

'''备份前检查，如果目录存在则退出，否则创建备份目录'''

if os.path.exists(TodayBackupPath):

res = 'The backup directory already exists: %s. exit ...' % TodayBackupPath

print res

logging.error(res)

sys.exit()

else:

os.makedirs(TodayBackupPath)

res1 = "creating backup folder %s " % TodayBackupPath

logging.info(res1)

def BackupDB():

'''备份数据库，定义备份指令，参数'''

Check()

logging.info('Start backing up the database.')

iArgs = "--slave-info --no-timestamp"

BackupCmd = "/usr/bin/innobackupex %s --host=%s --user=%s --password=%s %s " \

% (iArgs, DB_HOST, DB_USER, DB_USER_PASS, TodayBackupPath)

p = subprocess.Popen(BackupCmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True)

stdout,stderr = p.communicate()

logging.info(stdout)

logging.info(stderr)

logging.info('The database backup is complete')

def GetTimePoint(days):

''' 返回需要删除的时间点 '''

CurrTime = time.time()

DelTime = 3600*24*int(days)

TimePoint = CurrTime - DelTime

return TimePoint

def CheckDir(cdir):

''' 删除文件夹的函数 '''

try:

if os.path.isdir(cdir):

os.rmdir(cdir)

s = 'remove dir %s succ ...' % cdir

logging.info(s)

except Exception as e:

s = 'remove dir %s FAIL !!! %s' % (cdir, e)

logging.error(s)

def CleanOld(beforeTime, path):

''' 遍历备份目录，获取目录mtime时间,比对时间戳，删除之前目录 '''

logging.warn('Start cleaning up old backup data...')

for eachdir in os.listdir(path):

f = path + eachdir

lastMtime = os.stat(f).st_mtime

if lastMtime <= beforeTime:

CheckDir(f)

if __name__ == '__main__':

BackupDB()

t = GetTimePoint(DataSave)

CleanOld(t, BackupPath)