mysqldump备份时，如果有MyISAM表会锁表吗

mchdba

lujinke 发表于 2014-6-19 02:01
这个与我提的问题有关吗？我问的是全局锁 --lock-all-tables

全是小表的情况下，单个表lock的时间非常短的，让你几乎没有感觉到已经在锁表了。

lujinke

mchdba 发表于 2014-6-19 09:48
全是小表的情况下，单个表lock的时间非常短的，让你几乎没有感觉到已经在锁表了。

你还是没看清楚我的问题，我问的是指会不会加全局锁

mchdba

lujinke 发表于 2014-6-19 11:41
你还是没看清楚我的问题，我问的是指会不会加全局锁

嗯，我是针对  “确定吗，我今天试验料一把，在导出一个库时(里面有几个是MyISAM表)，我在这个库里面建表，插入数据貌似没有问题啊”

我针对你这个后面的回复的试验的问题而讨论的。

至于锁表，肯定会的，myisam为了数据一致性，mysqldump的时候，会锁表的。

sam_bowei

对myisam和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.   
       由上面可以看到几个关键信息：
           （1）可以在一个事务里对全部表获取一个一致性快照，这里保证了可以在此时获得此一时刻的一致性数据
           （2）只对有版本控制的存储引擎，目前为止是只有innodb有这个功能，同样大众的myisam引擎使用不了
           （3）在这个过程中，alter、drop、rename和truncate是无法隔离的
           （4）自动关闭 --lock-tables 选项
           我们打开mysql的general-log，来查看 mysqldump --single-transaction -B test >t.log到底发生了什么，查看general-log，如下：
              （1） SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
               （2）START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
               （3）UNLOCK TABLES
第一行是使当前session的事务级别为可重复读
第二行是开始一个事务并且获得一个一致性快照，其实这个时候就是对当前所有表的数据进行了一个保存，这里应该使用了MVCC多版本控制，这就是为什么只有innodb才有的功能
第三行是释放锁，这也解释了为什么说使用mysqldump不会锁表（因为第二行已经取得了快照，不需要锁表了）

sam_bowei

继续说明下吧，看楼主是想了解mysqldump导出完整的数据，又担心会锁表

二、master-data
master-data主要是为了记录binlog的log和pos，用于之后基于时间点的恢复，所以非常重要。   
  --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.
同样执行mysqldump  --master-data=2 -B test >t.log
（1）FLUSH  TABLES
（2）FLUSH TABLES WITH READ LOCK
（3）SHOW MASTER STATUS
   第一行flush tables
   To execute FLUSH, you must have the RELOAD privilege.   
   执行flush，需要reload权限
   Closes all open tables, forces all tables in use to be closed, and flushes the  query cache.  FLUSH TABLES also removes all query results from the query cache,  like the RESET QUERY CACHE statement
   关闭打开的表，清除query-cache里的缓存！一句话：使所有表回写到db

第二行FLUSH TABLES WITH READ LOCK
Closes all open tables and locks all tables for all databases with a global read lock until you explicitly release the lock by executing UNLOCK TABLES. This is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time.      
对所有表获得一个全局的读锁，并且直到你显式地“UNLOCK TABLES”才会释放锁
第三行无非是是记录下当前的binlog的log和pos
                           
三、从上面第二个例子可以看到没有出现出现unlock tables，因为没有 single-transaction，所以，是会锁表的！所以一般以上两个参数是会一起使用的：
mysqldump --single-transaction  --master-data=2
（1）FLUSH   TABLES
（2）FLUSH TABLES WITH READ LOCK
（3）SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
（4）START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
（5）SHOW MASTER STATUS
（6）UNLOCK TABLES
由上面六行可知，既保证不会锁表的情况下获得一致性快照、又可以精确地记下binlog位置!

lujinke

sam_bowei 发表于 2014-6-19 11:29
继续说明下吧，看楼主是想了解mysqldump导出完整的数据，又担心会锁表

二、master-data

你说的这些我都知道，其实我提的问题你们还是没有搞清楚
我知道--single-transaction备份，不会锁表，仅对innodb有效，可以获得一致性备份

但是我的问题是，我如果使用--single-transaction来备份含有MyISAM表的数据库，此时mysqldump是否会发出--lock-all-tables的命令

不管怎样，你这种实验思路确实挺有帮助的

lujinke

lujinke 发表于 2014-6-19 11:46
你说的这些我都知道，其实我提的问题你们还是没有搞清楚
我知道--single-transaction备份，不会锁表，仅 ...

自己重新测试了一遍，
结论：使用--single-transaction 备份含有MyISAM的表不会获得一致性备份，所有的innodb表可以获得事务开始时的一致性快照，但是MyISAM表获取的是备份该表时的最新快照，

测试库：test，包含表t1,t2,t3,t4,t5,t6  除t6是MyISAM表之外，其他均为innodb表
表t1,t2 数据量相同，均为288W，其他t3,t4,t5,t6 均只含几条记录

控制台：
mysqldump -uxx -pxx --single-transaction test >test.sql

mysql客户端：
mysql>insert into t6 values(15);  //t5是MyISAM表

第一次实验：
在控制台发出第一条命令，停留5s，在mysql客户端发出第二条命令，但是保证第二条命令式在第一条命令还未结束时发出，这样可以基本保后一个事务是在第一个事务之后发生，在备份文件中能够找到t6的记录15，
读取的是备份该表时的最新“快照”


控制台：
mysqldump -uxx -pxx --single-transaction test >test.sql

mysql客户端：
mysql>insert into t5 values(15);  //t5是innodb表
第二次实验：
在控制台发出第一条命令，停留5s，在mysql客户端发出第二条命令，但是保证第二条命令式在第一条命令还未结束时发出，这样可以基本保后一个事务是在第一个事务之后发生，在备份文件中不能够找到t5的记录15，
读取的是备份开始时的快照

lzd_7993

锁表是一定的。

幕南风

可以看下帮助 --help

--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.

Cloudhorizon

参数解释的很清楚，针对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.