最近比较苦逼,拿着测试的钱,干着运维的活,
估计这只有小屌丝能理解。。
小屌丝表示 不服。。。。
由于要做数据备份,所以就写了一个脚本,让它自己跑着吧~~
# -*- coding:utf-8 -*-
# @Time : 2020-07-29
# @Author : Dingjs
#DB基本信息
import os
import time
import datetime
import glob
import shutil
"""
Python3.7 来执行mysql数据备份操作
自动保持7天的数据
备份的sql文件名精确到时分秒,这样即使一天备份多次,也不会进行覆盖
"""
#数据库基本信息设定
DB_HOST = '输入数据库地址'
DB_USER = '输入数据库账号'
DB_USER_PASSWORD = '输入数据库密码'
DB_PORT = '端口号3306'
#多库备份
#写法一:把需要备份的库名写到txt文件中,然后读取txt文件内的数据
#DB_NAME = '/backup/dbnames.txt'
#写法二:通过执行sql语句来匹配下载的库
# sqlStr1 = "show databases like 'iot%'"
#单库备份
DB_NAME = 'iot_test'
#备份路径
BACKUP_PATH = '../conn_DB/sql_path/'
#设置编码格式
DB_CharSet = 'utf8'
#创建datatime,作为备份文件名称
DATETIME = time.strftime('%Y%m%d')
#这里主要是做.sql文件的日期区分
DT_time = time.strftime("%Y%m%d_%H%M%S")
TODAYBACKUPPATH = BACKUP_PATH + DATETIME
#保留7天的备份文件
print ('del folder seven days ago')
folders = glob.glob('/backup/dbbackup/*')
today = datetime.datetime.now()
for item in folders:
try:
foldername = os.path.split(item)[1]
day = datetime.datetime.strptime(foldername, "%Y%m%d")
diff = today - day
if diff.days >= 7:
shutil.rmtree(item)
except:
pass
print("creating backup folder")
#查看备份文件夹是否存在,不存在,则创建
if not os.path.exists(TODAYBACKUPPATH):
os.makedirs(TODAYBACKUPPATH)
# Code for checking if you want to take single database backup or assinged multiple backups in DB_NAME.
#开始备份文件
print ("checking for databases names file.")
if os.path.exists(DB_NAME):
file1 = open(DB_NAME)
multi = 1
print("Databases file found...")
print ("Starting backup of all dbs listed in file " + DB_NAME)
else:
print ("Databases file not found...")
print ("Starting backup of database " + DB_NAME)
multi = 0
# Starting actual database backup process.
if multi:
in_file = open(DB_NAME,"r")
flength = len(in_file.readlines())
in_file.close()
p = 1
dbfile = open(DB_NAME,"r")
while p <= flength:
# 从数据库读取文件
db = dbfile.readline()
db = db[:-1]
#在python中通过cmd执行mysqldump备份语句
# dumpcmd = "mysqldump -u " + DB_USER + "-h"+DB_HOST + " -p" + DB_USER_PASSWORD + " " + db + " > " + TODAYBACKUPPATH + "/" + db + ".sql"
# os.system(dumpcmd)
#在python中通过cmd来执行mysqldump命令的另一种写法
os.system( "mysqldump -h%s -u%s -p%s %s --default_character-set=%s > %s/%s_%s.sql" % (
DB_HOST, DB_USER, DB_USER_PASSWORD, db, DB_CharSet, TODAYBACKUPPATH, DT_time, db))
p = p + 1
dbfile.close()
else:
db = DB_NAME
# dumpcmd = "mysqldump -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + TODAYBACKUPPATH + "/" + db + ".sql"
# os.system(dumpcmd)
os.system("mysqldump -h%s -u%s -p%s %s --default_character-set=%s > %s/%s_%s.sql" % (
DB_HOST, DB_USER, DB_USER_PASSWORD, db, DB_CharSet, TODAYBACKUPPATH,DT_time, db))
#结束后,打印执行状态信息
print ("Backup script completed")
print("Your backups has been created in '" + TODAYBACKUPPATH + "' directory")
在执行的过程中,我遇到了一个问题
遇到这个问题,多半是mysql配置的问题,
嗯,看一下 mysqldump.exe是否在指定路径,或者是否配置到系统环境变量
>我今天重新安装了一下mysql,这种问题就不存在了<
我贴一下执行的结果,
执行一个数据库
执行多个数据库
import os
import paramiko
def RemoteScp(host_ip, host_port, host_username, host_password, remote_file, local_file):
scp = paramiko.Transport((host_ip, host_port))
scp.connect(username=host_username, password=host_password)
sftp = paramiko.SFTPClient.from_transport(scp)
sftp.get(remote_file, local_file)
scp.close()
return ("success")
if __name__ == '__main__':
host_ip = '输入远程服务器的ip'
host_port = 22
host_username = '输入远程服务器的账号'
host_password = '输入远程服务器的密码'
remote_path = '输入要备份的文件名称:/conn_DB/test.sql'
local_path = '输入备份到本地的文件路径就名称:/DB_backup/test.sql'
RemoteScp(host_ip, host_port, host_username, host_password, remote_path, local_path)
import os
import paramiko
def RemoteScp(host_ip, host_port, host_username, host_password, remote_path, local_path):
scp = paramiko.Transport((host_ip, host_port))
scp.connect(username=host_username, password=host_password)
sftp = paramiko.SFTPClient.from_transport(scp)
try:
remote_files = sftp.listdir(remote_path)
for file in remote_files: #遍历读取远程目录里的所有文件
local_file = local_path + file
remote_file = remote_path + file
sftp.get(remote_file, local_file)
except IOError: # 如果目录不存在则抛出异常
return ("remote_path or local_path is not exist")
scp.close()
if __name__ == '__main__':
host_ip = '输入远程服务器的ip'
host_port = 22
host_username = '输入远程服务器的账号'
host_password = '输入远程服务器的密码'
remote_path = '输入要备份的文件夹目录:/conn_DB/'
local_path = '输入备份到本地的文件夹目录:/DB_backup/'
RemoteScp(host_ip, host_port, host_username, host_password, remote_path, local_path)