利用Python脚本备份mysql数据库
近期利用空余时间学习了python的一些基础内容，用来实践，做了一个Mysql备份的脚本，按日备份并打包压缩；python比原来的shell只能运行在linux下面更广泛一些，而且后期扩展也更好。
##################################
Functions:
1）按日备份数据库，并将备份文件压缩打包；
2）成功则将备份信息写入日志，失败则发邮件告警给管理员；
3）如果已经备份成功，不再重复备份；
4）稍作改动，可用于WINDOWS备份
##################################
1 #!/usr/bin/env python 2 #_*_ coding:utf-8 _*_ 3 #!/usr/bin/env python 4 #_*_ coding:utf-8 _*_ 5 #Backup with mysqldump everyday 6 #Backup Mysql can use on windows or linux 7 #But TAR Fuction can only use on linux because command different between windows and linux 8 import time 9 import datetime 10 import os 11 import conf 12 import smtplib 13 from email.mime.text import MIMEText 14 15 #返回任意一天 16 class Day(object): 17 @staticmethod 18 def any_day(add_days): 19 today = datetime.date.today() 20 utl_add_day = str(today - datetime.timedelta(days=int(add_days))) 21 return utl_add_day 22 23 class MysqlBackup(object): 24 def __init__(self,**kargs): 25 self.__host = kargs['host'] 26 self.__dbname = kargs['db'] 27 self.__username = kargs['user'] 28 self.__password = kargs['passwd'] 29 self.__port = kargs['port'] 30 31 #mysqldump备份数据库 32 def BakData(self,backup_file): 33 cmd_bak='mysqldump -u '+self.__username+' -p'+self.__password+' -h'+self.__host +' '+self.__dbname+' --single-transaction '+' > '+backup_file 34 outp = os.system(cmd_bak) 35 return cmd_bak,outp 36 37 #Linux下用tar打包压缩备份文件 38 def TarData(self,date): 39 cmd_tar='tar zcf '+conf.backup_path+self.__dbname+'_'+date+'.tar.gz '+'-C '+conf.backup_path+' '+self.__dbname+'_'+date+'.dump >> /dev/null 2>&1' 40 outp = os.system(cmd_tar) 41 return cmd_tar,outp 42 43 @property 44 def dbname(self): 45 return self.__dbname 46 47 class sendmail(): 48 def __init__(self, *args,**kargs): 49 self.SMTPserver = kargs['address'] 50 self.sender = kargs['sender'] 51 self.password = kargs['password'] 52 self.destination = args[0] 53 54 #登录邮箱发送邮件 55 def send(self,message): 56 msg = MIMEText(message,_charset='utf-8') 57 msg['Subject'] = 'Mysql Backup Failed' 58 msg['From'] = self.sender 59 msg['To'] = self.destination 60 mailserver = smtplib.SMTP(self.SMTPserver, 25) 61 mailserver.login(self.sender, self.password) 62 mailserver.sendmail(self.sender, [self.destination], msg.as_string()) 63 mailserver.quit() 64 print 'send email success' 65 66 #主函数起始 67 def main(): 68 DATA_DATE = Day.any_day(1) 69 #发件人和收件人地址 70 MAIL_USER_ADDRESS = sendmail(*conf.note_user,**conf.contact_user) 71 #初始化需要备份的数据库类 72 DB_BAK_INFO = MysqlBackup(**conf.conn_dict) 73 backup_file = conf.backup_path+DB_BAK_INFO.dbname+'_'+DATA_DATE+'.dump' 74 print backup_file 75 log_file = conf.backup_path+DB_BAK_INFO.dbname+'.log' 76 #MAIL_USER_ADDRESS.send('I send a message by Python. 你好') 77 78 #保存日志 79 with open(log_file,'a') as f: 80 f.write('\n\n ***********************\n') 81 f.write(' * '+time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())+' *\n') 82 f.write(' ***********************\n') 83 #不存在dump备份文件才备份 84 if os.path.isfile(backup_file) is False: 85 #备份 86 cmd_result = DB_BAK_INFO.BakData(backup_file) 87 print cmd_result 88 f.write('** COMMAND :'+cmd_result[0]+'\n') 89 f.write('** DATABASE : '+DB_BAK_INFO.dbname+'\n') 90 f.write('** DATA_DATE : '+DATA_DATE+'\n') 91 f.write('** RESULT(BAK) : '+('succeed\n' if cmd_result[1] == 0 else 'failed\n')) 92 #备份成功打包 93 if cmd_result[1] == 0: 94 #打包 95 tar_result = DB_BAK_INFO.TarData(DATA_DATE) 96 f.write('** COMMAND :'+tar_result[0]+'\n') 97 f.write('** RESULT(TAR) : '+('succeed' if tar_result[1] == 0 else 'failed')) 98 #打包成功删除备份文件 99 if tar_result[1] == 0: 100 os.remove(backup_file) 101 else: 102 MAIL_USER_ADDRESS.send('Mysql备份打包失败，请核查！') 103 else: 104 MAIL_USER_ADDRESS.send('Mysql数据库备份失败，请核查！') 105 else: 106 f.write('** RESULT(BAK) : ' +'%s is already exists\n' %backup_file) 107 108 #程序入口 109 if __name__ == '__main__': 110 main() 111 #bakimeeting.bakData('backup_file')
配置文件conf.py如下：
1 #!/usr/bin/env python 2 #_*_ coding:utf-8 _*_ 3 conn_dict = dict(host='127.0.0.1',user='root',passwd='12345',db='08day05',port = 33061) 4 contact_user = dict(address='smtp.163.com',sender='xxxxxx@163.com',password='xxxxxx') 5 note_user = ('xxxxxx@126.com',) 6 backup_path = "D:/KuGou/"
后期优化
多线程提高性能，自动恢复
