python脚本按表备份MySQL数据库

脚本功能：

1.自动创建备份目录

2.自动备份数据库的每个表,并压缩

3.备份结果，邮件通知联系人

4.脚本内容

#!/usr/bin/env  python# --*-- coding:UTF-8 --*--# Create by JIANGLEI.YU on 2016/04/21# 多表自动备份ok。发送邮件失败。import  MySQLdbimport  sysimport  osimport  datetimeimport  smtplibfrom    email.mime.text   import MIMETextimport  sys# Define Mysql EnvironmentsHostname='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 EnvironmentsHost='smtp.exmail.qq.com'Port=25sender='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 eprint "邮件发送失败！"def backup_failed():global msgmsg = MIMEText('数据库单表备份失败')msg['subject'] = '数据库单表备份失败'msg['From'] = sendermsg['To'] = reciversemail()sys.exit(2)def backup_sucess():global msg msg = MIMEText(('数据库单表备份成功,共备份%d张表,共用时%.2f分钟.') % (tables_count,backup_period) ,  'plain','utf-8' ) msg['subject'] = '数据库单表备份成功'  msg['From'] = sendermsg['To'] = reciversemail()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 eprint "连接数据库失败"backup_failed()cursor.execute('show tables')f = cursor.fetchall()list_status = []for table in f:# print tablefor 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_counttables_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_periodbackup_period = ((end_time - start_time).seconds)/60.0backup_sucess()else:backup_failed()cursor.close()db.close()if __name__ ==  '__main__':main()