python脚本按表备份MySQL数据库
脚本功能:
成都创新互联公司是一家专注于网站制作、做网站与策划设计,龙泉网站建设哪家好?成都创新互联公司做网站,专注于网站建设10多年,网设计领域的专业建站公司;建站业务涵盖:龙泉等地区。龙泉做网站价格咨询:028-86922220
1.自动创建备份目录
2.自动备份数据库的每个表,并压缩
3.备份结果,邮件通知联系人
4.脚本内容
#!/usr/bin/env python # --*-- coding:UTF-8 --*-- # Create by JIANGLEI.YU on 2016/04/21 # 多表自动备份ok。发送邮件失败。 import MySQLdb import sys import os import datetime import smtplib from email.mime.text import MIMEText import sys # Define Mysql Environments Hostname='192.168.0.141' Username='root' Password='123456' Database='virtual' MYSQLDUMP='/usr/bin/mysqldump' GZIP='/usr/bin/gzip' timestamp=datetime.datetime.now().strftime("%Y%m%d%H%M%S") Destination_dir='/home/bak/tables/' + timestamp + '/' # Define Smtp Environments Host='smtp.exmail.qq.com' Port=25 sender='yujianglei@singulax.com' Pass='123456' recivers='jianglei.yu@foxmail.com' def main(): if os.path.exists(Destination_dir) == False: os.makedirs(Destination_dir) db_table_backup() else: db_table_backup() def email(): try: server= smtplib.SMTP() server.connect(Host,Port) server.login(sender,Pass) server.sendmail(sender,recivers,msg.as_string()) except Exception,e: print e print "邮件发送失败!" def backup_failed(): global msg msg = MIMEText('数据库单表备份失败') msg['subject'] = '数据库单表备份失败' msg['From'] = sender msg['To'] = recivers email() sys.exit(2) def backup_sucess(): global msg msg = MIMEText(('数据库单表备份成功,共备份%d张表,共用时%.2f分钟.') % (tables_count,backup_period) , 'plain','utf-8' ) msg['subject'] = '数据库单表备份成功' msg['From'] = sender msg['To'] = recivers email() def db_table_backup(): start_time=datetime.datetime.now() try: db = MySQLdb.connect(Hostname,Username,Password,Database,connect_timeout=2) cursor = db.cursor() except Exception, e: # print e print "连接数据库失败" backup_failed() cursor.execute('show tables') f = cursor.fetchall() list_status = [] for table in f: # print table for i in table: MYSQLDUMP_CMD = MYSQLDUMP + ' -h' + Hostname + ' -u' + Username + ' -p' + Password + ' ' + Database + ' ' + i + ' ' + '| ' + GZIP + ' >' + Destination_dir + Database + '-' + timestamp+ '.' + i + '.' + 'sql.gz' result = os.system(MYSQLDUMP_CMD) list_status.append(result) global tables_count tables_count = len(list_status) list_test=[0] j = set(list_status).issubset(set(list_test)) if j == True: end_time=datetime.datetime.now() global backup_period backup_period = ((end_time - start_time).seconds)/60.0 backup_sucess() else: backup_failed() cursor.close() db.close() if __name__ == '__main__': main()
网站名称:python脚本按表备份MySQL数据库
本文来源:http://myzitong.com/article/jschcc.html