（4.16）mysql备份还原——物理备份之XtraBackup实践
关键词：XtraBackup实践，物理备份，xtrabackup备份，innobackupex备份
【1】如何使用？
【3】系列：innobackupex --help |more
【4】系列：xtrabackup --help |more
版本特性：
2018年5月版本，2.4.11，在2.3.3之前备份会产生死锁，在2.3.3之后就不再死锁。
如果5.7使用，需要使用2.4.4之后的版本才可以
xtrabackup只能备份InnoDB和XtraDB两种数据表（但在高版本中2.4.11以及更高的版本中，xtrabackup已经集成了innobackupex，且innobackupex命令只是xtrabackup的一个软链接）
innobackupex-1.5.1则封装了xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，但在处理myisam时需要加一个读锁
核心总结：
------------------------------完整备份操作-------------------------------------------
【1】全备
【1.1】备份：innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --parallel=2 /mysql/backup/full_1
【1.2】恢复：
（1.2.1）prepare阶段，即使用--apply-log参数进行redo和undo达到事务一致性
innobackupex --apply-log --user-memory=1G /mysql/backup/full_1
（1.2.2）停服，备份删除现有数据库目录
service mysql stop mv /mysql/data/3306/data /mysql/data/3306/data_old mkdir /mysql/data/3306/data chown -R mysql:mysql /mysql/data/3306/data
（1.2.3）copy数据文件，需要原来的目录为空（--force-non-empty-directories，这个参数可以覆盖）-- 核心参数 --copy-back
innobackupex --defaults-file=/mysql/data/3306/my.cnf --copy-back --parallel=2 /mysql/backup/fullbackup_20190708_1
（1.2.4）修改权限（很重要！！！），启动数据库，验证数据是否恢复
chown -R mysql:mysql /mysql/data/3306/data
service mysql start
------------------------------增量备份操作-------------------------------------------
#核心参数 （1）--incremental 增量选项 （2）--incremental-basedir= 基于哪次备份的增量，参数值可以是全备目录，可以是上一层增量备份目录
#核心参数 （3）--rsync 是用来加速恢复的，最小化flush tables with read lock的阻塞时间，用rsync来拷贝非innodb的文件，有myisam才用。增量也可以用
#【1】完备 innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp /mysql/backup/full_1 #【2】构造增量数据1
use test1;
create table test13(id int,str varchar(50));
insert into test13 values(1,'test13_1'),(2,'test13_2');
commit;
#【3】开始增量备份1
innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/mysql/backup/full_1 /mysql/backup/diff_1
#【4】构造增量数据2
use test2;
create table test23(id int,str varchar(50));
insert into test23 values(1,'test23_1'),(2,'test23_2');
commit;
#【5】开始增量备份2（注意，这里的 --incremental--basedir= 的值如果是全备，那么就是差异备份。 如果值是上一次增量备份，那就是基于该目录的增量数据备份）
基于全备的差异备份：
#innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/mysql/backup/full_1 /mysql/backup/diff_2
基于上一次增量的增量备份：
innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/mysql/backup/diff_1 /mysql/backup/diff_2
#【6】构造增量数据3
use test3;
create table test33(id int,str varchar(50));
insert into test33 values(1,'test33_1'),(2,'test33_2');
commit;
#【7】开始增量备份3：基于增量备份1的增量备份（也就是把 --incremental-basedir=/mysql/backup/diff_1）
innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/mysql/backup/diff_1 /mysql/backup/diff_3
------------------------------增量还原操作/增量恢复操作-------------------------------------------
#核心参数 --redo-only（只需redo不作undo） ,--apply-log ==>2个整合起来就是 --apply-log-only
【1】测试，先删除一些数据
drop table test1.test11;
drop table test2.test23;
drop table test3.test33;
【2】全备恢复
innobackupex --apply-log --redo-only /mysql/backup/full_1
【3】增量备份1恢复
innobackupex --apply-log --redo-only /mysql/backup/full_1 --incremental-dir=/mysql/backup/diff_1
【4】增倍备份2恢复（最后一次增量恢复）
innobackupex --apply-log /mysql/backup/full_1 --incremental-dir=/mysql/backup/diff_2
【5】合并，准备完全恢复
innobackupex --apply-log /mysql/backup/full_1
【6】数据文件还原
(1) service mysql stop
(2) cd /mysql/data/3306
(3) mv data data_bak1
(4) mkdir data
(5) chown -R mysql:mysql /mysql
(6) innobackupex --defaults-file=/mysql/data/3306/my.cnf --copy-back --rsync /mysql/backup/full_1
#--rsync是用来加速恢复的，最小化flush table with read lock的阻塞时间，用rsync来拷贝非innodb数据。
【2】基本原理
参考：mysql备份原理
【3】案例演示与学习（测试演练）
【3.0】测试数据准备
测试环境：centos6.5,mysql5.7.24
前提条件：xtrabackup2.4.11（如何安装？），mysql开启binlog（这里为了测试特地reset master重置了binlog）
binlog的相关配置如下：
log-bin=/mysql/log/3306/binlog server-id=113306 binlog_format= ROW log_bin_index=/mysql/log/3306/binlog.index binlog_rows_query_log_events=on
binlog_row_image=FULL
测试数据
create database test1; create database test2; create database test3; commit; use test1; create table test11(id int,str varchar(50)); create table test12(id int,str varchar(50)); insert into test11 values(1,'test11_1'),(2,'test11_2'); insert into test12 values(1,'test12_1'),(2,'test12_2'); commit; use test2; create table test21(id int,str varchar(50)); create table test22(id int,str varchar(50)); insert into test21 values(1,'test21_1'),(2,'test21_2'); insert into test22 values(1,'test22_1'),(2,'test22_2'); commit;
use test3; create table test31(id int,str varchar(50)); create table test32(id int,str varchar(50)); insert into test31 values(1,'test31_1'),(2,'test31_2'); insert into test32 values(1,'test32_1'),(2,'test32_2'); commit;
【3.1】innobackupex备份与恢复
Innobackupex 参数解释： --defaults-file=[MY.CNF]该选项传递给xtrabackup子进程，从指定文件读取缺省选项 --apply-log 从备份恢复。 --redo-only 该选项强制跳过rollback阶段，只进行redo。这是有必要使用的，如果备份后，要使用增量改变的。 --copy-back 从备份目录拷贝数据和索引文件到datadir目录 --remote-host=HOSTNAME备份到远程主机上，使用ssh --stream=[tar|cpio(notimplemented)] 指定备份标准输出格式 --tmpdir=DIRECTORY默认与tmpdir相同。使用—remote-host或—stream参数后，传输日志文件将存放在临时目录下 --use-memory=MB选项传递给xtrabackup子进程。恢复使用内存大小 --parallel=NUMBER-OF-THREADS选项传递给xtrabackup子进程，指定数据传输线程总数。默认为1 --throttle=IOS选项传递给xtrabackup子进程，限制IO线程数量 --sleep=MS 选项传递给xtrabackup子进程。每拷贝1MB数据暂停多少MS时间 --compress[=LEVEL]选项传递给xtrabackup子进程。压缩级别在0-9.1快速压缩，9最佳压缩，0不压缩。默认为1. --include=REGEXP选项传递给xtrabackup子进程。使用正则进行匹配 --databases=LIST指定备份数据库 --tables-file=FILE --uncompress选项传递给xtrabackup子进程。对压缩过的InnoDB数据文件不进行压缩 --export 仅使用于prepare选项。选项传递给xtrabackup子进程。 --user=NAME --password=WORD --host=HOST --port=PORT --slave-info 备份复制从服务端，主从信息记录在ibbackup_slave_info文件中 --socket=SOCKET --no-timestamp不在备份根目录下创建以当前时间戳为名称的新的备份目录 --ibbackup=IBBACKUP-BINARYibbackup二进制路径 --no-lock 禁止表级锁。全部是InnoDB引擎表和不关系二进制日志位置下使用 --scpopt=SCP-OPTIONS指定scp参数
【3.1.1】普通全备（备份库）
-- (1）自动生成日期命名的文件夹 innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 /mysql/backup --（2）指定备份目录,操作步骤日志生成，不生成日期名（规范，推荐使用） innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --parallel=2 /mysql/backup/fullbackup_20190708_1 2>fullbackup_20190708_1.log
备份目录里面的信息如图：
（1）backup-my.cnf：备份命令用到的配置选项信息；
（2）xtrabackup_binlog_info：记录当前最新的log position（备份开始这一刻的binlog位置）
（3）xtrabackup_checkpoints：存放备份的起始位置begin_lsn和结束位置的end_lsn，增量备份需要这个lsn
（4）xtrabackup_info：备份的相关信息
（5）xtrabackup_logfile：备份的重做日志文件（无法明文查看）
图示：（1）backup-my.cnf：备份命令用到的配置选项信息；
（2）xtrabackup_binlog_info：记录当前最新的log position（备份完成那一刻的binlog位置）
（3）xtrabackup_checkpoints：存放备份的起始位置begin_lsn和结束位置的end_lsn，增量备份需要这个lsn
（4）xtrabackup_info：备份的相关信息
（5）xtrabackup_logfile：备份的重做日志文件（无法明文查看）
【3.1.2】根据innobackupex的全备与还原（备份库）
--------------------备份、全备-------------------------
-- (1）自动生成日期命名的文件夹 innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 /mysql/backup --（2）指定备份目录,操作步骤日志生成，不生成日期名（规范，推荐使用） innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --parallel=2 /mysql/backup/fullbackup_20190708_1 2>fullbackup_20190708_1.log
-------------------恢复、还原全备------------------------
#【3.1.2.1】实操
#核心参数，--apply-log , --copy-back --user-memory=1G
#（1）准备prepare一个完整备份（--apply-log），即恢复
#使用--apply-log的作用是应用重做日志（进行redo/undo操作），使数据库文件处于一致状态
#执行后，xtrabackup_checkpoints文件由backup_type=full-backuped 变为backup_type=full-prepared
innobackupex --apply-log --user-memory=1G /mysql/backup/fullbackup_20190708_1
#（2）关闭数据库，并备份删除现有数据库目录及文件
service mysql stop
mv /mysql/data/3306/data /mysql/data/3306/data_old
mkdir /mysql/data/3306/data
chown -R mysql:mysql /mysql/data/3306/data
#（3）copy数据文件，需要原来的目录为空（--force-non-empty-directories，这个参数可以覆盖）-- 核心参数 --copy-back innobackupex --defaults-file=/mysql/data/3306/my.cnf --copy-back --parallel=2 /mysql/backup/fullbackup_20190708_1
#（4）修改权限（很重要！！！），启动数据库，验证数据是否恢复 chown -R mysql:mysql /mysql/data/3306/data service mysql start mysql -uroot -p123456 show databases;
还原恢复小结：
（1）文件权限
xtrabackup以rw模式打开innodb数据文件，用内置的innodb库来打开文件
（2）复制数据文件
xtrabackup每次读写1M的数据，1M/16K=64页；如果是redo，每次512K
（3）备份安全问题
--throttle #限制每次的IO操作数据
值为数字，1，,1,20，单位不是M。20M可能是30M/S
（4）主从环境下如何备份？从库备份如下：
加了2个参数 --slave-info --safe-slave-backup，会记录GTID和binlog changer信息 > xtrabackup_slave_info
innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --slave-info --safe-slave-backup --parallel=2 /mysql/backup/fullbackup_20190708_1 2>fullbackup_20190708_1.log
【3.1.3】根据innobackupex备份还原多个数据库（备份库）
#核心参数 --databases="test1 test2 test3 "，注意test3后面有空格！ #【1】备份多个数据库 innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --databases="test1 test2 test3 " --parallel=3 /mysql/backup/partbackup_1 #【2】恢复数据库/还原数据库（该步骤还未实测通过，还会有数据不一致问题） #注意，不要直接还原，容易覆盖现有数据库。为了安全建议先全备一次 #【2.1】prepare阶段 innobackupex --apply-log /mysql/backup/partbackup_1
#【2.2】停服、删除需要恢复数据库的现有目录 service mysql stop
#注意，下面做删除前可以先拷贝一份，以免出问题 rm -rf /mysql/data/3306/data/test1 rm -rf /mysql/data/3306/data/test2 rm -rf /mysql/data/3306/data/test3 #或者可以sql删除 drop database test1;
#【2.3】copy阶段，部分备份和恢复，不能直接用--copy-back，只能手工复制响应的数据库+ibdata数据字典 cp -r /mysql/backup/partbackup_1/test1 /mysql/data/3306/data cp -r /mysql/backup/partbackup_1/test2 /mysql/data/3306/data cp -r /mysql/backup/partbackup_1/test3 /mysql/data/3306/data #需要删除ibdata*数据字典文件，用备份文件覆盖它 rm -f /mysql/data/3306/data/ibdata* cp -r /mysql/backup/partbackup_1/ibdata* /mysql/data/3306/data
#【2.4】改权限，启动数据库，验证数据 chown -R mysql:mysql /mysql/data/3306/data service mysql start mysql
#验证数据 应该要全部查一次，比如Select * from test1.test11;
【3.1.4】根据innobackupex备份还原多个表（备份表）
---------------备份表---------------
#核心参数--database="test1.test11 test2.test21 test3.test31 " #【1】备份多个库下的不同表 innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --databases="test1.test11 test2.test21 test3.test31 " --parallel=2 /mysql/backup/tab_backup_1 #核心参数--include="test1.test11,test1.test12"
#[warning]--include选项传递给xtrabackup --tables，对每个库中的每个表逐一匹配，因此会创建所有的库目录，不过是空的目录。[/warning] #【2】备份多个库下的不同表 innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --include="test1.test11,test1.test12,test2.test21" --parallel=2 /mysql/backup/tab_backup_2 #核心参数--table-file=/mysql/backup/tablename.txt
[warning]该选项传递给xtrabackup --tables-file，与--table选项不同，只有要备份的表的库才会被创建。[/warning] #【3】备份指定文件中的表 innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp ----table-file=/mysql/backup/tablename.txt --parallel=2 /mysql/backup/tab_backup_3 #/mysql/backup/tab_backup_1 文件格式如下（注意，文末不要多加行，也不要在行末加空格，否则可能全备） test1.test11 test2.test22
-------------还原表/恢复表（复制文件方式，容易不一致，不能使用！！！）--------------这里只演示一个【1】
#【1】prepare阶段
innobackupex --apply-log /mysql/backup/tab_backup_1
#【2】停服，删除对应表目录与文件
service mysql stop
#建议备份一份再删除
#mkdir -p /mysql/backup/tempbak
#cp -r /mysql/data/3306/data/test1 /mysql/backup/tempbak
rm -f /mysql/data/3306/data/test1/test11.*
rm -f /mysql/data/3306/data/test2/test21.*
rm -f /mysql/data/3306/data/test3/test31.*
#或者可以直接写sql，使用drop table 一一删除。
#【3】copy阶段，还原表，不能直接用--copy-back，只能手工复制表文件+ibdata*数据字典。
cp -r /mysql/backup/tab_backup_1/test1/* /mysql/data/3306/data/test1/
cp -r /mysql/backup/tab_backup_1/test2/* /mysql/data/3306/data/test2/
cp -r /mysql/backup/tab_backup_1/test3/* /mysql/data/3306/data/test3/
rm -f /mysql/data/3306/data/ibdata*
cp -r /mysql/backup/tab_backup_1/ibdata* /mysql/data/3306/data/
#【4】改权限，启动数据库，验证数据
chown -R mysql:mysql /mysql
service mysql start
#验证数据 应该要全部查一次，比如Select * from test1.test11;
-------------还原表/恢复表（表空间导入方式，推荐）--------------这里只演示一个【1】
【1】prepare阶段
innobackupex --apply-log --export /mysql/backup/tab_backup_1
#此时会对应生成表的.exp和.cfg文件
【2】导入表操作
【2.1】销毁表空间（假如误操作truncate了可以使用该办法。drop的话，可以去下载mysql utilities,用mysqlfrm解析frm重新创建表后再用该办法）
#drop table test1.test11;
alter table test1.test11 discard tablespace;
【2.2】复制文件（copy test1.test11表的ibd、cfg、exp文件）
cp -r /mysql/backup/tab_backup_1/test1/test11.ibd /mysql/data/3306/data/test1/
cp -r /mysql/backup/tab_backup_1/test1/test11.cfg /mysql/data/3306/data/test1/
cp -r /mysql/backup/tab_backup_1/test1/test11.exp /mysql/data/3306/data/test1/
【2.3】导入表空间
#先授权
chown -R mysql:mysql /mysql/data/3306/data
alter table test1.test11 import tablespace;
【3】授权、验证数据
select * from test1.test11;
【3.1.5】根据innobackupex增量备份（全库）
------------------------------增量备份操作-------------------------------------------
#核心参数 （1）--incremental 增量选项 （2）--incremental-basedir= 基于哪次备份的增量，参数值可以是全备目录，可以是上一层增量备份目录
#核心参数 （3）--rsync 是用来加速恢复的，最小化flush tables with read lock的阻塞时间，用rsync来拷贝非innodb的文件，有myisam才用。增量也可以用
#【1】完备 innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp /mysql/backup/full_1 #【2】构造增量数据1
use test1;
create table test13(id int,str varchar(50));
insert into test13 values(1,'test13_1'),(2,'test13_2');
commit;
#【3】开始增量备份1
innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/mysql/backup/full_1 /mysql/backup/diff_1
#【4】构造增量数据2
use test2;
create table test23(id int,str varchar(50));
insert into test23 values(1,'test23_1'),(2,'test23_2');
commit;
#【5】开始增量备份2（注意，这里的 --incremental--basedir= 的值如果是全备，那么就是差异备份。 如果值是上一次增量备份，那就是基于该目录的增量数据备份）
基于全备的差异备份：
#innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/mysql/backup/full_1 /mysql/backup/diff_2
基于上一次增量的增量备份：
innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/mysql/backup/diff_1 /mysql/backup/diff_2
#【6】构造增量数据3
use test3;
create table test33(id int,str varchar(50));
insert into test33 values(1,'test33_1'),(2,'test33_2');
commit;
#【7】开始增量备份3：基于增量备份1的增量备份（也就是把 --incremental-basedir=/mysql/backup/diff_1）
innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/mysql/backup/diff_1 /mysql/backup/diff_3
------------------------------增量还原操作/增量恢复操作-------------------------------------------
#核心参数 --redo-only（只需redo不作undo） ,--apply-log ==>2个整合起来就是 --apply-log-only
【1】测试，先删除一些数据
drop table test1.test11;
drop table test2.test23;
drop table test3.test33;
【2】全备恢复
innobackupex --apply-log --redo-only /mysql/backup/full_1
【3】增量备份1恢复
innobackupex --apply-log --redo-only /mysql/backup/full_1 --incremental-dir=/mysql/backup/diff_1
【4】增倍备份2恢复（最后一次增量恢复）
innobackupex --apply-log /mysql/backup/full_1 --incremental-dir=/mysql/backup/diff_2
【5】合并，准备完全恢复
innobackupex --apply-log /mysql/backup/full_1
【6】数据文件还原
(1) service mysql stop
(2) cd /mysql/data/3306
(3) mv data data_bak1
(4) mkdir data
(5) chown -R mysql:mysql /mysql
(6) innobackupex --defaults-file=/mysql/data/3306/my.cnf --copy-back --rsync /mysql/backup/full_1
#--rsync是用来加速恢复的，最小化flush table with read lock的阻塞时间，用rsync来拷贝非innodb数据。
【3.1.6】压缩备份
#--compress 压缩选项 compress[=LEVEL]选项传递给xtrabackup子进程。压缩级别在0-9.1快速压缩，9最佳压缩，0不压缩。默认为1. #--compress-threads=2 压缩线程
--------------开始压缩备份------------------------
#【1】压缩备份 innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 \ --compress --compress-threads=2 --no-timestamp --parallel=2 /mysql/backup/compress_1
#【2】关闭数据库、删除数据文件
service mysql stop
cd /mysql/data/3306
rm -rf data_bak
mv data data_bak
mkdir data
chown -R mysql:mysql data
#【3】解压备份文件
需要提前下载qpress（下载链接点击 or 直接下载）
解压tar -xvf
cp qpress /usr/bin
innobackupex --decompress --user-memory=1G /mysql/backup/compress_1
#【4】prepare阶段
innobackupex --apply-log --user-momory=1G /mysql/backup/compress_1
#【5】copy文件
innobackupex --defaults-file=/mysql/data/3306/my.cnf --copy-back /mysql/backup/compress_1
#【6】授权、起服、核验数据
chown -R mysql:mysql /mysql
service mysql start
【3.1.7】tar打包备份
【1】流备份打包
(1):innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --stream=tar /mysql/backup/tar_1 >/mysql/backup/tar20190716_1.tar
(2):innobackupex --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --no-timestamp --stream=tar /mysql/backup/tar_2 | gzip > /mysql/backup/tar20190716_2.tar.gz
【3.1.8】加密解密的方式备份还原
--待写
【4】使用 xtrabackup备份
相关参数
--extra-lsndir 额外导出一份 lsn 记录点文件，可以用用于增量备份指定
--rebuild-indexes Rebuild secondary indexes in InnoDB tables after applying #默认为FLASE --rebuild-threads=# Use this number of threads to rebuild indexes in a --rebuild-indexes. #默认为1
【4.1】xtrabackup的全备
#备份核心参数，--backup 备份必加参数，--no-timestamp --> 变成了 --target-dir=/mysql/backup/full_2
#还原核心参数，--apply-log --> 变成了 --prepare
#【1】备份 xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --backup --target-dir=/mysql/backup/full_2
#【2】还原
【2.1】停服备份数据
service mysql stop
cd /mysql/data/3306
rm -rf data_bak
mv data data_bak
mkdir data
chown -R mysql:mysql data
【2.2】prepare阶段
xtrabackup --defaults-file=/mysql/data/3306/my.cnf --prepare --user-memory=1G --target-dir=/mysql/backup/full_2
【2.3】copy阶段
#rsync ,（1）-a:归档模式，保持文件原有属性和所有子目录（2）-P:传输进度
#（3）-v 传输时的进度信息 （4）-r 递归 （5）-z传输时压缩
rsync -avrP /mysql/backup/full_2/* --exclude='xtrabackup_*' /mysql/data/3306/data/
【2.4】授权，启动mysql服务
chown -R mysql:mysql /mysql
service mysql start
#【3】主从情况下如何备份：主从模式，主提供服务，从提供备份
#参数：--slave-info --safe-slave-backup,会记录gtid和binlog changer信息>xtrabackup_slave_info
【3.1】从库备份
xtrabackup --defaults-file=/mysql/data/3306/my.cnf --user=root --password=123456 --backup --slave-info --safe-slave-backup --target-dir=/mysql/backup/slave_full_2
【3.2】还原与【2】中还原相同
【4.2】全备及增量备份恢复
##备份 xtrabackup --defaults-file=/etc/my.cnf --user=backup --password=backup123456 --socket=/tmp/mysql.sock --parallel=4 --backup --target-dir=/data/backup/xtrabackup/full/ xtrabackup --defaults-file=/etc/my.cnf --user=backup --password=backup123456 --socket=/tmp/mysql.sock --parallel=4 --backup --target-dir=/data/backup/xtrabackup/incr0/ --incremental-basedir=/data/backup/xtrabackup/full/ xtrabackup --defaults-file=/etc/my.cnf --user=backup --password=backup123456 --socket=/tmp/mysql.sock --parallel=4 --backup --target-dir=/data/backup/xtrabackup/incr1/ --incremental-basedir=/data/backup/xtrabackup/incr0/ ##准备 xtrabackup --prepare --apply-log-only --target-dir=/data/backup/xtrabackup1/full/ xtrabackup --prepare --apply-log-only --target-dir=/data/backup/xtrabackup1/full/ --incremental-basedir=/data/backup/xtrabackup1/incr0/ xtrabackup --prepare --target-dir=/data/backup/xtrabackup1/full/ --incremental-basedir=/data/backup/xtrabackup1/incr1/ ##复制 rm -rf /data1/mysql/data/* rm -rf /data1/logs/mysql/binlog.* xtrabackup --defaults-file=/etc/my1.cnf --copy-back --target-dir=/data/backup/xtrabackup1/full/ chown -R mysql.mysql /data1/logs/mysql/ chown -R mysql.mysql /data1/mysql/ mysqld_safe --defaults-file=/etc/my1.cnf &
【5】自动化备份脚本
（1）软件安装、解压、初始化
vi backup_init.sh
安装了 yum，同事安装所需要使用的 xtrabackup、qpress、mailx，并做好环境变量配置。并做好定时任务，每天运行
注意修改软件名称。
percona-xtrabackup-2.4.29-Linux-x86_64.glibc2.17.tar.gz
backup_xtrabackup.sh
centos7.repo
qpress-11-linux-x64.tar
#!/bin/bash source /etc/profile init(){ ip_addr=`ip a |grep 10.18|awk '{print $2}'|paste -d '|' -s` mail_receiver='guocq4@sany.com.cn' backup_dir='/data/backup' sofrware_dir='/data/dba/software/mysql_backup' mkdir -p ${backup_dir} mkdir -p ${sofrware_dir} if [ ! -f ${backup_dir}/backup_xtrabackup.sh ];then mv backup_xtrabackup.sh ${backup_dir} chmod +x ${backup_dir}/backup_xtrabackup.sh fi if [ ! -f ${sofrware_dir}/percona-xtrabackup-2.4.29-Linux-x86_64.glibc2.17.tar.gz ];then mv percona-xtrabackup-2.4.29-Linux-x86_64.glibc2.17.tar.gz ${sofrware_dir} fi if [ ! -f ${sofrware_dir}/qpress-11-linux-x64.tar ];then mv qpress-11-linux-x64.tar ${sofrware_dir} fi yum_flag=`ls /etc/yum.repos.d/ |grep centos7|wc -l` if [ $yum_flag -eq 0 ];then mkdir /etc/yum.repos.d/bak mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak mv centos7.repo /etc/yum.repos.d/ fi } decompress_software(){ which xtrabackup 2>/dev/null |grep $sofrware_dir >/dev/null if [ $? -eq 0 ];then tmp_dir=`which xtrabackup` new_tmpfile=${tmp_dir}`date "+%Y%m%d_%H%M%S"` echo -n "xtrabackup rename......" mv ${tmp_dir} ${new_tmpfile} echo "mv ${tmp_dir} ${new_tmpfile}" fi cd ${sofrware_dir} tar -xf qpress-11-linux-x64.tar chmod +x qpress mv qpress /usr/bin ll -d xtrabackup2.4.29 >/dev/null 2>&1 if [ ! $? -eq 0 ];then tar -zxf percona-xtrabackup-2.4.29-Linux-x86_64.glibc2.17.tar.gz mv percona-xtrabackup-2.4.29-Linux-x86_64.glibc2.17 xtrabackup2.4.29 echo "export PATH=${PATH}:`pwd`/xtrabackup2.4.29/bin" >>/etc/profile fi } mailx_install(){ yum install -y mailx >/dev/null 2>&1 echo >>/etc/mail.rc <<EOF set from=xx@xx.com.cn set smtp=smtp.xx.com.cn:25 set smtp-auth-user=xx@xx.com.cn set smtp-auth-password='!xx' set smtp-auth=login EOF } command_check(){ if [ $? -eq 0 ];then echo ' [OK] ' else echo '[ERROR]' fi } crontab_task(){ flag=`crontab -l|grep backup_xtrabackup|wc -l` if [ $flag -eq 0 ];then crontab -l>crontab_tmp.txt echo "1 2 * * * /bin/bash ${backup_dir}/backup_xtrabackup.sh" >>crontab_tmp.txt crontab crontab_tmp.txt rm -f crontab_tmp.txt echo "xtrabackup init crontab success" |mailx -s "${ip_addr}: xtrabackup crontab init success " "${mail_receiver}" fi } init_check(){ echo ' ----------- begin check list -----------' echo -n "mail check......" which mail >/dev/null 2>&1 command_check echo -n "qpress check......" which qpress >/dev/null 2>&1 command_check source /etc/profile >/dev/null 2>&1 echo -n "xtrbackup check......" xtrabackup -v >/dev/null 2>&1 command_check echo -n "crontab check......" `crontab -l|grep backup_xtrabackup ` command_check } main(){ init decompress_software mailx_install crontab_task init_check } main
（2）备份脚本
vi backup_xtrabackup.sh
1. 使用了压缩备份，指定备份目录为 /data/backup
2. 全备放 full 目录，增量放 increment 目录，若当前没有备份则直接全备，同时判断磁盘空间是否足够（会优先判断/data，没有就判断 /），否则每周六凌晨2点开始全备
3. 上一次备份成功的路径放 /data/backup/increment/laster_backupdir.txt 文件中
4. 根据日志判断成功失败，对应发邮件告警
5. 删除 /data/backup 目录下30天前的所有非 .sh 的文件与目录
#!/bin/bash source /etc/profile backup_dir='/data/backup' current_date=`date +%F` exec_logdir=${backup_dir}/logs mkdir -p $exec_logdir log=${exec_logdir}/${current_date}_xtrabackup.log error_log=${exec_logdir}/${current_date}_xtrabackup_err.log exec 1>>${log} exec 2>>${error_log} set -u set -e set -x init(){ mkdir -p ${backup_dir}/full mkdir -p ${backup_dir}/increment full_backup_dir=${backup_dir}/full increment_backup_dir=${backup_dir}/increment last_backup_record=${increment_backup_dir}/laster_backupdir.txt touch $last_backup_record weekday=`date +%u` ip_addr=`ip a |grep 10.18|awk '{print $2}'|paste -d '|' -s` mail_receiver='guocq4@xx.com.cn' } disk_space_check(){ case $1 in "full") full_first_flag=`lS ${backup_dir}/full |grep full|wc -l` if [ $full_first_flag -eq 0 ] ;then mysql_datadir=`cat /etc/my.cnf|grep datadir|awk -F '=' '{print $2}'` mysql_space_mb=`du -sm $mysql_datadir|awk '{print $1}'` let mysql_space_mb=mysql_space_mb/5 else last_full_dir=`ls $full_backup_dir|grep full|tail -n 1` mysql_space_mb=`du -sm ${full_backup_dir}/${last_full_dir}|awk '{print $1}'` fi ;; "increment") increment_first_flag=`ls ${backup_dir}/increment |grep increment|wc -l` if [ $increment_first_flag -eq 0 ] ;then last_full_dir=`ls $full_backup_dir |grep full|tail -n 1` mysql_space_mb=`du -sm ${full_backup_dir}/${last_full_dir}|awk '{print $1}'` let mysql_space_mb=mysql_space_mb/8 else last_increment_dir=`ls ${increment_backup_dir}|grep increment|tail -n 1` mysql_space_mb=`du -sm ${increment_backup_dir}/${last_increment_dir}|awk '{print $1}'` fi ;; *) echo "value error,please use full or increment." exec_check exit 1 ;; esac tmp=`df -m|grep '/data$'|awk '{print $4}'` if [ -z ${tmp} ];then disk_all_mb=`df -m|grep "/$" |awk '{print $2}'` disk_free_mb=`df -m|grep "/$" |awk '{print $4}'` else disk_all_mb=`df -m|grep '/data'|awk '{print $2}'` disk_free_mb=`df -m|grep '/data'|awk '{print $4}'` fi if [ ${mysql_space_mb} -lt ${disk_free_mb} ] && [ $((disk_free_mb-mysql_space_mb)) -gt $((disk_all_mb/10)) ];then echo -e "disk check ok!\ndisk_all：$((disk_all_mb/1024))GB ,disk_free：$((disk_free_mb/1024))GB,mysl_backup_need：$((mysql_space_mb/1024))GB(如果等于0则表示小于1G)." else echo -e "disk space not enough or free space precent < 10%!\ndisk_all：$((disk_all_mb/1024))GB ,disk_free：$((disk_free_mb/1024))GB,mysl_backup_need：$((mysql_space_mb/1024))GB(如果等于0则表示小于1G)." exec_check exit 1 fi } full_backup(){ echo "---------[${ip_addr} : `date "+%F %T"`] begin full backup ---------" backup_target=${full_backup_dir}/full_${current_date} if [ -d $backup_target ];then echo "${ip_addr} : ${backup_target} dir is already exit! please check the backup info." exec_check exit 1 fi disk_space_check full xtrabackup --default-file=/etc/my.cnf --backup --compress --compress-threads=4 --target-dir=${backup_target} echo ${backup_target}>${last_backup_record} echo "---------[${ip_addr} : `date "+%F %T"`] end full backup success ---------" } increment_backup(){ echo "---------[${ip_addr} : `date "+%F %T"`] begin increment backup ---------" backup_target=${increment_backup_dir}/increment_${current_date} if [ -d $backup_target ];then echo "${ip_addr} : ${backup_target} dir is already exit! please check the backup info." exec_check exit 1 fi disk_space_check increment xtrabackup --default-file=/etc/my.cnf --backup --compress --compress-threads=4 --incremental-basedir=${basedir} --target-dir=${backup_target} echo ${backup_target}>${last_backup_record} echo "---------[${ip_addr} : `date "+%F %T"`] end increment backup success ---------" } exec_check(){ success_flag=`tail -n 1 ${log}|grep success|wc -l` if [ ${success_flag} -eq 1 ];then title="[success] ${ip_addr} : xtrabackup 备份成功" mailx -s "${title}" ${mail_receiver} < $log else title="[fail] ${ip_addr}:xtrabackup 备份失败" mailx -s "${title}" -a ${error_log} ${mail_receiver} < $log fi } clean_old_file(){ find ${exec_logdir} -mtime +30 -name "*.log" -exec rm -rf {} \; find ${full_backup_dir} -maxdepth 1 -type d -mtime +30 -exec rm -rf {} \; find ${increment_backup_dir} -maxdepth 1 -type d -mtime +30 -not -name "*.txt" -exec rm -rf {} \; } handle_error() { success_flag=`tail -n 1 ${log}|grep success|wc -l` if [ ${success_flag} -eq 1 ];then title="[error] ${ip_addr} : xtrabackup 备份成功，脚本执行错误" mailx -s "${title}" -a ${error_log} ${mail_receiver} < $log else title="[fail] ${ip_addr}:xtrabackup 备份失败" mailx -s "${title}" -a ${error_log} ${mail_receiver} < $log fi } main(){ trap 'handle_error' ERR init basedir=`head -n 1 $last_backup_record` if [ -z $basedir ] || [ ${weekday} -eq 6 ];then full_backup else increment_backup fi clean_old_file exec_check } main
（3）测试结果和使用步骤
前置软件 xtrabackup2.4.29 ,qpress11 配置 操作 用时 阻塞 源大小 备份大小 命令 4核16G 直接备份 12m 瞬间锁 51G 44G xtrabackup --default-file=/etc/my.cnf --backup --target-dir=./backup/ 4核16G 压缩备份 6m 瞬间锁 51G 8.6G xtrabackup --default-file=/etc/my.cnf --backup --target-dir=./backup/ --compress --compress-threads=4 配置 操作 用时 阻塞 操作前 操作后 命令 4核16G 解压缩 25m / 8.6G 44G xtrabackup --decompress --parallel=4 --target-dir=./xtrabackup_backupfiles/ --remove-original 4核16G 全备恢复 1m / 文件形式 redo only xtrabackup --prepare --apply-log-only --target-dir=./full/full_2024-04-16/ 4核16G 增量恢复 1m / 文件形式 对全量目录进行增量应用并undo xtrabackup --prepare --target-dir=./full/full_2024-04-16/ --incremental-basedir=./increment/increment_2024-04-16/ 4核16G 还原拷贝 25m / full目录 根据my.cnf拷贝至datadir xtrabackup --parallel --copy-back --defaults-file=/etc/my.cnf --target-dir=./full/full_2024-04-16/ "不需要启动mysql 1. --prepare （如有增量需要 --apply-log-only） 2. my.cnf 设置好，最好是原来的，同时源目录一定要存在 3. 执行 --copy-back 拷贝备份 4. 记得设置主权限与组权限 5. 记得binlog清理 6. 恢复完成，启动Mysql 验证"
参考文章
