mysqldump原理和重要参数

1. 开启general_log

set global general_log=1
show gloabal variables like '%general%'; 


2.执行备份
mysqldump --master-data=2 --single-transaction -A > db.sql


参数解释：
--master-data=2        ：备份出来的sql语句里面 change master 带上注释了
--single-transaction   : 设置事务的隔离级别为可重复读repeatable read，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响
dump-slave             :从库上dump数据
set-gtid-purged=OFF|ON : 是否需要GTID的信息


3.查看general_log日志


connectroot@localhost on  using socket
query/*!40100 set @@sql_mode='' */
query/*!40103 set time_zone='+00:00' */
queryflush /*!40101 local */ tables      
queryflush tables with read lock         
queryset session transaction isolation level repeatable read #3
querystart transaction /*!40100 with consistent snapshot */  #4
queryshow variables like 'gtid\_mode'
queryselect @@global.gtid_executed  #5
queryshow master status   #6
queryunlock tables        #7
queryshow databases       #8
queryshow variables like 'ndbinfo\_version'
init dbadminsql
queryshow create database if not exists `adminsql`  #9
querysavepoint sp  #10
queryshow tables
queryshow table status like 'auth\_group' #11
queryset sql_quote_show_create=1
queryset session character_set_results = 'binary' #12
queryshow create table `auth_group`   #13
queryset session character_set_results = 'utf8' #14
queryshow fields from `auth_group`
queryshow fields from `auth_group`
queryselect /*!40001 sql_no_cache */ * from `auth_group` #15
queryset session character_set_results = 'binary'
queryuse `adminsql`
queryselect @@collation_database
queryshow triggers like 'auth\_group'
queryset session character_set_results = 'utf8'
queryrollback to savepoint sp    #16
queryshow table status like 'auth\_group\_permissions'
queryset sql_quote_show_create=1
queryset session character_set_results = 'binary'
queryshow create table `auth_group_permissions`
queryset session character_set_results = 'utf8'
queryshow fields from `auth_group_permissions`
queryshow fields from `auth_group_permissions`
queryselect /*!40001 sql_no_cache */ * from `auth_group_permissions`
queryset session character_set_results = 'binary'
queryuse `adminsql`
queryselect @@collation_database
queryshow triggers like 'auth\_group\_permissions'
queryset session character_set_results = 'utf8'
......
queryrollback to savepoint sp
queryshow table status like 'mysql\_tables'
queryset sql_quote_show_create=1
queryset session character_set_results = 'binary'
queryshow create table `mysql_tables`
queryset session character_set_results = 'utf8'
queryshow fields from `mysql_tables`
queryshow fields from `mysql_tables`
queryselect /*!40001 sql_no_cache */ * from `mysql_tables`
queryset session character_set_results = 'binary'
queryuse `adminsql`
queryselect @@collation_database
queryshow triggers like 'mysql\_tables'
queryset session character_set_results = 'utf8'
queryrollback to savepoint sp

queryrelease savepoint sp