使用xtrabackup備份mysql數據庫

抓取服務器有點複雜，可能熟悉bash的話是不用python這麼複雜。下面是python腳本：

#!/root/42/pythons/p35/bin/python
# -*- coding: utf8 -*-

#安裝依賴
#pip install paramiko
#pip install pysftp

#crontab每天4點定時執行
#0 4 * * * root (/root/42/pythons/p35/bin/python /root/42/script/main.py >> /data/backup/mysql/pythonOut.txt)

import pysftp,os,time,datetime,shutil

class FTP(object): 

    def __init__(self):
        self.saveDays = 30
        self.localBackupBaseDir = '/data/backup/mysql'
        self.hosts = [{'host':'11.22.33.44','username':'root','password':'yourPass','port':22,
                     'backupBaseDir':'/data/backup/xtrabackup'
                     }
                    ]
        
    def rmDir(self,days,fileList,localBackupDir,host):
        ts = int(time.time())-3600*24*days
        dt = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d_%H-%M')
        for d in fileList:
            if d<dt:
                shutil.rmtree(os.path.join(localBackupDir, d))
                self.log42(host+' '+d+' removed successfully')
        
    def fetchDir(self,host):
        self.log42(host['host']+' start') 
        localBackupDir = os.path.join(self.localBackupBaseDir, host['host'])
        if not os.path.exists(localBackupDir):
            os.makedirs(localBackupDir)
        files = os.listdir(localBackupDir)
        dirs = []
        for f in files:
            if os.path.isdir(os.path.join(localBackupDir, f)):
                dirs.append(f)
        
        try:
            cnopts = pysftp.CnOpts()
            cnopts.hostkeys.load('/root/.ssh/known_hosts')
            with pysftp.Connection(host['host'], username=host['username'], password=host['password'],
                                   port=host['port'],default_path=host['backupBaseDir'],cnopts=cnopts) as sftp:
                remoteFiles = sftp.listdir(host['backupBaseDir'])
                remoteDirs = []
                for f in remoteFiles:
                    if sftp.isdir(os.path.join(host['backupBaseDir'],f)):
                        remoteDirs.append(f)
#                     print(remoteDirs)
                for d in remoteDirs:
                    if d not in dirs:
                        try:
                            sftp.get_r(d,localBackupDir)  
                            self.log42(host['host']+' '+d+' done')
                        except:
                            shutil.rmtree(os.path.join(localBackupDir, d))
                            self.log42(host['host']+' '+d+' failed')                        
                
            self.log42(host['host']+' done')  
            self.rmDir(self.saveDays, dirs,localBackupDir,host['host'])
        except pysftp.SSHException as err:
            print('Error: {0}'.format(err))
        except:
            self.log42(host['host']+' failed')        
            print ("Unexpected error:", sys.exc_info()[0])
            raise

    def log42(self,logText):
        ts = int(time.time())
        dt = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
        with open(os.path.join(self.localBackupBaseDir,dt[0:7]+".log"), "a") as myfile:
            myfile.write(dt+" "+logText+"\n")
            
    def runTask(self):
        for host in self.hosts:
            self.fetchDir(host)
                


def main():
    myFtp = FTP()
    myFtp.runTask()


if __name__ == '__main__':
    main()

新增服務器時，需要現在命令行用ssh連接一次，這樣會在/root/.ssh/known_hosts中產生hostkey。如果沒有hostkey，corntab執行腳本時會報Error: No hostkey for host error.

本文更新於 2017/09/25。