Mysqldump What's been done during the backup process

Mysqldump Backup is convenient, easy to read, feature rich, I believe everyone has used this command for backup, but this command in the process of the backup did write what, the following open General_log to view:

1. Log in to the MySQL command-line client:

Set global general_log=1;

2. Use the following command to back up:

Mysqldump-uroot-p ' password '--opt--default-character-set=utf8--triggers-r--master-data=2--hex-blob-- Single-transaction--no-autocommit--all-databases-s/data/mysql3306/mysql3306.sock > Aa.sql

3. By default, the query log in the DataDir directory in the ' hostname '. Log, open this log to see the content:

160105 15:20:46-Connect [email protected] on
Query/*!40100 SET @ @SQL_MODE = "*/#修改sql模式
Query/*!40103 SET time_zone= ' +00:00 ' */#修改时区
+ Query FLUSH/*!40101 LOCAL */TABLES # #把内存中的表结构的改动同步到磁盘
+ Query FLUSH TABLES with read lock #对于非事务表, to the global plus intent s lock, can read the state of not write, so get a consistent backup, and so copy unlock TABLES;
The SET SESSION TRANSACTION isolation level repeatable Read #修改隔离级别为RR, under this isolation class, can achieve a consistent non-lock read, that can get a consistent snapshot
Query START TRANSACTION/*!40100 with consistent SNAPSHOT */#开始一个一致性快照, finally either rollback, or commit, note that this is a large transaction throughout the backup process
When you Query SHOW VARIABLES like ' Gtid\_mode ' #如果开启了gtid, you get the location of the gtid_executed, that is, the location of the transaction that has been synchronized to the data file
The Query SHOW MASTER status #对于Myisam表, in the case of global s lock, only read, no write, get binlog filename and position, that is, the location of the current backup data, for InnoDB table, because the front start Transaction explicitly opens a transaction, so the location of the snapshot data is captured within the transaction
+ Query UNLOCK TABLES #在获得了binlog filename and position, unlock table
#下面查询INFORMATION_SCHEMA. SQL for the files table is for the NDB storage engine
Query SELECT logfile_group_name, file_name, Total_extents, Initial_size, ENGINE, EXTRA from INFORMATION_SCHEMA. FILES WHERE file_type = ' UNDO LOG ' and file_name are not NULL for GROUP by Logfile_group_name, file_name, ENGINE ORDER by Logfi Le_group_name
+ Query SELECT DISTINCT tablespace_name, file_name, Logfile_group_name, Extent_size, Initial_size, ENGINE from Informati On_schema. FILES WHERE file_type = ' datafile ' ORDER by Tablespace_name, Logfile_group_name
Query SHOW DATABASES #查看有哪些库
+ Query SHOW VARIABLES like ' ndbinfo\_version ' #NDB基本用不到, there's no need to pay attention
Init DB MySQL #开始备份mysql库
+ Query SHOW CREATE DATABASE IF not EXISTS ' MySQL '
SavePoint SP #定义的一个保存点, in order to avoid errors in the overall backup of this large transaction, use the breakpoint setting, where a savepoint is defined, indicating that this location is from the show master status, followed by each table
Query Show Tables #查看mysql库有哪些表
+ Query Show Table status like ' Columns\_priv ' #查看这个表的状态
Query SET sql_quote_show_create=1 #给每个表的每个字段加个反引号
Query SET SESSION character_set_results = ' binary ' #表结构的备份都是binary格式, so change this first
+ Query Show create table ' Columns_priv ' #查看这个表的定义
Query SET SESSION character_set_results = ' UTF8 '
Query Show fields from ' Columns_priv ' #查看表有哪些字段
Query SELECT/*!40001 Sql_no_cache */* from ' columns_priv ' #查询表中的数据, combining the field information from the show fields from ' Columns_priv ' to generate the insert int O Statement
Query SET SESSION character_set_results = ' binary '
+ Query use ' mysql '
Query SELECT @ @collation_database
+ Query SHOW TRIGGERS like ' Columns\_priv '
Query SET SESSION character_set_results = ' UTF8 '
When you Query ROLLBACK to savepoint sp # #一个表执行完之后, Roll back to the location of the previous transaction label, that is, roll back to the location of the save point and start over again
... #此处省略其他表, the backup process for all tables is the same here.
When you release SavePoint SP #同一个库中的所有表备份完成之后, the save point will be released
+ Query use ' mysql '
Query SELECT @ @collation_database
Query SET SESSION character_set_results = ' binary '
-Query SHOW FUNCTION STATUS WHERE Db = ' MySQL ' #查询mysql库的所有存储函数
* Query SHOW PROCEDURE STATUS WHERE Db = ' MySQL ' #查询mysql库的所有存储过程
Query SET SESSION character_set_results = ' UTF8 '
+ Query Show Table status like ' General\_log ' #查询日志的表状态信息
Query SET sql_quote_show_create=1
Query SET SESSION character_set_results = ' binary '
+ Query Show create table ' General_log ' #查询日志的表定义
Query SET SESSION character_set_results = ' UTF8 '
Query Show fields from ' General_log ' #查询日志的表字段信息
+ Query Show Table status like ' Slow\_log ' #查询慢日志表状态信息
Query SET sql_quote_show_create=1
Query SET SESSION character_set_results = ' binary '
+ Query Show create table ' Slow_log ' #查询慢日志表结构
Query SET SESSION character_set_results = ' UTF8 '
Query Show fields from ' Slow_log ' #查询慢日志表字段信息
The Init DB test #初始化另外一个库, Cycle begins the process above

#整个过程完成 quit the process

Note: The above process for personal understanding, if there are errors, but also to correct

Mysqldump What's been done during the backup process