数据库自动备份并存储至阿里云OSS

因为自己的服务器挂了一些服务并会产生一些比较有用的数据,而且最近发现服务器老被ddos攻击,所以就做了一些安全措施

1. 安装安全狗软件
2. 阿里云安全中心服务购买
3. 最重要的也是本次要介绍的就是备份数据库, 本身使用宝塔面板的自动备份数据库只能保存备份到本地服务器, 然后自己想着如果服务器真被黑了就算有备份也白搭, 所有就想着吧数据库备份给提交到其他的存储位置, 所以就有了这个方案

使用python代码备份数据库并上传到OSS, 我用的数据库版本是Mysql5.6, 代码如下

# -*- coding: utf-8 -*-
import os,time,pymysql
import schedule
import zipfile
import oss2

ip = '127.0.0.1'
userAndPwd = [
{"username" : "你的数据库账户", "pwd" : "你的数据库登录密码", "database" : "你的数据库"}
]
endpoint = "你的OSS所在位置域名"
accessKeyID = "你的accessKeyID"
accessKeySecret = "你的accessKeySecret"
bucketStr = "你的bucket"

#检索数据库
def getDatabaseNames(username, pwd, database):
    conn = pymysql.connect(host="127.0.0.1", user=username, password=pwd, database=database, port=3306)
    cur = conn.cursor()
    cur.execute('show databases;')
    dbs = cur.fetchall()
    cur.close()
    conn.close()
    return dbs

#path trim一下然后创建
def mkdir(path):
    path = path.strip()
    path = path.rstrip("\\")
    isExists = os.path.exists(path)
    if not isExists:
        os.makedirs(path)
        return True
    else:
        return False
#文件压缩
def zip_ya(startdir,file_news, files):
    z = zipfile.ZipFile(file_news,'w',zipfile.ZIP_DEFLATED) #参数一：文件夹名
    for dirpath, dirnames, filenames in os.walk(startdir):
        fpath = dirpath.replace(startdir,'')
        fpath = fpath and fpath + os.sep or ''
        # for filename in filenames:
        z.write(os.path.join(dirpath, files + '.sql'),fpath+ files + '.sql')
        os.remove(startdir+ '/'+ files + '.sql')
    z.close()
    # os.rmdir(startdir)
    time.sleep(39)
    if oosyes == "y":
        auth = oss2.Auth(accessKeyID, accessKeySecret)
        bucket = oss2.Bucket(auth, "http://"+endpoint, bucketStr)
        timestrName = files + '_' + time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time()))
        path = os.path.split(os.path.realpath(__file__))
        objectPath = path[0]+"\\"+file_news
        replacePath = objectPath.replace("\\","/")#路径修正
        result = bucket.put_object_from_file('watersql/'+timestrName+".zip",replacePath)
        print('http status: {0}'.format(result.status))
        print('request_link:'+"https://"+bucketStr+"."+endpoint+"/watersql/"+timestrName+".zip")
        os.remove(replacePath)

def mainfunation():
    timestr = time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time()))
    folder = "mysql_data_bak/" + timestr
    mkdir(folder)
    
    for m in userAndPwd:
        dbs = getDatabaseNames(m['username'], m['pwd'], m["database"])
      
        for db in dbs:
            try:
                # dbname = db[0]
                # # 排除自带的db
                # if dbname == "mysql" or dbname == "performance_schema" or dbname == "information_schema" or dbname == "sys":
                #     continue
                # 导出db
                cmd = "mysqldump -h" + ip + " -u%s -P%s -p%s %s > %s/%s.sql" % (m['username'], 30868, m['pwd'], m["database"], folder , m["database"])
                # print(cmd)
                os.system(cmd)
            except Exception as e:
                print(e)
        zip_ya(folder, folder +'.zip', m["database"] )

# schedule.every().monday.at('23:00').do(mainfunation)
# while True:
    # schedule.run_pending()
    # time.sleep(1)
#  if __name__=="__main__":
mainfunation()
print('==================success==================' +  time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time())))

以上主要代码并非是我写的, 而是我从Gitee上面找到的开源代码并进行我自行修改后完成的, 以下是该代码原型的Gitee地址

https://gitee.com/WuHaojavaCode/automatic_database_backup?_from=gitee_search