MySQL Log management

MySQL Log management error log

Configuration method:

vim/etc/my.cnf[mysqld]log-error=/tmp/mysql.log

To see how to configure:

' %log%error% ';

Role:

Log the general status of MySQL database and error information, is our database of regular error processing of common logs.

General Query Log

Configuration method:

vim/etc/My.cnf[mysqld]general_log=ongeneral_log_file=/data/mysql/server2.log

To see how to configure:

' %gen% ';

Role:

Record MySQL all successful SQL statement information, can do audit, but we rarely open; can be used as an audit function, generally this log will not open, unless there are special requirements such as: ELK

Binary log

It is recommended that you open the binary log when you just deploy the MySQL database

Binary logs are not dependent on the storage engine, depend on the SQL layer, and the information about the SQL statements

• When the SQL layer has executed the completed statement, if it is a transaction, it should be the completed transaction
• Function: Backup and Point-in-time recovery, master-Slave

What does the binary log record?

Committed data records are logged to the binary in the form of an event

Binary record format

Be sure to configure the binary log

• Row (row mode): Table data changes process, record data in detail, but the IO requirements are relatively high, record data in any case is accurate. This mode is typically in production, which is the default mode after 5.7
• Statement: statement mode, which records SQL statements. The record information is concise, the SQL statement itself is recorded, but there is a function operation in the statement, it is possible to record the data is inaccurate (5.6 default mode, in the production environment with caution)
• Mixed: The mixture of the above two modes. After judging, choose a recording mode of row+statement mixing. Generally not

Open Binary Log

Profile Open:
vim/etc/my.cnf Log-bin=/data/mysql-bin   ---- in the global Open, the "Total gate"  command line of the binary log is turned on:  set sql_log　　 _bin=1/0;  ----the "sub-switch" of the binary log, which is temporarily active at the session level  , only the general gate is opened to make sense (by default), and sometimes it is temporarily shut down, affecting only the current session

Role:

1. Whether to open

2. Binary Log path/data

3. Binary log filename prefix mysql-bin

4. File name with "prefix". 000001~n

Set binary logging format (row is recommended)

Modifications in the configuration file:

vim/etc/my.cnfbinlog-format=after the row has been changed to restart

Command Line modification:

' STATEMENT ' ; MySQL ' ROW ' ; MySQL ' MIXED ';

View Binlog Settings

' %binlog% ';

How Binlog logs are viewed

View Binlog Original Information

Mysqlbin Mysql-bin. 000002

In row mode, translate into statements

Mysqlbinlog--base64-output='decode-rows' -v mysql-bin. 000002

View Binlog Events

show binary logs;      inch "' ;  View file contents show Master status;   To view the binary log files that are currently in use

Binary Log Management

By default, old log files are not deleted.

Delete binary Log

To delete a log based on its presence time:

73  day;reset master;  Delete all Binlog logs and regenerate, starting from 000001

To delete a log by file name:

' mysql-bin.000010 ';

Flush binary Log

Flush logs

Intercept binary logs

Mysqlbinlog--start-position=xx--stop-position=xx >a.sql

How to intercept binlog information content, restore on demand (general idea)

(1), using show binary logs;  Show master status; View files that are currently in use

(2), show Binlog events in '; Look back, find the wrong operation, judge the transaction to open position and end position

(3), remove the misoperation, leave normal operation to two scripts: Mysqlbinlog--start-position=xx--stop-position=xx binary log file >/tmp/in.sql

(4), the first Test library operation, the erroneous operation of the data export, and then the production database Recovery # # # Use: Source recovery, restore time, in the current window temporarily shut down binary logging

Problems encountered:

1. Long time

2, the production data has a certain impact, there may be redundant data

A good solution:

1, Flashback (Flash back function)

2, through backup, delay from the library

Slow logs slow log

is to log the relevant SQL statements that affect the performance of the database in the MySQL server to the journal file

Through the analysis of these special SQL statements, improved to achieve the purpose of improving the performance of the database.

tool logs during tuning, statistics collection execution slow statements

Slow Log settings

Configuration method:

vim/etc/My.cnfslow_query_log=noslow_query_log_file=/tmp/slow.log

• Long_query_time: Set the threshold of the slow query, the SQL that exceeds the sub-set value is logged to the slow query log, the default value is 10s, the existing version can specify fraction seconds
• Slow_query_log: Specifies whether to turn on the slow query log
• Slow_query_log_file: Specifies the slow log file location, can be empty, the system will give a default file Host_name-slow.log
• Min_examined_row_limit: Query check returns less than the specified row for SQL is not logged to the slow query log
• Log_queries_not_using_indexes: Slow query logs that do not use indexes are logged to the index

MySQL Log management