mysqldump详细了解

mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql
这个备份的命令会使用flush tables with read lock获取一个全局读锁。
这个锁一旦被获取，二进制日志协调器被读取锁被释放，如果在语句被执行的时候，有长的更新语句，备份的操作会被拖延到语句结束。之后dump会变成lock-free的状态不会打扰表上的读和写。
使用–singel-transaction可以确保mysqldump看到的数据是一致的。

经过测试，在mysqldump开始执行后，是可以drop表的，也即是mysqldump的过程中没有一直持有元数据锁。
在做全备份的时候，可以使用mysqldump --single-transaction --flush-logs --master-data=2
–all-databases > backup_sunday_1_PM.sql
这样产生了一个新的日志文件。以后的增量备份就在新的日志文件开始了。

flush tables with read lock获取的不是表锁，所以跟lock tables和unlock table不同，不会有表锁和隐式提交。
unlock table会在有lock tables锁住的表上隐式提交所有未提交的活动事务。
flush tables tab_name with read lock
这个语句对给定的表刷新和获取读锁，这个语句开始获取排他元数据锁，所以要等待这些表上的事务结束，然后再表缓存中刷新，重新打开表，获取表锁，降低元数据锁到共享，然后别的数据可以读取但是不能修改表。这个语句需要表锁，所以你要有lock tables的权限及reload的权限。

2020-04-17T17:24:27.734907+08:00            5 Query     FLUSH /*!40101 LOCAL */ TABLES
2020-04-17T17:24:27.738707+08:00            5 Query     FLUSH TABLES WITH READ LOCK
2020-04-17T17:24:27.739096+08:00            5 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2020-04-17T17:24:27.739326+08:00            5 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2020-04-17T17:24:27.739846+08:00            5 Query     SHOW VARIABLES LIKE 'gtid\_mode'
2020-04-17T17:24:27.749059+08:00            5 Query     SHOW MASTER STATUS
2020-04-17T17:24:27.749593+08:00            5 Query     UNLOCK TABLES
2020-04-17T17:24:27.750217+08:00            5 Query     SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMAT
ION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT 
LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION
_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP
_NAME
2020-04-17T17:24:27.812352+08:00            5 Query     SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENG
INE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITI
ONS WHERE TABLE_SCHEMA IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2020-04-17T17:24:27.822821+08:00            5 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'
2020-04-17T17:24:27.825456+08:00            5 Init DB   test
2020-04-17T17:24:27.825717+08:00            5 Query     SHOW CREATE DATABASE IF NOT EXISTS `test`
2020-04-17T17:24:27.825854+08:00            5 Query     SAVEPOINT sp
2020-04-17T17:24:27.826032+08:00            5 Query     show tables
2020-04-17T17:24:27.828702+08:00            5 Query     show table status like 'bai'
2020-04-17T17:24:27.831712+08:00            5 Query     SET SQL_QUOTE_SHOW_CREATE=1
2020-04-17T17:24:27.831995+08:00            5 Query     SET SESSION character_set_results = 'binary'
2020-04-17T17:24:27.832210+08:00            5 Query     show create table `bai`
2020-04-17T17:24:27.832538+08:00            5 Query     SET SESSION character_set_results = 'utf8mb4'
2020-04-17T17:24:27.832768+08:00            5 Query     show fields from `bai`
2020-04-17T17:24:27.833116+08:00            5 Query     show fields from `bai`
2020-04-17T17:24:27.833481+08:00            5 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `bai`
2020-04-17T17:24:27.834168+08:00            5 Query     SET SESSION character_set_results = 'binary'
2020-04-17T17:24:27.834318+08:00            5 Query     use `test`
2020-04-17T17:24:27.834475+08:00            5 Query     select @@collation_database
2020-04-17T17:24:27.834721+08:00            5 Query     SHOW TRIGGERS LIKE 'bai'
2020-04-17T17:24:27.835135+08:00            5 Query     SET SESSION character_set_results = 'utf8mb4'
2020-04-17T17:24:27.835271+08:00            5 Query     ROLLBACK TO SAVEPOINT sp
2020-04-17T17:24:27.860575+08:00            5 Query     RELEASE SAVEPOINT sp

flush tables的作用
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.其解析就是关闭所有已打开的表对象，同时将查询缓存中的结果清空。就是说Flush tables的一个效果就是会等待所有正在运行的SQL请求结束。这个只会阻塞当前表的dml

mysql@127.0.0.1.(none)>show processlist;
+----+------+------+------+---------+------+-------------------------+-----------------------------------+
| Id | User | Host | db   | Command | Time | State                   | Info                              |
+----+------+------+------+---------+------+-------------------------+-----------------------------------+
|  6 | root |      | test | Query   |   39 | User sleep              | select sleep(20),name from bai    |
|  7 | root |      | test | Query   |   33 | Waiting for table flush | flush tables                      |
|  8 | root |      | test | Query   |    3 | Waiting for table flush | insert into bai values(1111,'dd') |
|  9 | root |      | NULL | Query   |    0 | starting                | show processlist                  |
+----+------+------+------+---------+------+-------------------------+-----------------------------------+

Flush tables with read lock
Flush tables with read lock是另一个常见的操作，与Flush tables的作用是一样的，同样会等待所有正在运行的SQL请求结束，只是增加了一个全局读锁，即阻塞所有库所有表的写操作，直到unlock tables操作完成。

mysql@127.0.0.1.(none)>show processlist;
+----+------+------+------+---------+------+------------------------------+--------------------------------+
| Id | User | Host | db   | Command | Time | State                        | Info                           |
+----+------+------+------+---------+------+------------------------------+--------------------------------+
|  6 | root |      | test | Query   |   39 | User sleep                   | select sleep(20),name from bai |
|  7 | root |      | test | Query   |   33 | Waiting for table flush      | flush tables with read lock    |
|  8 | root |      | test | Query   |    3 | Waiting for global read lock | insert into tt values('cc')    |
|  9 | root |      | NULL | Query   |    0 | starting                     | show processlist               |
+----+------+------+------+---------+------+------------------------------+--------------------------------+

/*
We do first a FLUSH TABLES. If a long update is running, the FLUSH TABLES
will wait but will not stall the whole mysqld, and when the long update is
done the FLUSH TABLES WITH READ LOCK will start and succeed quickly. So,
FLUSH TABLES is to lower the probability of a stage where both mysqldump
and most client connections are stalled. Of course, if a second long
update starts between the two FLUSHes, we have that bad stall.
*/
为了减少堵塞，先加flush tables后加flush tables with read lock

下面是start TRANSACTION ，START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */的区别

begin与start TRANSACTION是等价的。

a事务在begin后，马上进行了select，另外b事务然后insert记录，a再次查是看不到b事务的改变的。但是如果b事务的insert在a事务的select之前，那么就能看到b事务的改变了，所以备份的时候这种情况也要避免，就出现了WITH CONSISTENT SNAPSHOT,这个语句执行后，a事务不管什么时候查数据，数据都是with consistent sanpshot时候的值。

在开始了with consistent sanpshot事务后，如果已经select了，那么别的操作会被阻塞，如果没有执行select，那么别的会话alter表后，a事务再去查表会报错，已经不是一致性的状态了

root@localhost:[test] 18:06:54> START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */;
Query OK, 0 rows affected (0.00 sec)
--另外的事务alter表
root@localhost:[test] 18:07:43> select * from bai;
ERROR 1412 (HY000): Table definition has changed, please retry transaction

每个表备份完只会都会rollback，释放表的锁，提高并发。