MySQL中error log和bin log怎么用

這篇文章主要介紹MySQL中error log和bin log怎么用，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

1.Error log

記錄DB啟動(dòng)，運(yùn)行，停止時(shí)嚴(yán)重錯(cuò)誤

可用—log-error=file_name 選項(xiàng)指定mysqld報(bào)錯(cuò)錯(cuò)誤文件的位置，如果沒(méi)有給定file_name值，使用：hostname.err 默認(rèn)在參數(shù)DATADIR指定的目錄中

2. BIN log

二進(jìn)制記錄所有DDL,DML，但不包含查詢的語(yǔ)句，描述了數(shù)據(jù)的更改過(guò)程。災(zāi)難時(shí)起著其重要作用

--log-bin如果沒(méi)有給出路徑，就寫在DATADIR

開啟bin log: log_bin

mysql> show variables like '%bin%';

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

| Variable_name                           | Value                |

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

| binlog_format                           | STATEMENT            |

| log_bin                                 | OFF                  |

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

修改參數(shù)文件D:\ProgramData\MySQL\MySQL Server 5.6\my.ini

log-bin=mysql-bin.log

重啟mysql

注意：在5.7.16 Linux 上有遇到

·  In MySQL 5.7.3 and later, if you specify this option without also specifying a --server-id, the server is not allowed to start. (Bug #11763963, Bug #56739)

2016-11-02T02:40:05.342650Z 0 [ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation

需要設(shè)置server-id：

[mysqld]

log-bin=/data/mysql/mysql-bin.log

server-id=1

查看bin log列表

mysql> show binary logs;

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

| Log_name         | File_size |

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

| mysql-bin.000001 |       120 |

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

1 row in set (0.00 sec)

查看第一個(gè)bin log 內(nèi)容

mysql> show binlog events ;

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

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info

                         |

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

| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4 |

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

1 row in set (0.00 sec)

查看指定bin log內(nèi)容

show binlog events in 'mysql-bin.000006';

查看當(dāng)前bin log是多少

show master status

binlog_format

1>  STATEMENT

MySQL 5.1之前只有這種方式，日誌記錄都是statement

優(yōu)點(diǎn)：日誌少，對(duì)I/O影響小

缺點(diǎn)：在某些情況下會(huì)導(dǎo)致master-slave中的數(shù)據(jù)不一致(如sleep()函數(shù)， last_insert_id()，以及user-defined functions(udf)等會(huì)出現(xiàn)問(wèn)題)

2>  ROW

MySQL 5.1.11后出現(xiàn)，每行變更記錄到日誌中

優(yōu)點(diǎn)：每行變化都記錄，不會(huì)某些情況下無(wú)法複製的情況

缺點(diǎn)：日誌大，I/O影響大

3>  MIXED

混合STATEMENT和ROW,默認(rèn)採(cǎi)用STATEMENT，特殊情況下採(cǎi)用ROW:

NDB,客戶端使用臨時(shí)表，客戶端採(cǎi)用了不確定函數(shù)如current_user()

注:可在global和session 級(jí)修改binlog_format參數(shù)

set binlog_format=MIXED ;

set global binlog_format=MIXED ;

mysqlbinlog

讀取binlog

D:\ProgramData\MySQL\MySQL Server 5.6\data>mysqlbinlog mysql-bin.000001

如日誌是ROW，可加上-v –vv參數(shù)進(jìn)行讀取

Binlog刪除

1>  reset master

刪除所有binlog

2>  purge master logs to 'mysql-bin.000002'

將000002前的刪除

3>  purge master logs before '2016-10-21 15:00:00';

將時(shí)間前的刪除

4>  expire_logs_days

set expire_logs_days=7

以上是“MySQL中error log和bin log怎么用”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！