mysqldump原理及实验

2331

                      **********************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; 保证了对数据库中的数据没有影响。