备份恢复（备份完成后需要检查是否备份成功）
1. 在备份恢复中的职责
1.1 备份策略的设计
(1) 备份周期:
根据数据量.
(2)备份工具:
mysqldump (MDP) , XBK (PBK) percona Xtrabackup , MEB(MySQL Enterprise BACKUP MEB) ,mysqlbinlog
(3)备份方式:
逻辑:
全备 mysqldump
增量 binlog (flush logs ,cp)
物理备份:
全备 : XBK
增量 : XBK
1.2 检查备份可用性
crontab -l ----->
备份脚本 ----->
备份路径 ----->
看备份日志,检查备份文件(大小,内容)
1.3 定期的恢复演练
1.4 数据恢复
只要备份和日志是完整的,恢复到故障之前的时间点(快速)
1.5 数据迁移 ***
操作系统不同的迁移
mysql -> mysql
其他 -> mysql
mysql -> 其他
2. 备份的介绍
2.1 备份的策略
2.2 备份的工具
2.3 备份类型
热备 : 对于业务影响最小 InnoDB
温备 : 长时间锁表备份 MyISAM
冷备 : 业务关闭情况下备份
3. mysqldump
3.1 连接数据库
-u
-p
-S
-h
P
3.2 基础备份参数
-A 全备
[root@db01 backup]# mysqldump -uroot -p123 -A >/backup/full.sql
-B 备份库
[root@db01 backup]# mysqldump -uroot -p123 -B world oldguo wordpress >/backup/db.sql
备份world库中的表city country
[root@db01 backup]# mysqldump -uroot -p123 world city country > /backup/tab.sql
备份指定的表
mysqldump -uroot -p -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction db_name --tables table1 table2|gzip >test.sql.gz
备份指定库表结构
mysqldump -uroot -ppassword --no-data --databases basename
备份指定库（包含数据和结构）
mysqldump -uroot -ppassword --databases basename
导出指定表的数据
mysqldump -uusername -ppassword -t database --tables table_name1 table_name2 table_name3 >db_script.sql
导出指定表的结构
mysqldump -uusername -ppassword -d database --tables table_name1 table_name2 table_name3>db_script.sql
导入sql:
mysql -u用户名 -p密码 --default-character-set=utf8 数据库名 < 备份文件名.sql
1、mysqldump 备份并压缩sql文件
mysqldump -h主机ip -u用户名 -p密码（也可不输入） 数据库名 | gzip > 压缩后文件位置
导入gzip:
gzip -d < 备份文件名.sql.gz | mysql -u用户名 -p密码
三种导出已查询到的数据方式
方式一
mysql -uroot -p --default-character-set=utf8mb4 -e "select * from 表名 where xxx;" 库名 > test.txt
mysql -uroot -p --default-character-set=utf8mb4 -e "select * from 库名.表名 where xxx;" > test.txt
方式二
指定 secure-file-priv 变量的值才能执行
SELECT * FROM (表名) INTO OUTFILE '/root/test.txt' CHARACTER SET utf8mb4;
方式三
mysqldump -uroot -p -c --default-character-set=utf8mb4 数据库名 数据表 --where="查询条件" > /abc.sql
3.3 特殊备份参数
-R 存储过程和函数
-E 事件
--triggers 触发器
--master-data=2 *****
(1) 记录备份时刻的binlog信息
(2) 自动锁表
不加--single-transaction ,温备份
加了--single-transaction,对于InnoDB表不锁表备份(快照备份)
--single-transaction *****
对于InnoDB的表,进行一致性快照备份,不锁表.
4. 恢复案例
4.1 背景环境：
正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量1-5M。
4.2 备份策略：
每天23:00点，计划任务调用mysqldump执行全备脚本
4.3 故障时间点：
年底故障演练:模拟周三上午10点误删除数据库.
4.4 思路：
1、停业务，挂维护页,避免数据的二次伤害
2、找一个临时库，恢复周二23：00全备
3、截取周二23：00 --- 周三10点误删除之间的binlog，恢复到临时库
4、测试可用性和完整性
5、
5.1 方法一：直接使用临时库顶替原生产库，前端应用割接到新库
5.2 方法二：将误删除的表导出，导入到原生产库
6、开启业务
处理结果：经过20分钟的处理，最终业务恢复正常
4.5 故障模拟演练
4.5.1 准备数据
create database backup;
use backup
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
rm -rf /backup/*
4.5.2 周二 23：00全备
mysqldump -uroot -p123 -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
4.5.3 模拟周二 23：00到周三 10点之间数据变化
use backup
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
commit;
4.5.4 模拟故障,删除表(只是模拟，不代表生产操作)
drop database backup;
4.6 恢复过程
4.6.1 准备临时数据库（多实例3307）
systemctl start mysqld3307
4.6.2 准备备份
（1）准备全备：
cd /backup
gunzip full_xxx.sql.gz
（2）截取二进制日志(查找起始位置进行截取)
vim full_xxx.sql.gz
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1941;
show binlog events in "mysql-bin.000001";
mysqlbinlog --skip-gtids --start-position=1941 --stop-position=2642 /data/binlog/mysql-bin.000002 >/backup/bin.sql
4.6.3 恢复备份到临时库(为数据安全着想在临时库进行恢复)
mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /backup/full_xxx.sql
source /backup/bin.sql
4.6.4 将故障表导出并恢复到生产
mysqldump -S /data/3307/mysql.sock -B backup >/backup/bak.sql
mysql -uroot -p123
set sql_log_bin=0
source /backup/bak.sql;
6. 扩展参数 ***
在构建主从时,使用AUTO/ON
--set-gtid-purged=AUTO/ON
仅是做普通的本机备份恢复时,可以添加
--set-gtid-purged=OFF
SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11';
--max_allowed_packet=128M 控制的是备份时传输数据包的大小.
mysqldump -uroot -p123 -A -R --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
Xtrabackup
7.1 安装依赖包：
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
yum -y install percona-xtrabackup
1. 备份的过程
(1) 非InnoDB表,进行短暂的锁表,然后Copy数据文件
(2) 对于InnoDB表,立即出发checkpoint,会立即记录一个LSN,COPY数据文件.
(3) 将备份过程中产生的redo进行截取和保存,并记录此时最新的LSN
2. 恢复过程
模拟了CSR的全过程,在恢复之前,将数据的LSN号和redo LSN号追平
恢复方法就是直接cp回去即可
3. XBK 全备和恢复体验
innobackupex --user=root --password=123 --no-timestamp /backup/full
innobackupex --apply-log /backup/full
4. 备份产生的文件介绍
(1) xtrabackup_binlog_info *****
记录备份时刻的二进制日志信息. 可以作为binlog截取的起点.
(2) xtrabackup_checkpoints *****
from : 备份中包含的LSN号的起点,全备:0,增量:上次备份的结束位置
to : ckpt 时的LSN
last-9 : 备份结束时的LSN.下次增量备份的起始位置.
5. 增量备份
5.1 清空备份路径
\rm -rf /backup/*
5.2 模拟数据
create database full charset utf8mb4;
use full;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
5.3 进行周日的全备
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full
5.4 模拟周一的数据变化
create database inc1 charset utf8mb4;
use inc1;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
5.5 进行周一的增量备份
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1
说明:
--incremental 开关
--incremental-basedir=/backup/full 基于哪个备份进行增量
/backup/inc1 增量备份的位置点
5.6 检查备份的LSN
[root@db01 backup]# cat /backup/full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 217478672
last_lsn = 217478681
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat /backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 217478672
to_lsn = 217484653
last_lsn = 217484662
compact = 0
recover_binlog_info = 0
5.7 模拟周二数据变化
create database inc2 charset utf8mb4;
use inc2;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
5.8 周二的增量
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2
5.9 周三的数据变化
create database inc3 charset utf8mb4;
use inc3;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
5.10 模拟上午10点数据库崩溃
pkill mysqld
\rm -rf /data/mysql/data/*
5.11 恢复思路
1. 停业务,挂维护页
2. 查找可用备份并处理备份:full+inc1+inc2
3. binlog: inc2 到 故障时间点的binlog
4. 恢复全备+增量+binlog
5. 验证数据
6. 起业务,撤维护页
5.12 恢复前的准备
(1) 整理full
innobackupex --apply-log --redo-only /backup/full
(2) 合并inc1到full,并整理备份
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full
(3) 合并inc2到full,并整理备份
innobackupex --apply-log --incremental-dir=/backup/inc2 /backup/full
(4) 最后一次整理full
innobackupex --apply-log /backup/full
5.13 截取二进制日志
起点:
cat /backup/inc2/xtrabackup_binlog_info
mysql-bin.000005 1912 75999fbc-f62c-11ea-bb91-000c2958d755:1-20
终点:
mysqlbinlog /data/binlog/mysql-bin.000031 |grep 'SET'
SET @@SESSION.GTID_NEXT= 'e16db3fd-a6e8-11e9-aee9-000c294a1b3b:12'/*!*/;
mysqlbinlog --skip-gtids --include-gtids='e16db3fd-a6e8-11e9-aee9-000c294a1b3b:10-12' /data/binlog/mysql-bin.000031>/backup/binlog.sql
mysqlbinlog --skip-gtids --start-gtid=' ' /data/binlog/mysql-bin.000005 >/backup/binlog.sql
5.14 恢复备份数据
[root@db01 /]# cp -a /backup/full/* /data/mysql/data/
[root@db01 /]# chown -R mysql. /data/
[root@db01 /]# /etc/init.d/mysqld start
mysql> set sql_log_bin=0;
mysql> source /backup/binlog.sql
5.15 验证数据
mysql> select * from full.t1;
mysql> select * from inc1.t1;
mysql> select * from inc2.t1;
mysql> select * from inc3.t1;
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
(5) 启动数据库
[root@oldboyedu data]# /etc/init.d/mysqld start
[root@oldboyedu data]# mysqladmin -uroot -p password 123
6.2 迁移5.6 数据到 5.7 (扩展)
(1) 5.6 数据库备份
[root@oldboyedu ~]# mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql
[root@oldboyedu ~]# scp /tmp/full.sql 10.0.0.51:/data/3308
(2)准备5.7数据库
[root@db01 /]# systemctl start mysqld3308
[root@db01 /]# mysql -S /data/3308/mysql.sock
mysql> source /data/3308/full.sql
[root@db01 /]# mysql_upgrade -uroot -p123 -S /data/3308/mysql.sock
(3) binlog的持续追加
(4) 停业务,恢复剩余的binlog
(5) 业务割接
