分类： Mysql/postgreSQL
2014-05-26 18:34:47
081022 17:39:33 7 Connect root@localhost on 7 Query /*!40100 SET @@SQL_MODE='' */ 7 Init DB yejr 7 Query SHOW TABLES LIKE 'yejr' 7 Query LOCK TABLES `yejr` READ /*!32311 LOCAL */ 7 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 7 Query show create table `yejr` 7 Query show fields from `yejr` 7 Query show table status like 'yejr' 7 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr` 7 Query UNLOCK TABLES 7 Quit
2. --lock-tables
跟上面类似，不过多加了一个 READ LOCAL LOCK，该锁不会阻止读，也不会阻止新的数据插入。
081022 17:36:21 5 Connect root@localhost on 5 Query /*!40100 SET @@SQL_MODE='' */ 5 Init DB yejr 5 Query SHOW TABLES LIKE 'yejr' 5 Query LOCK TABLES `yejr` READ /*!32311 LOCAL */ 5 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 5 Query show create table `yejr` 5 Query show fields from `yejr` 5 Query show table status like 'yejr' 5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr` 5 Query UNLOCK TABLES 5 Quit
3. --lock-all-tables
这个就有点不太一样了，它请求发起一个全局的读锁，会阻止对所有表的写入操作，以此来确保数据的一致性。备份完成后，该会话断开，会自动解锁。
081022 17:36:55 6 Connect root@localhost on 6 Query /*!40100 SET @@SQL_MODE='' */ 6 Query FLUSH TABLES 6 Query FLUSH TABLES WITH READ LOCK 6 Init DB yejr 6 Query SHOW TABLES LIKE 'yejr' 6 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 6 Query show create table `yejr` 6 Query show fields from `yejr` 6 Query show table status like 'yejr' 6 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr` 6 Quit
4. --master-data
除了和刚才的 --lock-all-tables 多了个 SHOW MASTER STATUS 之外，没有别的变化。
081022 17:59:02 1 Connect root@localhost on 1 Query /*!40100 SET @@SQL_MODE='' */ 1 Query FLUSH TABLES 1 Query FLUSH TABLES WITH READ LOCK 1 Query SHOW MASTER STATUS 1 Init DB yejr 1 Query SHOW TABLES LIKE 'yejr' 1 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 1 Query show create table `yejr` 1 Query show fields from `yejr` 1 Query show table status like 'yejr' 1 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr` 1 Quit
5. --single-transaction
InnoDB 表在备份时，通常启用选项 --single-transaction 来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ，以确保本次会话(dump)时，不会看到其他会话已经提交了的数据。
081022 17:23:35 1 Connect root@localhost on 1 Query /*!40100 SET @@SQL_MODE='' */ 1 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 1 Query BEGIN 1 Query UNLOCK TABLES 1 Init DB yejr 1 Query SHOW TABLES LIKE 'yejr' 1 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 1 Query show create table `yejr` 1 Query show fields from `yejr` 1 Query show table status like 'yejr' 1 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr` 1 Quit
6. --single-transaction and --master-data
本例中，由于增加了选项 --master-data，因此还需要提交一个快速的全局读锁。在这里，可以看到和上面的不同之处在于少了发起 BEGIN 来显式声明事务的开始。这里采用 START TRANSACTION WITH CONSISTENT SNAPSHOT 来代替 BEGIN的做法的缘故不是太了解，可以看看源代码来分析下。
081022 17:27:07 2 Connect root@localhost on 2 Query /*!40100 SET @@SQL_MODE='' */ 2 Query FLUSH TABLES 2 Query FLUSH TABLES WITH READ LOCK 2 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 2 Query START TRANSACTION WITH CONSISTENT SNAPSHOT 2 Query SHOW MASTER STATUS 2 Query UNLOCK TABLES 2 Init DB yejr 2 Query SHOW TABLES LIKE 'yejr' 2 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1 2 Query show create table `yejr` 2 Query show fields from `yejr` 2 Query show table status like 'yejr' 2 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `yejr` 2 Quit
关于隔离级别可以看手册 13.2.10.3. InnoDB and TRANSACTION ISOLATION LEVEL，
：。
关于 START TRANSACTION WITH CONSISTENT SNAPSHOT 的说明可以看下手册描述：
The WITH CONSISTENT SNAPSHOT clause 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 13.2.10.4, “Consistent Non-Locking Read”. The WITH CONSISTENT SNAPSHOT clause does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that allows consistent read (REPEATABLE READ or SERIALIZABLE).
12.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax
