mysqldump原理及实践

系统环境

# 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