MySQL解疑_mysqldump 解疑

一般mysqldump用于备份，另外也会和single-transaction 和master-data这两个参数配合着使用。即：

mysqldump --single-transaction  --master-data=2

一、single-transaction

先看一下官方解释，如下：

--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是无法隔离的，即不能使用浙西i额表操作

(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不会锁表(因为第二行已经取得了快照，不需要锁表了)

二、master-data

master-data主要是为了记录binlog的log和pos，用于之后基于时间点的恢复，所以非常重要。

同样执行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 tha

t 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位置!

四、隐患

我们先来看论坛上的两个留言：

Aryeh Gregorsays:April 24, 2010 at 6:23 pm

I have a story about this, actually. One morning I woke up to find my website (twcenter.net) was down. I figured out that everything was waiting on MySQL, so I restarted it to see if that would fix it without any further downtime. It did, so I went on with my life — thankfully just a game fan site, no real money lost.

Then I received an e-mail, saying “Backup failed: MySQL has gone away” or something to that effect. Eventually I realized what had happened. The previous day I had run some statistical query or other on a little-used log table. The query took too long to complete, so I aborted with Ctrl-C. In fact, in that version of MySQL, this only caused the client to exit, and left the query running in the background for hours.

That night, the backup script had run FLUSH TABLES WITH READ LOCK. It immediately acquired locks on all tables — effectively write locks, as you say — except this one log table. It held those locks waiting for the lock on the log table, shutting the site down until I came around to fix it manually, because the statistics-gathering query I ran was taking many hours to run. Some convoluted thing with a subquery, if I remember correctly.

I’ve since moved to InnoDB for everything, and use mysqldump –single-transaction for backups. Hopefully more people will start using InnoDB now that Oracle is making it the default engine in 5.5!

Shlomi Noachsays:April 24, 2010 at 8:27 pm

It should be worthwhile to note that even for InnoDB-only systems, using FLUSH TABLES WITH READ LOCK may be a requirement: in the case you want to do incremental backups, hence must store the master position.

That is, if your’e doing “mysqldump –single-transaction –master-data” – you’re good. But a mylvmbackup would have to use the FLUSH TABLES… in order to store the master’s log and pos.

可以总结上面的问题是：

当在执行“mysqldump –single-transaction –master-data”之前，如果有一个很长时间的查询(select)没有结束，那么“mysqldump –single-transaction –master-data”里的FLUSH TABLES WITH READ LOCK将会一直等待前一个查询结束才会执行，而更加严重的是，在没有执行完FLUSH TABLES WITH READ LOCK之前，其他的所有update、delete等更改操作都将会被阻塞！

以上的结论很恐怖，比如一不小心写了个很烂的每名中索引的全表扫描，执行了一晚上都没跑完，而凌晨的备份也将阻塞，同样也导致了数据无法写入和更改，这其实也就等同于整个系统已经瘫痪了！