Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use error log and bin log in MySQL. It is very detailed and has a certain reference value. Friends who are interested must finish it!

1.Error log

Logging DB starts, runs, stops with serious errors

The-log-error=file_name option is available to specify the location of mysqld reporting error files. If no file_ name value is specified, use: hostname.err default in the directory specified by the parameter DATADIR

2. BIN log

Binary records all DDL,DML, but does not contain query sentences, describing the process of changing the data. It plays an important role at the moment.

-- log-bin if there is no way out, write it on DATADIR

Open bin log: log_bin

Mysql > show variables like'% bin%'

+-+

| | Variable_name | Value |

+-+

| | binlog_format | STATEMENT |

| | log_bin | OFF |

+-+

Modify the parameter file D:\ ProgramData\ MySQL\ MySQL Server 5.6\ my.ini

Log-bin=mysql-bin.log

Restart mysql

Note: encountered on 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 needs to be configured:

[mysqld]

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

Server-id=1

View bin log list

Mysql > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 120 | |

+-+ +

1 row in set (0.00 sec)

View the first bin log content

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)

View the specified bin log content

Show binlog events in 'mysql-bin.000006'

Check what the pre-bin log is.

Show master status

Binlog_format

1 > STATEMENT

This is the only way before MySQL 5.1. log records are all statement.

Key points: fewer logs, less impact on Icano

Disadvantages: in some cases, it can lead to inconsistent data in master-slave (such as sleep () function, last_insert_id (), and user-defined functions (udf), etc.)

2 > ROW

MySQL appears after 5.1.11, and each line change is recorded in the log.

Key point: every line is recorded, and there is no situation that cannot be copied under certain circumstances.

Deficiency: the journal is big, and the Igamot shadow is big.

3 > MIXED

Mix STATEMENT and ROW, use STATEMENT by default, and use ROW under special circumstances:

NDB, the client uses a chronograph, and the client uses an uncertain function such as current_user ()

Note: binlog_format parameters can be modified at global and session levels

Set binlog_format=MIXED

Set global binlog_format=MIXED

Mysqlbinlog

Fetch binlog

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

If the log is ROW, you can add-v-vv parameter to access it.

Binlog deletion

1 > reset master

Delete all binlog

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

Delete those before 000002

3 > purge master logs before '2016-10-21 1515 purl 0000'

Pre-temporal deletion

4 > expire_logs_days

Set expire_logs_days=7

The above is all the contents of the article "how to use error log and bin log in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.