如何有效地提高 MySQL 的备份和恢复速度

如何有效地提高 MySQL 的备份和恢复速度~

　　一 加速备份
　　1、 加了single-transaction参数 备份时 需要先flush table with read lock 这个过程中会有一个锁表的过程，如果有事务或语句正在执行，没有结束，那么备份进程会一直等待，并且阻塞别的事务，那么也会影响业务。所以要先确认备份的时候没有大的事务在运行。具体 single-transaction的加锁可以参考 我的博客：mysqldump备份时加single-transaction会不会加锁
　　2 、mysqldump是单进程的，没有办法并行，但现在机器的瓶颈多是出现在IO方面，可以使用更了的IO设备加快速度
　　3 、mysqldump时如果空间够的话，不要边压缩边备份

　　二 加速恢复
　　1 关闭binlog:不写入Binlog会大大的加快数据导入的速度
　　2 innodb_flush_log_at_trx_commit=0
　　3 更好的配置

建议：
如果非要使用逻辑备份，可以考虑mysqldumper, mysqlpump(5.7)这两个工具去备份，这两个在备份的时候支持并行操作，mysqldumper还可以对单表进行恢复，在只需要恢复单表的情况下，恢复速度会大大加快
使用物理备份 xtrabackup (open source),MEB(oracle提供，收费)： 他们的备份原理是基于mysql crash recover， 备份速度 是和逻辑备份的相差不太大。但是恢复速度却有很大的提升。
　　逻辑备份 备出来的是sql语句文件，恢复时需要一条一条的执行sql,所以恢复很慢。
　　而物理备份和还原的速度 相当于直接copy文件，所以恢复的时候性能有很大的提升
　　并且这两个软件还支持并行，效果更好。

　　逻辑备份最大的优点是 备份好的文件经压缩后占用空间较小，最大缺点恢复太慢
　　物理备份可以很快的恢复，但是备份好的文件压缩后占用空间比逻辑备份要大。

一 加速备份
1、 加了single-transaction参数 备份时 需要先flush table with read lock 这个过程中会有一个锁表的过程，如果有事务或语句正在执行，没有结束，那么备份进程会一直等待，并且阻塞别的事务，那么也会影响业务。所以要先确认备份的时候没有大的事务在运行。具体 single-transaction的加锁可以参考 我的博客：mysqldump备份时加single-transaction会不会加锁
2 、mysqldump是单进程的，没有办法并行，但现在机器的瓶颈多是出现在IO方面，可以使用更了的IO设备加快速度
3 、mysqldump时如果空间够的话，不要边压缩边备份
二 加速恢复
1 关闭binlog:不写入Binlog会大大的加快数据导入的速度
2 innodb_flush_log_at_trx_commit=0
3 更好的配置

你好，
　　一 加速备份
　　1、 加了single-transaction参数 备份时 需要先flush table with read lock 这个过程中会有一个锁表的过程，如果有事务或语句正在执行，没有结束，那么备份进程会一直等待，并且阻塞别的事务，那么也会影响业务。所以要先确认备份的时候没有大的事务在运行。具体 single-transaction的加锁可以参考 我的博客：mysqldump备份时加single-transaction会不会加锁2 、mysqldump是单进程的，没有办法并行，但现在机器的瓶颈多是出现在IO方面，可以使用更了的IO设备加快速度3 、mysqldump时如果空间够的话，不要边压缩边备份二 加速恢复
　　1 关闭binlog:不写入Binlog会大大的加快数据导入的速度2 innodb_flush_log_at_trx_commit=0
　　3 更好的配置
　　建议：
　　如果非要使用逻辑备份，可以考虑mysqldumper, mysqlpump(5.7)这两个工具去备份，这两个在备份的时候支持并行操作，mysqldumper还可以对单表进行恢复，在只需要恢复单表的情况下，恢复速度会大大加快使用物理备份 xtrabackup (open source),MEB(oracle提供，收费)： 他们的备份原理是基于mysql crash recover， 备份速度 是和逻辑备份的相差不太大。但是恢复速度却有很大的提升。
　　逻辑备份 备出来的是sql语句文件，恢复时需要一条一条的执行sql,所以恢复很慢。
　　而物理备份和还原的速度 相当于直接copy文件，所以恢复的时候性能有很大的提升并且这两个软件还支持并行，效果更好。
　　逻辑备份最大的优点是 备份好的文件经压缩后占用空间较小，最大缺点恢复太慢物理备份可以很快的恢复，但是备份好的文件压缩后占用空间比逻辑备份要大

前言

MySQL 5.6引入了GTID，每个事务都会产生一个GTID，我们可以通过验证主从GTID来验证主从数据的一致性。

为了叙述简便，定义一个量ALL_GTID: 表示某个数据库实例上 所有存在过的 或 将要存在的事务 的GTID（包括已经被purge掉的事务）。

在讨论数据库可用性的场景中, 当发生主备切换时, 需要进行数据补偿。通过比较主备的ALL_GTID，可以确定需要补偿多少数据:

• 在实例存活的情况，可以在实例状态中查询ALL_GTID。

• 在实例崩溃的情况，无法在实例状态中查询ALL_GTID。可以通过查询BINLOG中的Previous-GTIDs计算来获得ALL_GTID。

下面列举与ALL_GTID相关的变量。

与ALL_GTID相关的变量

Previous-GTIDs

Previous-GTIDs格式如下（环境为MySQL5.7，日志手动flush binary logs获得）：

查看新轮转出的BINLOG：


下面为mysql-bin.00001中包含的GTID：

请点击输入图片描述


然后再次flush binary logs:

请点击输入图片描述


mysql-bin.00002中是没有任何GTID的。

请点击输入图片描述


综上Previous-GTIDs是本身这个BINLOG文件前面的所有BINLOG的集合。

请点击输入图片描述

全局变量中的GTID相关的变量

请点击输入图片描述

变量解释:


• gtid_executed 代表着server上所有事务执行产生的GTID（包含已经被purge的BINLOG中的GTID或者是手动set gtid_purged的GTID）。

• gtid_purged 代表着已经被purge到的GTID。gtid_purged是gtid_executed的子集。

• gtid_retrieved 是从机上relay_log中的GTID。

ALL_GTID 的计算

了解了GTID相关的变量之后，可以得到获得实例的All_GTID的集合的方法：

对象

方法

存活的Master实例    gtid_executed    

存活的Slave实例    gtid_executed和gtid_retrieved的并集    

非存活Master实例    最后一个BINLOG文件的Previous-GTIDs + 最后一个BINLOG文件中所有的GTID    

非存活Slave实例    最后一个BINLOG文件的Previous-GTIDs + 最后一个BINLOG文件中所有的GTID    

在获得非存活实例中的ALL_GTID时，最后一个BINLOG文件中的GTID可能不连续(比如事务同时来自于本实例客户端和复制回放)，所以需要扫描最后一个BINLOG文件。

生产中我们使用Xtrabackup来产生一个 从实例 的流程如下：

• 拉取备份，进行还原

• change master to

• set @@global.gtid_purged='xxx';

set @@global.gtid_purged='xxx'; 的影响:

• 将 从实例 的ALL_GTID手工置为xxx, 在通过GTID方式建立复制时不会出错.

• 将更新Binlog中记录的Previous-GTIDs (由于Binlog不可改变, 将产生新的Binlog, 记录新的Previous-GTIDs).

MySQL 5.7中set gtid_purged的行为变更

问题描述

回顾一下备份恢复的流程:

• 拉取备份，进行还原

• change master to

• set @@global.gtid_purged='xxx';

现象: 发现有一台MySQL 5.7的Slave服务器恢复后没有产生 正确的Previous-GTIDs。

分析

分析整个过程，解决问题应该分阶段进行手动模拟发现问题。以下为详细步骤：

• 手工还原备份

环境

BINLOG数量，Previous-GTIDs状态

Xtrabackup 2.4.2 & MySQL 5.6    1，空    

Xtrabackup 2.4.2 & MySQL 5.7    1，空    

Xtrabackup 2.2.9 & MySQL 5.6    1，空    

Xtrabackup 2.2.9 & MySQL 5.7    1，空    

可见: 恢复过程不会轮转BINLOG。

• 验证change master和set gtid_purged在不同的MySQL版本中执行的差异

环境

BINLOG数量，Previous-GTIDs状态

change master & MySQL 5.6    1，空    

change master & MySQL 5.7    1，空    

set gtid_purged & MySQL 5.6    2，正常    

set gtid_purged & MySQL 5.7    1，空    

可见: 执行set gtid_purged时不同版本的MySQL产生了差异

验证

对不同版本MySQL单独执行set @@global.gtid_purged='';语句。检查结果

环境

进行的操作

BINLOG数量，Previous-GTIDs状态

MySQL 5.7    reset master; set @@global.gtid_purged=”;    1，空    

MySQL 5.6    reset master; set @@global.gtid_purged=”;    2，正常    

结论

参考: http://bugs.mysql.com/bug.php?id=75767

官方解释: 在5.7版本中，执行SET GTID_PURGED语句后binlog_simple_gtid_recovery会给GTID_PURGED计算出一个错误的值。

由于5.7中新增了存储GTID的表。所以5.7版本中set @@global.gtid_purged='';语句被改成只修改存放GTID的表。

而5.6版本中会进行BINLOG轮转和向Previous_gtids_log_event中添加GTID。如果5.7需要产生和5.6相同结果的话，可以在SET GTID_PURGED语句后手动执行flush binary logs语句。

　　人面 不 知 何处

---

墉桥区13524559386： 如何有效地提高 MySQL 的备份和恢复速度 - ？
党夏护肝： 一 加速备份1、 加了single-transaction参数 备份时 需要先flush table with read lock 这个过程中会有一个锁表的过程,如果有事务或语句正在执行,没有结束,那么备份进程会一直等待,并且阻塞别的事务,那么也会影响业务.所以要先确认备份...

墉桥区13524559386： mysql怎么进行数据备份和性能调优 - ？
党夏护肝： mysql怎么进行数据备份和性能调优 如果是windows系统就写一个bat文件做成任务 如果是linux系统就写任意一个文件做到crontab中

墉桥区13524559386： 如何对数据库备份进行性能优化 - ？
党夏护肝： 一、选择合理的备份时机.虽然说,SQL Server数据库在联机或者活动状态,也可以进行备份.但是,一般情况下,笔者不建议这么做.因为在数据库活动的时候进行备份的话,一方面会增加备份的时间;另一方面,因为备份作业占用了一定的...

墉桥区13524559386： mysql如何快速备份 - ？
党夏护肝： 来源:知乎 河南-老宋(志强) 问题描述的不是非常的清晰 使用mysqldump备份时一般会会加上--single-transaction参数,这里假设你是加了这个参数.一 加速备份1 加了single-transaction参数 备份时 需要先flush table with read lock 这个过程中...

墉桥区13524559386： 针对数据仓库系统,如何设计有效的数据库备份策略? - ？
党夏护肝： 数据备份策略主要有以下三种:完全备份(fullbackup)、增量备份(incrementalbackup)、差分备份(differentialbackup);这个主要看用户对数据丢失的容忍程度,一般对数据比较重视的企业会跟一些容灾备份公司合作,国内这方面的公司经过这些年的技术更新已经拥有了不错的实力,譬如像中科同向这类的创新型企业,拥有很强的技术实力.

墉桥区13524559386： 如何实现Mysql数据库的差异性备份和增量备份? - ？
党夏护肝： Ø 首先,我们需要开启MySQL服务器的二进制日志功能,其实现方法有很多种,最常用的是在MySQL的配置文件的mysqld项中加入log-bin=[filepath]项;也可以使用mysqld –log-bin=[filepath]重新启动MySQL服务器.Ø 其次,使用mysqldump对...

墉桥区13524559386： 如何每天自动备份MySql文?如何每天自动备份MySql文件 ？
党夏护肝： 步骤如下: 1,运行ssh工具putty,输入服务器地址,帐号和密码连接上服务器, 2,切换到mysql安装目录下,然后使用VI创建一个文件(backup_mydb.sh): PS:vi操作方...

墉桥区13524559386： 如何设置mysql自动备份数据库 - ？
党夏护肝： 使用navicat工具连接mysql数据库,这里以navicat for Mysql工具为例.如果数据库在本机,那么连接ip处写localhost即可,如果数据库在其他机器,那需要写具体的ip地址.设置mysql备份文件的存储路径.新建连接页面,设置连接信息的右侧有...

墉桥区13524559386： 有什么简单的 mysql 增量备份方案 - ？
党夏护肝： 简单的 mysql 增量备份方案 想弄一个自动作业每天备份公司项目的一个mysql数据库,原本采取的方案是crontab+全量mysqldump 然后gzip压缩并按日期保存,最后生成文件大概30多M的样子.但是毕竟这种全量备份放在每天执行不是很好,因...

墉桥区13524559386： mysql数据库如何备份?？
党夏护肝： 目前 MySQL 支持的免费备份工具有:mysqldump、mysqlhotcopy,还可以用 SQL 语法进行备份:BACKUP TABLE 或者 SELECT INTO OUTFILE,又或者备份二进制日志(binlog),还可以是直接拷贝数据文件和相关的配置文件.MyISAM 表是保存成文件的形式,因此相对比较容易备份,上面提到的几种方法都可以使用.Innodb 所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件,或者是独立的表空间文件),相对来说比较不好备份,免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump.