MySQL error log和bin log

1.Error log

記錄DB啟動，執行，停止時嚴重錯誤

可用—log-error=file_name 選項指定mysqld報錯錯誤檔案的位置，如果沒有給定file_name值，使用：hostname.err 預設在引數DATADIR指定的目錄中

2. BIN log

二進位制記錄所有DDL,DML，但不包含查詢的語句，描述了資料的更改過程。災難時起著其重要作用

--log-bin如果沒有給出路徑，就寫在DATADIR

開啟bin log: log_bin

mysql> show variables like '%bin%';

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

| Variable_name                           | Value                |

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

| binlog_format                           | STATEMENT            |

| log_bin                                 | OFF                  |

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

修改引數檔案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

需要設定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)

檢視第一個bin log 內容

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內容

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

檢視當前bin log是多少

show master status

binlog_format

1>  STATEMENT

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

優點：日誌少，對I/O影響小

缺點：在某些情況下會導致master-slave中的資料不一致(如sleep()函式， last_insert_id()，以及user-defined functions(udf)等會出現問題)

2>  ROW

MySQL 5.1.11後出現，每行變更記錄到日誌中

優點：每行變化都記錄，不會某些情況下無法複製的情況

缺點：日誌大，I/O影響大

3>  MIXED

混合STATEMENT和ROW,預設採用STATEMENT，特殊情況下採用ROW:

NDB,客戶端使用臨時表，客戶端採用了不確定函式如current_user()

注:可在global和session 級修改binlog_format引數

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引數進行讀取

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';

將時間前的刪除

4>  expire_logs_days

set expire_logs_days=7

flush logs

手動重新整理log