解锁MySQL备份恢复的4种正确姿势

《解锁MySQL备份恢复的4种正确姿势》要点：
本文介绍了解锁MySQL备份恢复的4种正确姿势，希望对您有用。如果有疑问，可以联系我们。

作者介绍：
冯帅,点融网高级DBA,获有Oracle OCM、MySQL OCP,目前从事MySQL相关的运维和架构工作,擅长异构数据库交互.

分享大纲：
mysqldump
mysqlbackup
mysqlhotcopy
xtrabackup/innobackupex

备份高于一切,今天汇总一下常用的几种备份方法,以及恢复的步骤.

一、mysqldump

在日常工作中,我们会使用mysqldump命令创建SQL格式的转储文件来备份数据库.或者我们把数据导出后做数据迁移,主备搭建等操作.mysqldump是一个逻辑备份工具,复制原始的数据库对象定义和表数据产生一组可执行的SQL语句. 默认情况下,生成insert语句,也能生成其它分隔符的输出或XML格式的文件.

shell> mysqldump [arguments] > file_name

我们简单来看一下日常的用法：

备份所有的数据库：

备份指定的数据库：

当我们只备份一个数据的时候可以省去 –databases 直接写成：mysqldump test > dump.sql 不过有一些细微的差别,如果不加的话,数据库转储输出不包含创建数据库和use语句,所以可以不加这个参数直接导入到其它名字的数据库里.

当然我们也可以只备份某个表 ：

了解了简单的一些用法后我们再着重看一下几个参数：

• master-data 获取备份数据的Binlog位置和Binlog文件名,用于通过备份恢复的实例之间建立复制关系时使用,该参数会默认开启.
• dump-slave 用于在slave上dump数据,建立新的slave.因为我们在使用mysqldump时会锁表,所以大多数情况下,我们的导出操作一般会在只读备库上做,为了获取主库的Relay_Master_Log_File和Exec_Master_Log_Pos,需要用到这个参数,不过这个参数只有在5.7以后的才会有
• no-data,-d 不导出任何数据,只导出数据库表结构

刚刚我们说过在使用mysqldump的时候会锁表,我们来详细看一下它的锁机制.

我们开两个窗口,在第一个里面执行mysqldump -uroot -pxxxxx –master-data=2 –databases dbname > /tmp/dbnamedate +%F.sql 然后第二个窗口登陆进去,使用show process的命令可以看到目前dump的session正在执行.

SELECT /!40001 SQL_NO_CACHE?/ * FROM table_name; 可以看到这条SQL正在以no_cache的模式查询数据.

然后我们在同样的表上执行一下select,发现被阻塞了.光标一直不返回.

一般遇到这种文件,我们会想是不是有锁呢? 为了验证我们查看一下锁的信息,可以发现dump的进程实际上是加了锁的.

一般遇到这种文件,可以发现dump的进程实际上是加了锁的.

我们把具体的general_log打开,然后看一下当时的操作：

(关闭所有打开的表,同时对于所有数据库中的表都加一个读锁,直到显示地执行unlock tables,该操作常常用于数据备份的时候.)

(这是因为我用了–master-data=2)

所以这个时候表就会被锁住.

如果我不加–master-data参数(mysqldump -uroot -pxx –databases db > /tmp/dbnamedate +%F.sql) mysql会显示的对每一张要备份的表执行 LOCK TABLES?table_name1?READ,LOCK TABLES?table_name2?READ,并且也不会有读的阻塞.

那有没有不锁的方法,其实也是有的,就是使用–single-transaction把备份的操作放在一个事务里去进行.

带上–single-transaction参数的mysqldump备份过程：

如果是5.6版本的MySQL

（编辑：ASP站长网）