开发者社区> miles_wong> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

mysqldump原理及实践

简介: 系统环境 # cat /etc/redhat-release CentOS release 6.5 (Final) # uname -a Linux miles 2.
+关注继续查看

系统环境

# cat /etc/redhat-release
CentOS release 6.5 (Final)
# uname -a
Linux miles 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

软件版本


mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.27-log |
+------------+

打开general_log

mysql> set global general_log=1;
Query OK, 0 rows affected (0.04 sec)

mysql> show global variables like '%gen%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| general_log      | ON                    |
| general_log_file | /data/mysql/miles.log |
+------------------+-----------------------+
2 rows in set (0.01 sec)

创建测试数据

mysql> create database test;
Query OK, 1 row affected (0.03 sec)

mysql> use test
Database changed

mysql> create table t1 (
    -> id int primary key auto_increment,
    -> name varchar(10) not null,
    -> age int not null);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1(name,age) values('name1',10),('name2',10),('name3',10),('name4',10),('name5',10),('name6',10),('name7',10),('name8',10),('name9',10);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> create table t2(id int not null primary key auto_increment,name varchar(10) not null,age int not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2(name,age) values('name1',10),('name2',10),('name3',10),('name4',10),('name5',10),('name6',10),('name7',10),('name8',10),('name9',10);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

不开启–single-transaction执行备份

# mysqldump -uroot -p --socket=/data/mysql.sock -B test

查看对应的general_log

160728 21:28:00     5 Connect   root@localhost on 
                    5 Query     /*!40100 SET @@SQL_MODE='' */
                    5 Query     /*!40103 SET TIME_ZONE='+00:00' */
                    5 Query     SHOW VARIABLES LIKE 'gtid\_mode'
                    5 Query     SELECT 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 ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
                    5 Query     SELECT 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 ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
                    5 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'
                    5 Init DB   test
                    5 Query     SHOW CREATE DATABASE IF NOT EXISTS `test`
                    5 Query     show tables
                    5 Query     LOCK TABLES `t1` READ /*!32311 LOCAL */,`t2` READ /*!32311 LOCAL */
                    5 Query     show table status like 't1'
                    5 Query     SET SQL_QUOTE_SHOW_CREATE=1
                    5 Query     SET SESSION character_set_results = 'binary'
                    5 Query     show create table `t1`
                    5 Query     SET SESSION character_set_results = 'utf8'
                    5 Query     show fields from `t1`
                    5 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
                    5 Query     SET SESSION character_set_results = 'binary'
                    5 Query     use `test`
                    5 Query     select @@collation_database
                    5 Query     SHOW TRIGGERS LIKE 't1'
                    5 Query     SET SESSION character_set_results = 'utf8'
                    5 Query     show table status like 't2'
                    5 Query     SET SQL_QUOTE_SHOW_CREATE=1
                    5 Query     SET SESSION character_set_results = 'binary'
                    5 Query     show create table `t2`
                    5 Query     SET SESSION character_set_results = 'utf8'
                    5 Query     show fields from `t2`
                    5 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
                    5 Query     SET SESSION character_set_results = 'binary'
                    5 Query     use `test`
                    5 Query     select @@collation_database
                    5 Query     SHOW TRIGGERS LIKE 't2'
                    5 Query     SET SESSION character_set_results = 'utf8'
                    5 Query     UNLOCK TABLES

原理:

加全局读锁(防止备份数据不一致)
LOCK TABLES `t1` READ /*!32311 LOCAL */,`t2` READ /*!32311 LOCAL */
获取建表信息生成建表语句
show create table `t1`
当前字符集会做一些处理,防止乱码
SET SESSION character_set_results = 'utf8'
获取行记录从而转换成insert插入语句
show fields from `t1`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
重复表t1操作
释放读锁
UNLOCK TABLES

开启–single-transaction备份

# mysqldump -uroot -p --socket=/data/mysql.sock -B test --single-transaction

查看对应的general_log

160728 21:28:41     6 Connect   root@localhost on 
                    6 Query     /*!40100 SET @@SQL_MODE='' */
                    6 Query     /*!40103 SET TIME_ZONE='+00:00' */
                    6 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
                    6 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
                    6 Query     SHOW VARIABLES LIKE 'gtid\_mode'
                    6 Query     UNLOCK TABLES
                    6 Query     SELECT 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 ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
                    6 Query     SELECT 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 ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
                    6 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'
                    6 Init DB   test
                    6 Query     SHOW CREATE DATABASE IF NOT EXISTS `test`
                    6 Query     SAVEPOINT sp
                    6 Query     show tables
                    6 Query     show table status like 't1'
                    6 Query     SET SQL_QUOTE_SHOW_CREATE=1
                    6 Query     SET SESSION character_set_results = 'binary'
                    6 Query     show create table `t1`
                    6 Query     SET SESSION character_set_results = 'utf8'
                    6 Query     show fields from `t1`
                    6 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
                    6 Query     SET SESSION character_set_results = 'binary'
                    6 Query     use `test`
                    6 Query     select @@collation_database
                    6 Query     SHOW TRIGGERS LIKE 't1'
                    6 Query     SET SESSION character_set_results = 'utf8'
                    6 Query     ROLLBACK TO SAVEPOINT sp
                    6 Query     show table status like 't2'
                    6 Query     SET SQL_QUOTE_SHOW_CREATE=1
                    6 Query     SET SESSION character_set_results = 'binary'
                    6 Query     show create table `t2`
                    6 Query     SET SESSION character_set_results = 'utf8'
                    6 Query     show fields from `t2`
                    6 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
                    6 Query     SET SESSION character_set_results = 'binary'
                    6 Query     use `test`
                    6 Query     select @@collation_database
                    6 Query     SHOW TRIGGERS LIKE 't2'
                    6 Query     SET SESSION character_set_results = 'utf8'
                    6 Query     ROLLBACK TO SAVEPOINT sp
                    6 Query     RELEASE SAVEPOINT sp

原理:

更改当前会话隔离级别为rr
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
开始事务(并生成一致性快照)
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
释放表锁
UNLOCK TABLES
设置一个savepoint
SAVEPOINT sp
查看数据库下的表
show tables
对t1表进行操作,获得表信息、建表语句及数据
show create table `t1`
SET SESSION character_set_results = 'utf8'
show fields from `t1`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
回滚到savepoint sp
ROLLBACK TO SAVEPOINT sp
对其他表重复该过程
最后释放savepoint
RELEASE SAVEPOINT sp

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mysqldump的简单使用
背景 需要搞mysql数据同步,从一台服务器把数据库同步到另外一台服务器上,如果折腾过的话,就知道有个这玩意--mysqldump,其实就是mysql自带的一个命令而已 操作步骤 1. 认识mysqldump 一般都是mysql安装目录/bin/下,命令也简单可以通过mysqldump --help 查看下 2.
692 0
【mysql】mysqldump用法
mysqldump备份用法小结
1842 0
mysqldump的几个主要选项
--single-transaction InnoDB 表在备份时,通常启用选项 --single-transaction 来保证备份的一致性,实际上它的工作原理是设定本次会话的隔离级别为:REPEATABLE READ,以确保本次会话(dump)时,不会看到其他会话已经提交了的数据 --complete-insert,-c 导出的数据采用包含字段名的完整 INSERT 方式,也就是把所有的值都写在一行。
683 0
使用mysqldump导出数据库
    mysqldump是mysql用于转存储数据库的客户端程序。它主要产生一系列的SQL语句,可以封装到文件,该文件包含有所有重建您的数据库所需要的SQL命令如CREATE DATABASE,CREATE TABLE,INSERT等等。
1206 0
10、mysqldump的简单用法
1、mysqldump - a database backup program mysqldump [options] [db_name [tbl_name ...]] 如果你不给定任何表,整个数据库将被导出。
725 0
+关注
miles_wong
Java核心技术 数据存储与数据库 分布式系统与计算 系统研发与运维 编程语言
87
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载