同大多数关系型数据库一样，日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志可以帮助我们定位mysqld内部发生的事件，数据库性能故障，记录数据的变更历史，用户恢复数据库等等。二进制日志，也叫binary log，是MySQL Server中最为重要的日志之一，本文主要描述二进制日志。
1、MySQL日志文件系统的组成
a、错误日志：记录启动、运行或停止mysqld时出现的问题。
b、通用日志：记录建立的客户端连接和执行的语句。
c、更新日志：记录更改数据的语句。该日志在MySQL 5.1中已不再使用。
d、二进制日志：记录所有更改数据的语句。还用于复制。
e、慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
f、Innodb日志：innodb redo log
缺省情况下，所有日志创建于mysqld数据目录中。
可以通过刷新日志，来强制mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。
当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时，则日志被老化。
对于存在MySQL复制的情形下，从复制服务器将维护更多日志文件，被称为接替日志。
2、二进制日志(Binary log)
a、它包含的内容及作用如下：
包含了所有更新了数据或者已经潜在更新了数据(比如没有匹配任何行的一个DELETE)
包含关于每个更新数据库(DML)的语句的执行时间信息
不包含没有修改任何数据的语句，如果需要启用该选项，需要开启通用日志功能
主要目的是尽可能的将数据库恢复到数据库故障点，因为二进制日志包含备份后进行的所有更新
用于在主复制服务器上记录所有将发送给从服务器的语句
启用该选项数据库性能降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整。有些类似于oracle开启归档模式。
b、开启二进制日志的方法及属性
使用--log-bin[=file_name]选项或在配置文件中指定log-bin启动时，mysqld写入包含所有更新数据的SQL命令的日志文件。
对于未给出file_name值， 默认名为-bin后面所跟的主机名。
在未指定绝对路径的情形下，缺省位置保存在数据目录下。
每个二进制日志名会添加一个数字扩展名用于日志老化，因此不支持自定义的扩展名，会被mysql数字扩展名动态替换。
若当前的日志大小达到max_binlog_size，则自动创建新的二进制日志。
对于大的事务，二进制日志会超过max_binlog_size设定的值。也即是事务仅仅写入一个二进制日志。
由是可知，二进制日志文件大小接近，其size不是完全相等，这点不同于oracle。
二进制日志文件会有一个对应二进制日志索引文件，该文件包含所有的二进制日志，其文件名与二进制日志相同，扩展名为.index
二进制索引文件通过--log-bin-index[=file_name]选项来指定
RESET MASTER语句将删除所有二进制日志文件，这将影响到从库。也可以用PURGE MASTER LOGS只删除部分二进制文件。
3、二进制日志相关演示
[sql]
1. a、启用二进制日志
2. --当前环境
3. root@localhost[(none)]> show variables like '%version%';
4. +-------------------------+------------------------------+
5. | Variable_name | Value |
6. +-------------------------+------------------------------+
7. | innodb_version | 5.5.39 |
8. | protocol_version | 10 |
9. | slave_type_conversions | |
10. | version | 5.5.39 |
11. | version_comment | MySQL Community Server (GPL) |
12. | version_compile_machine | x86_64 |
13. | version_compile_os | Linux |
14. +-------------------------+------------------------------+
15.
16. root@localhost[(none)]> show variables like '%log_bin%';
17. +---------------------------------+-------+
18. | Variable_name | Value |
19. +---------------------------------+-------+
20. | log_bin | OFF | --该参数用于设定是否启用二进制日志
21. | log_bin_trust_function_creators | OFF
22. | sql_log_bin | ON
23. +---------------------------------+-------+
24.
25. --以下为binary log相关参数
26. root@localhost[(none)]> show variables like '%binlog%';
27. +-----------------------------------------+----------------------+
28. | Variable_name | Value |
29. +-----------------------------------------+----------------------+
30. | binlog_cache_size | 32768 |
31. | binlog_direct_non_transactional_updates | OFF
32. | binlog_format | STATEMENT |
33. | binlog_stmt_cache_size | 32768 |
34. | innodb_locks_unsafe_for_binlog | OFF
35. | max_binlog_cache_size | 18446744073709547520 |
36. | max_binlog_size | 1073741824 |
37. | max_binlog_stmt_cache_size | 18446744073709547520 |
38. | sync_binlog | 0 |
39. +-----------------------------------------+----------------------+
40.
41. --当前mysql服务器数据文件的缺省位置
42. root@localhost[(none)]> show variables like '%datadir%';
43. +---------------+-----------------+
44. | Variable_name | Value |
45. +---------------+-----------------+
46. | datadir | /var/lib/mysql/ |
47. +---------------+-----------------+
48.
49. --停止mysql服务器
50. suse11b:~ # service mysql stop
51. Shutting down MySQL.... done
52.
53. --编辑my.cnf来设定binary log日志位置(注，配置二进制日志路径及文件名后，系统变量log_bin被自动置为on)
54. suse11b:~ # vi /etc/my.cnf
55. suse11b:~ # grep -v ^# /etc/my.cnf
56. [mysqld]
57. log-error=/tmp/suse11b.err
58. log_bin=/var/lib/mysql/binarylog/binlog
59. suse11b:~ # mkdir -p /var/lib/mysql/binarylog
60. suse11b:~ # chown -R mysql:mysql /var/lib/mysql/binarylog
61.
62. suse11b:~ # /etc/init.d/mysql start
63. Starting MySQL.. done
64. suse11b:~ # ls -hltr /var/lib/mysql/binarylog/*
65. -rw-rw---- 1 mysql mysql 39 Oct 3 13:41 /var/lib/mysql/binarylog/binlog.index #索引文件
66. -rw-rw---- 1 mysql mysql 107 Oct 3 13:41 /var/lib/mysql/binarylog/binlog.000001 #日志文件
67.
68.
69. b、切换日志
70. suse11b:~ # mysql -uroot -pxxx
71. root@localhost[(none)]> flush logs;
72. Query OK, 0 rows
73.
74. root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/*
75. -rw-rw---- 1 mysql mysql 78 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.index
76. -rw-rw---- 1 mysql mysql 107 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000002 #切换后产生了000002
77. -rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001
78.
79. root@localhost[(none)]> system mysqladmin flush-logs #使用mysqladmin命令行工具flush-logs方式切换日志
80. root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/*
81. -rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001
82. -rw-rw---- 1 mysql mysql 117 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.index
83. -rw-rw---- 1 mysql mysql 107 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000003 #切换后产生了000003
84. -rw-rw---- 1 mysql mysql 147 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000002
85.
86. root@localhost[(none)]> system mysqladmin refresh #使用mysqladmin命令行工具refresh方式切换日志
87. root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/*
88. -rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001
89. -rw-rw---- 1 mysql mysql 147 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000002
90. -rw-rw---- 1 mysql mysql 156 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.index
91. -rw-rw---- 1 mysql mysql 107 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000004 #切换后产生了000004
92. -rw-rw---- 1 mysql mysql 147 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000003
93.
94.
95. c、模拟产生二进制日志及查看内容
96. root@localhost[(none)]> create database
97. Query OK, 1 row affected (0.00 sec)
98.
99. root@localhost[(none)]> use tempdb
100. Database
101. root@localhost[tempdb]> create table tb1(id smallint,val varchar(10));
102. Query OK, 0 rows
103.
104. root@localhost[tempdb]> insert into tb1 values(1,'jack');
105. Query OK, 1 row affected (0.01 sec)
106.
107. root@localhost[tempdb]> system strings /var/lib/mysql/binarylog/binlog.000004
108. bin?8.T
109. 5.5.39-log
110. z=.T
111. tempdb
112. create database
113. tempdb
114. create table tb1(id smallint,val varchar(10))
115. tempdb
116. BEGIN
117. tempdb
118. insert into tb1 values(1,'jack')
119.
120. root@localhost[tempdb]> system more /var/lib/mysql/binarylog/binlog.index
121. /var/lib/mysql/binarylog/binlog.000001
122. /var/lib/mysql/binarylog/binlog.000002
123. /var/lib/mysql/binarylog/binlog.000003
124. /var/lib/mysql/binarylog/binlog.000004
125.
126. --使用命令行工具mysqlbinlog直接提取二进制日志的内容
127. root@localhost[tempdb]> system mysqlbinlog /var/lib/mysql/binarylog/binlog.000004
128. /*!50530 SET
129. /*!40019 SET
130. /*!50003 SET
131. DELIMITER /*!*/;
132. # at
133. #141003 13:46:39 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.39-log created 141003 13:46:39
134. # Warning: this binlog is either in use or was not
135. BINLOG '
136. PzguVA8BAAAAZwAAAGsAAAABAAQANS41LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
137. AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
138. '/*!*/;
139. # at
140. #141003 14:08:58 server id 1 end_log_pos 194 Query thread_id=1 exec_time=0 error_code=0
141. SET TIMESTAMP=1412316538/*!*/;
142. SET
143. SET
144. SET
145. SET
146. /*!\C utf8 *//*!*/;
147. SET
148. SET
149. SET @@session.collation_database=DEFAULT/*!*/;
150. create database
151. /*!*/;
152. # at
153. #141003 14:09:36 server id 1 end_log_pos 304 Query thread_id=1 exec_time=0 error_code=0
154. use `tempdb`/*!*/;
155. SET TIMESTAMP=1412316576/*!*/;
156. create table tb1(id smallint,val varchar(10))
157. /*!*/;
158. # at
159. #141003 14:09:56 server id 1 end_log_pos 374 Query thread_id=1 exec_time=0 error_code=0
160. SET TIMESTAMP=1412316596/*!*/;
161. BEGIN
162. /*!*/;
163. # at
164. #141003 14:09:56 server id 1 end_log_pos 471 Query thread_id=1 exec_time=0 error_code=0
165. SET TIMESTAMP=1412316596/*!*/;
166. insert into tb1 values(1,'jack')
167. /*!*/;
168. # at
169. #141003 14:09:56 server id 1 end_log_pos 498 Xid = 25
170. COMMIT/*!*/;
171. DELIMITER ;
172. # End of
173. ROLLBACK /* added by
174. /*!50003 SET
175. /*!50530 SET
176. --从以上的内容可以看出二进制日志记录了所有操作的DML语句及其开销，以及一些系统环境变量的信息。
177.
178.
179. d、管理二进制日志
180. --对于二进制日志，应尽可能保存在安全的位置，与数据分开存储
181. --使用show binary logs获取二进制日志相关信息
182. root@localhost[(none)]> help show binary
183. Name: 'SHOW BINARY LOGS'
184. Description:
185. Syntax:
186. SHOW BINARY
187. SHOW MASTER LOGS
188.
189. Lists the binary log files on the server. This statement is used as
190. part of the procedure described in [HELP PURGE BINARY
191. how to
192.
193. root@localhost[tempdb]> show binary
194. +---------------+-----------+
195. | Log_name | File_size |
196. +---------------+-----------+
197. | binlog.000001 | 147 |
198. | binlog.000002 | 147 |
199. | binlog.000003 | 147 |
200. | binlog.000004 | 498 |
201. +---------------+-----------+
202.
203. show binlog events用于在二进制日志中显示事件。如果未指定'log_name'，则显示第一个二进制日志。
204. root@localhost[(none)]> help show binlog events; --获取帮助信息
205. Name: 'SHOW BINLOG EVENTS'
206. Description:
207. Syntax:
208. SHOW BINLOG EVENTS
209. IN 'log_name'] [FROM
210.
211. Shows the events in the binary log. If you do not specify 'log_name',
212. the first binary log is
213.
214. root@localhost[(none)]> show binlog events;
215. +---------------+-----+-------------+-----------+-------------+---------------------------------------+
216. | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
217. +---------------+-----+-------------+-----------+-------------+---------------------------------------+
218. | binlog.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.39-log, Binlog ver: 4 |
219. | binlog.000001 | 107 | Rotate | 1 | 147 | binlog.000002;pos=4 |
220. +---------------+-----+-------------+-----------+-------------+---------------------------------------+
221.
222. root@localhost[(none)]> show binlog events in 'binlog.000005'; --binlog.000005不存在，所以报错
223. ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not
224.
225. --下面的这个查询中，前面执行的DML在这里均可以看到
226. root@localhost[tempdb]> show binlog events in 'binlog.000004';
227. +---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
228. | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
229. +---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
230. | binlog.000004 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.39-log, Binlog ver: 4 |
231. | binlog.000004 | 107 | Query | 1 | 194 | create database
232. | binlog.000004 | 194 | Query | 1 | 304 | use `tempdb`; create table tb1(id smallint,val varchar(10)) |
233. | binlog.000004 | 304 | Query | 1 | 374 | BEGIN
234. | binlog.000004 | 374 | Query | 1 | 471 | use `tempdb`; insert into tb1 values(1,'jack') |
235. | binlog.000004 | 471 | Xid | 1 | 498 | COMMIT
236. +---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
237.
238. root@localhost[tempdb]> show binlog events in 'binlog.000004' from
239. +---------------+-----+------------+-----------+-------------+------------------------------------------------+
240. | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
241. +---------------+-----+------------+-----------+-------------+------------------------------------------------+
242. | binlog.000004 | 374 | Query | 1 | 471 | use `tempdb`; insert into tb1 values(1,'jack') |
243. | binlog.000004 | 471 | Xid | 1 | 498 | COMMIT
244. +---------------+-----+------------+-----------+-------------+------------------------------------------------+
245.
246. root@localhost[tempdb]> show binlog events in 'binlog.000004' from
247. +---------------+-----+------------+-----------+-------------+------------------------------------------------+
248. | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
249. +---------------+-----+------------+-----------+-------------+------------------------------------------------+
250. | binlog.000004 | 374 | Query | 1 | 471 | use `tempdb`; insert into tb1 values(1,'jack') |
251. +---------------+-----+------------+-----------+-------------+------------------------------------------------+
252.
253.
254. d、删除历史日志
255. --使用purge手动删除指定日志
256. --使用expire-log-days删除失效日志，设置变量expire_logs_days，删除超出这个变量保留期之前的所有日志被删除
257. --自动日志删除通常发生在服务器启动以及日志flush
258. --reset master方式
259. root@localhost[(none)]> help purge;
260. Name: 'PURGE BINARY LOGS'
261. Description:
262. Syntax:
263. PURGE { BINARY
264. TO 'log_name'
265.
266. Examples:
267. PURGE BINARY LOGS TO 'mysql-bin.010';
268. PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
269.
270. root@localhost[tempdb]> purge binary logs to 'binlog.000003';
271. Query OK, 0 rows
272.
273. root@localhost[tempdb]> show binary
274. +---------------+-----------+
275. | Log_name | File_size |
276. +---------------+-----------+
277. | binlog.000003 | 147 |
278. | binlog.000004 | 498 |
279. +---------------+-----------+
280.
281. root@localhost[tempdb]> system ls -hltr /var/lib/mysql/binarylog/*
282. -rw-rw---- 1 mysql mysql 147 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000003
283. -rw-rw---- 1 mysql mysql 498 Oct 3 14:09 /var/lib/mysql/binarylog/binlog.000004
284. -rw-rw---- 1 mysql mysql 78 Oct 3 14:23 /var/lib/mysql/binarylog/binlog.index
285.
286. --使用before子句purge日志，binlog.000003被删除
287. root@localhost[tempdb]> purge binary logs before '2014-10-03 14:09:56';
288. Query OK, 0 rows
289.
290. root@localhost[tempdb]> show binary
291. +---------------+-----------+
292. | Log_name | File_size |
293. +---------------+-----------+
294. | binlog.000004 | 498 |
295. +---------------+-----------+
296.
297. --Author: Leshami
298. --重置所有日志
299. --reset master将删除在索引文件中列出所有的日志文件并重置索引文件，最后生成一个新的binlog文件。
300. --该操作之前先备份binlog至其它位置以备以后需要。
301. root@localhost[tempdb]> help reset master;
302. Name: 'RESET MASTER'
303. Description:
304. Syntax:
305. RESET MASTER
306.
307. Deletes all binary log files listed in the index
308. binary log index file to be empty, and creates a new binary
309. This statement is intended to be used only when the master is
310. for the first time.
311.
312. root@localhost[tempdb]> reset master;
313. Query OK, 0 rows
314.
315. root@localhost[tempdb]> show binary
316. +---------------+-----------+
317. | Log_name | File_size |
318. +---------------+-----------+
319. | binlog.000001 | 107 | --reset之后，从000001开始生成全新空日志
320. +---------------+-----------+
321.
322. --expire_log系统变量控制二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。启动时和二进制日志循环时可能删除。
323. root@localhost[tempdb]> show variables like 'expire_log%';
324. +------------------+-------+
325. | Variable_name | Value |
326. +------------------+-------+
327. | expire_logs_days | 0 |
328. +------------------+-------+
329.
330. root@localhost[tempdb]> set expire_logs_days=7; --提示次系统变量为全局变量
331. ERROR 1229 (HY000): Variable 'expire_logs_days' is a GLOBAL variable and should be set with SET GLOBAL
332. root@localhost[tempdb]> set global expire_logs_days=7; --设置
333. Query OK, 0 rows
334.
335. root@localhost[tempdb]> select
336. +--------------------+
337. | @@expire_logs_days |
338. +--------------------+
339. | 7 |
340. +--------------------+
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
