mysql single_mysql dump --single transaction 命令

mysqldump :

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

创建一个一致性的快照通过dump 所有的表到一个单独的transaction.

只能用于支持多版本的(目前只有InnoDB)

对他的存储引擎 dump 是不能保证一直的。 当带上了 --single-transaction参数,

确保一个正确的dump 文件(正确的表内容和binary log 位置)

没有其他的连接来使用下面的语句；ALTER TABLE,DROP TABLE,RENAME TABLE ,TRUNCATE TABLE

[mysql@master ~]$ mysqldump  test t3 >t3.sql

mysql> insert into t3 values(25255,'a','a','20110101')

-> ; --HANG

默认锁表:

[mysql@master ~]$ mysqldump  --single-transaction  test t3 >t3.sql

mysql> insert into t3 values(25255,'a','a','20110101');

Query OK, 1 row affected (0.10 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

---可以看到加了 --single-transaction 参数后就可以进行insert 操作

表是可以 update, insert, delete, select 表中的数据的，

只是不能 ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, 就是说锁是在表级别的，不能修改数据库表

的结构而已

[mysql@master ~]$ mysqldump  --single-transaction  test t3 >t3.sql

alter table t3  modify column name varchar(50); --此时HANG。

-