**********************mysqldump 所需权限*************************************

dump tables:SELECT

dump views:SHOW VIEW

dump triggers:TRIGGER

LOCK TABLES（如果不使用--single-transaction）

RELOAD,SUPER, REPLICATION CLIENT (--master-data=2)

数据库字符集更改：ALTER DATABASE

**********************mysqldump 适用场景************************************

全量表结构、存储过程、触发器等备份

单表（小表）备份

**********************mysqldump 缺点****************************************

逻辑备份，导出比较慢

单线程备份，5.7有mysqlpump以及开源版的mydumper可进行并行和压缩备份恢复

使用--single-transaction可实现在线备份，但是碰到操作时间过长的事物可能会使数据库出问题

5.6以下版本使用可能会使buffer pool热数据刷出，5.6及以上版本可通过innodb_old_blocks_time来控制热数据的进出

**********************mysqldump 常用语句****************************************

只导出表结构：

mysqldump -d --trigger=false

只导出存储过程：

mysqldump -f -Rtdn --trigger=false

只导出触发器：

mysqldump -f -tdn --trigger

只导出事件：

mysqldump -f -Etdn --triggers=False

只导出数据:

mysqldump -f --single-transaction --triggers=false -t

导出表中某些行：

mysqldump dbname --where="1=1 limit 1000"

在线建从库：

mysqldump --master-data=2 --single-transaction -A

**********************mysqldump 后台工作细节**************************************

查看general log是否打开：

mysql> set global general_log = on; 

Query OK, 0 rows affected (0.01 sec)


mysql> show variables like '%general%';

+------------------+-----------------------------------------------------+

| Variable_name    | Value                                               |

+------------------+-----------------------------------------------------+

| general_log      | ON                                                  |

| general_log_file | /app/data/mysql/3336/data/BJ-ECS-XHM-TEST-YL-19.log |

+------------------+-----------------------------------------------------+

2 rows in set (0.02 sec)


创建数据库及测试数据：

create database gaoquan;

create table t1(id int not null primary key auto_increment,name varchar(10) not null,age int not null);

insert into t1(name,age) values('name1',10),('name2',10),('name3',10),('name4',10),('name5',10),('name6',10),('name7',10),('name8',10),('name9',10);

create table t2(id int not null primary key auto_increment,name varchar(10) not null,age int not null);

insert into t2(name,age) values('name1',10),('name2',10),('name3',10),('name4',10),('name5',10),('name6',10),('name7',10),('name8',10),('name9',10);

创建mysqldump备份用户：

grant select,show view,trigger,lock tables on *.* to backup@'%' identified by 'backup007';


不开启--single-transaction执行备份：


/usr/local/mysql/bin/mysqldump -u backup -p gaoquan -h 10.50.12.33 -P 3336  

general log 输出：

160105 11:29:55  296 Connectbackup@10.50.12.33 on 

 296 Query/*!40100 SET @@SQL_MODE='' */

 296 Query/*!40103 SET TIME_ZONE='+00:00' */

 296 QuerySHOW VARIABLES LIKE 'gtid\_mode'

 296 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 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 ('gaoquan'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME

 296 QuerySELECT 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 ('gaoquan')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

 296 QuerySHOW VARIABLES LIKE 'ndbinfo\_version'

 296 Init DBgaoquan

 296 Queryshow tables

 296 QueryLOCK TABLES `t1` READ /*!32311 LOCAL */,`t2` READ /*!32311 LOCAL */

 296 Queryshow table status like 't1'

 296 QuerySET SQL_QUOTE_SHOW_CREATE=1

 296 QuerySET SESSION character_set_results = 'binary'

 296 Queryshow create table `t1`

 296 QuerySET SESSION character_set_results = 'utf8'

 296 Queryshow fields from `t1`

 296 QuerySELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`

 296 QuerySET SESSION character_set_results = 'binary'

 296 Queryuse `gaoquan`

 296 Queryselect @@collation_database

 296 QuerySHOW TRIGGERS LIKE 't1'

 296 QuerySET SESSION character_set_results = 'utf8'

 296 Queryshow table status like 't2'

 296 QuerySET SQL_QUOTE_SHOW_CREATE=1

 296 QuerySET SESSION character_set_results = 'binary'

 296 Queryshow create table `t2`

 296 QuerySET SESSION character_set_results = 'utf8'

 296 Queryshow fields from `t2`

 296 QuerySELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`

 296 QuerySET SESSION character_set_results = 'binary'

 296 Queryuse `gaoquan`

 296 Queryselect @@collation_database

 296 QuerySHOW TRIGGERS LIKE 't2'

 296 QuerySET SESSION character_set_results = 'utf8'

 296 QueryUNLOCK TABLES

 296 Quit


原理：

加全局读锁(防止备份数据不一致)->当前字符集会做一些处理，防止乱码->获取建表信息生成建表语句->获取行记录从而转换成insert插入语句->释放读锁



开启--single-transaction备份：

/usr/local/mysql/bin/mysqldump -u backup -p gaoquan -h 10.50.12.33 -P 3336  --single-transaction


160105 11:33:41  298 Connectbackup@10.50.12.33 on 

 298 Query/*!40100 SET @@SQL_MODE='' */

 298 Query/*!40103 SET TIME_ZONE='+00:00' */

 298 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

 298 QuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

 298 QuerySHOW VARIABLES LIKE 'gtid\_mode'

 298 QueryUNLOCK TABLES

 298 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 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 ('gaoquan'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME

 298 QuerySELECT 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 ('gaoquan')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

 298 QuerySHOW VARIABLES LIKE 'ndbinfo\_version'

 298 Init DBgaoquan

 298 QuerySAVEPOINT sp

 298 Queryshow tables

 298 Queryshow table status like 't1'

 298 QuerySET SQL_QUOTE_SHOW_CREATE=1

 298 QuerySET SESSION character_set_results = 'binary'

 298 Queryshow create table `t1`

 298 QuerySET SESSION character_set_results = 'utf8'

 298 Queryshow fields from `t1`

 298 QuerySELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`

 298 QuerySET SESSION character_set_results = 'binary'

 298 Queryuse `gaoquan`

 298 Queryselect @@collation_database

 298 QuerySHOW TRIGGERS LIKE 't1'

 298 QuerySET SESSION character_set_results = 'utf8'

 298 QueryROLLBACK TO SAVEPOINT sp

 298 Queryshow table status like 't2'

 298 QuerySET SQL_QUOTE_SHOW_CREATE=1

 298 QuerySET SESSION character_set_results = 'binary'

 298 Queryshow create table `t2`

 298 QuerySET SESSION character_set_results = 'utf8'

 298 Queryshow fields from `t2`

 298 QuerySELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`

 298 QuerySET SESSION character_set_results = 'binary'

 298 Queryuse `gaoquan`

 298 Queryselect @@collation_database

 298 QuerySHOW TRIGGERS LIKE 't2'

 298 QuerySET SESSION character_set_results = 'utf8'

 298 QueryROLLBACK TO SAVEPOINT sp

 298 QueryRELEASE SAVEPOINT sp

 298 Quit


原理：

更改当前会话隔离级别为rr->开始事物->释放表锁->导出建表语句->设置一个savepoint->导出表数据->回滚到savepoint sp->对其他表重复该过程->最后释放savepoint

整个过程，没有任何锁。RR隔离级别保证在事务中只读取本事务之前的一致性的数据 。 rollback to savepoint sp; 保证了对数据库中的数据没有影响。