Python script backs up MySQL database by table

Scripting function:

1. Automatically create backup directory

two。 Automatically back up each table in the database and compress

3. Backup results, email notification contact

4. Script content

#! / usr/bin/env python#-- *-- coding:UTF-8-- *-- # Create by JIANGLEI.YU on 2016Universe 04Universe automatically backs up ok. Failed to send mail. 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 "failed to send mail!" Def backup_failed (): global msgmsg = MIMEText ('database single table backup failed') msg ['subject'] =' database single table backup failed 'msg [' From'] = sendermsg ['To'] = reciversemail () sys.exit (2) def backup_sucess (): global msgmsg = MIMEText ((' database single table backup succeeded, total d tables backed up% .2f min.)% (tables_count,backup_period), 'plain' 'utf-8') msg [' subject'] = 'database single table backup succeeded' 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 Ecursor.execute # print eprint "failed to connect to the database" backup_failed () cursor.execute ('show tables') f = cursor.fetchall () list_status = [] for table in FRV # print tablefor i in table:MYSQLDUMP_CMD = MYSQLDUMP +'-h' + Hostname +'- u' + Username +'- p' + Password +'+ Database +'+ I +'+'|'+ GZIP +'>'+ Destination_dir + Database +'-'+ timestamp+ 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 ()