[MySQL] MySQL备份方案（综述及脚本）

　　系统为centos 6.5

　　需要安装mutt和msmtp并可以发送邮件
　　需要安装python 2.6.6

　　需要安装xtrabackup

#!/usr/bin/env python
#coding:utf-8
#auther:Bran Guo
#date:10/23/2015 
#description:myql自动备份脚本，添加定时任务自动运行，不要修改mysqlbackup文件夹中的文件
#version:V1.0
import ConfigParser,os,datetime,logger
#读取配置文件
conf = ConfigParser.ConfigParser()
conf.read("mysqlbak.conf")
bakdir_new = conf.get("file_path","bakdir_new")
bakdir_last = conf.get("file_path","bakdir_last")
bak_cycle = conf.get('bak_cycle','bak_cycle')
bak_output = conf.get('log_file','bak_output')
bak_user = conf.get('mysql_set','bak_user')
bak_passwd = conf.get('mysql_set','bak_passwd')
os.environ['bakdir_new']=str(bakdir_new)
os.environ['bak_output']=str(bak_output)
os.environ['bak_user']=str(bak_user)
os.environ['bak_passwd']=str(bak_passwd)
 
#判断备份文件夹个数
dirnew_count = int(os.popen('ls %s |wc -l' % bakdir_new).read())
if dirnew_count == 0:
os.system('echo %s full backup start ---------------------------------------------------- >> $bak_output ' % datetime.datetime.now())
ret = os.system('innobackupex --user=$bak_user --password=$bak_passwd $bakdir_new &>> $bak_output') 
logger.logger(ret)
elif dirnew_count >= int(bak_cycle):
os.system('rm -rf %s/*' % bakdir_last)
os.system('mv %s/* %s' % (bakdir_new,bakdir_last))
os.system('tar zcf %s/`date +%%m-%%d-%%Y`.tar.gz %s/*' %(bakdir_last,bakdir_last))
os.system('echo %s full backup start ---------------------------------------------------- >> $bak_output ' % datetime.datetime.now())
ret = os.system('innobackupex --user=$bak_user --password=$bak_passwd $bakdir_new &>> $bak_output') 
logger.logger(ret)
else:
    full_file = os.popen('ls %s' % bakdir_new).readlines()
    for file_name in full_file :
file_list= []
file_list.append(file_name)
    basedir = file_list[-1]
os.system('echo %s incremental backup start ---------------------------------------------------- >> $bak_output ' % datetime.datetime.now())
os.environ['basedir']=str(basedir)
ret = os.system('innobackupex --user=$bak_user --password=$bak_passwd --incremental $bakdir_new --incremental-basedir=$bakdir_new/$basedir &>> $bak_output')
logger.logger(ret)

#!/usr/bin/python
#coding:utf-8
#auther:Bran Guo
#date:10/26/2015
#version:V1.0
import os,ConfigParser
#读取配置文件
conf = ConfigParser.ConfigParser()
conf.read("mysqlbak.conf")
bakdir_new = conf.get('file_path','bakdir_new')
bakdir_last = conf.get('file_path','bakdir_last')
mysql_data_path = conf.get('mysql_set','mysql_data_path')
dirlist_new = os.popen('ls %s' % bakdir_new).readlines()
dirlist_last = os.popen('ls %s' % bakdir_last).readlines()
#备份函数
def restore(dir_count=len(dirlist_new),bakdir=bakdir_new,dirlist=dirlist_new):
if dir_count == 1:
print dir_count,bakdir,dirlist
ret=os.system('innobackupex --apply-log --redo-only %s/%s' %(bakdir,dirlist[0]))
if ret != 0:
print "prepare failed"
exit()
else:
ret=os.system('innobackupex --apply-log --redo-only %s/%s' %(bakdir,dirlist[0]))
count = 1
while (count < dir_count):
incrdir = dirlist[count]
basedir = dirlist[0]
os.environ['incrdir'] = str(incrdir)
os.environ['basedir'] = str(basedir)
os.environ['bakdir'] = str(bakdir)
ret=os.system('innobackupex --apply-log --redo-only $bakdir/$basedir  --incremental-dir=$bakdir/$incrdir')
if ret != 0:
print "prepare failed"
count +=1
os.system('service mysqld stop')
os.system('rm -rf %s' % mysql_data_path)
os.system('innobackupex --copy-back %s/%s' %(bakdir,dirlist[0]))
os.system('chown -R mysql:mysql %s' % mysql_data_path)
os.system('service mysqld start')
#输入菜单
while True:
user_input = raw_input('Command (m for help):').strip()
if user_input == 'm':
print '''Warning: The following command will remove mysql datafile, should be used with caution.
rrestore to recent backup
sshow backup list
nchoose backup restore from new
lchoose backup restore from last
qquit
''',
elif user_input == 'r':
restore()
elif user_input == 'q':
exit()
elif user_input == 's':
print 'New:'
os.system('ls %s' % bakdir_new)
print 'Last'
os.system('ls %s' % bakdir_new)
elif user_input == 'n':
os.system('ls -l %s' % bakdir_new)
while True:
user_input = raw_input('Please enter line number restore:').strip()
if user_input == 'q':
exit()
try:
line_number = int(user_input)
dir_count = len(dirlist_new)
if line_number <= dir_count:
restore(line_number)
else:
print '''Please enter a number less then line or "q".'''
except ValueError:
print '''Please enter a number less then line or "q".'''
elif user_input == 'l':
os.system('ls -l %s' % bakdir_last)
while True:
user_input = raw_input('Please enter line number restore:').strip()
if user_input == 'q':
exit()
try:
line_number = int(user_input)
dir_count = len(dirlist_last)
if line_number <= dir_count:
restore(line_number,bakdir_last,dirlist_last)
else:
print '''Please enter a number less then line sum or "q".'''
except ValueError:
print '''Please enter a number less then line sum "q".'''

#!/usr/bin/env python
#coding:utf-8
#auther:Bran Guo
#date:11/11/2015 
#description:myql备份文件抓取脚本，添加定时任务在mysql备份完成后自动运行，不要修改mysqlback文件夹中的文件
#version:V1.0
import os,ConfigParser,logger
conf = ConfigParser.ConfigParser()
conf.read("/home/dev/scripts/mysql/mysqlbak.conf")
src_dir = conf.get('source','src_dir')
dst_user = conf.get('destination','dst_user')
dst_ip = conf.get('destination','dst_ip')
dst_dir = conf.get('destination','dst_dir')
rsync_log = conf.get('log_file','rsync_log')
bak_cycle = conf.get('bak_cycle','bak_cycle')
his_dir = conf.get('history','his_dir') 
ssh_port = conf.get('destination','ssh_port')
os.environ['src_dir']=str(src_dir) 
os.environ['dst_dir']=str(dst_dir)
os.environ['dst_user']=str(dst_user)
os.environ['dst_ip']=str(dst_ip)
os.environ['rsync_log']=str(rsync_log)
os.environ['ssh_port']=str(ssh_port)
  
os.system('echo `date` sync mysql back file start ------------------------------------ >> $rsync_log')
ret = os.system('''rsync -Paz --delete -e "ssh -p $ssh_test"  $ddst_page@$dst_ip:$dst_dir $src_dir &>> $rsync_log''')
logger.logger(ret)
dirnew_count = int(os.popen('ls %s |wc -l' % dst_dir).read())
if dirnew_count >= int(bak_cycle):
    os.system('tar zcf %s/`date +%%m-%%d-%%Y`.tar.gz %s/new/*' %(his_dir,src_dir))
else:
    exit()

#!/usr/bin/python
import datetime,os,ConfigParser
conf = ConfigParser.ConfigParser()
conf.read("mysqlbak.conf")
bak_log = conf.get("log_file","bak_log")
mail_addr = conf.get('mail','mail_addr')
logfile = 'backup.log'
def logger(ret):
if ret == 0:
echo_line = "%s\tBackup mysql data file succes\n" % datetime.datetime.now()
else:
echo_line = "%s\tBackup mysql data file failed, plz check bakoutput.log\n" % datetime.datetime.now()
os.system('echo "%s" | mutt -s "Backup mysql failed" %s' %(echo_line,mail_addr))
f = file(bak_log,'a')
f.write(echo_line)
f.flush()
f.close()

#备份文件路径
[file_path]
bakdir_new = /home/dev/mysqlbackup/new
bakdir_last = /home/dev/mysqlbackup/last
#备份周期
[bak_cycle]
bak_cycle = 7
#日志文件位置
[log_file]
bak_output = bakoutput.log
bak_log = backup.log
#MySQL设置
[mysql_set]
mysql_data_path = /var/lib/mysql
bak_user = user
bak_passwd = password
#备份失败发送邮件地址
[mail]
mail_addr = xxxxxx@xxx.com