MySQL 二进制日志(Binary Log)

朴灿烈づ我的快乐病毒、 2022-08-13 00:40 338阅读 0赞 
  1. 同大多数关系型数据库一样，日志文件是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、二进制日志相关演示 

  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. +-------------------------+------------------------------+
  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. +---------------------------------+-------+
  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. +-----------------------------------------+----------------------+
  41. --当前mysql服务器数据文件的缺省位置
  42. root@localhost[(none)]> show variables like '%datadir%';
  43. +---------------+-----------------+
  44. | Variable_name | Value           |
  45. +---------------+-----------------+
  46. | datadir       | /var/lib/mysql/ |
  47. +---------------+-----------------+
  49. --停止mysql服务器
  50. suse11b:~ # service mysql stop
  51. Shutting down MySQL....                                               done
  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
  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 #日志文件
  69. b、切换日志
  70. suse11b:~ # mysql -uroot -pxxx
  71. root@localhost[(none)]> flush logs;
  72. Query OK, 0 rows affected (0.04 sec)
  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
  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
  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 
  95. c、模拟产生二进制日志及查看内容
  96. root@localhost[(none)]> create database tempdb;
  97. Query OK, 1 row affected (0.00 sec)
  99. root@localhost[(none)]> use tempdb
  100. Database changed
  101. root@localhost[tempdb]> create table tb1(id smallint,val varchar(10));
  102. Query OK, 0 rows affected (0.00 sec)
  104. root@localhost[tempdb]> insert into tb1 values(1,'jack');
  105. Query OK, 1 row affected (0.01 sec)
  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 tempdb
  113. tempdb
  114. create table tb1(id smallint,val varchar(10))
  115. tempdb
  116. BEGIN
  117. tempdb
  118. insert into tb1 values(1,'jack')
  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
  126. --使用命令行工具mysqlbinlog直接提取二进制日志的内容
  127. root@localhost[tempdb]> system mysqlbinlog /var/lib/mysql/binarylog/binlog.000004
  128. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  129. /*!40019 SET @@session.max_insert_delayed_threads=0*/;
  130. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  131. DELIMITER /*!*/;
  132. # at 4
  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 closed properly.
  135. BINLOG '
  136. PzguVA8BAAAAZwAAAGsAAAABAAQANS41LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  137. AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
  138. '/*!*/;
  139. # at 107
  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 @@session.pseudo_thread_id=1/*!*/;
  143. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  144. SET @@session.sql_mode=0/*!*/;
  145. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  146. /*!\C utf8 *//*!*/;
  147. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
  148. SET @@session.lc_time_names=0/*!*/;
  149. SET @@session.collation_database=DEFAULT/*!*/;
  150. create database tempdb
  151. /*!*/;
  152. # at 194
  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 304
  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 374
  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 471
  169. #141003 14:09:56 server id 1  end_log_pos 498   Xid = 25
  170. COMMIT/*!*/;
  171. DELIMITER ;
  172. # End of log file
  173. ROLLBACK /* added by mysqlbinlog */;
  174. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  175. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  176. --从以上的内容可以看出二进制日志记录了所有操作的DML语句及其开销，以及一些系统环境变量的信息。
  179. d、管理二进制日志
  180. --对于二进制日志，应尽可能保存在安全的位置，与数据分开存储  
  181. --使用show binary logs获取二进制日志相关信息
  182. root@localhost[(none)]> help show binary logs;
  183. Name: 'SHOW BINARY LOGS'
  184. Description:
  185. Syntax:
  186. SHOW BINARY LOGS
  187. SHOW MASTER LOGS
  189. Lists the binary log files on the server. This statement is used as
  190. part of the procedure described in [HELP PURGE BINARY LOGS], that shows
  191. how to determine which logs can be purged.
  193. root@localhost[tempdb]> show binary logs;
  194. +---------------+-----------+
  195. | Log_name      | File_size |
  196. +---------------+-----------+
  197. | binlog.000001 |       147 |
  198. | binlog.000002 |       147 |
  199. | binlog.000003 |       147 |
  200. | binlog.000004 |       498 |
  201. +---------------+-----------+
  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 pos] [LIMIT [offset,] row_count]
  211. Shows the events in the binary log. If you do not specify 'log_name',
  212. the first binary log is displayed.
  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. +---------------+-----+-------------+-----------+-------------+---------------------------------------+
  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 find target log
  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 tempdb                                      |
  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 /* xid=25 */                                         |
  236. +---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
  238. root@localhost[tempdb]> show binlog events in 'binlog.000004' from 374;
  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 /* xid=25 */                            |
  244. +---------------+-----+------------+-----------+-------------+------------------------------------------------+
  246. root@localhost[tempdb]> show binlog events in 'binlog.000004' from 374 limit 1;
  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. +---------------+-----+------------+-----------+-------------+------------------------------------------------+
  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 | MASTER } LOGS
  264.     { TO 'log_name' | BEFORE datetime_expr }
  266. Examples:
  267. PURGE BINARY LOGS TO 'mysql-bin.010';
  268. PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
  270. root@localhost[tempdb]> purge binary logs to 'binlog.000003';
  271. Query OK, 0 rows affected (0.12 sec)
  273. root@localhost[tempdb]> show binary logs;
  274. +---------------+-----------+
  275. | Log_name      | File_size |
  276. +---------------+-----------+
  277. | binlog.000003 |       147 |
  278. | binlog.000004 |       498 |
  279. +---------------+-----------+
  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
  286. --使用before子句purge日志，binlog.000003被删除
  287. root@localhost[tempdb]> purge binary logs before '2014-10-03 14:09:56';
  288. Query OK, 0 rows affected (0.02 sec)
  290. root@localhost[tempdb]> show binary logs;
  291. +---------------+-----------+
  292. | Log_name      | File_size |
  293. +---------------+-----------+
  294. | binlog.000004 |       498 |
  295. +---------------+-----------+
  297. --Author: Leshami
  298. --Blog  : http://blog.csdn.net/leshami
  299. --重置所有日志
  300. --reset master将删除在索引文件中列出所有的日志文件并重置索引文件，最后生成一个新的binlog文件。
  301. --该操作之前先备份binlog至其它位置以备以后需要。
  302. root@localhost[tempdb]> help reset master;
  303. Name: 'RESET MASTER'
  304. Description:
  305. Syntax:
  306. RESET MASTER
  308. Deletes all binary log files listed in the index file, resets the
  309. binary log index file to be empty, and creates a new binary log file.
  310. This statement is intended to be used only when the master is started
  311. for the first time.
  313. root@localhost[tempdb]> reset master;
  314. Query OK, 0 rows affected (0.13 sec)
  316. root@localhost[tempdb]> show binary logs;
  317. +---------------+-----------+
  318. | Log_name      | File_size |
  319. +---------------+-----------+
  320. | binlog.000001 |       107 |  --reset之后，从000001开始生成全新空日志
  321. +---------------+-----------+
  323. --expire_log系统变量控制二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。启动时和二进制日志循环时可能删除。
  324. root@localhost[tempdb]> show variables like 'expire_log%';
  325. +------------------+-------+
  326. | Variable_name    | Value |
  327. +------------------+-------+
  328. | expire_logs_days | 0     |
  329. +------------------+-------+
  331. root@localhost[tempdb]> set expire_logs_days=7;   --提示次系统变量为全局变量
  332. ERROR 1229 (HY000): Variable 'expire_logs_days' is a GLOBAL variable and should be set with SET GLOBAL
  333. root@localhost[tempdb]> set global expire_logs_days=7;  --设置
  334. Query OK, 0 rows affected (0.01 sec)
  336. root@localhost[tempdb]> select @@expire_logs_days;
  337. +--------------------+
  338. | @@expire_logs_days |
  339. +--------------------+
  340. |                  7 |
  341. +--------------------+

鹏城DBA总群

发表评论

表情：
评论列表 （有 0 条评论，338人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MySQL 二进制日志bin-log

    二进制日志文件 MySQL 的二进制日志记录的是所有 MySQL 数据库发生变更的信息，对数据库维护有很大作用。 作用 参考 [MySQL DBA 系统学习（

    蔚落蔚落/ 0 赞/ 302 阅读