mysql error log_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

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