系统环境
# 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，保证了对数据库中的数据没有影响。 时间： 2024-10-31 04:15:48