xtrabackup 备份mysql数据库 --python脚本

快来打我* 2022-05-09 03:16 254阅读 0赞

自己写的python备份脚本(每天凌晨00点全备，每一个小时做增量)：

[root@localhost mysql_backup]# cat ../scripts/daily_full_mysqlDateBackup.py
    # -*- coding: UTF-8 -*-
    #!/usr/bin/python
    # ===============================
    # Author: xushuo
    # Last modified: 2017-09-06
    # Filename: daily_full_mysqlDateBackup.py
    # Description: backup mysql files,base percona xtrabackup
    # ===============================
    import  datetime
    import  subprocess
    import  os
    import  sys
    import  logging
    
    #logging.basicConfig(level=logging.DEBUG,
    #       format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
    #        datefmt='%a,%d %b %Y %H:%M:%S',
    #        filename='backup.log',
    #        filemode='a')
    backuser = 'testuser'
    backpass = 'password'
    databases='\"db1 db2 db3\"'
    basedir = '/home/data_backup/mysql_backup/backup'
    tomorrowdate = datetime.date.fromordinal(datetime.date.today().toordinal()+1).strftime("%y%m%d")
    todaydate = datetime.datetime.now().strftime("%y%m%d")
    fullback_dir = '%s/%s' % (basedir, todaydate)
    cuhour = datetime.datetime.now().strftime("%H")
    increment_dir = '%s/%s' % (basedir, cuhour)
    increbase_dir = ' '
    stores = '/home/data_backup/mysql_backup'
    
    # 转储老的备份数据，移动到以当前年月日命名的文件夹内，目录如170209-bak
    def storebefore():
        suffix = (datetime.date.today() - datetime.timedelta(days = 1)).strftime("%Y%m%d") 
        storedir = "%s/%s-bak" % (stores, suffix)
        if not os.path.exists(storedir):
            os.makedirs(storedir)
    
        command = "mv %s  %s" % (basedir, storedir)
        subprocess.call(command, shell=True)
    
    
    # 删除转储目录中超过7天的备份数据
    def cleanstore():
        command = "find %s -type d -mtime +7 |xargs rm -fr" % stores
        subprocess.call(command, shell=True)
    
    
    # 备份方法，每天0点完整备份，1-23点增量备份
    def backupfull():
        commandfull = "innobackupex  --user=%s --password=%s  --databases=%s  --no-timestamp %s" % (backuser, backpass, databases, fullback_dir)
        print 'backupfull'
        subprocess.call(commandfull, shell=True)
    
    def backup():
        commandfull = "innobackupex  --user=%s --password=%s --databases=%s  --no-timestamp %s" % (backuser, backpass, databases, fullback_dir)
        if int(cuhour) == 00:
            storebefore()
            command_clear = "rm -fr %s/*" % basedir
            subprocess.call(command_clear, shell=True)
            backupfull()
        elif int(cuhour) - 1 == 0:
            increbase_dir = '%s/%s' % (basedir, str(int(cuhour) - 1))
            #满足int(cuhour) - 1 == 0 条件后 没有上个小时的增量备份 以当天的全量备份为基础 做本小时的增量备份
            commandincre_init = "innobackupex  --user=%s --password=%s --databases=%s  --no-timestamp --incremental %s --incremental-basedir=%s" % (backuser, backpass, databases, increment_dir, fullback_dir)
            subprocess.call(commandincre_init, shell=True)
            print '以当天的全量备份为基础 做本小时的增量备份'
        elif int(cuhour) - 1 > 0:   #如果存在上个小时的备份 就以上个小时的增量备份为基础 做本小时的增量备份
            increbase_dir = '%s/%s' % (basedir, str(int(cuhour) - 1))
            commandincre = "innobackupex  --user=%s --password=%s --databases=%s  --no-timestamp --incremental %s --incremental-basedir=%s" % (backuser, backpass, databases, increment_dir, increbase_dir)
            subprocess.call(commandincre, shell=True)
            print '以上个小时的增量备份为基础 做本小时的增量备份'
    
        else:
            sys.exit(0)
    
    if __name__ == '__main__':
            backup()
            cleanstore() 
            sys.exit(0)