Xtrabackup备份MySQL

使用mysqldump进行数据库或表的备份非常方便，操作简单使用灵活，在小数据量的备份和恢复时间可以接受，如果数据量较大，mysqldump恢复的时间会很长而难以接受。 xtrabackup是一款高效的备份工具，备份时并不会影响原数据库的正常更新。

xtrabackup安装

mkdir xtrbackup; cd xtrbackup
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
wget https://www.percona.com/downloads/percona-toolkit/3.0.6/binary/redhat/7/x86_64/percona-toolkit-3.0.6-1.el7.x86_64.rpm
yum install ./*.rpm 

创建备份用户及目录

#mysql备份用户
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

#备份目录
mkdir /opt/bak/mysql -p

全量备份

1、备份阶段（备份文件会存储在一个以备份时间命名的子目录下）

innobackupex --host=127.0.0.1 --port=3306 --user=bkuser --password=123456 /opt/bak/mysql/

2、准备阶段

创建备份后，备份数据还处于不可用状态。因为redo log中可能存在未提交的事务和已经提交的事务，需要通过准备阶段使备份数据达到一致状态；如果状体为'completed OK'，则表明innobackupex执行完了所有所需的操作，数据达到一致状态

innobackupex --apply-log /opt/bak/mysql/2018-01-13_14-07-05

3、恢复阶段

恢复的过程中，datadir目录必须为空，如果不为空，innobackupes --copy-back将不会复制

cd /var/lib/mysql;rm -rf ./*

在恢复过程中，数据库需要处于关闭的状态（导入部分备份除外)

systemctl stop mysqld

innobackupex将所有的数据相关的文件复制到服务器中的datadir目录

innobackupex --copy-back --defaults-file=/opt/app/mysql/my.cnf /opt/bak/mysql/2018-01-13_14-07-05

复制完成后，文件属性不会改变，大多数情况下，在启动MySQL数据库之前，需要修改文件的所有权

chown -R mysql.mysql /var/lib/mysql

启动mysql

systemctl start mysqld

建议与提醒

• 请使用高版本的innobackupex
• 建议远程备份
• 尽量不要使用innbackupex自带的增量备份，恢复比较麻烦容易出错
• 备份结束时，请立即apply-log,这样能够知道备份集是否可用，如果出问题，还可以立即再备份一次或报告出来

#!/usr/bin/python
# coding=utf-8
# Author:heboan Date:2018-04-16

import os
import subprocess
from datetime import date
import re
import tarfile
import smtplib
from email.mime.text import MIMEText

class Bkdb:
    host = 'localhost'
    port = 3306
    user = 'bkuser'
    passwd = 'xxxxxx'
    socket = '/opt/app/mysql-5.6.34/tmp/mysql.sock'
    bk_paths = ['/opt/bak', '/opt/bak/tgz']
    bk_date = date.today().isoformat()   #2018-04-16
    bk_log = bk_paths[1] + '/' + bk_date + '.log' 
 
    def __create_bkdir(self, bk_paths):
        '''创建备份目录'''
        for bk_path in bk_paths:
            if not os.path.exists(bk_path):
                os.makedirs(bk_path)
                subprocess.call('chown -R heboan.heboan {}'.format(bk_path), shell=True)

    def __backup(self):
        '''备份'''        
        subprocess.call('innobackupex --host={0} 
                        --port={1} 
                        --user={2} 
                        --password={3} 
                        --socket={4} {5} >{6} 2>&1'.format(Bkdb.host, Bkdb.port, Bkdb.user, Bkdb.passwd, Bkdb.socket, Bkdb.bk_paths[0], Bkdb.bk_log),shell=True)
        with open(Bkdb.bk_log, 'r') as f:
            lines = f.readlines()
            last_line = lines[-1].strip('
')
            if last_line[-3:-1] == 'OK':
                #print '备份完成'
                return True
            else:
                return False

    def __apply(self, is_ok):
        '''确定备份是否可用'''
        if is_ok:
            dirs = os.listdir(Bkdb.bk_paths[0])
            for dir in dirs:
                if Bkdb.bk_date in dir:
                    subprocess.call('innobackupex 
                                    --apply-log {0}/{1} >{2} 2>&1'.format(Bkdb.bk_paths[0], dir, Bkdb.bk_log), shell=True)
                    with open(Bkdb.bk_log, 'r') as f:
                        lines = f.readlines()
                        last_line = lines[-1].strip('
')
                        if last_line[-3:-1] == 'OK':
                            #print '备份可用'
                            return dir
                        else:
                            return False
        else:
            return False

    def __tgz(self, dir_name):
        '''备份打包'''
        if dir_name:
            try:
                os.chdir(Bkdb.bk_paths[0])
                tgz_name = Bkdb.bk_paths[1] + '/mysql_' + dir_name + '.tar.gz'
                with tarfile.open(tgz_name, 'w:gz') as f:
                    f.add(dir_name)
                #print '打包成功'
                subprocess.call('rm -rf {}'.format(Bkdb.bk_paths[0] + '/' + dir_name), shell=True)                
                size = self.__formatSize(os.path.getsize(tgz_name))
                return {"file_name": tgz_name, "file_size": size}
            except Exception as e:
                return False
        else:
            return False

    def __sendemail(self, file_info):
        '''邮件通知'''
        HOST = 'smtp.xxxxx.com'  #发件邮箱的smtp
        SUBJECT = '数据库备份报告'
        To = ['xxxxx@qq.com,xxxx@7atour.com']  #收件人，可以写多个
        FROM = 'monitor@xxxx.com'   #发件邮箱

        if file_info:
            msg = MIMEText("""
             <table width="800p" border="0" cellspacing="0", cellpadding="4">
                 <tr>
                     <td bgcolor="#CECFAD" height="20" style="font-size:14px"> 数据库备份报告</td>
                 </tr>
                 <tr>
                     <td bgcolor="#EFEBDE" height="100" style="font-size:13px">
                         备份文件名: {} <br>
                         备份文件大小: {} <br>
                         备份来源: pr-db-01(从库) <br>
                         备份状态: 成功
                     </td>
                 </tr>
            </table> """.format(file_info['file_name'], file_info['file_size']), "html", "utf-8")
        else:
            msg = MIMEText("""
             <table width="800p" border="0" cellspacing="0", cellpadding="4">
                 <tr>
                     <td bgcolor="#CECFAD" height="20" style="font-size:14px"> 数据库备份报告</td>
                 </tr>
                 <tr>
                     <td bgcolor="#EFEBDE" height="100" style="font-size:13px">
                         备份状态: 失败
                     </td>
                 </tr>
            </table> """, "html", "utf-8")

        msg['Subject'] = SUBJECT
        msg['From'] = FROM
        msg['To'] = ';'.join(To)
        try:
            server = smtplib.SMTP_SSL(host=HOST, port=465)
            #server.connect(HOST, "25")
            #server.starttls()
            server.login("monitor@agentool.com", "bt6eJtLBPz")
            server.sendmail(FROM, To, msg.as_string())
            server.quit()
            #print '发送成功'
        except Exception as e:
            #print '发送失败: ' + str(e)
            pass

    def __formatSize(self, bytes):
        bytes = float(bytes)
        kb = bytes / 1024
        if kb >= 1024:
            M = kb /1024
            if M >= 1024:
                G = M /1024
                return "{} G".format(G)
            else:
                return "{} M".format(M)
        else:
            return "{} K".format(kb)

    def go(self):
        self.__create_bkdir(Bkdb.bk_paths)
        is_ok = self.__backup()
        dir_name = self.__apply(is_ok)
        file_info = self.__tgz(dir_name)
        self.__sendemail(file_info)


bkdb = Bkdb()
bkdb.go()