道者编程


mysqldump详解

mysqldump 是mysql自带的一个客户端备份工具,通过协议(默认TCP协议)链接mysql来备份我们所指定的库、表数据或者结构等。mysqldump备份出来的是文本结构,重新构建数据库、表等脚本,数据则转换成对应insert语句。

注意:mysqldump在mysql安装目录的bin目录下面,不要在mysql控制台里面找 偷笑

一:基本语法:

1:语法可以人为的分为三部分:连接到mysql,导出参数,导出到哪里。

格式:mysqldump -h主机地址 -u用户名 -p用户密码
//本机可以省略-h
mysqldump -uroot -p123
完整的三部分:

//把本机 blog 数据库 导出到\tmp目录下,导出后的数据库名为:blog.sql
mysqldump -uroot -p123 --databases blog  > \tmp\blog.sql

重点要注意中间部分,也就是导出参数。

2:mysqldump默认是否会锁表?

据说默认是锁表的,我们也别瞎猜了,亲自验证一下,首先开启mysql的通用查询日志,这个日志会记录所有的mysql操作记录,生产环境一定不能随便开启,调试的时候可以控制台开启一下。我们输入命令查看一下是否开启:

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
可以看到未开启,我们开启一下。

mysql> set global general_log=on;
Query OK, 0 rows affected (0.05 sec)
查看一下日志的位置在哪里

mysql> show variables like 'general_log_file';
+------------------+--------------------------------------------------------+
| Variable_name    | Value                                                  |
+------------------+--------------------------------------------------------+
| general_log_file | D:\phpStudy\PHPTutorial\MySQL\data\LAPTOP-UQTQL6TT.log |
+------------------+--------------------------------------------------------+
1 row in set (0.00 sec)
 我这里默认日志在D盘。

好了,现在再运行一下刚才上面那个备份。

LOCK TABLES后面一堆表名,这里我没有截完,所以mysqldump默认是锁表的。

再往下面找

备份结束后这里有个解锁命令

什么意思?就是mysqldump在处理的时候,整个库处于一个只读状态,业务读(查询)没问题,但是不能写,如果数据量很大,备份时间长,对业务的影响是显而易见的。

那么问题来了?不锁表怎么操作?所以这里介绍除了基本参数以外的第一个导出参数: --skip-lock-tables

3:--skip-lock-tables (导出不锁表)

mysqldump -uroot -p123 --databases past --skip-lock-tables  > \tmp\blog.sql

再查看日志,发现刚刚的导出操作并没有LOCK TABLES,也就是没有锁表,这样导出的过程就不会影响业务。

仔细想想,这里又会有问题,什么问题?如果不锁表,备份的过程中,数据发生了改变怎么办?这样备份下来的数据就会有差异。所以这里又涉及到一个问题,这就是我们下面要解决的问题。

4:--single-transaction (一致性备份)

上面的语句带上这个参数,看看发生了什么?

mysqldump -uroot -p123 --databases blog --single-transaction  > \tmp\blog.sql
查看日志:

这里有三句话:

第一句:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ:设置设置会话事务隔离级别可重复读取

第二句,START TRANSACTION:开启事务。

第三句:WITH CONSISTENT SNAPSHOT:设置一致快照

START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */:这句话的意思是,获取当前数据库快照,执行事务的同时建立本事务一致性读的快照。因为如果只是可重复读,事务在开始还没有执行到第一条语句的时候,这时候其他线程如果提交了结果,那么这时候的数据就不是事务开始的数据。数据就会不一致。

接着看上图,还有一句:UNLOCK TABLES,释放锁,其实我们这里貌似没用到,先不管它,我们下一个参数就会用到了。

没有发现有锁表,也就是说--single-transaction 开启后目的是加上事务,从而创建一个一致性快照,但不会锁表,上图设置的是对当前session的REPEATABLE-READ (可重复读)。

mysql官方的介绍:

The WITH CONSISTENT SNAPSHOT modifier starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table. See Section 14.2.2.2, “Consistent Nonlocking Reads”. The WITH CONSISTENT SNAPSHOT modifier does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits a consistent read. The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored. As of MySQL 5.7.2, a warning is generated when the WITH CONSISTENT SNAPSHOT clause is ignored.

5:--master-data=2 (取得log坐标)

mysqldump -uroot -p123 --databases blog --single-transaction --master-data=2  > \tmp\blog.sql
查看日志:

49 Query  FLUSH /*!40101 LOCAL */ TABLES //关闭打开的表,刷新表
49 Query  FLUSH TABLES WITH READ LOCK //关闭打开的表,加锁,因为--master-data参数,需要提交一个全局read lock,然后取binlog坐标
49 Query  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
49 Query  START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
49 Query  SHOW MASTER STATUS
49 Query  UNLOCK TABLES //解锁

这里的日志有一部分已经在第4点说明了,这里补充一下。

(1):这里都还没有备份就执行了解锁,UNLOCK TABLES,为什么?利用了 innodb 引擎的 MVCC 机制,开启快照读后,就能获得那个时间点的一致性数据,无论备份多长时间。举个例子:假如1点备份,5点备份完。这个时候获取的都是1点这个点的数据,1点到5点的数据不会备份。

(2):获取的log坐标点在哪里?这个坐标点在导出的sql文件中,在文件的开头:


几个疑问:

a:为什么需要获取这个log日志坐标:因为 --single-transaction --master-data=2 不会锁表(只是获取坐标的时候短暂锁定一下),导出的过程中,依然有写入,所以导出后的表和最新的表有差异的。知道了log坐标点就可以根据这个搞增量备份,也可以在同步的时候做异常处理,比如不停机,不影响业务的情况下,重新备份。

b:--master-data=2 是什么意思:master-data:读锁+获取log日志坐标,1和2的区别是在导出的sql中有一段话是否注释,我们查看一下,

这段话是注释的,如果是1,这段话就不是注释的。这段话搞过数据库同步的都很熟悉,具体百度。

有一点要注意:--master-data 会内部执行:FLUSH /*!40101 LOCAL */ TABLES ,如果当前有sql在运行,FLUSH TABLES 会一直等待(阻塞),而FLUSH TABLES 的阻塞会引起其他的sql阻塞等待。

说了半天好处也出来了:比如在做数据库同步的时候,假如发生异常,数据不一致,需要重新同步,第一步肯定是把主库的数据先备份下来,然后从节点同步,那么主库就需要设置锁,不然数据很可能不一致,但是加锁就会影响业务。所以这里就可以用这种方式,通过一致性备份解决这个问题,导出的过程中,照样可以读,可以写,完毕后,再通过log坐标同步,不过不支持MyISAM,因为是通过事务实现的,MyISAM压根不支持事务。

关于 flush tables 阻塞 自己可以动手试下:

1:打开一个终端延迟执行:select sleep(30) from t limit 1:

2:然后再打开一个终端备份:mysqldump -uroot -p123 --databases blog --single-transaction --master-data=2  > \tmp\blog.sql

3:还打开一个终端:随便执行一条sql

会发现:1阻塞了2,2阻塞了3……


最新评论:
我要评论:

看不清楚


链接