数据库备份和恢复
1.运维人员在备份恢复方面的职责
1.1 设计备份策略
1）备份内容 ：数据，二进制日志
2）数据容量：大小
3）备份周期：1.每天全备
2.周期全备+增量备份
3.周期全备+日志备份
4)备份时间：晚上，
5）备份目标位置：
1.2日常备份检查
日志检查、备份的内容、备份大小
1.3定期恢复演练
每半年做一次
1.4故障时的恢复
快速准确恢复数据
1.5平台数据迁移
同构平台
异构平台
2.备份工具介绍
2.1介绍
mysqldump (MDP) 逻辑备份工具
Percona Xtrabackup (xbk, pbk, pxb)物理备份
2.2区别
MDP:逻辑备份，SQL文件，文本形式，可读性高，便于处理，压缩比高、备份相对较慢，适用于100G以内的备份。
100G数据，备份30-40分钟
xbk: 物理备份，数据文件、可读性低，压缩比低、备份相对较快，适用于100G以内的备份。
100G 10分钟
3.mysqldump
3.1 备份逻辑
将磁盘数据，加载到临时表，转换为SQL语句(建库、建表、插入语句)
3.2 核心参数
1）连接参数
本地：-u - p -S
远程：-u -p -H -P
2)备份参数
1.全备参数
-A全备
-B 指定多个库名
[root@db01 ~]# mysqldump -uroot -p123 -A >/data/backup/full.sql
2.单表或多库备份
[root@db01 ~]# mysqldump -uroot -p123 -B bgx oldboy test world mysql > /data/backup/db.sql
3.单标或多表备份
[root@db01 ~]# mysqldump -uroot -p123 world city country >/data/backup/tab.sql
4.只导出建表语句
[root@db01 ~]# mysqldump -uroot -p123 world city country --no-data >/data/backup/tab1.sql
5.--master-data=2
(1) 自动记录备份时的binlog filename 及pos
(2) 自动加锁和解锁
(3) 加了--single-transaction , 会有不一样的效果( global read lock is only taken a short time )
mysqldump -uroot -p123 -A --master-data=2 >/data/backup/full.sql
6. --single-transaction
开启独立事务,备份InnoDB表的一致性快照.实现InnoDB表的热备功能.
补充:
对于非InnoDB表(FTWRL Flush tables with read lock 全局锁表) ,对于mysq1库下的系统表备份的时候,会加global read lock
Flush tables with read lock 全局锁表
unlock tables关闭锁表
7. 特殊的数据库对象
-R 存储过程和函数
--triggers 触发器
-E 事件
mysqldump -uroot -p -A --master-data=2 --single-transaction -R --triggers -E >/data/backup/full.sql
8. --max_allowed_packet=128M
1153 - Got a packet bigger than 'max_allowed_packet' bytes
出现场景: 在数据库有超大比表时,需要加此的参数.
9. 终极备份语句
mysqldump -uroot -p -A --master-data=2 --single-transaction -R --triggers -E --max_allowed_packet=128M |gzip >/data/backup/full_date +%F.sql.gz
3.3 模拟故障和恢复(mysqldump 全备+ binlog)
(1) 模拟原始数据
create database mdp charset utf8mb4;
use mdp
create table t1 (id int);
create table t2 (id int);
create table t3 (id int);
insert into t1 values(1);
commit;
insert into t2 values(1);
commit;
insert into t3 values(1);
commit;
(2) 模拟全备
mysqldump -uroot -p -A --master-data=2 --single-transaction -R --triggers -E --max_allowed_packet=128M |gzip >/data/backup/full_date +%F.sql.gz
(3) 模拟新的数据
use mdp
insert into t1 values(2);
commit;
insert into t2 values(2);
commit;
insert into t3 values(2);
commit;
(4) 搞破坏
drop database mdp;
(5) 恢复思路
-- 1. 挂维护页
-- 2. 找测试库
-- 3. 准备备份
full
截取binlog部分
-- 4. 恢复全备+binlog到测试库 , 业务测试.
-- 5. 导出故障库数据,恢复到生产 .
-- 6. 撤维护页.
恢复过程:
1. 准备备份
vim /data/backup/full_2019-12-26.sql
第30行:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2570;
2. 截取二进制日志
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2570;
起点 : 2570
mysql> show binlog events in 'mysql-bin.000003';
| mysql-bin.000003 | 3388 | Query | 6 | 3477 | drop database mdp
终点 : 3388
mysqlbinlog --skip-gtids --start-position=2570 --stop-position=3388 /data/mysql/binlog_3306/mysql-bin.000003 >/data/backup/bin.sql
3. 恢复备份
set sql_log_bin=0;
source /data/backup/full_2019-12-26.sql
source /data/backup/bin.sql
set sql_log_bin=1;
4. 检查数据
mysql> show tables;
mysql> select * from t1;
mysql> select * from t2;
mysql> select * from t3;
5. 扩展: 从全备中提取 单库 单表的数据进行恢复
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.获取单库的备份
