python / 未分类 · 2016年4月29日

python备份mysql


#!/usr/bin/env python
# -*- coding: utf-8 -*-
import os
import datetime
import glob
import time
import zipfile
import urllib
import re
import shutil
import subprocess
import sys
import commands
# by evan this one is good
#定义系统日志
def syslog(message=''):
systemlog=open(bak_log,'a')
systemlog.write(message+'\n')
systemlog.close()
def get_ip():
try:
url = "http://pv.sohu.com/cityjson"
#http://www.whereismyip.com
#http://www.ip138.com/ip2city.asp
request = urllib.urlopen(url).read()
myip = re.findall(r"\d{1,3}\.\d{1,3}\.\d{1,3}.\d{1,3}",request)
return myip[0]
except:
myip = u"获取不到外网IP,请重新运行脚本"
return myip
exit(1)
def zip_files(zip_src,zip_dest):
f = zipfile.ZipFile(zip_dest, 'w' ,zipfile.ZIP_DEFLATED)
f.write(zip_src)
f.close()
def zip_dir(dirname,zipfilename):
filelist = []
if os.path.isfile(dirname):
filelist.append(dirname)
else :
for root, dirs, files in os.walk(dirname):
for name in files:
filelist.append(os.path.join(root, name))
zf = zipfile.ZipFile(zipfilename, "w", zipfile.zlib.DEFLATED)
for tar in filelist:
arcname = tar[len(dirname):]
#print arcname
zf.write(tar,arcname)
zf.close()
def get_spid():
spid = []
zones = []
if games:
#spid = games[0].split("_")[1]
VSPDef = r"%s\data\VSPDef.txt" % games[0]
with open(VSPDef,"r") as f:
for line in f.readlines():
if "SPID =" in line:
spid = line.split("=")[-1].strip()
for zone in games:
zones.append(zone.split("\\")[-1].split("_")[-1])
else:
print u"空服务器"
exit(1)
return spid,zones
# 本机备份保留7天数据
def delOldBak(beforeTime, path = "."):
for root, dirs, files in os.walk(path):
for name in files:
f = os.path.join(root, name)
lastMTime = os.stat(f).st_mtime
if lastMTime <= beforeTime: try: if os.path.isfile(f): os.remove(f) elif os.path.isdir(f): shutil.rmtree(f) else: os.remove(f) print u"删除 {0}, 成功!".format(f) syslog("[%s]: 删除 [%s] 成功!: " % (timestat2,f)) except Exception as e: print u"删除 {0}, 失败! 错误如下:".format(f) syslog("[%s]: 删除 [%s] 失败!: " % (timestat2,f)) print e if __name__=="__main__": # 解决编码问题 #reload(sys) #sys.setdefaultencoding('utf-8') #获取本机公网ip ExtranetIP = get_ip() # 定义 timestat2 = time.strftime("%Y%m%d-%H%M%S", time.localtime()) bak_path = 'D:/gamebackup1' bakunix_path = '/cygdrive/d/gamebackup1/*' bak_log = "%s/backup.log" % bak_path games_path = 'D:/games' games = glob.glob(r"%s/*" % games_path) # 保留 7天数据 currTime = time.time() deltTime = 3600*24*7 # 获取spid spid,zones = get_spid() print spid,zones db_host="192.168.1.200" db_user="root" db_passwd="123456" db_charset="utf8" actor = "t_actor" ''' Host = "127.0.0.1", Port = 3306, DBName = "t1_actor", DBUser = "cqmaster", DBPass = "yFk1L/lNIiul9YDvcD8P6g==", ''' bak_host = { "hostname":'59.38.194.114', # 备份机器 "username":'root', # 账号 "password":'test', # 密码 "port":16247 # 端口 } #清理本地文件 #清理老文件 delOldBak(currTime - deltTime,bak_path) print u"[%s]: 备份的数据库和runtime列表:%s" % (timestat2,zones) syslog("[%s]: 备份的数据库和runtime列表:%s" % (timestat2,zones)) for zone in zones: #备份runtime #创建zip runtime_backup_name = bak_path+'/'+spid+'_runtime_'+ExtranetIP+'_'+zone+'_'+timestat2+".zip" print runtime_backup_name zip_dir(games_path+'/'+zone,runtime_backup_name) #备份mysql db_backup_name = bak_path+'/'+spid+'sql_'+ExtranetIP+'_'+zone+'_'+timestat2+".sql" print db_backup_name try: mysqldump = r"mysqldump.exe" rundump = "mysqldump.exe -a --add-locks -C -c -x -R -h%s -u%s -p%s %s%s --default_character-set=%s > %s" %(db_host, db_user, db_passwd, actor, zone[1:], db_charset, db_backup_name)
os.system(rundump)
print(rundump)
except Exception,e:
print u"[%s]: mysqldump数据库失败:%s%s" % (timestat2,actor,zone),e
#syslog("[%s]: mysqldump数据库失败:%s%s" % (timestat2,actor,z))
'''
#同步rsync文件到备份服
print u"[%s]: rsync同步数据到备份服务器:[%s]" % (timestat2,bak_host['hostname'])
syslog("[%s]: rsync同步数据到备份服务器:[%s]" % (timestat2,bak_host['hostname']))
rsync_bin = r"D:/cqAdmin/cwRsync_5.5.0/bin/rsync.exe"
rsync_option = r"-vzrtopgu --ignore-existing --progress --port="+str(bak_host['port'])+" "+bakunix_path+" root@%s::gamebackup" % bak_host['hostname']
print rsync_option
if os.path.exists(rsync_bin):
try:
rsync = os.popen("\"%s\" %s" % (rsync_bin,rsync_option))
output = rsync.read()
rsync_error = []
for num in zones:
runtime_backup_name = spid+'_runtime_'+ExtranetIP+'_'+z
if output.find(runtime_backup_name) == -1:
rsync_error.append(runtime_backup_name)
if len(rsync_error) == 0:
print u"[%s]: rsync 备份数据同步成功" % timestat2
syslog("[%s]: rsync 备份数据同步成功" % timestat2)
else:
print u"[%s]: rsync 备份数据同步失败:%s" % (timestat2,rsync_error)
syslog("[%s]: rsync 备份数据同步失败:%s" % (timestat2,rsync_error))
#判断是否成功
except:
print u"[%s]: rsync 同步执行异常" % timestat2
syslog("[%s]: rsync 同步执行异常" % timestat2)
else:
print u" rsync 客户端未安装,rsync同步失败"
syslog(" rsync 客户端未安装,rsync同步失败")
'''