mysql数据库中，flush logs语句的作用

需求描述：

今天在研究mysql数据库的备份和恢复，用到了flush logs这个SQL语句。

所以，在此进行测试，并且记录该SQL语句的作用。

概念描述：

在mysql数据库，如果数据库启动的时候，启用了log-bin选项，那么，

所有对于数据库的修改都会记录在binary log中，binary log可以用于数据库的恢复（基于时间点的恢复）

操作过程：

1.查看my.cnf中配置的log-bin参数

[mysql@redhat6 ~]$ grep "log-bin" /etc/my.cnf 
log-bin=/mysql/data/mysql-bin/mysql-bin   #定义binary log所在的目录及bin log以什么名字开始。
#log-bin=mysql-bin

2.在mysql数据库中，查看log_bin系统变量的设置
复制代码

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

复制代码

备注：以上的设置，表示数据库已经开启了binary log.

3.查看binary log在操作系统上生成的文件
复制代码

[mysql@redhat6 ~]$ cd /mysql/data/mysql-bin/
[mysql@redhat6 mysql-bin]$ ls -l
total 969552
-rw-rw----. 1 mysql mysql 27693 Jan 18 17:15 mysql-bin.000001
-rw-rw----. 1 mysql mysql 1133097 Jan 18 17:15 mysql-bin.000002
-rw-rw----. 1 mysql mysql 264 Jan 18 17:20 mysql-bin.000003
-rw-rw----. 1 mysql mysql 26636 Jan 18 17:24 mysql-bin.000004
-rw-rw----. 1 mysql mysql 1133097 Jan 18 17:24 mysql-bin.000005
-rw-rw----. 1 mysql mysql 126 Jan 18 17:34 mysql-bin.000006
-rw-rw----. 1 mysql mysql 126 Jan 18 17:36 mysql-bin.000007
-rw-rw----. 1 mysql mysql 126 Jan 18 17:38 mysql-bin.000008
-rw-rw----. 1 mysql mysql 126 Jan 18 17:40 mysql-bin.000009
-rw-rw----. 1 mysql mysql 126 Jan 19 09:59 mysql-bin.000010
-rw-rw----. 1 mysql mysql 126 Jan 19 10:03 mysql-bin.000011
-rw-rw----. 1 mysql mysql 126 Jan 19 10:05 mysql-bin.000012
-rw-rw----. 1 mysql mysql 126 Jan 19 10:06 mysql-bin.000013
-rw-rw----. 1 mysql mysql 8428 Jan 19 17:57 mysql-bin.000014
-rw-rw----. 1 mysql mysql 172320573 Jan 19 18:00 mysql-bin.000015
-rw-rw----. 1 mysql mysql 126 Jan 22 18:07 mysql-bin.000016
-rw-rw---- 1 mysql mysql 1202 Jan 23 18:21 mysql-bin.000017
-rw-rw---- 1 mysql mysql 231 Jan 23 22:44 mysql-bin.000018
-rw-rw---- 1 mysql mysql 486 Jan 24 18:13 mysql-bin.000019
-rw-rw---- 1 mysql mysql 126 Jan 25 11:40 mysql-bin.000020
-rw-rw---- 1 mysql mysql 126 Jan 25 14:05 mysql-bin.000021
-rw-rw---- 1 mysql mysql 126 Jan 25 14:10 mysql-bin.000022
-rw-rw---- 1 mysql mysql 126 Jan 25 18:02 mysql-bin.000023
-rw-rw---- 1 mysql mysql 126 Jan 26 21:21 mysql-bin.000024
-rw-rw---- 1 mysql mysql 126 Jan 29 14:20 mysql-bin.000025
-rw-rw---- 1 mysql mysql 3779 Jan 29 14:41 mysql-bin.000026
-rw-rw---- 1 mysql mysql 126 Jan 29 15:29 mysql-bin.000027
-rw-rw---- 1 mysql mysql 520 Jan 29 16:12 mysql-bin.000028
-rw-rw---- 1 mysql mysql 126 Jan 29 17:59 mysql-bin.000029
-rw-rw---- 1 mysql mysql 2261 Jan 30 17:32 mysql-bin.000030
-rw-rw---- 1 mysql mysql 2161843 Jan 31 12:58 mysql-bin.000031
-rw-rw---- 1 mysql mysql 2243815 Jan 31 18:43 mysql-bin.000032
-rw-rw---- 1 mysql mysql 165265 Feb 1 17:37 mysql-bin.000033
-rw-rw---- 1 mysql mysql 12792 Feb 2 19:25 mysql-bin.000034
-rw-rw---- 1 mysql mysql 264 Feb 5 11:22 mysql-bin.000035
-rw-rw---- 1 mysql mysql 744 Feb 5 17:04 mysql-bin.000036
-rw-rw---- 1 mysql mysql 1769 Feb 5 19:07 mysql-bin.000037
-rw-rw---- 1 mysql mysql 5600 Feb 26 17:31 mysql-bin.000038
-rw-rw---- 1 mysql mysql 126 Feb 27 10:13 mysql-bin.000039
-rw-rw---- 1 mysql mysql 150 Feb 27 10:14 mysql-bin.000040
-rw-rw---- 1 mysql mysql 126 Feb 27 10:14 mysql-bin.000041
-rw-rw---- 1 mysql mysql 126 Feb 27 10:30 mysql-bin.000042
-rw-rw---- 1 mysql mysql 126 Feb 27 10:32 mysql-bin.000043
-rw-rw---- 1 mysql mysql 126 Feb 27 10:51 mysql-bin.000044
-rw-rw---- 1 mysql mysql 126 Feb 27 10:53 mysql-bin.000045
-rw-rw---- 1 mysql mysql 126 Feb 27 11:04 mysql-bin.000046
-rw-rw---- 1 mysql mysql 126 Feb 27 12:41 mysql-bin.000047
-rw-rw---- 1 mysql mysql 626 Feb 27 14:34 mysql-bin.000048
-rw-rw---- 1 mysql mysql 715342328 Feb 27 15:54 mysql-bin.000049
-rw-rw---- 1 mysql mysql 308 Feb 27 16:03 mysql-bin.000050
-rw-rw---- 1 mysql mysql 305 Feb 27 16:27 mysql-bin.000051
-rw-rw---- 1 mysql mysql 126 Feb 27 16:28 mysql-bin.000052
-rw-rw---- 1 mysql mysql 126 Feb 27 18:17 mysql-bin.000053
-rw-rw---- 1 mysql mysql 126 Feb 28 18:33 mysql-bin.000054
-rw-rw---- 1 mysql mysql 126 Feb 28 18:40 mysql-bin.000055
-rw-rw---- 1 mysql mysql 126 Mar 1 15:04 mysql-bin.000056
-rw-rw---- 1 mysql mysql 126 Mar 1 17:45 mysql-bin.000057
-rw-rw---- 1 mysql mysql 6611 Mar 2 17:04 mysql-bin.000058
-rw-rw---- 1 mysql mysql 16241 Mar 5 10:47 mysql-bin.000059
-rw-rw---- 1 mysql mysql 126 Mar 6 17:48 mysql-bin.000060
-rw-rw---- 1 mysql mysql 1111 Mar 8 18:40 mysql-bin.000061
-rw-rw---- 1 mysql mysql 126 Mar 9 10:06 mysql-bin.000062
-rw-rw---- 1 mysql mysql 107 Mar 9 10:12 mysql-bin.000063
-rw-rw---- 1 mysql mysql 42103 Mar 9 18:27 mysql-bin.000064
-rw-rw---- 1 mysql mysql 7690 Mar 12 23:28 mysql-bin.000065
-rw-rw---- 1 mysql mysql 107 Mar 15 16:48 mysql-bin.000066
-rw-rw---- 1 mysql mysql 126 Mar 19 18:04 mysql-bin.000067
-rw-rw---- 1 mysql mysql 126 Mar 20 09:49 mysql-bin.000068
-rw-rw---- 1 mysql mysql 126 Mar 20 09:53 mysql-bin.000069
-rw-rw---- 1 mysql mysql 264 Mar 20 10:05 mysql-bin.000070
-rw-rw---- 1 mysql mysql 264 Mar 20 10:50 mysql-bin.000071
-rw-rw---- 1 mysql mysql 126 Mar 20 10:51 mysql-bin.000072
-rw-rw---- 1 mysql mysql 126 Mar 20 10:55 mysql-bin.000073
-rw-rw---- 1 mysql mysql 264 Mar 20 10:57 mysql-bin.000074
-rw-rw---- 1 mysql mysql 107 Mar 20 10:57 mysql-bin.000075
-rw-rw---- 1 mysql mysql 52702236 Mar 21 15:01 mysql-bin.000076
-rw-rw---- 1 mysql mysql 107 Mar 26 14:40 mysql-bin.000077
-rw-rw---- 1 mysql mysql 23077422 Mar 28 18:13 mysql-bin.000078
-rw-rw---- 1 mysql mysql 22032005 Mar 29 15:26 mysql-bin.000079
-rw-rw---- 1 mysql mysql 107 Mar 30 14:06 mysql-bin.000080
-rw-rw---- 1 mysql mysql 107 Apr 2 09:21 mysql-bin.000081
-rw-rw---- 1 mysql mysql 150 Apr 3 15:58 mysql-bin.000082
-rw-rw---- 1 mysql mysql 150 Apr 3 16:00 mysql-bin.000083
-rw-rw---- 1 mysql mysql 150 Apr 3 16:46 mysql-bin.000084
-rw-rw---- 1 mysql mysql 150 Apr 3 16:47 mysql-bin.000085
-rw-rw---- 1 mysql mysql 150 Apr 3 16:52 mysql-bin.000086
-rw-rw---- 1 mysql mysql 238 Apr 3 17:12 mysql-bin.000087
-rw-rw---- 1 mysql mysql 150 Apr 3 17:14 mysql-bin.000088
-rw-rw---- 1 mysql mysql 107 Apr 3 17:14 mysql-bin.000089
-rw-rw----. 1 mysql mysql 2611 Apr 3 17:14 mysql-bin.index

复制代码

备注：binary log是以mysql-bin开头的，然后点后面是binary log的序号。

4.mysql-bin.index文件是所有bin log文件的列表（列出所有binary log所在路径和名字，./表示的是data目录）
复制代码

[mysql@redhat6 mysql-bin]$ cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009
./mysql-bin.000010
./mysql-bin.000011
./mysql-bin.000012
./mysql-bin.000013
./mysql-bin.000014
./mysql-bin.000015
./mysql-bin.000016
./mysql-bin.000017
./mysql-bin.000018
./mysql-bin.000019
./mysql-bin.000020
./mysql-bin.000021
./mysql-bin.000022
./mysql-bin.000023
./mysql-bin.000024
./mysql-bin.000025
./mysql-bin.000026
./mysql-bin.000027
./mysql-bin.000028
./mysql-bin.000029
./mysql-bin.000030
./mysql-bin.000031
./mysql-bin.000032
./mysql-bin.000033
./mysql-bin.000034
./mysql-bin.000035
./mysql-bin.000036
./mysql-bin.000037
./mysql-bin.000038
./mysql-bin.000039
./mysql-bin.000040
./mysql-bin.000041
./mysql-bin.000042
./mysql-bin.000043
/mysql/data/mysql-bin/mysql-bin.000044
/mysql/data/mysql-bin/mysql-bin.000045
/mysql/data/mysql-bin/mysql-bin.000046
/mysql/data/mysql-bin/mysql-bin.000047
/mysql/data/mysql-bin/mysql-bin.000048
/mysql/data/mysql-bin/mysql-bin.000049
/mysql/data/mysql-bin/mysql-bin.000050
/mysql/data/mysql-bin/mysql-bin.000051
/mysql/data/mysql-bin/mysql-bin.000052
/mysql/data/mysql-bin/mysql-bin.000053
/mysql/data/mysql-bin/mysql-bin.000054
/mysql/data/mysql-bin/mysql-bin.000055
/mysql/data/mysql-bin/mysql-bin.000056
/mysql/data/mysql-bin/mysql-bin.000057
/mysql/data/mysql-bin/mysql-bin.000058
/mysql/data/mysql-bin/mysql-bin.000059
/mysql/data/mysql-bin/mysql-bin.000060
/mysql/data/mysql-bin/mysql-bin.000061
/mysql/data/mysql-bin/mysql-bin.000062
/mysql/data/mysql-bin/mysql-bin.000063
/mysql/data/mysql-bin/mysql-bin.000064
/mysql/data/mysql-bin/mysql-bin.000065
/mysql/data/mysql-bin/mysql-bin.000066
/mysql/data/mysql-bin/mysql-bin.000067
/mysql/data/mysql-bin/mysql-bin.000068
/mysql/data/mysql-bin/mysql-bin.000069
/mysql/data/mysql-bin/mysql-bin.000070
/mysql/data/mysql-bin/mysql-bin.000071
/mysql/data/mysql-bin/mysql-bin.000072
/mysql/data/mysql-bin/mysql-bin.000073
/mysql/data/mysql-bin/mysql-bin.000074
/mysql/data/mysql-bin/mysql-bin.000075
/mysql/data/mysql-bin/mysql-bin.000076
/mysql/data/mysql-bin/mysql-bin.000077
/mysql/data/mysql-bin/mysql-bin.000078
/mysql/data/mysql-bin/mysql-bin.000079
/mysql/data/mysql-bin/mysql-bin.000080
/mysql/data/mysql-bin/mysql-bin.000081
/mysql/data/mysql-bin/mysql-bin.000082
/mysql/data/mysql-bin/mysql-bin.000083
/mysql/data/mysql-bin/mysql-bin.000084
/mysql/data/mysql-bin/mysql-bin.000085
/mysql/data/mysql-bin/mysql-bin.000086
/mysql/data/mysql-bin/mysql-bin.000087
/mysql/data/mysql-bin/mysql-bin.000088
/mysql/data/mysql-bin/mysql-bin.000089

复制代码

5.查看当前数据库binary log的位置
复制代码

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000089 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

复制代码

备注：当前使用的bin log是“mysql-bin.000089”，位置是107.

6.执行flush logs命令
复制代码

mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000090 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

复制代码

备注：通过执行flush logs命令之后，再次查询binary log信息，发现已经使用了一个新的bin log文件了。

7.查看操作系统上是否也多了一个binary log文件
复制代码

-rw-rw----  1 mysql mysql  22032005 Mar 29 15:26 mysql-bin.000079
-rw-rw----  1 mysql mysql       107 Mar 30 14:06 mysql-bin.000080
-rw-rw----  1 mysql mysql       107 Apr  2 09:21 mysql-bin.000081
-rw-rw----  1 mysql mysql       150 Apr  3 15:58 mysql-bin.000082
-rw-rw----  1 mysql mysql       150 Apr  3 16:00 mysql-bin.000083
-rw-rw----  1 mysql mysql       150 Apr  3 16:46 mysql-bin.000084
-rw-rw----  1 mysql mysql       150 Apr  3 16:47 mysql-bin.000085
-rw-rw----  1 mysql mysql       150 Apr  3 16:52 mysql-bin.000086
-rw-rw----  1 mysql mysql       238 Apr  3 17:12 mysql-bin.000087
-rw-rw----  1 mysql mysql       150 Apr  3 17:14 mysql-bin.000088
-rw-rw----  1 mysql mysql       150 Apr  3 17:35 mysql-bin.000089
-rw-rw----  1 mysql mysql       107 Apr  3 17:35 mysql-bin.000090  #操作系统上生成了新的binary log文件。
-rw-rw----. 1 mysql mysql      2650 Apr  3 17:35 mysql-bin.index

小结：

flush logs命令的作用就是关闭当前使用的binary log，然后打开一个新的binary log文件，文件的序号加1.