开启general_log日志,获取mysqldump执行语句
show VARIABLES like 'general_log%'
set GLOBAL general_log=on
执行备份命令
mysqldump --single-transaction -uroot -pdmysqlbadmin --master-data=2 -A -R -E >/data/test.sql
得到general_log日志
161109 14:48:25 8 Connectroot@localhost 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 LOCK
8 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
8 QuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
8 QuerySHOW MASTER STATUS
8 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 IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_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 DBaccesslog
8 QuerySHOW CREATE DATABASE IF NOT EXISTS `accesslog`
8 Queryshow tables
8 Queryshow table status like 'accesslog'
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 Queryuse `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_results = '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'
测试方法,这里就不贴了,总结
1、非事务引擎表,有数据不一致风险,例如:myisam。
2、执行备份过程中,不允许有create table 及create database操作,事务里无法控制该类型语句,导致不一致备份。
3、如果之前有使用osc在线修改表结构,等它执行完后,再做备份。