MySQL备份与恢复脚本

# -*- coding: utf-8 -*-import osimport sysimport MySQLdbimport datetimeimport shutilimport tracebackUSER = "root"HOST = "localhost"PASSWD = "123456"BACKUP_DAYS = 5BACKUP_DIR  = "/root/backup"class BackUpDb(object):    def __init__(self,host,user,passwd):        self.host = host        self.user = user        self.passwd = passwd        self.db_connect()    def db_connect(self):#连接数据库        self.connect = MySQLdb.connect(            host = self.host,            user = self.user,            passwd = self.passwd            )    def set_autocommit(self, status):        sql = "SET autocommit=%d" % status        self.db_query(sql)    def db_close(self):        self.connect.commit()    #innodb表需要commit        self.connect.close()    def db_query(self, sql, result=0):        db_cursor = self.connect.cursor()        mod_num = db_cursor.execute(sql)        ret = db_cursor.fetchall()        return ret def backup_db():    conn = BackUpDb(HOST,USER,PASSWD)    exclude_dbs = [            "information_schema",            "mysql",            "performance_schema",            "test",    ]    sql = "show databases;"    query_result = conn.db_query(sql)    conn.db_close()        dbs = []    for db_name in query_result:        if db_name[0] not in exclude_dbs:            dbs.append(db_name[0])        for db in dbs:        print 'start backupdb:%s'%db        backup_file_name = "%s_%s_backup.sql"%(db,datetime.datetime.now().strftime("%Y%m%d%H%M%S"))        d_dir = os.path.join(BACKUP_DIR,datetime.datetime.now().strftime("%Y%m%d"))        if not os.path.exists(d_dir):            try:                os.makedirs(d_dir)            except:                traceback.print_exc()                backup_file_name = os.path.join(d_dir,backup_file_name)                command = "mysqldump --default-character-set=utf8 -u%(user)s -p%(pwd)s -B %(db)s -h %(host)s >%(f)s"%{            "user":USER,            "db":db,            "host":HOST,            "f":backup_file_name,            "pwd":PASSWD,        }        print command        os.system(command)        os.system("gzip %s"%backup_file_name)                print 'end backupdb:%s\n'%db        print 'check and delete backups'    if os.path.exists(BACKUP_DIR):        count_dirs = 0        all_backup_day_dirs = os.listdir(BACKUP_DIR)        all_backup_day_dirs.sort()        for f in all_backup_day_dirs:            if os.path.isdir(os.path.join(BACKUP_DIR,f)):                try:                    d = datetime.datetime.strptime(f,"%Y%m%d")                    count_dirs += 1                except:                    traceback.print_exc()                #删除多余操作的文件备份        if count_dirs>BACKUP_DAYS:            for i  in range(count_dirs - BACKUP_DAYS):                try:                    last_d = (datetime.date.today() - datetime.timedelta(days=BACKUP_DAYS)).strftime("%Y%m%d")                    if all_backup_day_dirs[i] < last_d:                        d = os.path.join(BACKUP_DIR,all_backup_day_dirs[i])                        print "last_d='%s',delete dir ='%s'"%(last_d,d)                        shutil.rmtree(d)                except:                    traceback.print_exc()    def restore_db():    files = os.listdir(".")    for f in files:        if os.path.isfile(f) and f.endswith("_backup.sql"):            db_name = os.path.split(f)[1][:-11]            print 'start restore:%s'%db_name            command = u"mysql --default-character-set=utf8 -h %(host)s -u%(user)s -p%(pwd)s < %(f)s"%{                "f":f,                "user":USER,                "pwd":PASSWD,                "host":HOST,            }            print command            os.system(command)            print 'end restore:%s\n'%db_name        if __name__ == "__main__":    u"""        备份的时候        1、FLUSH TABLES WITH READ LOCK;        2、执行脚本        3、unlock        4、/sbin/chkconfig --level 345 mysqld off    """    if len(sys.argv)<2:         raise Exception("command like this python backup_db backup|restore")        command = sys.argv[1]    if command not in ["backup","restore"]:        raise Exception("command like this python backup_db backup|restore")        if command == "backup":        backup_db()