数据库的备份与同步方案

前言※

数据的重要性不用多说，所以需要定时做好数据备份工作。

备份※

针对自己服务器上的一些网站服务，我使用 python 进行定期备份，并且删除超过指定天数的备份。

# -*- coding:utf-8 -*-
import os
import time
import shutil
import schedule
from datetime import datetime,timedelta
import zipfile
from bkfile import *

DB_HOST = ''
DB_USER = ''
DB_USER_PASSWD = ''
BACKUP_PATH = ''
DB_NAME = []
day=3

class Run_backups:
    def __init__(self):
        self.DATETIME = time.strftime('%Y%m%d-%H%M%S')
        self.TODAYBACKUPPATH = BACKUP_PATH + '/' + self.DATETIME
        #创建备份文件夹
        if not os.path.exists(self.TODAYBACKUPPATH):
            os.makedirs(self.TODAYBACKUPPATH)
        self.run()

    def run_backup(self):
        for dbname in DB_NAME:
            dumpcmd = "mysqldump -u" + DB_USER + " -p" + DB_USER_PASSWD + " " + dbname + " > " + self.TODAYBACKUPPATH + "/" + dbname + ".sql"
            os.system(dumpcmd)
   
    #执行压缩的函数
    def run_tar(self):
        make_zip(self.TODAYBACKUPPATH,BACKUP_PATH+'/'+str(self.DATETIME)+'.zip')
        #删除备份文件夹
        shutil.rmtree(self.TODAYBACKUPPATH)
   
    #备份数据库文件存在就执行备份和压缩，否则退出
    def run(self):
        self.run_backup()
        self.run_tar()
            
def rmAssignDir(path, day=3):
    dirs = os.listdir(path)
    current_date = datetime.utcnow()+timedelta(hours=8)
    for dir in dirs:
        _date = dir.split('-')[0]
        _date = datetime.strptime(_date,"%Y%m%d")+timedelta(days=day)
        if _date < current_date:
            os.remove(path+'/'+dir)
            print('已删除超过%s 天的备份：  %s'%(day,path+dir))
                

def make_zip(source_dir, output_filename):
    zipf = zipfile.ZipFile(output_filename, 'w')
    dirlist = os.listdir(source_dir)
    for file in dirlist:
        zipf.write(os.path.join(source_dir, file))
    zipf.close()


def job(path, day):
    Run_backups()
    rmAssignDir(path, day)

#定时任务
def runSchedule(job, path, day):
    schedule.every().day.at("23:00").do(job, path, day)
    # 备份图片
    schedule.every().day.at("23:30").do(runbkfile)

def runbackup():
    #打印信息
    runSchedule(job, BACKUP_PATH, day)
    while True:
        schedule.run_pending()
        time.sleep(1)

恢复※

由于备份的是.sql文件所以登录 mysql 命令行直接恢复即可。

mysql  -u root -p;

set dbname;

use names utf8;

source  <sql 文件位置>;