前言

官方手冊(cè):https://dev.mysql.com/doc/refman/5.7/en/server-logs.html

不管是哪個(gè)數(shù)據(jù)庫(kù)產(chǎn)品，一定會(huì)有日志文件。在MariaDB/MySQL中，主要有5種日志文件：

1.錯(cuò)誤日志(error log)：記錄mysql服務(wù)的啟停時(shí)正確和錯(cuò)誤的信息，還記錄啟動(dòng)、停止、運(yùn)行過(guò)程中的錯(cuò)誤信息。

2.查詢(xún)?nèi)罩?general log)：記錄建立的客戶(hù)端連接和執(zhí)行的語(yǔ)句。

3.二進(jìn)制日志(bin log)：記錄所有更改數(shù)據(jù)的語(yǔ)句，可用于數(shù)據(jù)復(fù)制。

4.慢查詢(xún)?nèi)罩?slow log)：記錄所有執(zhí)行時(shí)間超過(guò)long_query_time的所有查詢(xún)或不使用索引的查詢(xún)。

5.中繼日志(relay log)：主從復(fù)制時(shí)使用的日志。

除了這5種日志，在需要的時(shí)候還會(huì)創(chuàng)建DDL日志。本文暫先討論錯(cuò)誤日志、一般查詢(xún)?nèi)罩?、慢查?xún)?nèi)罩竞投M(jìn)制日志，中繼日志和主從復(fù)制有關(guān)，將在復(fù)制的章節(jié)中介紹。下一篇文章將介紹innodb事務(wù)日志，見(jiàn)：MySQL的事務(wù)日志。

1.日志刷新操作

以下操作會(huì)刷新日志文件，刷新日志文件時(shí)會(huì)關(guān)閉舊的日志文件并重新打開(kāi)日志文件。對(duì)于有些日志類(lèi)型，如二進(jìn)制日志，刷新日志會(huì)滾動(dòng)日志文件，而不僅僅是關(guān)閉并重新打開(kāi)。

mysql> FLUSH LOGS;
shell> mysqladmin flush-logs
shell> mysqladmin refresh

2.錯(cuò)誤日志

錯(cuò)誤日志是最重要的日志之一，它記錄了MariaDB/MySQL服務(wù)啟動(dòng)和停止正確和錯(cuò)誤的信息，還記錄了mysqld實(shí)例運(yùn)行過(guò)程中發(fā)生的錯(cuò)誤事件信息。

可以使用"--log-error=[file_name]"來(lái)指定mysqld記錄的錯(cuò)誤日志文件，如果沒(méi)有指定file_name，則默認(rèn)的錯(cuò)誤日志文件為datadir目錄下的`hostname`.err，hostname表示當(dāng)前的主機(jī)名。

也可以在MariaDB/MySQL配置文件中的mysqld配置部分，使用log-error指定錯(cuò)誤日志的路徑。

如果不知道錯(cuò)誤日志的位置，可以查看變量log_error來(lái)查看。

mysql> show variables like 'log_error';
+---------------+----------------------------------------+
| Variable_name | Value                                  |
+---------------+----------------------------------------+
| log_error     | /var/lib/mysql/node1.longshuai.com.err |
+---------------+----------------------------------------+

在MySQL 5.5.7之前，刷新日志操作(如flush logs)會(huì)備份舊的錯(cuò)誤日志(以_old結(jié)尾)，并創(chuàng)建一個(gè)新的錯(cuò)誤日志文件并打開(kāi)，在MySQL 5.5.7之后，執(zhí)行刷新日志的操作時(shí)，錯(cuò)誤日志會(huì)關(guān)閉并重新打開(kāi)，如果錯(cuò)誤日志不存在，則會(huì)先創(chuàng)建。

在MariaDB/MySQL正在運(yùn)行狀態(tài)下刪除錯(cuò)誤日志后，不會(huì)自動(dòng)創(chuàng)建錯(cuò)誤日志，只有在刷新日志的時(shí)候才會(huì)創(chuàng)建一個(gè)新的錯(cuò)誤日志文件。

以下是MySQL 5.6.35啟動(dòng)的日志信息。

2017-03-29 01:15:14 2362 [Note] Plugin 'FEDERATED' is disabled.
2017-03-29 01:15:14 2362 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-03-29 01:15:14 2362 [Note] InnoDB: The InnoDB memory heap is disabled
2017-03-29 01:15:14 2362 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-03-29 01:15:14 2362 [Note] InnoDB: Memory barrier is not used
2017-03-29 01:15:14 2362 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-29 01:15:14 2362 [Note] InnoDB: Using Linux native AIO
2017-03-29 01:15:14 2362 [Note] InnoDB: Using CPU crc32 instructions
2017-03-29 01:15:14 2362 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-03-29 01:15:14 2362 [Note] InnoDB: Completed initialization of buffer pool
2017-03-29 01:15:14 2362 [Note] InnoDB: Highest supported file format is Barracuda.
2017-03-29 01:15:14 2362 [Note] InnoDB: 128 rollback segment(s) are active.
2017-03-29 01:15:14 2362 [Note] InnoDB: Waiting for purge to start
2017-03-29 01:15:14 2362 [Note] InnoDB: 5.6.35 started; log sequence number 3911610
2017-03-29 01:15:14 2362 [Note] Server hostname (bind-address): '*'; port: 3306
2017-03-29 01:15:14 2362 [Note] IPv6 is available.
2017-03-29 01:15:14 2362 [Note]   - '::' resolves to '::';
2017-03-29 01:15:14 2362 [Note] Server socket created on IP: '::'.
2017-03-29 01:15:14 2362 [Warning] 'proxies_priv' entry '@ root@xuexi.longshuai.com' ignored in --skip-name-resolve mode.
2017-03-29 01:15:14 2362 [Note] Event Scheduler: Loaded 0 events
2017-03-29 01:15:14 2362 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.35'  socket: '/mydata/data/mysql.sock'  port: 3306  MySQL Community Server (GPL)

3.一般查詢(xún)?nèi)罩?/h2>

查詢(xún)?nèi)罩痉譃橐话悴樵?xún)?nèi)罩竞吐樵?xún)?nèi)罩?，它們是通過(guò)查詢(xún)是否超出變量long_query_time指定時(shí)間的值來(lái)判定的。在超時(shí)時(shí)間內(nèi)完成的查詢(xún)是一般查詢(xún)，可以將其記錄到一般查詢(xún)?nèi)罩局校墙ㄗh關(guān)閉這種日志（默認(rèn)是關(guān)閉的），超出時(shí)間的查詢(xún)是慢查詢(xún)，可以將其記錄到慢查詢(xún)?nèi)罩局小?/p>

使用"--general_log={0|1}"來(lái)決定是否啟用一般查詢(xún)?nèi)罩?，使?quot;--general_log_file=file_name"來(lái)指定查詢(xún)?nèi)罩镜穆窂健２唤o定路徑時(shí)默認(rèn)的文件名以`hostname`.log命名。

和查詢(xún)?nèi)罩居嘘P(guān)的變量有：

long_query_time = 10 # 指定慢查詢(xún)超時(shí)時(shí)長(zhǎng)，超出此時(shí)長(zhǎng)的屬于慢查詢(xún)，會(huì)記錄到慢查詢(xún)?nèi)罩局?
log_output={TABLE|FILE|NONE}  # 定義一般查詢(xún)?nèi)罩竞吐樵?xún)?nèi)罩镜妮敵龈袷剑恢付〞r(shí)默認(rèn)為file

TABLE表示記錄日志到表中，F(xiàn)ILE表示記錄日志到文件中，NONE表示不記錄日志。只要這里指定為NONE，即使開(kāi)啟了一般查詢(xún)?nèi)罩竞吐樵?xún)?nèi)罩?，也都不?huì)有任何記錄。

和一般查詢(xún)?nèi)罩鞠嚓P(guān)的變量有：

general_log=off # 是否啟用一般查詢(xún)?nèi)罩?，為全局變量，必須在global上修改。
sql_log_off=off # 在session級(jí)別控制是否啟用一般查詢(xún)?nèi)罩?，默認(rèn)為off，即啟用
general_log_file=/mydata/data/hostname.log  # 默認(rèn)是庫(kù)文件路徑下主機(jī)名加上.log

在MySQL 5.6以前的版本還有一個(gè)"log"變量也是決定是否開(kāi)啟一般查詢(xún)?nèi)罩镜?。?.6版本開(kāi)始已經(jīng)廢棄了該選項(xiàng)。

默認(rèn)沒(méi)有開(kāi)啟一般查詢(xún)?nèi)罩荆膊唤ㄗh開(kāi)啟一般查詢(xún)?nèi)罩?。此處打開(kāi)該類(lèi)型的日志，看看是如何記錄一般查詢(xún)?nèi)罩镜摹?/p>

首先開(kāi)啟一般查詢(xún)?nèi)罩尽?/p>

mysql> set @@global.general_log=1;

[root@xuexi data]# ll *.log
-rw-rw---- 1 mysql mysql 5423 Mar 20 16:29 mysqld.log
-rw-rw---- 1 mysql mysql  262 Mar 29 09:31 xuexi.log

執(zhí)行幾個(gè)語(yǔ)句。

mysql> select host,user from mysql.user;
mysql> show variables like "%error%";
mysql> insert into ttt values(233);
mysql> create table tt(id int);
mysql> set @a:=3;

查看一般查詢(xún)?nèi)罩镜膬?nèi)容。

[root@xuexi data]# cat xuexi.log 
/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /mydata/data/mysql.sock
Time                Id Command    Argument
180421 20:04:41     13 Query      select user,host from mysql.user
180421 20:06:06     13 Query      show variables like "%error%"
180421 20:07:28     13 Query      insert into ttt values(233)
180421 20:11:47     13 Query      create table tt(id int)
180421 20:12:29     13 Query      set @a:=3

由此可知，一般查詢(xún)?nèi)罩静樵?xún)的不止是select語(yǔ)句，幾乎所有的語(yǔ)句都會(huì)記錄。

4.慢查詢(xún)?nèi)罩?/h2>

查詢(xún)超出變量long_query_time指定時(shí)間值的為慢查詢(xún)。但是查詢(xún)獲取鎖(包括鎖等待)的時(shí)間不計(jì)入查詢(xún)時(shí)間內(nèi)。

mysql記錄慢查詢(xún)?nèi)罩臼窃诓樵?xún)執(zhí)行完畢且已經(jīng)完全釋放鎖之后才記錄的，因此慢查詢(xún)?nèi)罩居涗浀捻樞蚝蛨?zhí)行的SQL查詢(xún)語(yǔ)句順序可能會(huì)不一致(例如語(yǔ)句1先執(zhí)行，查詢(xún)速度慢，語(yǔ)句2后執(zhí)行，但查詢(xún)速度快，則語(yǔ)句2先記錄)。

注意，MySQL 5.1之后就支持微秒級(jí)的慢查詢(xún)超時(shí)時(shí)長(zhǎng)，對(duì)于DBA來(lái)說(shuō)，一個(gè)查詢(xún)運(yùn)行0.5秒和運(yùn)行0.05秒是非常不同的，前者可能索引使用錯(cuò)誤或者走了表掃描，后者可能索引使用正確。

另外，指定的慢查詢(xún)超時(shí)時(shí)長(zhǎng)表示的是超出這個(gè)時(shí)間的才算是慢查詢(xún)，等于這個(gè)時(shí)間的不會(huì)記錄。

和慢查詢(xún)有關(guān)的變量：

long_query_time=10 # 指定慢查詢(xún)超時(shí)時(shí)長(zhǎng)(默認(rèn)10秒)，超出此時(shí)長(zhǎng)的屬于慢查詢(xún)
log_output={TABLE|FILE|NONE} # 定義一般查詢(xún)?nèi)罩竞吐樵?xún)?nèi)罩镜妮敵龈袷?，默認(rèn)為file
log_slow_queries={yes|no}    # 是否啟用慢查詢(xún)?nèi)罩?，默認(rèn)不啟用
slow_query_log={1|ON|0|OFF}  # 也是是否啟用慢查詢(xún)?nèi)罩荆俗兞亢蚻og_slow_queries修改一個(gè)另一個(gè)同時(shí)變化
slow_query_log_file=/mydata/data/hostname-slow.log  #默認(rèn)路徑為庫(kù)文件目錄下主機(jī)名加上-slow.log
log_queries_not_using_indexes=OFF # 查詢(xún)沒(méi)有使用索引的時(shí)候是否也記入慢查詢(xún)?nèi)罩?/pre>

現(xiàn)在啟用慢查詢(xún)?nèi)罩尽?/p>

mysql> set @@global.slow_query_log=on;

因?yàn)槟J(rèn)超時(shí)時(shí)長(zhǎng)為10秒，所以進(jìn)行一個(gè)10秒的查詢(xún)。

mysql> select sleep(10);

查看慢查詢(xún)?nèi)罩疚募?。這里看到雖然sleep了10秒，但是最后查詢(xún)時(shí)間超出了847微秒，因此這里也記錄了該查詢(xún)。

[root@xuexi data]# cat xuexi-slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /mydata/data/mysql.sock
Time                 Id Command    Argument
# Time: 170329  9:55:58
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 10.000847  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1490752558;
select sleep(10);

隨著時(shí)間的推移，慢查詢(xún)?nèi)罩疚募械挠涗浛赡軙?huì)變得非常多，這對(duì)于分析查詢(xún)來(lái)說(shuō)是非常困難的。好在提供了一個(gè)專(zhuān)門(mén)歸類(lèi)慢查詢(xún)?nèi)罩镜墓ぞ適ysqldumpslow。

[root@xuexi data]# mysqldumpslow --help
  -d           debug 
  -v           verbose：顯示詳細(xì)信息
  -t NUM       just show the top n queries：僅顯示前n條查詢(xún)
  -a           don't abstract all numbers to N and strings to 'S'：歸類(lèi)時(shí)不要使用N替換數(shù)字，S替換字符串
  -g PATTERN   grep: only consider stmts that include this string：通過(guò)grep來(lái)篩選select語(yǔ)句。

該工具歸類(lèi)的時(shí)候，默認(rèn)會(huì)將同文本但變量值不同的查詢(xún)語(yǔ)句視為同一類(lèi)，并使用N代替其中的數(shù)值變量，使用S代替其中的字符串變量。可以使用-a來(lái)禁用這種替換。如：

[root@xuexi data]# mysqldumpslow xuexi-slow.log 
Reading mysql slow query log from xuexi-slow.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select sleep(N)

[root@xuexi data]#  mysqldumpslow -a xuexi-slow.log   
Reading mysql slow query log from xuexi-slow.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select sleep(10)

顯然，這里歸類(lèi)后的結(jié)果只是精確到0.01秒的，如果想要顯示及其精確的秒數(shù)，則使用-d選項(xiàng)啟用調(diào)試功能。

[root@xuexi data]#  mysqldumpslow -d xuexi-slow.log   
Reading mysql slow query log from xuexi-slow.log
[[/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /mydata/data/mysql.sock
Time                 Id Command    Argument
# Time: 170329  9:55:58
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 10.000847  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1490752558;
select sleep(10);
]]
<<>>
<<# Time: 170329  9:55:58
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 10.000847  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1490752558;
select sleep(10);
>> at /usr/local/mysql/bin/mysqldumpslow line 97, <> chunk 1.
[[# Time: 170329  9:55:58
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 10.000847  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1490752558;
select sleep(10);
]]
{{  select sleep(N)}}

Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select sleep(N)

慢查詢(xún)?cè)赟QL語(yǔ)句調(diào)優(yōu)的時(shí)候非常有用，應(yīng)該將它啟用起來(lái)，且應(yīng)該讓慢查詢(xún)閾值盡量小，例如1秒甚至低于1秒。就像一天執(zhí)行上千次的1秒語(yǔ)句，和一天執(zhí)行幾次的20秒語(yǔ)句，顯然更值得去優(yōu)化這個(gè)1秒的語(yǔ)句。

5.二進(jìn)制日志

5.1 二進(jìn)制日志文件

二進(jìn)制日志包含了引起或可能引起數(shù)據(jù)庫(kù)改變(如delete語(yǔ)句但沒(méi)有匹配行)的事件信息，但絕不會(huì)包括select和show這樣的查詢(xún)語(yǔ)句。語(yǔ)句以"事件"的形式保存，所以包含了時(shí)間、事件開(kāi)始和結(jié)束位置等信息。

二進(jìn)制日志是以事件形式記錄的，不是事務(wù)日志(但可能是基于事務(wù)來(lái)記錄二進(jìn)制日志)，不代表它只記錄innodb日志，myisam表也一樣有二進(jìn)制日志。

對(duì)于事務(wù)表的操作，二進(jìn)制日志只在事務(wù)提交的時(shí)候一次性寫(xiě)入(基于事務(wù)的innodb二進(jìn)制日志)，提交前的每個(gè)二進(jìn)制日志記錄都先cache，提交時(shí)寫(xiě)入。

所以，對(duì)于事務(wù)表來(lái)說(shuō)，一個(gè)事務(wù)中可能包含多條二進(jìn)制日志事件，它們會(huì)在提交時(shí)一次性寫(xiě)入。而對(duì)于非事務(wù)表的操作，每次執(zhí)行完語(yǔ)句就直接寫(xiě)入。

MariaDB/MySQL默認(rèn)沒(méi)有啟動(dòng)二進(jìn)制日志，要啟用二進(jìn)制日志使用--log-bin=[on|off|file_name]選項(xiàng)指定，如果沒(méi)有給定file_name，則默認(rèn)為datadir下的主機(jī)名加"-bin"，并在后面跟上一串?dāng)?shù)字表示日志序列號(hào)，如果給定的日志文件中包含了后綴(logname.suffix)將忽略后綴部分。

或者在配置文件中的[mysqld]部分設(shè)置log-bin也可以。注意：對(duì)于mysql 5.7，直接啟動(dòng)binlog可能會(huì)導(dǎo)致mysql服務(wù)啟動(dòng)失敗，這時(shí)需要在配置文件中的mysqld為mysql實(shí)例分配server_id。

[mysqld]
# server_id=1234
log-bin=[on|filename]

mysqld還創(chuàng)建一個(gè)二進(jìn)制日志索引文件，當(dāng)二進(jìn)制日志文件滾動(dòng)的時(shí)候會(huì)向該文件中寫(xiě)入對(duì)應(yīng)的信息。所以該文件包含所有使用的二進(jìn)制日志文件的文件名。默認(rèn)情況下該文件與二進(jìn)制日志文件的文件名相同，擴(kuò)展名為'.index'。要指定該文件的文件名使用--log-bin-index[=file_name]選項(xiàng)。當(dāng)mysqld在運(yùn)行時(shí)不應(yīng)手動(dòng)編輯該文件，免得mysqld變得混亂。

當(dāng)重啟mysql服務(wù)或刷新日志或者達(dá)到日志最大值時(shí)，將滾動(dòng)二進(jìn)制日志文件，滾動(dòng)日志時(shí)只修改日志文件名的數(shù)字序列部分。

二進(jìn)制日志文件的最大值通過(guò)變量max_binlog_size設(shè)置(默認(rèn)值為1G)。但由于二進(jìn)制日志可能是基于事務(wù)來(lái)記錄的(如innodb表類(lèi)型)，而事務(wù)是絕對(duì)不可能也不應(yīng)該跨文件記錄的，如果正好二進(jìn)制日志文件達(dá)到了最大值但事務(wù)還沒(méi)有提交則不會(huì)滾動(dòng)日志，而是繼續(xù)增大日志，所以max_binlog_size指定的值和實(shí)際的二進(jìn)制日志大小不一定相等。

因?yàn)槎M(jìn)制日志文件增長(zhǎng)迅速，但官方說(shuō)明因此而損耗的性能小于1%，且二進(jìn)制目的是為了恢復(fù)定點(diǎn)數(shù)據(jù)庫(kù)和主從復(fù)制，所以出于安全和功能考慮，極不建議將二進(jìn)制日志和datadir放在同一磁盤(pán)上。

5.2 查看二進(jìn)制日志

MySQL中查看二進(jìn)制日志的方法主要有幾種。

1.使用mysqlbinlog工具。

2.使用show顯示對(duì)應(yīng)的信息。

SHOW {BINARY | MASTER} LOGS      # 查看使用了哪些日志文件
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos]   # 查看日志中進(jìn)行了哪些操作
SHOW MASTER STATUS         # 顯式主服務(wù)器中的二進(jìn)制日志信息

5.2.1 mysqlbinlog

二進(jìn)制日志可以使用mysqlbinlog命令查看。

mysqlbinlog [option] log-file1 log-file2...

以下是常用的幾個(gè)選項(xiàng)：

-d,--database=name：只查看指定數(shù)據(jù)庫(kù)的日志操作

-o,--offset=#：忽略掉日志中的前n個(gè)操作命令

-r,--result-file=name：將輸出的日志信息輸出到指定的文件中，使用重定向也一樣可以。

-s,--short-form：顯示簡(jiǎn)單格式的日志，只記錄一些普通的語(yǔ)句，會(huì)省略掉一些額外的信息如位置信息和時(shí)間信息以及基于行的日志。可以用來(lái)調(diào)試，生產(chǎn)環(huán)境千萬(wàn)不可使用

--set-charset=char_name：在輸出日志信息到文件中時(shí)，在文件第一行加上set names char_name

--start-datetime,--stop-datetime：指定輸出開(kāi)始時(shí)間和結(jié)束時(shí)間內(nèi)的所有日志信息

--start-position=#,--stop-position=#：指定輸出開(kāi)始位置和結(jié)束位置內(nèi)的所有日志信息

-v,-vv：顯示更詳細(xì)信息，基于row的日志默認(rèn)不會(huì)顯示出來(lái)，此時(shí)使用-v或-vv可以查看

在進(jìn)行測(cè)試之前，先對(duì)日志進(jìn)行一次刷新，以方便解釋二進(jìn)制日志的信息。

shell> mysqladmin -uroot -p refresh

假設(shè)現(xiàn)在的日志文件是mysql-bin.000001，里面暫時(shí)只有一些初始信息，沒(méi)有記錄任何操作過(guò)的記錄。

下面是每個(gè)二進(jìn)制日志文件的初始信息。可以看到記錄了時(shí)間和位置信息(at 4)。

[root@xuexi data]# mysqlbinlog mysql-bin.000001 
/*!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
#170329  2:18:10 server id 1  end_log_pos 120 CRC32 0x40f62523  Start: binlog v 4, server v 5.6.35-log created 170329  2:18:10 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
4qjaWA8BAAAAdAAAAHgAAAABAAQANS42LjM1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADiqNpYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAASMl
9kA=
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

現(xiàn)在在數(shù)據(jù)庫(kù)中執(zhí)行下面的操作：

use test;
create table student(studentid int not null primary key,name varchar(30) not null,gender enum('female','mail'));
alter table student change gender gender enum('female','male');
insert into student values(1,'malongshuai','male'),(2,'gaoxiaofang','female');

再查看二進(jìn)制日志信息。

[root@xuexi data]# mysqlbinlog mysql-bin.000001 
/*!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
#170329  2:18:10 server id 1  end_log_pos 120 CRC32 0x40f62523  Start: binlog v 4, server v 5.6.35-log created 170329  2:18:10 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
4qjaWA8BAAAAdAAAAHgAAAABAAQANS42LjM1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADiqNpYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAASMl
9kA=
'/*!*/;
# at 120
#170329  5:20:00 server id 1  end_log_pos 305 CRC32 0xbac43912  Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1490736000/*!*/;
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=1075838976/*!*/;
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 table student(studentid int not null primary key,name varchar(30) not null,gender enum('female','mail'))
/*!*/;
# at 305
#170329  5:21:21 server id 1  end_log_pos 441 CRC32 0xde67f702  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1490736081/*!*/;
alter table student change gender gender enum('female','male')
/*!*/;
# at 441
#170329  5:21:33 server id 1  end_log_pos 520 CRC32 0x05a9c5a1  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1490736093/*!*/;
BEGIN
/*!*/;
# at 520
#170329  5:21:33 server id 1  end_log_pos 671 CRC32 0xad9e7dc8  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1490736093/*!*/;
insert into student values(1,'malongshuai','male'),(2,'gaoxiaofang','female')
/*!*/;
# at 671
#170329  5:21:33 server id 1  end_log_pos 702 CRC32 0xb69b0f7d  Xid = 32
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 

將上述信息整理為下圖：其中timestamp記錄的是從1970-01-01到現(xiàn)在的總秒數(shù)時(shí)間戳，可以使用date -d '@1490736093'轉(zhuǎn)換。

• 位置0-120記錄的是二進(jìn)制日志的一些固定信息。
• 位置120-305記錄的是use和create table語(yǔ)句，語(yǔ)句的記錄時(shí)間為5:20:00。但注意，這里的use不是執(zhí)行的use語(yǔ)句，而是MySQL發(fā)現(xiàn)要操作的數(shù)據(jù)庫(kù)為test，而自動(dòng)進(jìn)行的操作并記錄下來(lái)。人為的use語(yǔ)句是不會(huì)記錄的。
• 位置305-441記錄的是alter table語(yǔ)句，語(yǔ)句的記錄時(shí)間為5:20:21。
• 位置441-702記錄的是insert操作，因?yàn)樵摬僮魇荄ML語(yǔ)句，因此記錄了事務(wù)的開(kāi)始BEGIN和提交COMMIT。
  • begin的起止位置為441-520；
  • insert into語(yǔ)句的起止位置為520-671，記錄的時(shí)間和自動(dòng)開(kāi)啟事務(wù)的begin時(shí)間是一樣的；
  • commit的起止位置為671-702。

使用-r命令將日志文件導(dǎo)入到指定文件中，使用重定向也可以實(shí)現(xiàn)同樣的結(jié)果。并使用-s查看簡(jiǎn)化的日志文件。

[root@xuexi data]# mysqlbinlog mysql-bin.000001 -r /tmp/binlog.000001
[root@xuexi data]# mysqlbinlog mysql-bin.000001 -s>/tmp/binlog.sample

比較這兩個(gè)文件，看看簡(jiǎn)化的日志文件簡(jiǎn)化了哪些東西。

從上圖中可以看出，使用-s后，少了基于行的日志信息，也少了記錄的位置和時(shí)間信息。

使用-o可以忽略前N個(gè)條目，例如上面的操作涉及了6個(gè)操作。忽略掉前3個(gè)后的日志顯示如下：可以看到直接從位置441開(kāi)始顯示了。

[root@xuexi data]# mysqlbinlog mysql-bin.000001 -o 3
...前面固定部分省略...

'/*!*/;
# at 441
#170329  5:21:33 server id 1  end_log_pos 520 CRC32 0x05a9c5a1  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1490736093/*!*/;
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=1075838976/*!*/;
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/*!*/;
BEGIN
/*!*/;
# at 520
#170329  5:21:33 server id 1  end_log_pos 671 CRC32 0xad9e7dc8  Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1490736093/*!*/;
insert into student values(1,'malongshuai','male'),(2,'gaoxiaofang','female')
/*!*/;
# at 671
#170329  5:21:33 server id 1  end_log_pos 702 CRC32 0xb69b0f7d  Xid = 32
COMMIT/*!*/;
DELIMITER ;

...后面固定部分省略... 

使用-d可以只顯示指定數(shù)據(jù)庫(kù)相關(guān)的操作。例如先切換到其他數(shù)據(jù)庫(kù)進(jìn)行一番操作，然后再使用-d查看日志。

[root@xuexi data]# mysqlbinlog mysql-bin.000001 --start-datetime='2017-03-28 00:00:01' --stop-datetime='2017-03-29 05:21:23'
...前面固定部分省略...
'/*!*/;
# at 120
#170329  5:20:00 server id 1  end_log_pos 305 CRC32 0xbac43912  Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1490736000/*!*/;
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=1075838976/*!*/;
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 table student(studentid int not null primary key,name varchar(30) not null,gender enum('female','mail'))
/*!*/;
# at 305
#170329  5:21:21 server id 1  end_log_pos 441 CRC32 0xde67f702  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1490736081/*!*/;
alter table student change gender gender enum('female','male')
/*!*/;
DELIMITER ;
...后面固定部分省略...

可以看到，除了指定的mysql數(shù)據(jù)庫(kù)的信息輸出了，還非常簡(jiǎn)化的輸出了其他數(shù)據(jù)庫(kù)的信息。

mysqlbinlog最有用的兩個(gè)選項(xiàng)就是指定時(shí)間和位置來(lái)輸出日志。

指定時(shí)間時(shí)，將輸出指定時(shí)間范圍內(nèi)的日志。指定的時(shí)間可以不和日志中記錄的日志相同。

[root@xuexi data]# mysqlbinlog mysql-bin.000001 --start-position=150 --stop-position=441
...前面固定部分省略...
'/*!*/;
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 4202496, event_type: 0
...后面固定部分省略... 

[root@xuexi data]# mysqlbinlog mysql-bin.000001 --start-position=305 --stop-position=500
...前面固定部分省略... 
'/*!*/;
# at 305
#170329  5:21:21 server id 1  end_log_pos 441 CRC32 0xde67f702  Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1490736081/*!*/;
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=1075838976/*!*/;
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/*!*/;
alter table student change gender gender enum('female','male')
/*!*/;
# at 441
#170329  5:21:33 server id 1  end_log_pos 520 CRC32 0x05a9c5a1  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1490736093/*!*/;
BEGIN
/*!*/;
DELIMITER ;
...后面固定部分省略...

同理指定位置也一樣，但是指定位置時(shí)有個(gè)要求是如果指定起始位置，則必須指定日志文件中明確的起始位置。例如，日志文件中有位置120、305、441，可以指定起始和結(jié)束位置為120、500，但是不可以指定起止位置為150、500，因?yàn)槿罩疚募胁淮嬖?50這個(gè)位置。

[root@xuexi data]# mysqlbinlog mysql-bin.000001 --start-position=150 --stop-position=441
...前面固定部分省略...
'/*!*/;
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 4202496, event_type: 0
...后面固定部分省略... 

[root@xuexi data]# mysqlbinlog mysql-bin.000001 --start-position=305 --stop-position=500
...前面固定部分省略... 
'/*!*/;
# at 305
#170329  5:21:21 server id 1  end_log_pos 441 CRC32 0xde67f702  Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1490736081/*!*/;
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=1075838976/*!*/;
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/*!*/;
alter table student change gender gender enum('female','male')
/*!*/;
# at 441
#170329  5:21:33 server id 1  end_log_pos 520 CRC32 0x05a9c5a1  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1490736093/*!*/;
BEGIN
/*!*/;
DELIMITER ;
...后面固定部分省略...

5.2.2 show binary logs

該語(yǔ)句用于查看當(dāng)前使用了哪些二進(jìn)制日志文件。

可以通過(guò)查看二進(jìn)制的index文件來(lái)查看當(dāng)前正在使用哪些二進(jìn)制日志。

[root@xuexi data]# cat mysql-bin.index 
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006

也可以在mysql環(huán)境中使用show {binary | master} logs來(lái)查看。binary和master是同義詞。

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |       167 |
| mysql-bin.000004 |       785 |
| mysql-bin.000005 |      1153 |
| mysql-bin.000006 |       602 |
+------------------+-----------

5.2.3 show binlog events

該語(yǔ)句用于查看日志中進(jìn)行了哪些操作。

mysql> show binlog events in 'mysql-bin.000005';

可以指定起始位置。同樣，起始位置必須指定正確，不能指定不存在的位置。

mysql> show binlog events in 'mysql-bin.000005' from 961;
+------------------+------+------------+-----------+-------------+--------------------------------+
| Log_name         | Pos  | Event_type | Server_id | End_log_pos | Info                           |
+------------------+------+------------+-----------+-------------+--------------------------------+
| mysql-bin.000005 |  961 | Table_map  |         1 |        1019 | table_id: 98 (test.student)    |
| mysql-bin.000005 | 1019 | Write_rows |         1 |        1075 | table_id: 98 flags: STMT_END_F |
| mysql-bin.000005 | 1075 | Xid        |         1 |        1106 | COMMIT /* xid=129 */           |
| mysql-bin.000005 | 1106 | Rotate     |         1 |        1153 | mysql-bin.000006;pos=4         |
+------------------+------+------------+-----------+-------------+--------------------------------+ 

5.2.4 show master status

該語(yǔ)句用于顯示主服務(wù)器中的二進(jìn)制日志信息。如果是主從結(jié)構(gòu)，它只會(huì)顯示主從結(jié)構(gòu)中主服務(wù)器的二進(jìn)制日志信息。

mysql> show master status;    
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      602 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

可以查看到當(dāng)前正在使用的日志及下一事件記錄的開(kāi)始位置，還能查看到哪些數(shù)據(jù)庫(kù)需要記錄二進(jìn)制日志，哪些數(shù)據(jù)庫(kù)不記錄二進(jìn)制日志。

5.3 刪除二進(jìn)制日志

刪除二進(jìn)制日志有幾種方法。不管哪種方法，都會(huì)將刪除后的信息同步到二進(jìn)制index文件中。

1.reset master將會(huì)刪除所有日志，并讓日志文件重新從000001開(kāi)始。

mysql> reset master;

2.PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

purge master logs to "binlog_name.00000X"將會(huì)清空00000X之前的所有日志文件。例如刪除000006之前的日志文件。

mysql> purge master logs to "mysql-bin.000006";
mysql> purge binary logs to "mysql-bin.000006";

master和binary是同義詞

purge master logs before 'yyyy-mm-dd hh:mi:ss'將會(huì)刪除指定日期之前的所有日志。但是若指定的時(shí)間處在正在使用中的日志文件中，將無(wú)法進(jìn)行purge。

mysql> purge master logs before '2017-03-29 07:36:40';

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1868 | file ./mysql-bin.000003 was not purged because it is the active log file. |
+---------+------+---------------------------------------------------------------------------+

3.使用--expire_logs_days=N選項(xiàng)指定過(guò)了多少天日志自動(dòng)過(guò)期清空。

5.4 二進(jìn)制日志的記錄格式

在MySQL 5.1之前，MySQL只有一種基于語(yǔ)句statement形式的日志記錄格式。即將所有的相關(guān)操作記錄為SQL語(yǔ)句形式。但是這樣的記錄方式對(duì)某些特殊信息無(wú)法同步記錄，例如uuid，now()等這樣動(dòng)態(tài)變化的值。

從MySQL 5.1開(kāi)始，MySQL支持statement、row、mixed三種形式的記錄方式。row形式是基于行來(lái)記錄，也就是將相關(guān)行的每一列的值都在日志中保存下來(lái)，這樣的結(jié)果會(huì)導(dǎo)致日志文件變得非常大，但是保證了動(dòng)態(tài)值的確定性。還有一種mixed形式，表示如何記錄日志由MySQL自己來(lái)決定。

日志的記錄格式由變量binlog_format來(lái)指定。其值有：row,statement,mixed。innodb引擎的創(chuàng)始人之一在博客上推薦使用row格式。

下面將記錄格式改為row。

mysql> alter table student add birthday datetime default  now();
mysql> flush logs;
mysql> set binlog_format='row';
mysql> insert into student values(7,'xiaowoniu','female',now()); 

查看產(chǎn)生的日志。

[root@xuexi data]# mysqlbinlog mysql-bin.000005
...前面固定部分省略...
'/*!*/;
# at 120
#170329  8:06:24 server id 1  end_log_pos 200 CRC32 0x0ac02649  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1490745984/*!*/;
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=1075838976/*!*/;
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.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 200
#170329  8:06:24 server id 1  end_log_pos 258 CRC32 0xb8cdfd09  Table_map: `test`.`student` mapped to number 94
# at 258
#170329  8:06:24 server id 1  end_log_pos 314 CRC32 0x8ce6f72c  Write_rows: table id 94 flags: STMT_END_F

BINLOG '
gPraWBMBAAAAOgAAAAIBAAAAAF4AAAAAAAEABHRlc3QAB3N0dWRlbnQABAMP/hIFHgD3AQAMCf3N
uA==
gPraWB4BAAAAOAAAADoBAAAAAF4AAAAAAAEAAgAE//AHAAAACXhpYW93b25pdQGZnDqBmCz35ow=
'/*!*/;
# at 314
#170329  8:06:24 server id 1  end_log_pos 345 CRC32 0x7a48c057  Xid = 114
COMMIT/*!*/;
DELIMITER ;
...后面固定部分省略...

發(fā)現(xiàn)是一堆看不懂的東西，使用-vv可將這些顯示出來(lái)?？梢钥闯?，結(jié)果中記錄的非常詳細(xì)，這也是為什么基于row記錄日志會(huì)導(dǎo)致日志文件極速變大。

[root@xuexi data]# mysqlbinlog mysql-bin.000005 -vv
...前面省略...
BINLOG '
gPraWBMBAAAAOgAAAAIBAAAAAF4AAAAAAAEABHRlc3QAB3N0dWRlbnQABAMP/hIFHgD3AQAMCf3N
uA==
gPraWB4BAAAAOAAAADoBAAAAAF4AAAAAAAEAAgAE//AHAAAACXhpYW93b25pdQGZnDqBmCz35ow=
'/*!*/;
### INSERT INTO `test`.`student`
### SET
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2='xiaowoniu' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
###   @4='2017-03-29 08:06:24' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 314
...后面省略...

還有一種mixed模式。這種模式下默認(rèn)會(huì)采用statement的方式記錄，只有以下幾種情況會(huì)采用row的形式來(lái)記錄日志。

1.表的存儲(chǔ)引擎為NDB，這時(shí)對(duì)表的DML操作都會(huì)以row的格式記錄。

2.使用了uuid()、user()、current_user()、found_rows()、row_count()等不確定函數(shù)。但測(cè)試發(fā)現(xiàn)對(duì)now()函數(shù)仍會(huì)以statement格式記錄，而sysdate()函數(shù)會(huì)以row格式記錄。

3.使用了insert delay語(yǔ)句。

4.使用了臨時(shí)表。

5.5 二進(jìn)制日志相關(guān)的變量

注意：在配置binlog相關(guān)變量的時(shí)候，相關(guān)變量名總是搞混，因?yàn)橛械氖莃inlog，有的是log_bin，當(dāng)他們分開(kāi)的時(shí)候，log在前，當(dāng)它們一起的時(shí)候，bin在前。在配置文件中也同樣如此。

• log_bin = {on | off | base_name} #指定是否啟用記錄二進(jìn)制日志或者指定一個(gè)日志路徑(路徑不能加.否則.后的被忽略)
• sql_log_bin ={ on | off } #指定是否啟用記錄二進(jìn)制日志，只有在log_bin開(kāi)啟的時(shí)候才有效
• expire_logs_days = #指定自動(dòng)刪除二進(jìn)制日志的時(shí)間，即日志過(guò)期時(shí)間
• binlog_do_db = #明確指定要記錄日志的數(shù)據(jù)庫(kù)
• binlog_ignore_db = #指定不記錄二進(jìn)制日志的數(shù)據(jù)庫(kù)
• log_bin_index = #指定mysql-bin.index文件的路徑
• binlog_format = { mixed | row | statement } #指定二進(jìn)制日志基于什么模式記錄
• binlog_rows_query_log_events = { 1|0 } # MySQL5.6.2添加了該變量，當(dāng)binlog format為row時(shí)，默認(rèn)不會(huì)記錄row對(duì)應(yīng)的SQL語(yǔ)句，設(shè)置為1或其他true布爾值時(shí)會(huì)記錄，但需要使用mysqlbinlog -v查看，這些語(yǔ)句是被注釋的，恢復(fù)時(shí)不會(huì)被執(zhí)行。
• max_binlog_size = #指定二進(jìn)制日志文件最大值，超出指定值將自動(dòng)滾動(dòng)。但由于事務(wù)不會(huì)跨文件，所以并不一定總是精確。
• binlog_cache_size = 32768 #基于事務(wù)類(lèi)型的日志會(huì)先記錄在緩沖區(qū)，當(dāng)達(dá)到該緩沖大小時(shí)這些日志會(huì)寫(xiě)入磁盤(pán)
• max_binlog_cache_size = #指定二進(jìn)制日志緩存最大大小，硬限制。默認(rèn)4G，夠大了，建議不要改
• binlog_cache_use：使用緩存寫(xiě)二進(jìn)制日志的次數(shù)(這是一個(gè)實(shí)時(shí)變化的統(tǒng)計(jì)值)
• binlog_cache_disk_use:使用臨時(shí)文件寫(xiě)二進(jìn)制日志的次數(shù)，當(dāng)日志超過(guò)了binlog_cache_size的時(shí)候會(huì)使用臨時(shí)文件寫(xiě)日志，如果該變量值不為0，則考慮增大binlog_cache_size的值
• binlog_stmt_cache_size = 32768 #一般等同于且決定binlog_cache_size大小，所以修改緩存大小時(shí)只需修改這個(gè)而不用修改binlog_cache_size
• binlog_stmt_cache_use：使用緩存寫(xiě)二進(jìn)制日志的次數(shù)
• binlog_stmt_cache_disk_use: 使用臨時(shí)文件寫(xiě)二進(jìn)制日志的次數(shù)，當(dāng)日志超過(guò)了binlog_cache_size的時(shí)候會(huì)使用臨時(shí)文件寫(xiě)日志，如果該變量值不為0，則考慮增大binlog_cache_size的值
• sync_binlog = { 0 | n } #這個(gè)參數(shù)直接影響mysql的性能和完整性
  • sync_binlog=0:不同步，日志何時(shí)刷到磁盤(pán)由FileSystem決定，這個(gè)性能最好。
  • sync_binlog=n:每寫(xiě)n次事務(wù)(注意，對(duì)于非事務(wù)表來(lái)說(shuō)，是n次事件，對(duì)于事務(wù)表來(lái)說(shuō)，是n次事務(wù)，而一個(gè)事務(wù)里可能包含多個(gè)二進(jìn)制事件)，MySQL將執(zhí)行一次磁盤(pán)同步指令fdatasync()將緩存日志刷新到磁盤(pán)日志文件中。Mysql中默認(rèn)的設(shè)置是sync_binlog=0，即不同步，這時(shí)性能最好，但風(fēng)險(xiǎn)最大。一旦系統(tǒng)奔潰，緩存中的日志都會(huì)丟失。

在innodb的主從復(fù)制結(jié)構(gòu)中，如果啟用了二進(jìn)制日志(幾乎都會(huì)啟用)，要保證事務(wù)的一致性和持久性的時(shí)候，必須將sync_binlog的值設(shè)置為1，因?yàn)槊看问聞?wù)提交都會(huì)寫(xiě)入二進(jìn)制日志，設(shè)置為1就保證了每次事務(wù)提交時(shí)二進(jìn)制日志都會(huì)寫(xiě)入到磁盤(pán)中，從而立即被從服務(wù)器復(fù)制過(guò)去。

5.6 二進(jìn)制日志定點(diǎn)還原數(shù)據(jù)庫(kù)

只需指定二進(jìn)制日志的起始位置（可指定終止位置）并將其保存到sql文件中，由mysql命令來(lái)載入恢復(fù)即可。當(dāng)然直接通過(guò)管道送給mysql命令也可。

至于是基于位置來(lái)恢復(fù)還是基于時(shí)間點(diǎn)來(lái)恢復(fù)，這兩種行為都可以。選擇時(shí)間點(diǎn)來(lái)恢復(fù)比較直觀些，并且跨日志文件恢復(fù)時(shí)更方便。

mysqlbinlog --stop-datetime="2014-7-2 15:27:48" /tmp/mysql-bin.000008 | mysql -u user -p password

恢復(fù)多個(gè)二進(jìn)制日志文件時(shí)：

mysqlbinlog mysql-bin.[*] | mysql -uroot -p password

或者將它們導(dǎo)入到一個(gè)文件中后恢復(fù)。

mysqlbinlog mysql-bin.000001 > /tmp/a.sql
mysqlbinlog mysql-bin.000002 >>/tmp/a.sql
mysql -u root -p password -e "source /tmp/a.sql"

總結(jié)

到此這篇關(guān)于MySQL日志的文章就介紹到這了,更多相關(guān)MySQL日志分析內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論