MySQL 5.5 mysqldump backup principle

Open General_log log, get mysqldump EXECUTE statement

Show VARIABLES like ' general_log% ' set GLOBAL general_log=on

Execute backup command

Mysqldump--single-transaction-uroot-pdmysqlbadmin--master-data=2-a  -r-e >/data/test.sql

Get General_log Log

161109 14:48:25 8 Connect[email protected] on 8 query/*!40100 set @ @SQL_MODE = ' */8 query/*!40103 Set Time_zone = ' +00:00 ' */8 Queryflush/*!40101 LOCAL */TABLES 8 QueryFLUSH TABLES with READ LOCK8 QuerySET SESSION TRANSACTION Isolation LevelRepeatable READ8 QuerySTART TRANSACTION/*!40100 with consistent SNAPSHOT * /8 QuerySHOW MASTER STATUS8 QueryUNLOCK TABLES    8 Queryselect 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 8 queryselect DISTINCT tablespace_name, file_name, Logfile_group_name, Extent_size, INITIAL_SIZE, ENGINE From INFORMATION_SCHEMA. FILES WHERE file_type = ' datafile ' ORDER by Tablespace_name, Logfile_group_name 8 queryshow DATABASES 8 Init dbacces Slog 8 queryshow CREATE DATABASE IF not EXISTS ' accesslog ' 8 queryshow tables 8 queryshow table status like ' Acce Sslog ' 8 QuerySET OPTION sql_quote_show_create=1 8 QuerySET SESSION character_set_results = ' binary ' 8 queryshow CREATE TABLE ' Accesslog ' 8 QuerySET SESSION character_set_results = ' UTF8 ' 8 queryshow fields from ' Accesslog ' 8 Queryselect/*!40001 Sql_no_cache */* from ' accesslog ' 8 QuerySET SESSION character_set_results = ' binary ' 8 Queryu Se ' ACCesslog ' 8 queryselect @ @collation_database 8 queryshow TRIGGERS like ' accesslog ' 8 QuerySET SESSION character _set_results = ' UTF8 ' 8 queryshow events 8 Queryuse ' Accesslog ' 8 queryselect @ @collation_database 8 QuerySET Session character_set_results = ' binary ' 8 queryshow CREATE EVENT ' e_delaccesslog ' 8 QuerySET SESSION character_set_ results = ' UTF8 ' 8 queryuse ' accesslog ' 8 queryselect @ @collation_database 8 QuerySET SESSION Character_set_resul     ts = ' binary ' 8 queryshow FUNCTION STATUS where db = ' Accesslog ' 8 queryshow PROCEDURE STATUS where db = ' Accesslog ' 8 queryshow CREATE PROCEDURE ' p_delaccesslog ' 8 QuerySET SESSION character_set_results = ' UTF8 '

Test method, which is not posted here, summed up

1, non-transaction engine table, there is data inconsistency risk, for example: MyISAM. 2, during the backup process, no CREATE table and create database operation is allowed, the transaction cannot control the type of statement, resulting in inconsistent backup.
3, if you have previously used the OSC to modify the table structure online, and so on after it executes, then do the backup.

MySQL 5.5 mysqldump backup principle