mysql 备份的一致性_【20180206】mysqldump一致性备份流程

FLUSH /!40101 LOCAL/ TABLES

刷盘，将内存的数据fsync到磁盘上面

FLUSH TABLES WITH READ LOCK

设置全局读锁，禁止全局写入

write和read俩者的区别在于就是设置read之后，当前session也不能对数据的写入，修改等dml操作。write设置之后当前session可以进行修改，插入，删除等操作

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

设置MySQL的隔离级别是RR，主要是为使用MVCC版本特性。

START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT/

显性开启事务，在当前事务内，由于RR隔离级别的MVCC版本控制，在当前事务内相同的select语句无论何时获取的数据是一摸一样的。

SHOW VARIABLES LIKE 'gtid_mode'

SHOW MASTER STATUS

获取当前的binlog的filename和position信息

UNLOCK TABLES

解锁。

SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_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 ('$databasename'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME

SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('$databasename')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

SHOW VARIABLES LIKE 'ndbinfo_version'

Init DB

use $databasename

SAVEPOINT sp

设置断点

show tables

show table status like '$tablename'

SET SQL_QUOTE_SHOW_CREATE=1

SET SESSION character_set_results = 'binary'

show create table `$tablename`

SET SESSION character_set_results = 'utf8'

show fields from `$tablename`

SELECT /!40001 SQL_NO_CACHE/ * FROM `$tablename`

SQL_NO_CACHE指的是query cache，但是一般qc不建议开启，5.7默认是关闭的。

SET SESSION character_set_results = 'binary'

use `$databasename`

select @@collation_database

SHOW TRIGGERS LIKE '$tablename'

备份触发器

SET SESSION character_set_results = 'utf8'

ROLLBACK TO SAVEPOINT sp

以table为单位重复循环14～26步骤直到所有的表循环完毕

RELEASE SAVEPOINT sp