xtrabackup備份mysql5.7_xtrabackup2.4_MySQL5.7備份與恢複

xtrabackup的主要優點：

1,實體備份，備份速度快

2,熱備份、無需停機

3,恢複速度快

4.自動會對備份檔案進行驗證

1. xtrabackup 工具的安裝

#1.1 安裝依賴包

#No package perl-Digest-MD5 available. yum install -y rsync perl l perl-Digest-MD5 perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

#rpm -ich libev-4.15-1.el6.rf.x86_64.rpm 可以不安裝

#error: Failed dependencies:libev.so.4()(64bit) is needed，添加 --nodeps

rpm -ivh percona-xtrabackup-24-2.4.21-1.el6.x86_64.rpm --force --nodeps

-------------------------------------

#常見報錯

(1) libev.so.4()(64bit) is needed by percona-xtrabackup-24-2.4.4-1..el6.x86_64

解決辦法：

rpm -ivh ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm

(2)libnuma.so.1()(64bit) is needed by percona-xtrabackup-24-2.4.4-1.el6.x86_64

解決辦法：

rpm -ivh http://mirror.centos.org/centos/6/os/x86_64/Packages/numactl-2.0.9-2.el6.x86_64.rpm

#參考Centos7.4下MySql5.7安裝Percona XtraBackup備份

1、安裝Percona XtraBackup

--安裝yum 源

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

--安裝XtraBackup 2.4版本的

yum install -y percona-xtrabackup-24.x86_64

#1.2下載下傳及安裝說明

#目前測試使用的資料庫為MySQL5.7 是以下載下傳2.4版本

#另外，2.4.10及以後版本中，對libgcrypt進行了區分，是以也需要查詢自己伺服器的libgcrypt版本，然後選擇對應的安裝包

[[email protected] ~]# yum list installed|grep libgcrypt libgcrypt.x86_64 1.5.3-14.el7 @anaconda

#去percona官網下載下傳對應版本的安裝包 https://www.percona.com/downloads , 需要根據資料庫的版本選擇對應的xtrabackup的安裝包，選擇Percona XtraBackup.

--實際找到的包

wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm

wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.18/binary/tarball/percona-xtrabackup-2.4.18-Linux-x86_64.libgcrypt153.tar.gz

wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.18/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.18-r29b4ca5-el7-x86_64-bundle.tar

二進制包解壓後即可使用，rpm包下載下傳解壓後 rpm -ivh 安裝即可。操作均很簡單，此處略過

xtrabackup通常使用 innobackupex指令，可以使用 innobackupex --help 指令檢視參數及說明。

其中常用參數及說明如下：

--defaults-file 待備份mysql執行個體的配置檔案 --user=USER 備份使用者，預設為目前系統使用者,建議使用備份專用賬号 --password=PASSWD 備份使用者密碼 --port=PORT 資料庫端口，預設3306 --host=HOST 需備份資料庫的IP --apply-log 前滾、復原日志，恢複前用 --database 指定需要備份的資料庫，多個資料庫之間以空格分開 --copy-back 将備份資料複制到目标路徑 --incremental 增量備份，後面接的參數值為指定的增量備份的路徑 --incremental-basedir=DIRECTORY 增量備份時使用指向上一次的增量備份所在的目錄 --incremental-dir=DIRECTORY 增量備份還原的時候用來合并增量備份到全量，用來指定全備路徑 --redo-only 對增量備份進行合并 --rsync 加快本地檔案傳輸，适用于non-InnoDB資料庫引擎。不與--stream共用 --no-timestamp 生成的備份檔案不以時間戳為目錄

1.3 建立備份賬号

#備份的賬号建議單獨建立，并授予相關權限*********************

CREATE USER 'backup'@'localhost' IDENTIFIED BY '[email protected]'; GRANT SUPER,RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'backup'@'localhost'; FLUSH PRIVILEGES;

#參考

grant reload,lock tables,replication client,create tablespace,process,super on *.* to [email protected]'localhost' identified by '[email protected]';

2. 全量備份

2.1 本地全量備份，不壓縮

#建立備份結果的目錄 mkdir -p /app

rm -rf /app

#如果存在keygen，可跳過 ssh-keygen ssh-copy-id -i [email protected]'target_ip'

#遠端機器建立目錄

mkdir -p /app/backup/

#之後就可以将類似于本地備份的模式将備份結果發送到遠端機器

#如果存在大表或超大事務運作時不建議使用/tmp目錄

/usr/bin/innobackupex --defaults-file=/etc/my.cnf --socket=/var/lib/mysql/mysql.sock --port=3306 --user=backup --password='[email protected]' --stream=tar /tmp --no-timestamp | ssh [email protected]'192.168.92.18' "cat - > /app/backup/remote_backup.tar"

---------------------------------------------------------------------

--備份檔案中删除已表空間加密的表t1,tab_type或删除加密表所在的庫，仍然無法恢複

#檢視所有加密表(表空間):t1,tab_type

select engine,TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS from information_schema.tables where create_options like '%ENCRYPTION%="Y"%';

cd /home/backup

cp -R 2020-05-29_12-37-02 2020-05-29_12-37-02-bak

cd /home/backup/2020-05-29_12-37-02

rm -rf test

--cd /home/backup/2020-05-29_12-37-02/test

--rm -rf t1.frm t1.ibd tab_type.frm tab_type.ibd

systemctl stop mysqld

--先應用日志

innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log /home/backup/2020-05-29_12-37-02/

--再拷貝資料

innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /home/backup/2020-05-29_12-37-02/

---------------------------------------------------------------------

3. 增量備份

mysql -uroot -pmysql

use test;

create table test.t1(id int,name varchar(200));

insert into test.t1 select 1,'n1';

3.1 第一次增量備份

#可以先根據2.1的本地全備的基礎上進行增量備份,首次增量備份腳本：

#此時，備份的結果放在/app/backup目錄下，由于沒指定no-timestamp參數，備份的結果存在在以三期時間命名的目錄下：

/usr/bin/innobackupex --defaults-file=/etc/my.cnf --socket=/var/lib/mysql/mysql.sock --port=3306 --user=backup --password='[email protected]' --incremental /app/backup/ --incremental-basedir=/app/all_backup

注：增量備份僅能應用于InnoDB或XtraDB表等事務表，對于MyISAM表而言，執行增量備份時依舊進行全量備份

3.2 第二次增量備份

#第二次增量備份是在第一次增量備份的基礎上進行，是以incremental-basedir參數内容指定為上一次增量備份的目錄即可

#完成後生成一個新的目錄

3.3 第三次增量備份

#inc_backup.sh方式調用

sh inc_backup.sh 2020-11-25_09-47-12

#輸出最後一個日期的檔案夾

echo `ls -lr /app/backup |grep -v "total" |awk '{print $9}' |awk 'NR==1{print}'`

echo `ls -lr /app/backup |grep -v "total" |awk '{print $9}' |awk 'NR==1'`

vi inc_backup.sh

v_incremental_basedir=`ls -lr /app/backup |grep -v "total" |awk '{print $9}' |awk 'NR==1{print}'`

--------------

#參數方式調用

sh test.sh `ls -lr /app/backup |grep -v "total" |awk '{print $9}' |awk 'NR==1{print}'`

#test.sh如下

4. 恢複

4.1 基于全量備份的恢複

4.1.1 回放日志

ps -ef | grep mysql

kill -9

#備份後的檔案不能直接用作恢複，還需要先使用apply-log 做前滾和復原

cd /var/lib/mysql

#目錄需要為空，rm -rf `ls | grep -v mysqlmaster-bin`

rm -rf /var/lib/mysql /usr/bin/innobackupex --apply-log --redo-only /app/all_backup /usr/bin/innobackupex --apply-log --redo-only /app/all_backup --incremental-dir=/app/backup/2020-11-25_08-38-21

如果基于多次增量備份的恢複，則在全量備份及第一次增量備份的基礎上繼續合并後續的備份即可

/usr/bin/innobackupex --apply-log --redo-only /app/all_backup --incremental-dir=/app/backup/2020-11-25_08-41-19

/usr/bin/innobackupex --apply-log --redo-only /app/all_backup --incremental-dir=/app/backup/2020-11-25_09-47-12

/usr/bin/innobackupex --defaults-file=/etc/my.cnf --copy-back /app/all_backup

chown -R mysql:mysql /var/lib/mysql

mysqld_safe --defaults-file=/etc/my.cnf > /dev/null 2>&1 &

ps -ef | grep mysql

mysql -uroot -pmysql

show databases;

use test;

show tables;

------------------------------------------------------------------------------------

#全量備份mysql shell腳本1

mkdir -p /opt/script

sh /opt/script/fullbak.sh

vi /opt/script/fullbak.sh

#!/bin/bash

# mysql full back

#date 2020.11.25

#author:zh

time=`date +%F`

logfile="/data/bakup/mysql_full_bak_time.log"

fullbakup_logfile="/data/bakup/fullbakup_logfile.log"

mysql_bakdir="/data/bakup"

user=backup

password='[email protected]'

[ ! -d $mysql_bakdir ] && mkdir -p $mysql_bakdir

[ ! -e $logfile ] && touch $logfile

[ ! -e $fullbakup_logfile ] && touch $fullbakup_logfile

innobackupex --defaults-file=/etc/my.cnf --user=$user --password=$password --compress ${mysql_bakdir} 2>&1 >> $fullbakup_logfile

ret=$?

finishtime=`date +%Y%m%d_%H%M%S`

[ $ret -eq 0 ] && echo "Mysql fullbakup finished, time: $finishtime" >> $logfile || echo "Mysql fullbakup failed,please check time:$finishtime" >> $logfile

------------------------------------------------------------------------------------

#全量備份mysql shell腳本2

vi backup_auto.sh

#!/bin/bash

#function:xtrabackup database

#version:1.0

#author:zh

v_mysql_user="root"

v_mysql_password="mysql"

v_mysql_host="localhost"

v_mysql_port="3306"

v_mysql_charset="utf8"

#v_mysql_socket=/data/my13306

v_backup_location=/data/xtrabackup

v_expire_backup_delete="ON" #是否開啟過期備份删除 ON為開啟 OFF為關閉

v_expire_days=3 #過期時間天數預設為三天，此項隻有在 v_expire_backup_delete 開啟時有效

v_backup_time=`date +%Y%m%d%H%M` #定義備份詳細時間

v_backup_Ymd=`date +%Y-%m-%d` #定義備份目錄中的年月日時間

v_backup_3ago=`date -d '3 days ago' +%Y-%m-%d` #3天之前的日期

v_backup_dir=$v_backup_location/$v_backup_Ymd

v_backup_log=$v_backup_location/$v_backup_Ymd/backup.log

v_welcome_msg="Welcome to use MySQL backup tools!"

#判斷MYSQL是否啟動,mysql沒有啟動則備份退出

v_mysql_ps=`ps -ef |grep mysql |wc -l`

v_mysql_listen=`netstat -an |grep LISTEN |grep $v_mysql_port|wc -l`

if [ $v_mysql_ps == "0" -o $v_mysql_listen == "0" ]; then

echo "ERROR:MySQL is not running! backup stop!"

exit

else

echo $v_welcome_msg

#連接配接到mysql資料庫，無法連接配接則備份退出

mysql -h$v_mysql_host -P$v_mysql_port -u$v_mysql_user -p$v_mysql_password <

use mysql;

select host,user from user where user='root' and host='localhost';

exit

end

flag=$?

if [ $flag != "0" ]; then

echo "ERROR:Can’t connect mysql server! xtrabackup stop!"

exit

else

echo "MySQL connect ok! Please wait…"

#判斷有沒有定義備份的資料庫，如果定義則開始備份，否則退出備份

v_dbname=""

echo "database $v_dbname backup start…"

mkdir -p $v_backup_dir

innobackupex --host=$v_mysql_host --user=$v_mysql_user --port=$v_mysql_port --password=$v_mysql_password $v_backup_dir --parallel=2 --no-timestamp >>$v_backup_log 2>&1

flag=$?

if [ $flag == "0" ];then

echo "database $v_dbname success backup to $v_backup_dir"

else

echo "database $v_dbname backup fail!"

#如果開啟了删除過期備份，則進行删除操作

if [ $v_expire_backup_delete == "ON" -a $v_backup_location != "" ];then

#find $v_backup_location/ -type d -o -type f -ctime +$v_expire_days -exec rm -rf {} \;

find $v_backup_location/ -type d -mtime +$v_expire_days | xargs rm -rf

echo "Expired backup data delete complete!"

echo "All database backup success!"

exit

------------------------------------------------------------------------------------

#全量增量自動備份mysql shell腳本3

#建立備份使用者

mysql -uroot -pmysql

grant reload,lock tables,replication client,create tablespace,process,super on *.* to [email protected]'localhost' identified by '[email protected]';

#加入自動執行任務計劃

crontab -e

12 3 * * * sh /opt/script/xtrabackup.sh

#建立備援目錄和建立備份使用者：

mkdir -p /database/detect/backup/

ll /database/detect/backup/

ll /database/detect/redundency/

#配置備份腳本

vi /opt/script/xtrabackup.sh

#!/bin/bash

BEGINTIME=`date +"%Y-%m-%d %H:%M:%S"`

format_time=`date +"%Y-%m-%d_%H:%M:%S"`

week=`date +%Y-%m-%d`

backupbin=/usr/bin

backdir=/database/detect/backup/

redun=/database/detect/redundency/

file_cnf=/etc/my.cnf

user_name=backupuser

password="[email protected]"

socket="/var/lib/mysql/mysql.sock"

out_log=$backdir/xtrabackup_log_$format_time

time_cost=$backdir/xtrabackup_time.txt

[ ! -d $redun ] && mkdir -p $redun

if [ -d "$backdir/incr5" ];then

tar -czvf ${redun}\/redundency_${week}.tar.gz $backdir >/dev/null 2>&1

rm -rf $backdir/*

mkdir -p $backdir

chown -R mysql.mysql $backdir

# del backup

DEL_UNTIL_DATE=`date --date='7 day ago' +%Y-%m-%d`

sleep 30

/bin/rm -f /${redun}/*${DEL_UNTIL_DATE}.tar.gz >/dev/null 2>&1

#full

if [ ! -d "$backdir/full" ];then

echo "#####start full backup at $BEGINTIME to directory full" >>$time_cost

$backupbin/innobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --socket=$socket $backdir/full 1> $out_log 2>&1

break;

elif [ ! -d "$backdir/incr0" ];then

echo "#####start 0 incremental backup at $BEGINTIME to directory incr0" >>$time_cost

$backupbin/innobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --socket=$socket --incremental --incremental-basedir=$backdir/full $backdir/incr0 1> $out_log 2>&1

break;

elif [ ! -d "$backdir/incr1" ];then

echo "#####start 1 incremental backup at $BEGINTIME to directory incr1" >>$time_cost

break;

elif [ ! -d "$backdir/incr2" ];then

echo "#####start 2 incremental backup at $BEGINTIME to directory incr2" >>$time_cost

break;

elif [ ! -d "$backdir/incr3" ];then

echo "#####start 3 incremental backup at $BEGINTIME to directory incr3" >>$time_cost

break;

elif [ ! -d "$backdir/incr4" ];then

echo "#####start 4 incremental backup at $BEGINTIME to directory incr4" >>$time_cost

break;

elif [ ! -d "$backdir/incr5" ];then

echo "#####start 5 incremental backup at $BEGINTIME to directory incr5" >>$time_cost

break;

ENDTIME=`date +"%Y-%m-%d %H:%M:%S"`

begin_data=`date -d "$BEGINTIME" +%s`

end_data=`date -d "$ENDTIME" +%s`

spendtime=`expr $end_data - $begin_data`

echo "it takes $spendtime sec for packing the data directory" >>$time_cost