环境:
centos7最小化,python2.7.5。
功能实现:
第一,用Centos7自带python2.7.5备份mysql5.7
第二,实现备份失败进行自动发送邮件
第三,对打包后的tar.gz文件进行ftp传输至目的ftp服务器。
以下是我的Pycharm结构:
main.py是主要实现功能就是备份数据库。
代码如下:
# -*- coding:UTF-8 -*-
import os
import time
import yaml
import sys
import subprocess
import myemail
import targz
import myFtp
reload(sys)
sys.setdefaultencoding('utf-8')
# 定义执行备份脚本,读取文件中的数据库名称,注意按行读写,不校验是否存在该库
def run_backup(user, dbname, todayDetail):
dumpcmd = "mysqldump -u" + user + " --default-character-set=utf8 " + dbname + " > " + todayDetail + os.sep + dbname + ".sql"
print '执行命令:' + dumpcmd
# result = os.system(dumpcmd)
result = subprocess.Popen(dumpcmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
thisErrors = result.stderr.read()
if thisErrors == '':
print '备份成功:' + dbname
else:
print '失败:' + dbname
print thisErrors
# errors.append(dbname)
errors = '数据库:' + dbname + '备份失败' + '\n' +'异常:' + thisErrors
# 备份失败则直接发送邮件
myemail.email(errors)
def mkdir(today, todayDetail, content, user, names, backpath):
# 创建备份文件夹
if not os.path.exists(todayDetail):
os.makedirs(todayDetail)
print "年月日文件夹:" + today
print "具体时间文件夹:" + todayDetail
# 备份数据库文件存在就执行备份和压缩,否则退出
if content['db_name'] == None:
print '无数据库!'
pass
else:
if len(names) != 0:
for name in names:
dbname = name['name']
print "开始备份数据库:" + (name['name'])
run_backup(user, dbname, todayDetail)
else:
print "没有发现数据库..."
pass
# 进行清理文件夹
filepath = targz.run_tar()
# ftp 上传
myFtp.upftp(filepath)
if __name__ == '__main__':
# 配置文件是config.yaml 读取出来后得到的结果赋值给变量。
filename = os.path.join(os.path.dirname(__file__), 'config.yaml').replace("\\", "/")
f = open(filename)
content = (yaml.load(f, Loader=yaml.FullLoader))['mysql']
# 从yaml配置文件中提取:用户名、密码、数据库名称(多个库分行放置)和备份的路径
host = content['db_host']
user = content['db_user']
password = content['db_password']
names = content['db_name']
backpath = content['db_backupPath']
# 创建时间
dateTime = time.strftime('%H-%M-%S')
today = backpath + os.sep + time.strftime('%Y%m%d')
todayDetail = today + os.sep + dateTime
# 创建文件夹
mkdir(today, todayDetail, content, user, names, backpath)
myemail.py模块:
# -*- coding:UTF-8 -*-
import sys
import os
import smtplib
from email.mime.text import MIMEText
import yaml
import time
import urllib2
import re
reload(sys)
def email(errors):
# 邮件发送配置:
filename = os.path.join(os.path.dirname(__file__), 'config.yaml').replace("\\", "/")
f = open(filename)
content = (yaml.load(f, Loader=yaml.FullLoader))['email']
host = content['email_smtp']
password = content['email_passwd']
sender = content['email_sender']
receivers = content['email_receivers']
port = content['email_port']
# 获取公网ip
url = urllib2.urlopen("http://txt.go.sohu.com/ip/soip")
text = url.read()
ip = re.findall(r'\d+.\d+.\d+.\d+', text)[0]
# print ip
for receiver in receivers:
receiver = receiver['name']
subject = ip + '服务器--' + time.strftime('%Y%m%d-%H点%M分%S秒') + '数据库备份异常!'
msg = MIMEText(errors)
msg['Subject'] = subject
msg['From'] = sender
msg['To'] = receiver
try:
s = smtplib.SMTP(host, port)
s.login(sender, password)
s.sendmail(sender, receiver, msg.as_string())
print '备份失败邮件已发送成功!'
except smtplib.SMTPException as e:
print errors + '发送失败' + format(e)
# -*- coding:UTF-8 -*-
import sys
import os
import time
import yaml
reload(sys)
# 执行压缩的函数
def run_tar():
filename = os.path.join(os.path.dirname(__file__), 'config.yaml').replace("\\", "/")
f = open(filename)
content = (yaml.load(f, Loader=yaml.FullLoader))['mysql']
# 备份路径
backuppath = content['db_backupPath']
compress_file = time.strftime('%Y%m%d') + ".tar.gz"
compress_cmd = "tar -czvf " + compress_file + " " + time.strftime('%Y%m%d')
# print compress_cmd
# 切入需要打包的环境下然后进行打包
os.chdir(backuppath)
os.system(compress_cmd)
print "打包%s完成!" % compress_file
# print backuppath + compress_file
# 删除备份文件夹
# remove_cmd = "rm -rf " + backuppath + os.sep + time.strftime('%Y%m%d')
# os.system(remove_cmd)
# print "删除" + backuppath + os.sep + time.strftime('%Y%m%d') + "完成!"
return str(backuppath + os.sep + compress_file)
ftp传输块:
# -*- coding:UTF-8 -*-
import sys
from ftplib import FTP
import time
import yaml
import os
reload(sys)
def upmakedir(ftp, path):
count = 1
try:
res = ftp.mkd(path)
print '建立的远程文件夹:' + res
count += 1
except Exception as e:
print '报错:' + str(e)
path = path + '-' + str(1)
res = ftp.mkd(path)
print '建立的远程文件夹:' + res
return path
def upftp(localpath):
filename = os.path.join(os.path.dirname(__file__), 'config.yaml').replace("\\", "/")
f = open(filename)
content = (yaml.load(f, Loader=yaml.FullLoader))['ftp']
# 创建一个ftp的实例
ftp = FTP(host=content['ftp_host'])
# 打开调试级别2,显示详细信息
ftp.set_debuglevel(2)
# 连接ftp服务器,需要输入IP,端口,以及超时时间
ftp.connect(host=content['ftp_host'], port=content['ftp_port'], timeout=600)
# 连接的用户名、密码
ftp.login(str(content['ftp_user']), str(content['ftp_password']))
# 建立远程文件夹,以年月日命名, 如果已存在远程目录则将抛出异常并跳过继续上传。
path = time.strftime('%Y%m%d')
# 远程创建文件夹
path = upmakedir(ftp, path)
# 缓冲
bufsize = 1024
# ftp服务器上该文件的名字,并放在当天目录下
remotepath = path + os.sep + localpath.split('/')[-1]
# print remotepath
with open(localpath, 'rb') as fp:
# 将这个文件上传
ftp.storbinary('STOR ' + remotepath, fp, bufsize)
ftp.set_debuglevel(0)
ftp.quit()
配置文件:config.yaml
# mysql数据库配置:
mysql :
# 数据库ip
db_host : localhost
# 数据库账号
db_user : root
# 数据库密码
db_password : 123456
# mysqldump 备份数据目录
db_backupPath : /backup/mysql
# 需要备份的数据库
db_name :
- name : zuida
- name : mysql
- name : 51job
# 邮箱设置:
email :
# 设置服务器
email_smtp : smtp.163.com
# 服务器端口
email_port : 25
# 口令
email_passwd : 123456
# 发送者
email_sender : ceshi@163.com
# 接收者
email_receivers :
- name : ceshi@qq.com
# - name : ceshi2@qq.com
# ftp配置项:
ftp :
# 用户名
ftp_user : admin
# 密码
ftp_password : 123456
# ftp的ip
ftp_host : 192.168.0.107
# 端口
ftp_port : 21
再讲讲我这中间遇到的问题吧:
第一就是这个mysql的全备:
mysqldump -uroot -p123456 --default-character-set=utf8 dbname > dbname.sql
1、由于有时候备份不知道为什么会备份失败,失败在于它不能完全备份,就是只有一条插入语句
2、由于有时候可能配置中把数据库名写错,会导致这个命令报错,所以经过大佬们的建议用的是subprocess库来实现。
errot = subprocess.Popen("python", stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
thisErrors = errot.stderr.read()
此处thisErrors返回就是报错,可以记录
第二个就是ftp备份,查阅了一些资料
你们可以参考这个:
https://www.cnblogs.com/gongxr/p/7529949.html
写的不是很详细,有些地方还是留了个坑,有什么疑问可以留言区讨论。