MySQL-备份恢复及迁移

1、备份类型（笔试）

1.1 冷备（cold backup）

业务停止或数据库关闭时进行备份，业务影响最大.

1.2 温备（warm backup）

锁表备份、只读备份，阻塞所有的变更操作，只能读

1.3 热备（hot backup）

不锁表备份，只能针对具备事务型引擎的表（例如：innoDB），业务的影响最小.

2、备份工具介绍

2.1 mysqldump

简称MDP
优势：逻辑备份工具，备份的都是SQL语句，文本格式、压缩比高，可读性较强，便于二次处理、自带工具。
劣势：相对较慢（从磁盘调取数据---->内存---->转换成SQL----->xxx.sql）、不能做增量备份

2.2 Xtrabackup（percona）

简称XBK、PBK
物理备份工具，备份的是数据文件（类似cp）,
优势：速度快、支持热备、自带了增量备份
劣势：需要安装、可读性比较差、不便于二次处理、压缩比低

2.3 选择建议（了解）

小于100G-----------MDP、XBK
超过100G-1T------XBK
超过TB级别--------XBK、MDP

3、备份策略

备份方式：
    全备:全库备份，备份所有数据
    增量:备份变化的数据

逻辑备份=mysqldump+binlog
    物理备份
    XBK_full+xbk_inc+binlog
    或者
    xtrabackup_full+binlog
    
备份周期:
    根据数据量设计备份周期
    比如：周日全备，周1-周6增量

4、mysqldump

4.1 客户端通用的参数

-u：指定用户名
-p：输入密码
-h：指定远程ip地址
-P：指定端口号
-S:

本地备份:
mysqldump -uroot -p  -S /tmp/mysql.sock
远程备份:
mysqldump -uroot -p  -h 10.0.0.51 -P3306

4.2 基础备份参数

-A：全库备份
-B：单库或多库备份

4.2.1 实例练习

1> 全库备份（参数 -A 的使用）

[root@db01 ~]# mysqldump -uroot -p123456 -A >/root/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@db01 ~]#

2> 只备份world和test两个库（参数 -B 的使用）

[root@db01 ~]# mysqldump -uroot -p123456 -B world test >/backup/world-test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@db01 ~]#

3> 单库或多表备份

[root@db01 ~]# mysqldump -uroot -p123456 world city country >/backup/tab.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@db01 ~]#

说明：
mysqldump -uroot -p123456  world   city    country >/backup/tab.sql
                           库名     表1      表2
使用source恢复

4.3 特殊功能参数

4.3.1 -R --triggers -E 数据库特殊对象备份参数

-R --triggers -E  数据库特殊对象备份参数
-R：存储过程、函数
--triggers：触发器
-E：事件

4.3.2 --master-data=2 ※※※※※

功能:

以注释形式，记录备份时binlog文件名和position号（截取二进制日志的起点）。

自动锁表功能，加--single-transaction，减少锁表。自动解锁。

自动解锁。

4.3.3 --single-transaction ※※※※※

快照备份、热备

4.3.4 --set-gtid-purged=OFF (GTID模式独有的参数.)

作用,去除gtid所有信息,在日常备份恢复时可加.
做主从复制应用的时候,不能加此参数.

4.3.5 --max-allowed-packet=512M

注意：正式库上完整的备份语句
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --set-gtid-purged=OFF --max-allowed-packet=256M > /backup/full.sql

mysqldump -uroot -p123 -A -E -R --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M |gzip > /backup/full_$(date +%F).sql.gz

5、企业故障恢复案例

5.1 背景环境：

正在运行的小型网站系统，mysql-5.7.20 数据库，数据量50G.
每天23:00点，计划任务调用mysqldump执行全备脚本
[root@db01 /backup]# mysqldump -uroot -p123 -A -E -R --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M |gzip > /backup/full_$(date +%F).sql.gz

5.2 故障时间点：

年底故障演练:模拟周三上午10点误删除数据库，并进行恢复.

5.3 恢复思路

(1) 停止故障业务,挂维护页.
(2) 准备测试库,进行全库恢复
(3) 截取从全备开始一直到故障时刻的binlog
(4) 测试数据可用和完整性
(5) 将故障数据导出,导入到生产
(6) 撤维护页,开启业务.

5.4 演练

5.4.1 故障现场模拟

（1）模拟数据

wenjuan[(none)]>create database mdp charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

wenjuan[(none)]>use mdp;
Database changed

wenjuan[mdp]>create table t1(id int) engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

wenjuan[mdp]>create table t1(id int) engine=innodb charset=utf8mb4;
ERROR 1050 (42S01): Table 't1' already exists
wenjuan[mdp]>insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

wenjuan[mdp]>commit;
Query OK, 0 rows affected (0.00 sec)

（2）模拟晚上23:00全备

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

（3）模拟备份后的数据变化

wenjuan[mdp]>insert into t1 values(11),(12),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

wenjuan[mdp]>commit;
Query OK, 0 rows affected (0.00 sec)

wenjuan[mdp]>

（4）搞破坏

wenjuan[mdp]>drop database mdp;
Query OK, 1 row affected (0.00 sec)

wenjuan[(none)]>

5.4.1 恢复数据

（1）停止故障业务，挂维护页。
（2）全备准备

[root@db01 /backup]# gunzip full_2019-08-16.sql.gz  

----binlog准备
[root@db01 /backup]# vim full_2019-08-16.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=1867;

wenjuan[(none)]>show binlog events in 'mysql-bin.000008';
wenjuan[(none)]>show master status;
| mysql-bin.000008 | 2128 | Gtid           |         6 |        2193 | SET @@SESSION.GTID_NEXT= '936b9a3f-b75a-11e9-bd16-000c290143b9:16' |


[root@db01 /backup]# mysqlbinlog  --skip-gtids  --start-position=1867 --stop-position=2193 /data/3306/binlog/mysql-bin.000008 >/backup/binlog.sql

（3）进行恢复

wenjuan[(none)]>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

wenjuan[(none)]>source /backup/full_2019-08-16.sql 
wenjuan[mdp]>select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

wenjuan[mdp]>


wenjuan[mdp]>source /backup/binlog.sql;
wenjuan[mdp]>select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   12 |
|   13 |
+------+
6 rows in set (0.00 sec)

wenjuan[mdp]>

6、XBK（Percona-Xtrabackup）---物理备份工具

6.1 percona-xtrabackup工具安装

安装依赖包：
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

下载并安装
提前下载rpm包到本地，并上传到虚拟机
yum install -y percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

6.2 介绍

物理备份工具，类似于cp数据

6.3 备份细节

非InnoDB：例如：MyISAM，自动锁表备份，会有短暂的全局锁（FTWRL）.
InnoDB：
    立即进行CKPT，将当前所有的已提交事务的脏页，立即刷写到磁盘上。（to_lsn）
    拷贝所有InnoDB的数据文件，系统数据文件也一并拷贝。
    将备份过程中产生的redo截取并备份走。（last_lsn）

6.4 innobackupex 备份应用

6.4.1 修改配置文件

[root@db01 ~]# vim /etc/my.cnf
[client]
socket=/tmp/mysql.sock

[root@db01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESSinnobackupex! 
[root@db01 ~]#

6.4.2 全备

[root@db01 ~]# innobackupex --user=root --password=123456  /backup/xbk
[root@db01 ~]# ll /backup/xbk
total 4
drwxr-x--- 16 root root 4096 Aug 16 10:34 2019-08-16_10-34-04


[root@db01 ~]# innobackupex --user=root --password=123456  --no-timestamp  /backup/xbk/full
[root@db01 ~]# ll /backup/xbk
total 12
drwxr-x--- 17 root root 4096 Aug 16 12:33 full

说明：
--no-timestamp   不要用时间生成目录，可以自己制定目录名

6.4.3 搞破坏

[root@db01 ~]# pkill mysqld 
[root@db01 ~]# rm -rf /data/3306/data/*
[root@db01 ~]#

6.4.4 数据恢复准备（备份处理）

[root@db01 /backup/xbk]# innobackupex --apply-log /backup/xbk/full/

说明：
--apply-log     数据处理

6.4.5 恢复数据

[root@db01 ~]# innobackupex --copy-back   /backup/xbk/full/ 
授权
[root@db01 /data/3306/data]# chown -R mysql.mysql *

说明：
--copy-back (依赖于my.cnf [mysqld]的配置)     把备份恢复回去，即cp
恢复数据也可以用cp：
[root@db01 ~]# cp -a /backup/xbk/full/* /data/3306/data/
[root@db01 /data/3306/data]# chown -R mysql.mysql *

6.4.6 重启mysql服务测试

[root@db01 /data/3306/data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/3306/data/mysql.log'.
 SUCCESS! 
[root@db01 /data/3306/data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

wenjuan[(none)]>

--apply-log 参数说明（面试重点）:

模仿了InnoDB引擎的ACSR的过程。

将备份集中的数据和日志 LSA追平。

利用redo进行前滚。

利用undo进行回滚。

6.4.7 备份集的文件认识

xtrabackup_binlog_info：存储的是binlog截取的起始点信息(position,gtid)
xtrabackup_checkpoints
xtrabackup_info
xtrabackup_logfile
xtrabackup_master_key_id


[root@db01 /backup/xbk/full]# cat xtrabackup_binlog_info 
mysql-bin.000002    194 63c5b880-bfc1-11e9-beaf-000c2980e248:1-6

[root@db01 /backup/xbk/full]# cat xtrabackup_checkpoints
backup_type = full-prepared     备份类型（分为全备、增备）
from_lsn = 0                    整个备份包含的最起始的LSN号码
to_lsn = 167153081              ckpt后数据页(ibd)的LSN
last_lsn = 167153090           备份结束时,redo的LSN,(在当前5.7版本,会有9个LSN可以忽略)
[root@db01 /backup/xbk/full]#

7、XBK增量(incremental)备份

Xtrabackup企业级增量恢复实战
背景：
某大型网站，mysql数据库，数据量500G，每日更新量20M-30M
备份策略：
xtrabackup，每周日23:00进行全备，周一到周六23:00进行增量备份。
故障场景：
周三下午2点出现数据库意外删除表操作。
如何恢复？

7.1 清除以往备份

[root@db01 /backup/xbk]# rm -rf /backup/xbk/*

7.2 模拟数据

wenjuan[(none)]>create database xbk charset utf8mb4;
Query OK, 1 row affected (0.01 sec)

wenjuan[(none)]>use xbk;
Database changed
wenjuan[xbk]>create table t1(id int) engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

wenjuan[xbk]>insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

wenjuan[xbk]>commit;
Query OK, 0 rows affected (0.00 sec)

7.3 模拟周日全备

[root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp /backup/xbk/full

7.4 模拟周一的数据变化

wenjuan[xbk]>use xbk;
Database changed
wenjuan[xbk]>insert into t1 values(11),(12),(13);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

wenjuan[xbk]>commit;
Query OK, 0 rows affected (0.00 sec)

wenjuan[xbk]>

7.5 模拟周一晚上增加备份

[root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/xbk/full /backup/xbk/inc1

--incremental  打开增量备份的开关
--incremental-basedir     设定增量备份的基备份（一般是上一天）

7.6 模拟周二白天的数据变化

wenjuan[xbk]>use xbk;
Database changed
wenjuan[xbk]>insert into t1 values(111),(222),(333);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

wenjuan[xbk]>commit;
Query OK, 0 rows affected (0.01 sec)

wenjuan[xbk]>

7.7 模拟周二增量

[root@db01 /backup/xbk]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/xbk/inc1 /backup/xbk/inc2

7.8 模拟周三白天的数据变化

wenjuan[xbk]>use xbk;
Database changed
wenjuan[xbk]>insert into t1 values(1111),(2222),(3333);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

wenjuan[xbk]>commit;
Query OK, 0 rows affected (0.00 sec)

wenjuan[xbk]>

7.9 搞破坏（比如删库）

[root@db01 /data/3306/data]# pkill mysqld
[root@db01 /data/3306/data]# rm -rf /data/3306/data/*
[root@db01 /data/3306/data]# ll
total 0
[root@db01 /data/3306/data]#

7.10 恢复思路

(1) 测试库,维护页
(2) 处理备份
    合并
    准备 
(3) 截取二进制日志
(4) 数据恢复

7.11 开始恢复演练

7.11.1 处理备份

（1）处理原始全备

[root@db01 ~]# innobackupex --apply-log --redo-only /backup/xbk/full

--apply-log    
--redo-only    只做前滚不做回滚
为了保证LNS号码上一个号码和下一个号码有连接性

（2）合并周一并处理

[root@db01 ~]# innobackupex --apply-log --redo-only  --incremental-dir=/backup/xbk/inc1 /backup/xbk/full

（3）合并周二并处理

[root@db01 ~]# innobackupex --apply-log  --incremental-dir=/backup/xbk/inc2   /backup/xbk/full

（4）处理合并后的新全备数据

[root@db01 ~]# innobackupex --apply-log  /backup/xbk/full

7.11.2 恢复备份，并启动数据库

[root@db01 ~]# cp -a /backup/xbk/full/* /data/3306/data/
[root@db01 /data/3306/data]# chown -R mysql.mysql *

[root@db01 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/3306/data/mysql.log'.
. SUCCESS! 
[root@db01 ~]#

7.11.3 截取binlog

[root@db01 ~]# cat /backup/xbk/inc2/xtrabackup_binlog_info
mysql-bin.000010    1339    400dd8cc-bfd3-11e9-9a4a-000c290143b9:1-5,
936b9a3f-b75a-11e9-bd16-000c290143b9:1-16
[root@db01 ~]# 

wenjuan[(none)]>show binlog events in 'mysql-bin.000010';
| mysql-bin.000010 | 1569 | Xid            |         6 |        1600 | COMMIT /* xid=81 */                     


[root@db01 ~]# mysqlbinlog --skip-gtids --start-position=1339 --stop-position=1600 /data/3306/binlog/mysql-bin.000010 >/backup/bin.sql


wenjuan[(none)]>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

wenjuan[(none)]>source /backup/bin.sql;


wenjuan[(none)]>use xbk;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
wenjuan[xbk]>select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   12 |
|   13 |
|  111 |
|  222 |
|  333 |
| 1111 |
| 2222 |
| 3333 |
+------+
12 rows in set (0.00 sec)

wenjuan[xbk]>

最后编辑于：2019.08.17 22:29:22

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,547评论 6赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,399评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,428评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,599评论 1赞 274
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,612评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,577评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,941评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,603评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,852评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,605评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,693评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,375评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,955评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,936评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,172评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 43,970评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,414评论 2赞 342