因为自己的服务器挂了一些服务并会产生一些比较有用的数据,而且最近发现服务器老被ddos攻击,所以就做了一些安全措施
- 安装安全狗软件
- 阿里云安全中心服务购买
- 最重要的也是本次要介绍的就是备份数据库, 本身使用宝塔面板的自动备份数据库只能保存备份到本地服务器, 然后自己想着如果服务器真被黑了就算有备份也白搭, 所有就想着吧数据库备份给提交到其他的存储位置, 所以就有了这个方案
使用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