當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL 二进制日志(Binary Log)

發布時間：2024/4/17 数据库 33 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL 二进制日志(Binary Log) 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

??? 同大多數關系型數據庫一樣，日志文件是MySQL數據庫的重要組成部分。

MySQL有幾種不同的日志文件。通常包括錯誤日志文件，二進制日志，通用日志，慢查詢日志，等等。這些日志能夠幫助我們定位mysqld內部發生的事件。數據庫性能故障，記錄數據的變更歷史。用戶恢復數據庫等等。

二進制日志，也叫binary log。是MySQL Server中最為重要的日志之中的一個，本文主要描寫敘述二進制日志。

1、MySQL日志文件系統的組成
???a、錯誤日志：記錄啟動、運行或停止mysqld時出現的問題。
???b、通用日志：記錄建立的client連接和運行的語句。

???c、更新日志：記錄更改數據的語句。

該日志在MySQL 5.1中已不再使用。
???d、二進制日志：記錄全部更改數據的語句。

還用于復制。
???e、慢查詢日志：記錄全部運行時間超過long_query_time秒的全部查詢或不使用索引的查詢。

???f、Innodb日志：innodb redo log
?
???缺省情況下，全部日志創建于mysqld數據文件夾中。
???能夠通過刷新日志。來強制mysqld來關閉和又一次打開日志文件（或者在某些情況下切換到一個新的日志）。
???當你運行一個FLUSH LOGS語句或運行mysqladmin flush-logs或mysqladmin refresh時，則日志被老化。
???對于存在MySQL復制的情形下。從復制server將維護很多其它日志文件，被稱為接替日志。

2、二進制日志(Binary log)
??a、它包括的內容及作用例如以下：
????包括了全部更新了數據或者已經潛在更新了數據(比方沒有匹配不論什么行的一個DELETE)
????包括關于每一個更新數據庫(DML)的語句的運行時間信息
????不包括沒有改動不論什么數據的語句，假設須要啟用該選項，須要開啟通用日志功能
????主要目的是盡可能的將數據庫恢復到數據庫故障點，由于二進制日志包括備份后進行的全部更新
????用于在主復制server上記錄全部將發送給從server的語句
????啟用該選項數據庫性能減少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、二進制日志相關演示

a、啟用二進制日志 --當前環境 root@localhost[(none)]> show variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.5.39 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.39 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------+root@localhost[(none)]> show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | --該參數用于設定是否啟用二進制日志 | log_bin_trust_function_creators | OFF | | sql_log_bin | ON | +---------------------------------+-------+--下面為binary log相關參數 root@localhost[(none)]> show variables like '%binlog%'; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_stmt_cache_size | 32768 | | innodb_locks_unsafe_for_binlog | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | sync_binlog | 0 | +-----------------------------------------+----------------------+--當前mysqlserver數據文件的缺省位置 root@localhost[(none)]> show variables like '%datadir%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+--停止mysqlserver suse11b:~ # service mysql stop Shutting down MySQL.... done--編輯my.cnf來設定binary log日志位置(注，配置二進制日志路徑及文件名稱后，系統變量log_bin被自己主動置為on) suse11b:~ # vi /etc/my.cnf suse11b:~ # grep -v ^# /etc/my.cnf [mysqld] log-error=/tmp/suse11b.err log_bin=/var/lib/mysql/binarylog/binlog suse11b:~ # mkdir -p /var/lib/mysql/binarylog suse11b:~ # chown -R mysql:mysql /var/lib/mysql/binarylogsuse11b:~ # /etc/init.d/mysql start Starting MySQL.. done suse11b:~ # ls -hltr /var/lib/mysql/binarylog/* -rw-rw---- 1 mysql mysql 39 Oct 3 13:41 /var/lib/mysql/binarylog/binlog.index #索引文件 -rw-rw---- 1 mysql mysql 107 Oct 3 13:41 /var/lib/mysql/binarylog/binlog.000001 #日志文件b、切換日志 suse11b:~ # mysql -uroot -pxxx root@localhost[(none)]> flush logs; Query OK, 0 rows affected (0.04 sec)root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/* -rw-rw---- 1 mysql mysql 78 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.index -rw-rw---- 1 mysql mysql 107 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000002 #切換后產生了000002 -rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001root@localhost[(none)]> system mysqladmin flush-logs #使用mysqladmin命令行工具flush-logs方式切換日志 root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/* -rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001 -rw-rw---- 1 mysql mysql 117 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.index -rw-rw---- 1 mysql mysql 107 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000003 #切換后產生了000003 -rw-rw---- 1 mysql mysql 147 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000002root@localhost[(none)]> system mysqladmin refresh #使用mysqladmin命令行工具refresh方式切換日志 root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/* -rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001 -rw-rw---- 1 mysql mysql 147 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000002 -rw-rw---- 1 mysql mysql 156 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.index -rw-rw---- 1 mysql mysql 107 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000004 #切換后產生了000004 -rw-rw---- 1 mysql mysql 147 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000003 c、模擬產生二進制日志及查看內容 root@localhost[(none)]> create database tempdb; Query OK, 1 row affected (0.00 sec)root@localhost[(none)]> use tempdb Database changed root@localhost[tempdb]> create table tb1(id smallint,val varchar(10)); Query OK, 0 rows affected (0.00 sec)root@localhost[tempdb]> insert into tb1 values(1,'jack'); Query OK, 1 row affected (0.01 sec)root@localhost[tempdb]> system strings /var/lib/mysql/binarylog/binlog.000004 bin?

8.T 5.5.39-log z=.T tempdb create database tempdb tempdb create table tb1(id smallint,val varchar(10)) tempdb BEGIN tempdb insert into tb1 values(1,'jack') root@localhost[tempdb]> system more /var/lib/mysql/binarylog/binlog.index /var/lib/mysql/binarylog/binlog.000001 /var/lib/mysql/binarylog/binlog.000002 /var/lib/mysql/binarylog/binlog.000003 /var/lib/mysql/binarylog/binlog.000004 --使用命令行工具mysqlbinlog直接提取二進制日志的內容 root@localhost[tempdb]> system mysqlbinlog /var/lib/mysql/binarylog/binlog.000004 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #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 # Warning: this binlog is either in use or was not closed properly. BINLOG ' PzguVA8BAAAAZwAAAGsAAAABAAQANS41LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; # at 107 #141003 14:08:58 server id 1 end_log_pos 194 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1412316538/*!*/; SET @@session.pseudo_thread_id=1/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create database tempdb /*!*/; # at 194 #141003 14:09:36 server id 1 end_log_pos 304 Query thread_id=1 exec_time=0 error_code=0 use `tempdb`/*!*/; SET TIMESTAMP=1412316576/*!*/; create table tb1(id smallint,val varchar(10)) /*!*/; # at 304 #141003 14:09:56 server id 1 end_log_pos 374 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1412316596/*!*/; BEGIN /*!*/; # at 374 #141003 14:09:56 server id 1 end_log_pos 471 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1412316596/*!*/; insert into tb1 values(1,'jack') /*!*/; # at 471 #141003 14:09:56 server id 1 end_log_pos 498 Xid = 25 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; --從以上的內容能夠看出二進制日志記錄了全部操作的DML語句及其開銷，以及一些系統環境變量的信息。

d、管理二進制日志 --對于二進制日志，應盡可能保存在安全的位置，與數據分開存儲 --使用show binary logs獲取二進制日志相關信息 root@localhost[(none)]> help show binary logs; Name: 'SHOW BINARY LOGS' Description: Syntax: SHOW BINARY LOGS SHOW MASTER LOGS Lists the binary log files on the server. This statement is used as part of the procedure described in [HELP PURGE BINARY LOGS], that shows how to determine which logs can be purged. root@localhost[tempdb]> show binary logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000001 | 147 | | binlog.000002 | 147 | | binlog.000003 | 147 | | binlog.000004 | 498 | +---------------+-----------+ show binlog events用于在二進制日志中顯示事件。假設未指定'log_name'，則顯示第一個二進制日志。 root@localhost[(none)]> help show binlog events; --獲取幫助信息 Name: 'SHOW BINLOG EVENTS' Description: Syntax: SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] Shows the events in the binary log. If you do not specify 'log_name', the first binary log is displayed. root@localhost[(none)]> show binlog events; +---------------+-----+-------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+-------------+-----------+-------------+---------------------------------------+ | binlog.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.39-log, Binlog ver: 4 | | binlog.000001 | 107 | Rotate | 1 | 147 | binlog.000002;pos=4 | +---------------+-----+-------------+-----------+-------------+---------------------------------------+ root@localhost[(none)]> show binlog events in 'binlog.000005'; --binlog.000005不存在，所以報錯 ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log --以下的這個查詢中。前面運行的DML在這里均能夠看到 root@localhost[tempdb]> show binlog events in 'binlog.000004'; +---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+ | binlog.000004 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.39-log, Binlog ver: 4 | | binlog.000004 | 107 | Query | 1 | 194 | create database tempdb | | binlog.000004 | 194 | Query | 1 | 304 | use `tempdb`; create table tb1(id smallint,val varchar(10)) | | binlog.000004 | 304 | Query | 1 | 374 | BEGIN | | binlog.000004 | 374 | Query | 1 | 471 | use `tempdb`; insert into tb1 values(1,'jack') | | binlog.000004 | 471 | Xid | 1 | 498 | COMMIT /* xid=25 */ | +---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+ root@localhost[tempdb]> show binlog events in 'binlog.000004' from 374; +---------------+-----+------------+-----------+-------------+------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+------------+-----------+-------------+------------------------------------------------+ | binlog.000004 | 374 | Query | 1 | 471 | use `tempdb`; insert into tb1 values(1,'jack') | | binlog.000004 | 471 | Xid | 1 | 498 | COMMIT /* xid=25 */ | +---------------+-----+------------+-----------+-------------+------------------------------------------------+ root@localhost[tempdb]> show binlog events in 'binlog.000004' from 374 limit 1; +---------------+-----+------------+-----------+-------------+------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+------------+-----------+-------------+------------------------------------------------+ | binlog.000004 | 374 | Query | 1 | 471 | use `tempdb`; insert into tb1 values(1,'jack') | +---------------+-----+------------+-----------+-------------+------------------------------------------------+ d、刪除歷史日志 --使用purge手動刪除指定日志 --使用expire-log-days刪除失效日志，設置變量expire_logs_days，刪除超出這個變量保留期之前的全部日志被刪除 --自己主動日志刪除通常發生在server啟動以及日志flush --reset master方式 root@localhost[(none)]> help purge; Name: 'PURGE BINARY LOGS' Description: Syntax: PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr } Examples: PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; root@localhost[tempdb]> purge binary logs to 'binlog.000003'; Query OK, 0 rows affected (0.12 sec) root@localhost[tempdb]> show binary logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000003 | 147 | | binlog.000004 | 498 | +---------------+-----------+ root@localhost[tempdb]> system ls -hltr /var/lib/mysql/binarylog/* -rw-rw---- 1 mysql mysql 147 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000003 -rw-rw---- 1 mysql mysql 498 Oct 3 14:09 /var/lib/mysql/binarylog/binlog.000004 -rw-rw---- 1 mysql mysql 78 Oct 3 14:23 /var/lib/mysql/binarylog/binlog.index --使用before子句purge日志，binlog.000003被刪除 root@localhost[tempdb]> purge binary logs before '2014-10-03 14:09:56'; Query OK, 0 rows affected (0.02 sec) root@localhost[tempdb]> show binary logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000004 | 498 | +---------------+-----------+ --Author: Leshami --Blog : http://blog.csdn.net/leshami --重置全部日志 --reset master將刪除在索引文件里列出全部的日志文件并重置索引文件。最后生成一個新的binlog文件。 --該操作之前先備份binlog至其他位置以備以后須要。 root@localhost[tempdb]> help reset master; Name: 'RESET MASTER' Description: Syntax: RESET MASTER Deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file. This statement is intended to be used only when the master is started for the first time. root@localhost[tempdb]> reset master; Query OK, 0 rows affected (0.13 sec) root@localhost[tempdb]> show binary logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000001 | 107 | --reset之后，從000001開始生成全新空日志 +---------------+-----------+ --expire_log系統變量控制二進制日志自己主動刪除的天數。

默認值為0,表示“沒有自己主動刪除”。

啟動時和二進制日志循環時可能刪除。 root@localhost[tempdb]> show variables like 'expire_log%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 0 | +------------------+-------+ root@localhost[tempdb]> set expire_logs_days=7; --提示次系統變量為全局變量 ERROR 1229 (HY000): Variable 'expire_logs_days' is a GLOBAL variable and should be set with SET GLOBAL root@localhost[tempdb]> set global expire_logs_days=7; --設置 Query OK, 0 rows affected (0.01 sec) root@localhost[tempdb]> select @@expire_logs_days; +--------------------+ | @@expire_logs_days | +--------------------+ | 7 | +--------------------+

轉載于:https://www.cnblogs.com/mfrbuaa/p/5264744.html

總結

以上是生活随笔為你收集整理的MySQL 二进制日志(Binary Log)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： .net面试总结
下一篇： [记录]java.math.bigint