MySQL 二進制日志(Binary Log)

2024-07-24 13:06:23

字體：大中小

來源：轉載

供稿：網友

同大多數關系型數據庫一樣，日志文件是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、二進制日志相關演示

a、啟用二進制日志

--當前環境

root@localhost[(none)]>showvariableslike'%version%';

+-------------------------+------------------------------+

|Variable_name|Value|

+-------------------------+------------------------------+

|innodb_version|5.5.39|

|protocol_version|10|

|slave_type_conversions||

|version|5.5.39|

|version_comment|MySQLCommunityServer(GPL)|

|version_compile_machine|x86_64|

|version_compile_os|Linux|

+-------------------------+------------------------------+

root@localhost[(none)]>showvariableslike'%log_bin%';

+---------------------------------+-------+

|Variable_name|Value|

+---------------------------------+-------+

|log_bin|OFF|--該參數用于設定是否啟用二進制日志

|log_bin_trust_function_creators|OFF|

|sql_log_bin|ON|

+---------------------------------+-------+

--以下為binarylog相關參數

root@localhost[(none)]>showvariableslike'%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|

+-----------------------------------------+----------------------+

--當前mysql服務器數據文件的缺省位置

root@localhost[(none)]>showvariableslike'%datadir%';

+---------------+-----------------+

|Variable_name|Value|

+---------------+-----------------+

|datadir|/var/lib/mysql/|

+---------------+-----------------+

--停止mysql服務器

suse11b:~#servicemysqlstop

ShuttingdownMySQL....done

--編輯my.cnf來設定binarylog日志位置(注，配置二進制日志路徑及文件名后，系統變量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-Rmysql:mysql/var/lib/mysql/binarylog

suse11b:~#/etc/init.d/mysqlstart

StartingMySQL..done

suse11b:~#ls-hltr/var/lib/mysql/binarylog/*

-rw-rw----1mysqlmysql39Oct313:41/var/lib/mysql/binarylog/binlog.index#索引文件

-rw-rw----1mysqlmysql107Oct313:41/var/lib/mysql/binarylog/binlog.000001#日志文件

b、切換日志

suse11b:~#mysql-uroot-pxxx

root@localhost[(none)]>flushlogs;

QueryOK,0rowsaffected(0.04sec)

root@localhost[(none)]>systemls-hltr/var/lib/mysql/binarylog/*

-rw-rw----1mysqlmysql78Oct313:43/var/lib/mysql/binarylog/binlog.index

-rw-rw----1mysqlmysql107Oct313:43/var/lib/mysql/binarylog/binlog.000002#切換后產生了000002

-rw-rw----1mysqlmysql147Oct313:43/var/lib/mysql/binarylog/binlog.000001

root@localhost[(none)]>systemmysqladminflush-logs#使用mysqladmin命令行工具flush-logs方式切換日志

root@localhost[(none)]>systemls-hltr/var/lib/mysql/binarylog/*

-rw-rw----1mysqlmysql147Oct313:43/var/lib/mysql/binarylog/binlog.000001

-rw-rw----1mysqlmysql117Oct313:45/var/lib/mysql/binarylog/binlog.index

-rw-rw----1mysqlmysql107Oct313:45/var/lib/mysql/binarylog/binlog.000003#切換后產生了000003

-rw-rw----1mysqlmysql147Oct313:45/var/lib/mysql/binarylog/binlog.000002

root@localhost[(none)]>systemmysqladminrefresh#使用mysqladmin命令行工具refresh方式切換日志

root@localhost[(none)]>systemls-hltr/var/lib/mysql/binarylog/*

-rw-rw----1mysqlmysql147Oct313:43/var/lib/mysql/binarylog/binlog.000001

-rw-rw----1mysqlmysql147Oct313:45/var/lib/mysql/binarylog/binlog.000002

-rw-rw----1mysqlmysql156Oct313:46/var/lib/mysql/binarylog/binlog.index

-rw-rw----1mysqlmysql107Oct313:46/var/lib/mysql/binarylog/binlog.000004#切換后產生了000004

-rw-rw----1mysqlmysql147Oct313:46/var/lib/mysql/binarylog/binlog.000003

c、模擬產生二進制日志及查看內容

root@localhost[(none)]>createdatabasetempdb;

QueryOK,1rowaffected(0.00sec)

root@localhost[(none)]>usetempdb

Databasechanged

root@localhost[tempdb]>createtabletb1(idsmallint,valvarchar(10));

QueryOK,0rowsaffected(0.00sec)

root@localhost[tempdb]>insertintotb1values(1,'jack');

QueryOK,1rowaffected(0.01sec)

root@localhost[tempdb]>systemstrings/var/lib/mysql/binarylog/binlog.000004

bin?8.T

5.5.39-log

z=.T

tempdb

createdatabasetempdb

tempdb

createtabletb1(idsmallint,valvarchar(10))

tempdb

BEGIN

tempdb

insertintotb1values(1,'jack')

root@localhost[tempdb]>systemmore/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]>systemmysqlbinlog/var/lib/mysql/binarylog/binlog.000004

/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019SET@@session.max_insert_delayed_threads=0*/;

/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER/*!*/;

#at4

#14100313:46:39serverid1end_log_pos107Start:binlogv4,serverv5.5.39-logcreated14100313:46:39

#Warning:thisbinlogiseitherinuseorwasnotclosedproperly.

BINLOG'

PzguVA8BAAAAZwAAAGsAAAABAAQANS41LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/*!*/;

#at107

#14100314:08:58serverid1end_log_pos194Querythread_id=1exec_time=0error_code=0

SETTIMESTAMP=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/*!*/;

/*!/Cutf8*//*!*/;

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/*!*/;

createdatabasetempdb

/*!*/;

#at194

#14100314:09:36serverid1end_log_pos304Querythread_id=1exec_time=0error_code=0

use`tempdb`/*!*/;

SETTIMESTAMP=1412316576/*!*/;

createtabletb1(idsmallint,valvarchar(10))

/*!*/;

#at304

#14100314:09:56serverid1end_log_pos374Querythread_id=1exec_time=0error_code=0

SETTIMESTAMP=1412316596/*!*/;

BEGIN

/*!*/;

#at374

#14100314:09:56serverid1end_log_pos471Querythread_id=1exec_time=0error_code=0

SETTIMESTAMP=1412316596/*!*/;

insertintotb1values(1,'jack')

/*!*/;

#at471

#14100314:09:56serverid1end_log_pos498Xid=25

COMMIT/*!*/;

DELIMITER;

#Endoflogfile

ROLLBACK/*addedbymysqlbinlog*/;

/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

--從以上的內容可以看出二進制日志記錄了所有操作的DML語句及其開銷，以及一些系統環境變量的信息。

d、管理二進制日志

--對于二進制日志，應盡可能保存在安全的位置，與數據分開存儲

--使用showbinarylogs獲取二進制日志相關信息

root@localhost[(none)]>helpshowbinarylogs;

Name:'SHOWBINARYLOGS'

Description:

Syntax:

SHOWBINARYLOGS

SHOWMASTERLOGS

Liststhebinarylogfilesontheserver.Thisstatementisusedas

partoftheproceduredescribedin[HELPPURGEBINARYLOGS],thatshows

howtodeterminewhichlogscanbepurged.

root@localhost[tempdb]>showbinarylogs;

+---------------+-----------+

|Log_name|File_size|

+---------------+-----------+

|binlog.000001|147|

|binlog.000002|147|

|binlog.000003|147|

|binlog.000004|498|

+---------------+-----------+

showbinlogevents用于在二進制日志中顯示事件。如果未指定'log_name'，則顯示第一個二進制日志。

root@localhost[(none)]>helpshowbinlogevents;--獲取幫助信息

Name:'SHOWBINLOGEVENTS'

Description:

Syntax:

SHOWBINLOGEVENTS

[IN'log_name'][FROMpos][LIMIT[offset,]row_count]

Showstheeventsinthebinarylog.Ifyoudonotspecify'log_name',

thefirstbinarylogisdisplayed.

root@localhost[(none)]>showbinlogevents;

+---------------+-----+-------------+-----------+-------------+---------------------------------------+

+---------------+-----+-------------+-----------+-------------+---------------------------------------+

|binlog.000001|4|Format_desc|1|107|Serverver:5.5.39-log,Binlogver:4|

|binlog.000001|107|Rotate|1|147|binlog.000002;pos=4|

+---------------+-----+-------------+-----------+-------------+---------------------------------------+

root@localhost[(none)]>showbinlogeventsin'binlog.000005';--binlog.000005不存在，所以報錯

ERROR1220(HY000):ErrorwhenexecutingcommandSHOWBINLOGEVENTS:Couldnotfindtargetlog

--下面的這個查詢中，前面執行的DML在這里均可以看到

root@localhost[tempdb]>showbinlogeventsin'binlog.000004';

+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+

+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+

|binlog.000004|4|Format_desc|1|107|Serverver:5.5.39-log,Binlogver:4|

|binlog.000004|107|Query|1|194|createdatabasetempdb|

|binlog.000004|194|Query|1|304|use`tempdb`;createtabletb1(idsmallint,valvarchar(10))|

|binlog.000004|304|Query|1|374|BEGIN|

|binlog.000004|374|Query|1|471|use`tempdb`;insertintotb1values(1,'jack')|

|binlog.000004|471|Xid|1|498|COMMIT/*xid=25*/|

+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+

root@localhost[tempdb]>showbinlogeventsin'binlog.000004'from374;

+---------------+-----+------------+-----------+-------------+------------------------------------------------+

+---------------+-----+------------+-----------+-------------+------------------------------------------------+

|binlog.000004|374|Query|1|471|use`tempdb`;insertintotb1values(1,'jack')|

|binlog.000004|471|Xid|1|498|COMMIT/*xid=25*/|

+---------------+-----+------------+-----------+-------------+------------------------------------------------+

root@localhost[tempdb]>showbinlogeventsin'binlog.000004'from374limit1;

+---------------+-----+------------+-----------+-------------+------------------------------------------------+

+---------------+-----+------------+-----------+-------------+------------------------------------------------+

|binlog.000004|374|Query|1|471|use`tempdb`;insertintotb1values(1,'jack')|

+---------------+-----+------------+-----------+-------------+------------------------------------------------+

d、刪除歷史日志

--使用purge手動刪除指定日志

--使用expire-log-days刪除失效日志，設置變量expire_logs_days，刪除超出這個變量保留期之前的所有日志被刪除

--自動日志刪除通常發生在服務器啟動以及日志flush

--resetmaster方式

root@localhost[(none)]>helppurge;

Name:'PURGEBINARYLOGS'

Description:

Syntax:

PURGE{BINARY|MASTER}LOGS

{TO'log_name'|BEFOREdatetime_expr}

Examples:

PURGEBINARYLOGSTO'mysql-bin.010';

PURGEBINARYLOGSBEFORE'2008-04-0222:46:26';

root@localhost[tempdb]>purgebinarylogsto'binlog.000003';

QueryOK,0rowsaffected(0.12sec)

root@localhost[tempdb]>showbinarylogs;

+---------------+-----------+

|Log_name|File_size|

+---------------+-----------+

|binlog.000003|147|

|binlog.000004|498|

+---------------+-----------+

root@localhost[tempdb]>systemls-hltr/var/lib/mysql/binarylog/*

-rw-rw----1mysqlmysql147Oct313:46/var/lib/mysql/binarylog/binlog.000003

-rw-rw----1mysqlmysql498Oct314:09/var/lib/mysql/binarylog/binlog.000004

-rw-rw----1mysqlmysql78Oct314:23/var/lib/mysql/binarylog/binlog.index

--使用before子句purge日志，binlog.000003被刪除

root@localhost[tempdb]>purgebinarylogsbefore'2014-10-0314:09:56';

QueryOK,0rowsaffected(0.02sec)

root@localhost[tempdb]>showbinarylogs;

+---------------+-----------+

|Log_name|File_size|

+---------------+-----------+

|binlog.000004|498|

+---------------+-----------+

--Author:Leshami

--Blog:http://blog.csdn.net/leshami

--重置所有日志

--resetmaster將刪除在索引文件中列出所有的日志文件并重置索引文件，最后生成一個新的binlog文件。

--該操作之前先備份binlog至其它位置以備以后需要。

root@localhost[tempdb]>helpresetmaster;

Name:'RESETMASTER'

Description:

Syntax:

RESETMASTER

Deletesallbinarylogfileslistedintheindexfile,resetsthe

binarylogindexfiletobeempty,andcreatesanewbinarylogfile.

Thisstatementisintendedtobeusedonlywhenthemasterisstarted

forthefirsttime.

root@localhost[tempdb]>resetmaster;

QueryOK,0rowsaffected(0.13sec)

root@localhost[tempdb]>showbinarylogs;

+---------------+-----------+

|Log_name|File_size|

+---------------+-----------+

|binlog.000001|107|--reset之后，從000001開始生成全新空日志

+---------------+-----------+

--expire_log系統變量控制二進制日志自動刪除的天數。默認值為0,表示“沒有自動刪除”。啟動時和二進制日志循環時可能刪除。

root@localhost[tempdb]>showvariableslike'expire_log%';

+------------------+-------+

|Variable_name|Value|

+------------------+-------+

|expire_logs_days|0|

+------------------+-------+

root@localhost[tempdb]>setexpire_logs_days=7;--提示次系統變量為全局變量

ERROR1229(HY000):Variable'expire_logs_days'isaGLOBALvariableandshouldbesetwithSETGLOBAL

root@localhost[tempdb]>setglobalexpire_logs_days=7;--設置

QueryOK,0rowsaffected(0.01sec)

root@localhost[tempdb]>select@@expire_logs_days;

+--------------------+

|@@expire_logs_days|

+--------------------+

|7|

+--------------------+

上一篇：MySQL 用戶與權限管理

下一篇：一千行的MySQL學習筆記匯總

學習交流

硬盤分區如何設置準確的分區空間

硬盤分區如何設置準確的分區空間...

熱門圖片

猜你喜歡的新聞

猜你喜歡的關注

新聞熱點

最牛同桌！我考了696分我同桌考了703分

2024-06-26 22:28:41

650分！高二女生考入北大：遺憾不能上高三

2024-06-26 22:26:16

男生估分600只考了397 媽媽：高考雖重要，但不代表所有

2024-06-26 22:23:01

唐尚珺回應是否會直播帶貨：有人出100萬想和他合作！

2024-06-25 19:29:23

名校搶人名場面：清華、北大太拼了！

2024-06-25 19:22:14

男生高考語文滿分！網友：第一次聽說

2024-06-25 19:19:15

疑難解答

圖片精選

網友關注