备份恢复
备份策略的设计
备份周期 根据数据量
备份工具 mysqldump，XBK，MEB
备份方式
逻辑
全备 mysqldump
增量 binlog(flush logs，cp)
物理备份
全备 XBK
增量 XBK
检查备份可用性
crontab -l
备份脚本
备份路径
看备份日志，检查备份文件(大小，内容)
定期的恢复演练
数据恢复
只要备份和日志是完整的，恢复到故障之前的时间点(快速)
数据迁移
备份类型
热备 对于业务影响最小 InnoDB
温备 长时间锁表备份 MyISAM
冷备 业务关闭情况下备份
逻辑备份
mysqldump
连接数据库
-u
-p
-S
-h
基础备份参数
-A
mysqldump -uroot -p123 -A >/backup/full.sql
-B
mysqldump -uroot -p123 -B world oldguo wordpress >/backup/db.sql
库表备份
mysqldump -uroot -p123 world city country >/backup/tab.sql
特殊备份参数
-R 存储过程和函数
-E 事件
--triggers 触发器
--master-data=2
记录备份时刻的binlog信息
自动锁表
不加--single-transaction 温备份
加了--single-transaction 对于InnoDB表不锁表备份(快照备份)
--single-transaction 对于InnoDB的表，进行一致性快照备份，不锁表
恢复案例
背景环境 正在运行的网站系统，mysql-5.7.20数据库，数据量50G，日业务量1-5M
备份策略 每天23:00点，计划任务调用mysqldump执行全备脚本
故障时间点 年底故障演练，模拟周三上午10点误删数据库，并进行恢复
思路
停业务，避免数据的二次伤害
找一个临时库，恢复周三23:00全备
截取周二23:00-周三10点误删除之间的binlog，恢复到临时库
测试可用性和完整性
方法1 直接使用临时库顶替原生产库，前端应用割接到新库
方法2 将误删的表导出，导入到原生产库
开启业务 经过20分钟的处理，最终业务恢复正常
故障模拟演练
准备数据
create database backup;
use backup
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
rm -rf /backup/*
周二23:00全备
mysqldump -uroot -p123 -A -R --striggers --set-gtid-purged=OFF --master-data=2
--single-transaction | gzip >/backup/full_$(data +%F).sql.gz
模拟周二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);
模拟故障，删除表(只是模拟，不代表生产操作)
drop database backup;
恢复过程
准备临时数据库
systemctl start mysqld3307;
准备全备
cd /backup
gunzip full_2018-10-14.sql.gz
截取二进制日志
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=753;
mysqlbinlog --skip-gtids --start-position=753 --stop-position=1519 /data/binlog/mysql-bin.000002>/backup/bin.sql
恢复备份到临时库
mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /backup/full_2019-07-15.sql
source /backup/bin.sql
将故障表导出并恢复到生产
mysqldump -S /data/3307/mysql.sock -B backup >/backup/t1.sql
mysql -uroot -p123
set sql_log_bin=0
source /backup/bak.sql;
扩展参数
--set-gtid-purged=AUTO/ON 在构建主从时，使用AUTO/ON
--set-gtid-purged=OFF 仅是做普通的本机备份恢复时，可以添加
--max_allowed_packet=128M 控制的是备份时传输数据包的大小
物理备份(XBK)
安装依赖包
下载软件并安装
innobackupex使用
备份的过程
针对非InnoDB，进行锁表备份，copy所有的非innoDN表文件
针对InnoDB表，立即触发CKPT，copy所有InnoDB表相关的文件(ibdata1，ibd，frm)，并且将备份过程中产生的新的数据变化的部分redo一起备份走
在恢复时，xbk会调用InnoDB引擎的CSR过程，将数据和redo的LSN追平，然后进行一致性恢复
XBK全备和恢复体验
innobackupex --user=root --passwd=123 --no-timestamp /backup/full
innobackupex --apply-log /backup/full
备份产生的文件介绍
xtrabackup_binlog_info
记录备份时刻的二进制日志信息，可以作为binlog截取的起点
xtrabackup_checkpoints
from 备份中包含的LSN号的起点，全备是0，增量是上次备份的结束位置
to cptk时的LSN
last 备份结束时的LSN，下次增量备份的起始位置
增量备份
清空备份路径
\rm -rf /backup/*
模拟数据
create database full charset utf8mb4;
use full;
create table t1 (id int);
insert into te values(1),(2),(3);
commit;
进行周日的全备
innobackupex --user=root --password=123 --no-timestamp /backup/full
模拟周一的数据变化
create database inc1 charset utf8mb4;
use inc1;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
进行周一的增量备份
innobackupex --user=root --password123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/incl
检查备份的LSN
cat /backup/full/xtrabackup_checkpoints
cat /backup/inc1/xtrabackup_checkpoints
模拟周二数据变化
create database inc2 charset utf8mb4;
use inc2;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
周二的增量
innobackupex --user=root --password=123 --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(1),(2),(3);
commit;
模拟上午10点数据库崩溃
pkill mysqld
\rm -rf /data/mysql/data/*
恢复思路
停业务，挂维护页
查找可用备份并处理备份 full+inc1+inc2
binlog inc2到故障时间点的binlog
恢复全备+增量+binlog
验证数据
起业务，撤维护页
恢复过程
整理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 --incremental-dir=/backup/inc2 /backup/full
最后一次整理full
innobackupex --apply-log /backup/full
截取二进制日志
起点
cat /backup/inc2/xtrabackup_binlog_info
终点
mysqlbinlog /data/binlog/mysql-bin.000031 | grep 'SET'
截取
mysqlbinlog --skip-gtids --include-gtids='e16db3fd-a6e8-11e9-aee9-000c294a1b3b:10-12' /data/binlog/mysql-bin.000031 >/backup/binlog.sql
恢复备份数据
cp -a /backup/full/* /data/mysql/data/
chown -R mysql /data/
/etc/init.d/mysqld start
set sql_log_bin=0;
source /backup/binlog.sql
验证数据
select * from full.t1;
select * from inc1.t1;
select * from inc2.t1;
select * from inc3.t1;
备份集中单独恢复表
如果误删除的表只有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.6.44-->5.7.26)
搭建5.6的测试环境
mkdir /data/mysql/data -p
mkdir /application/ -p
mkdir /data/binlog -p
上传至/application下并解压
建用户，改权限
useradd mysql
chown -R mysql. /data /application
修改环境变量
vim /etc/profile
export PATH=/application/mysql/bin:$PATH
source /etc/profile
数据初始化
yum remove mariadb-libs
yum install -y libaio-devel
\rm -rf /data/mysql/data/*
/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
准备配置文件和启动脚本
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
cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld
启动数据库
/etc/init.d/mysql start
mysqladmin -uroot -p password 123
迁移5.6数据到5.7
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
准备5.7数据库
systemctl start mysqld3308
mysql -S /data/3308/mysql.sock
source /data/3308/full.sql
mysql_upgrade -uroot -p123 -S /data/3308/mysql.sock
binlog的持续追加
停业务，恢复剩余的binlog
业务割接
网友评论