数据库备份和恢复

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.获取单库的备份

sed -n '/^-- Current Database: world/,/^-- Current Database: `/p' all.sql >world.sql

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 213,864评论 6 494
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 91,175评论 3 387
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 159,401评论 0 349
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 57,170评论 1 286
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,276评论 6 385
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,364评论 1 292
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,401评论 3 412
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,179评论 0 269
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,604评论 1 306
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,902评论 2 328
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,070评论 1 341
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,751评论 4 337
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,380评论 3 319
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,077评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,312评论 1 267
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,924评论 2 365
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,957评论 2 351

推荐阅读更多精彩内容