MySQL之备份恢复

1. 数据损坏种类

1.1 物理损坏

• 磁盘损坏：硬件、坏道、dd、格式化

• 文件损坏：数据文件损坏、redo log损坏

1.2 逻辑损坏

• drop
• delete
• truncate
• update

2. 运维人员在备份、恢复的职责

2.1 设计备份、容灾策略

• 备份策略：
  • 备份工具选择
  • 备份周期设计
  • 备份监控方法
• 容灾策略：
  • 备份
  • 架构：高可用、延迟从库、灾备库(两地三中心)

2.2 定期的备份、容灾检查

2.3 定期的故障恢复演练

2.4 数据损坏时的快速准确恢复

2.5 数据迁移工作

3. 常用备份工具

3.1 逻辑备份方式

• mysqldump(MDP)
• replication
• mydumper
• load data in file

3.2 物理备份方式

• MySQL Enterprise Backup
• Percona Xtrabackup(PBK、XBK)

4. mysqldump

4.1 介绍

逻辑备份工具，备份的是SQL语句

对InnoDB表：
可以采用快照备份的方式：开启一个独立的事务，获取当前最新的一致性快照(不需要锁表)
将快照数据放在临时表中，转换成SQL(create database、create table、insert)保存到SQL文件中

对于非InnoDB表(MyISAM)：
需要锁表备份，会触发FTWRL，全局锁表，转换成SQL，保存到SQL文件中

4.2 核心参数

4.2.1 连接参数

和mysql客户端的参数一样
-u		user，用户名
-p		password，密码
-h		host，主机地址
-P		port，端口
-S		socket

4.2.2 备份参数

-A		全备份，在恢复时会覆盖恢复(删除已有的重名表)
[root@db01 ~]# mkdir -p /data/backup
[root@db01 ~]# chown -R mysql.mysql /data/*
[root@db01 ~]# mysqldump -A > /data/backup/full.sql
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. 

-B		备份1个或多个库，备份时会添加create database和use database两条语句
[root@db01 ~]# mysqldump -B world test > /data/backup/db.sql

不加参数可以单独备份表，第一个参数为库名，后面的为该库下面的表
[root@db01 ~]# mysqldump world city country > /data/backup/tab.sql
用该方法备份全库时需要自行创建数据库并use到该库后再恢复        

使用和不使用-B备份全库的区别

4.2.3 高级参数

• --master-data：备份时将binlog的位置和文件名追加到输出中，1直接追加，2为注释追加，0为不追加，配合下一个参数使用可以减小锁表的影响

--master-data[=#] This causes the binary log position and filename to be
    appended to the output. If equal to 1, will print it as a
    CHANGE MASTER command; if equal to 2, that command will
    be prefixed with a comment symbol. This option will turn
    --lock-all-tables on, unless --single-transaction is
    specified too (in which case a global read lock is only
    taken a short time at the beginning of the dump; don't
    forget to read about --single-transaction below). In all
    cases, any action on logs will happen at the exact moment
    of the dump. Option automatically turns --lock-tables
    off.

• –single-transaction：对于InnoDB引擎表备份时，开启一个独立的事务，获取一致性快照再进行备份(就不用锁表了)

--single-transaction 
    Creates a consistent snapshot by dumping all tables in a
    single transaction. Works ONLY for tables stored in
    storage engines which support multiversioning (currently
    only InnoDB does); the dump is NOT guaranteed to be
    consistent for other storage engines. While a
    --single-transaction dump is in process, to ensure a
    valid dump file (correct table contents and binary log
    position), no other connection should use the following
    statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
    TRUNCATE TABLE, as consistent snapshot is not isolated
    from them. Option automatically turns off --lock-tables.

• -R -E --traiggers：导出存储过程、事件、触发器
• --max_allowed_packet：发送包数据最大值

参数使用场景分析：

场景：每周全备，每天备份binlog，所有备份都是完整的，有一天一个扑街删库跑路了

方案：恢复全备+所需要的binlog恢复
痛点：binlog起点和终点的截取
终点：drop操作前的位置点
起点：
	方案一：备份时使用-F选项，每次开始备份时都会滚动一次日志，但每备份一个库都会滚动一次日志，会生成很多binlog日志文件
	方案二：使用上述高级参数，全备中会记录开启备份时的binlog位置点和文件信息

4.3 mysql+binlog故障恢复案例

案例场景：
	基础环境：CentOS 7.6 + MySQL 5.7.28，LNMT 网站业务，数据量100G，每天增长5-10M数据
	备份策略：mysqldump每天全备，binlog定时备份
	故障说明：周三上午10点数据故障，如：核心业务库被误删

恢复思路：
	1. 挂维护页面(503?)，告诉用户我们是在维护升级，不是数据库坏了