聊聊数据库：MySQL备份恢复~通用篇

MySQL备份恢复

• 前言

• 全备、差异备份、增量备份的概念

• 工具介绍

• mysqldump

• 1.备份

• 1.1.常用备份方式

• 1.2.指定表的备份

• 1.3.master-data的说明

• 1.4.指定条件的备份

• 1.5.结构和数据分开备份

• 2.恢复

• 2.1.常规恢复

• 2.2.分结构和数据备份的恢复

• 2.3.提下几个可能出现的错误

前言

上篇回顾：聊聊数据库：MySQL监控 ~ 性能指标

SQLServer的备份在2017年的时候就说过，图形+SQL+Demo，感兴趣的同志可以回顾一下：

https://www.cnblogs.com/dunitian/p/6260481.html

说到数据库备份，常想到的就是这三大块：全备份、增量备份、差异备份，这个在SQLServer中是最常见的备份方式

但说到MySQL咱们往往最常使用的是物理备份（物理文件拷贝），然后才是逻辑备份（生成sql文件）

PS：逻辑备份的可移植性比较强，但恢复效率较低；物理备份移植性比较差（DB的环境版本要一致）但恢复效率较高

提示：如果是诸如Memory类型的表（数据没存在磁盘中）那么物理备份只能备份表结构

PS：不管是逻辑备份还是物理备份，通常都需要使用binlog ==> 也对binlog进行一波备份

全备、差异备份、增量备份的概念

画一张图说明一下差异备份：在上一次全备的基础上进行差异备份 ==> 每次备份文件都会越来越大，直到下一次全备份

PS：第一天全备了，那么第2天差异备份就只会备份第2天本身新增的数据，第3天则会备份第2天和第3天新增的数据

增量备份则只备份每天新增的数据（PS：SQLServer中我们最常用的就是全备份和增量备份）

PS：缺点也很明显，还原数据的时候需要先还原最近的全备份，然后依次还原每次的增量备份，恢复所需要的时间就比较长了

工具介绍

备份不一定是root账号，只要具有 select、reload、locktables、replication client、show view、process
权限就可以了（如果使用 --tab
那么还需要 file
权限）

PS：create user'dbbak'@'%'identifiedby'密码';
grantselect,reload,locktables,replication client,show view,process on*.*to'dbbak'@'%';

mysqldump

mysqldump在对表备份的时候会加锁，这时候线上有写操作产生就会阻塞（表越大阻塞时间越长）

PS：原理就是先读出来，然后写入备份sql文件中，这个过程会污染innodb buffer pool，可能就会把热数据挤出，那查询性能就必然会下降

先看下帮助文档（最常用： mysqldump[OPTIONS]database[tables]
）

[OPTIONS]

# 数据库中有这些就加上
-E, --events             # 备份计划任务
-R, --routines           # 备份存储过程和函数
--triggers               # 备份触发器（一般用不到）

-F, --flush-logs         # 备份的时候同时刷新一份新的二进制日志
--master-data=1|2        # 将二进制的信息（名称、偏移量）写入到sql中（1生成CHANGE MASTER，2则注释掉）
--max-allowed-packet=x   # 设置最大包的值（<=目标库的max-allowed-packet值）

--single-transaction     # 用于备份事务性存储引擎时使用（保证数据一致性）
-l, --lock-tables        # 锁定指定数据库下的所有表（备份过程中只能读）
-x, --lock-all-tables    # 锁定所有数据库的表

-w, --where=name         # 条件备份时候使用（只支持单表的导出）

--hex-blob               # 以16进制的形式保存（防止出现文本乱码）
--tab=path               # 为数据库中的每个表生成两个文件，一个存储表结构，一个存储表数据（一般都是mysqldump与mysqld在同一台机器才使用）
复制

再说下 --single-transaction
，这个参数一般在存储引擎都是InnoDB的时候使用，备份的时候会启动一个事物来保证数据的一致性

PS：如果有其他非事务性存储引擎，那么只能使用低效率的 --lock-tables
了（这也是为什么推荐存储引擎全部使用InnoDB）

其他的两款下次继续说~

1.备份

1.1.常用备份方式

这边就简单演示下备份数据库：mysqldump-uroot-p--databases safe_db>safe_db.sql

PS：完整写法： mysqldump-uroot-p--master-data=2--single-transaction--events--routines--triggers db_name>db_name.sql

1.2.指定表的备份

有时候我们还会对数据库中需要分析的表进行备份，然后在新环境下进行进一步的分析（比如访问ip、上传记录等）

eg：mysqldump-uroot-p safe_db users>safe_db_users_tb.sql

1.3.master-data的说明

在主从模式下还会使用到 master-data
，我们也来简单演示下，这个到底是个啥（看图秒懂）

eg：mysqldump-uroot-p--database safe_db--master-data=2>safe_db2.sql

下面见证奇迹的时刻到了~区别只是有没有把binlog文件名和偏移量注释而已

然后说过踩的坑：使用 --master-data
的时候如果没有使用 --single-transaction
，那么DB默认会使用 --lock-all-tables

PS：这也是为什么各大公司都推荐，建表的时候都使用InnoDB的根本原因 ==> 不影响线上的后期维护太麻烦

1.4.指定条件的备份

再说个场景：数据库数据都是假删，有些时候这些删除的数据比真实数据还要多，在一些场景下需要收缩数据库，那么就可以使用条件备份

eg：mysqldump-uroot-p safe_db file_records--where"datastatus=1">safe_db_file_records_tb.sql

先看下表结构

导出和恢复对比图（新数据库中表数据只含符合条件的）

PS：mysql-uroot-p safe_db_bak<safe_db_file_records_tb.sql
（只支持单表的导出）

PS：旧表删不删除你得看SQL ==> DROP TABLE IF EXISTS file_records;

1.5.结构和数据分开备份

最后再提一个常用的场景：结构和数据分开备份（ --tab=path
）

eg：mysqldump-uroot-p--master-data=2--single-transaction--events--routines--triggers--tab=/tmp safe_db

使用 --tab
mysql备份账户必须有file的权限才能操作，然后指定的目录mysql必须有可写权限

PS：没权限 ==> chown mysql:mysql目录名
or /tmp

因为安全性的缘故，CentOS中Mysql写入tmp的文件都在这个临时文件夹中，如果你删除了这个临时文件夹，那么只会写入失败（重启mysql方能生效）

PS：这个我在讲DB系统表的时候说过（ selectintooutfile
and secure_file_priv
），可以一览：https://mp.weixin.qq.com/s/rQnMlllRSlyseEmSOCTVGw

文字很抽象，一张图示即可秒懂

PS：根本原因就是因为 selectintooutfile
会导致一些安全问题，CentOS做了处理，而MySQL还是以为自己直接写入/tmp中了（后面说恢复会讲）

2.恢复

2.1.常规恢复

恢复就比较简单了，但是单线程的操作，数据库内容较多可能就得等会了（这也是为什么线上故障恢复常用物理备份的原因），简单提一下：mysql-uroot-p safe_db_bak<safe_db2.sql

PS：在mysql命令行下使用 source/xxx/safe_db2.sql
也一样

如果没创建新DB就会报错

快速创建：mysql-uroot-p-e"create database safe_db_bak charset=utf8"

2.2.分结构和数据备份的恢复

最后再提下上面说的分结构和数据的导入方式：source/tmp/xxx.sql;
and load data infile'/tmp/xxx.txt'intotable tb_name;

恢复表结构：source/tmp/file_records.sql;

恢复表数据：load data infile'/tmp/file_records.txt'intotable file_records;

若出现任何异常（权限也好，错误也罢）依旧可看我上面贴的文章，里面讲这些已经很透彻了

PS：https://mp.weixin.qq.com/s/rQnMlllRSlyseEmSOCTVGw

2.3.提下几个可能出现的错误

其一：mysqldump:Error:Binloggingon servernotactive
==> binlog没有打开，如果不会可以看看之前写的binlog文章

博客园：https://www.cnblogs.com/dotnetcrazy/p/11029323.html，微信：https://mp.weixin.qq.com/s/58auoCY0SU7qNjdTa5dc_Q

其二：@@GLOBAL.GTID_PURGED can only besetwhen@@GLOBAL.GTID_EXECUTEDisempty
，这个在集群的情况下会遇到，简单说下解决：

1.mysql命令行中输入 reset mater
；2.执行mysqldump的时候后面加个参数 --set-gtid-purged=off
(不导出gtid的信息）

今天就先到这边吧，感兴趣的同志可以写个sh或者py脚本，然后结合 crontab
就可以投入生产了~

下级预告：mysqlpump与xtrabackup，并发备份与物理备份，以及在线数据库如何保证备份时的数据一致性...