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.
文档创建时间：2018年4月3日17:37:23
