欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL之备份恢复

程序员文章站 2022-04-19 12:35:22
1. 数据损坏种类1.1 物理损坏磁盘损坏:硬件、坏道、dd、格式化文件损坏:数据文件损坏、redo log损坏1.2 逻辑损坏dropdeletetruncateupdate2. 运维人员在备份、恢复的职责2.1 设计备份、容灾策略备份策略:备份工具选择备份周期设计备份监控方法容灾策略:备份架构:高可用、延迟从库、灾备库(两地三中心)2.2 定期的备份、容灾检查2.3 定期的故障恢复演练2.4 数据损坏时的快速准确恢复2.5 数据迁...

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备份全库的区别

MySQL之备份恢复

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