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 databasecreate tableinsert)保存到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?),告诉用户我们是在维护升级,不是数据库坏了

                    
  
                
THE END
喜欢就支持一下吧
点赞8分享