MySQL备份mysqldump+XBK

备份的方式：
逻辑备份： mysqldump
物理备份：xbk

注意事项：

1.检查备份的可用性 定时任务 备份的脚本 备份路径
2.定期的演练
3.数据的恢复 

1. mysqldump

1. 1 参数：

连接数据库的参数

-u	用户名
-p	密码
-S	socket文件
-h	连接的ip
-P	端口

mysqldump -uroot -p123456 -S /tmp/mysql.socket -h 192.168.80.90 -P 3306

基础的备份参数

-A	全备，所有的库表
-B	可以是数据库，可以是 数据库 表 ；库后边写的数表，多个表空格分开

mysqldump -uroot -p123456 -A /tmp/full.sql

mysqldump -uroot -p123456 -B world city oldguo >/tmp/world.sql
                              库     表    表

特殊的备份参数

-R	存储过程中和函数
-E	事件
–triggers	触发器
–master-data=2	记录备份时刻的binlog ，自动锁表

扩展参数

–set-gtid-purged	参数AUTO NO OFF 主从的时候不能OFF
–max_allowed_packet=128M	控制的是备份时传输数据包的大小.

1.2 案列

背景环境：
正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量1-5M。
备份策略：
每天23:00点，计划任务调用mysqldump执行全备脚本
故障时间点：
年底故障演练:模拟周三上午10点误删除数据库.
思路：
1、停业务，挂维护页,避免数据的二次伤害
2、找一个临时库，恢复周二23：00全备
3、截取周二23：00  --- 周三10点误删除之间的binlog，恢复到临时库
4、测试可用性和完整性
5、 
    5.1 方法一：直接使用临时库顶替原生产库，前端应用割接到新库
    5.2 方法二：将误删除的表导出，导入到原生产库
6、开启业务
处理结果：经过20分钟的处理，最终业务恢复正常

1.2.1 数据的准备

create database backup;
use backup
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
mkdir /backup

1.2.2 模拟每天的数据全备

mysqldump  -uroot -p123456 -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip>/tmp/full_$(date +%F).sql.gz

可以写成脚本，放在定时任务里边

crontab -e
* 23 * * 2 /usr/bin/sh /opt/backup.sh

这里我们就手动的备份就可以了

[root@sjk tmp]# ll
total 292
-rw-r--r-- 1 root  root  284829 Nov 11 20:28 full_2021-11-11.sql.gz

1.2.3 模拟周二到周三的数据的变化

use backup 
insert into t1 values(222),(333),(555);
commit;
craete table t2(id int);
commit;

1.2.4 模拟故障

drop database backup

1.2.5 恢复过程

临时数据库的准备
准备全备

gunzip full_2021-11-11.sql.gz

截取二进制文件

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=693546;
693546

693980 

日志的截取

mysqlbinlog --skip-gtids   --start-position=693546 --stop-position=693980  /data/binlog/mysql-bin.000010>/backup/bin.sql

恢复到临时库

mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /backup/full_2021-11-11.sql
source /backup/bin.sql

恢复到生产库

mysqldump -S /data/3307/mysql.sock -B backup >/backup/full.sql
mysql -uroot -p123456
set sql_log_bin=0;
source /backup/full.sql

2. XBK物理备份

备份核心理念
1. 针对非InnoDB,进行锁表备份,copy所有的非innoDB表文件
2. 针对InnoDB表,立即触发CKPT,copy所有InnoDB表相关的文件(ibdata1,ibd,frm).
并且将备份过程中产生,新的数据变化的部分redo一起备份走
3. 在恢复时,xbk会调用InnoDB引擎的CSR过程,将数据和redo的LSN追平,然后进行一致性恢复.

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y percona-xtrabackup-24
yum install -y qpress
xtrabackup -v

参数：

--user：该选项表示备份账号。
--password：该选项表示备份的密码。
--port：该选项表示备份数据库的端口。
--host：该选项表示备份数据库的地址。
--socket：该选项表示mysql.sock所在位置，以便备份进程登录mysql。
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置。
--databases：该选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；

innobackupex --user=root --password=123456  --socket=/tmp/mysql.sock --no-timestamp /backup/full
innobackupex --apply-log /backup/full

备份产生的文件介绍：

备份产生的文件介绍
(1) xtrabackup_binlog_info  
记录备份时刻的二进制日志信息. 可以作为binlog截取的起点.
[root@sjk full]# cat xtrabackup_binlog_info
mysql-bin.000010        694143  61deb43b-3b02-11ec-a22a-000c29d2f486:1-44

(2) xtrabackup_checkpoints  
from : 备份中包含的LSN号的起点,全备:0,增量:上次备份的结束位置
to 	 : ckpt 时的LSN
last-9 : 备份结束时的LSN.下次增量备份的起始位置.
[root@sjk full]# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 3555969
last_lsn = 3555978
compact = 0
recover_binlog_info = 0
flushed_lsn = 3555978

2.1 增量备份

rm -rf /backup/*

模拟数据

create database full charset utf8mb4;
use full
create table t1(id int);
insert into t1 values(1),(2);
commit

全备

innobackupex --user=root --password=123456 --socket=/tmp/mysql.sock --no-timestamp /bakkup/full

模拟周一的数据变化

insert into t1 values(11);
create table t1(id int);
insert into t2 values(1);
commit;

进行周一的增量备份

innobackupex --user=root --password=123456 --socket=/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1

--incremental    开关 
--incremental-basedir=/backup/full 基于哪个备份进行增量
/backup/inc1     增量备份的位置点

检查备份的LSN

[root@sjk backup]# cat full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 3563847
last_lsn = 3563856
compact = 0
recover_binlog_info = 0
flushed_lsn = 3563856
[root@sjk backup]# cat inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 3563847
to_lsn = 3571789
last_lsn = 3571798
compact = 0
recover_binlog_info = 0
flushed_lsn = 3571798

模拟周二的增量

create database inc2 charset utf8mb4;
use inc2;
create table t1(id int);
insert into t1 values(1);
commit;

周二的增量

innobackupex --user=root --password=123456 --socket=/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2

周三的数据

create database inc3 charset utf8mb4;
use inc3;
create table t1(id int);
insert into t1 values(4444);
commit;

模拟数据的崩溃

pkill mysqld
rm -rf /data/mysql/data/*

恢复思路

1. 停业务,挂维护页
2. 查找可用备份并处理备份:full+inc1+inc2 
3. binlog: inc2 到 故障时间点的binlog
4. 恢复全备+增量+binlog
5. 验证数据
6. 起业务,撤维护页

恢复前的准备
整理full

innobackupex --apply-log --redo-only /backup/full

合并Inc1到full

innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full

合并inc2到full

innobackupex --apply-log --incremantal-dir=/backup/inc2 /backup/full

最后整理一下full

innobackupex --apply-log /backup/full

截取二进制文件
起点：

[root@sjk backup]# cat inc2/xtrabackup_binlog_info
mysql-bin.000012        2093    61deb43b-3b02-11ec-a22a-000c29d2f486:1-53

终点：

mysqlbinlog /data/binlog/mysql-bin.000031 |grep 'SET'
SET @@SESSION.GTID_NEXT= '61deb43b-3b02-11ec-a22a-000c29d2f486:56'/*!*/;

mysqlbinlog --skip-gtids --include-gtids='61deb43b-3b02-11ec-a22a-000c29d2f486:54-56' /data/binlog/mysql-bin.000012 >/backup/bin.sql

恢复备份数据

cp -a  /backup/full/* /data/mysql/data/
chown -R mysql. /data/
/etc/init.d/mysqld start
mysql> set sql_log_bin=0;
mysql> source /backup/binlog.sql

作业4：备份集中单独恢复表
思考:在之前的项目案例中,如果误删除的表只有10M,而备份有500G,该如何快速恢复误删除表?
提示：
drop table city;
create table city like city_bak;
alter table city discard tablespace;
cp /backup/full/world/city.ibd  /application/mysql/data/world/
chown -R mysql.mysql  /application/mysql/data/world/city.ibd 
alter table city import  tablespace;


作业5： 从mysqldump 全备中获取库和表的备份
1、获得表结构
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql
2、获得INSERT INTO 语句，用于数据的恢复
# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &
3.获取单库的备份
# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql


6. 迁移(5.6.44  --->  5.7.26)
6.1 搭建5.6的测试环境
(1)创建必须的目录
[root@oldboyedu ~]# mkdir /data/mysql/data  -p 
[root@oldboyedu ~]# mkdir /application/ -p
[root@oldboyedu ~]# mkdir /data/binlog -p
上传软件至 /application 下并解压
(2) 建用户,改权限
[root@oldboyedu ~]# useradd mysql
[root@oldboyedu ~]# chown -R mysql. /data /application/
(3) 修改环境变量
vim /etc/profile
export PATH=/application/mysql/bin:$PATH
source /etc/profile

(4)数据初始化 
[root@oldboyedu ~]# yum remove mariadb-libs
[root@oldboyedu ~]# yum install -y libaio-devel
[root@oldboyedu ~]# \rm -rf /data/mysql/data/*
[root@oldboyedu ~]# /application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data

(5) 准备配置文件和启动脚本
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql 
datadir=/data/mysql/data 
server_id=99
user=mysql
log_bin=/data/binlog/mysql-bin
binlog_format=row
socket=/tmp/mysql.sock 
[mysql]
socket=/tmp/mysql.sock
EOF

[root@oldboyedu data]# cp  /application/mysql/support-files/mysql.server /etc/init.d/mysqld

3. 数据迁移

(1) 5.6 数据库备份
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql
scp /tmp/full.sql 10.0.0.51:/data/3308 
(2)准备5.7数据库
systemctl start mysqld3308
mysql -S /data/3308/mysql.sock
mysql> source /data/3308/full.sql
mysql_upgrade  -uroot -p123 -S /data/3308/mysql.sock
(3) binlog的持续追加
(4) 停业务,恢复剩余的binlog
(5) 业务割接