Python script backs up MySQL database by table

Scripting Features:

1. Automatically create a backup directory

2. Automatically backs up each table of the database and compresses

3. Backup results, email notification contacts

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M00/7F/4F/wKioL1cZtvvDoUbzAAo7g4GrjaI962.jpg "style=" float: none; "title=" Figure 1.jpg "alt=" Wkiol1cztvvdoubzaao7g4grjai962.jpg "/>

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M01/7F/4F/wKioL1cZtv2zt8bIAAKrolPflWs714.jpg "style=" float: none; "title=" Figure 2.jpg "alt=" Wkiol1cztv2zt8biaakrolpflws714.jpg "/>

4. Script Content

#!/usr/bin/env  python# --*-- coding:utf-8 --*--# create by jianglei. yu on 2016/04/21#  Multi-table automatic backup OK. Failed to send message. 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= ' [email protected] ' pass= ' 123456 ' recivers= ' [email  Protected] ' 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  "Mail send failed! "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 msg msg = mimetext (' Database single-table backup succeeded, total%d tables, total%.2f minutes. ')  %  (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, e:# print  eprint  "Connection Database Failed" 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 () 

This article from the "Do not ask for the best, only better" blog, please be sure to keep this source http://yujianglei.blog.51cto.com/7215578/1766668

Python script backs up MySQL database by table