MySQL Database log Introduction

MysqlIntroduction to Database logging

1th Chapter BinlogLog1.1 BinlogLog Introduction

MySQL binlog log function is used to record mysql internal additions and deletions to the MySQL database has updated content records (changes to the database), the statement of the database query, such as show,Select the beginning of the statement, will not be binlog log records. binlog logs are used for incremental recovery of the database as well as master-slave replication.

mysql MySQL binlog log:

[[EMAIL&NBSP;PROTECTED]&NBSP;~]#&NBSP;LL&NBSP;/DATA/3306/TOTAL&NBSP;88-RW-RW---- 1 mysql mysql    703 Feb  3 14:21 mysql-bin.000001         #<==binlog log file-RW-RW---- 1 mysql mysql   126 Feb  &NBSP;3&NBSP;14:23&NBSP;MYSQL-BIN.000002-RW-RW---- 1 mysql mysql   126 feb &NBSP;&NBSP;3&NBSP;14:24&NBSP;MYSQL-BIN.000003-RW-RW---- 1 mysql mysql   479 &NBSP;FEB&NBSP;&NBSP;3&NBSP;14:36&NBSP;MYSQL-BIN.000004-RW-RW---- 1 mysql mysql   &NBSP;477&NBSP;FEB&NBSP;&NBSP;3&NBSP;14:39&NBSP;MYSQL-BIN.000005-RW-RW---- 1 mysql mysql  18699&NBSP;FEB&NBSP;&NBSP;4&NBSP;03:05&NBSP;MYSQL-BIN.000006-RW-RW---- 1 mysql mysql  &NBSP;&NBSP;150&NBSP;FEB&NBSP;&NBSP;4&NBSP;03:05&NBSP;MYSQL-BIN.000007-RW-RW---- 1 mysql mysql  13334 feb   4 04:08 mysql-bin.000008 

1.2 Binloglog function is turned on

The Log-bin feature needs to be turned on in profile my.cnf to generate the corresponding binlog log file

[[email protected] 3306]# grep "log-bin"/data/3306/my.cnf[mysqld]log-bin =/data/3306/mysql-bin #<== added in mysqld configuration Log-bin function

To view the status of the Bin-log feature:

mysql> show variables like  '%log_bin% '; +---------------------------------+-------+|  Variable_name                    | value |+---------------------------------+-------+| log_bin                           | ON    |              #<== Record Binlog Switch | log_bin_trust_function_creators |  off   | |  sql_log_bin                      | ON    |              #<== temporarily does not record Binlog switch +---------------------------------+-------+3  rows in set  (0.00&NBSP;SEC) 

temporarily do not record Bin-log:

Mysql> Set Session Sql_log_bin = OFF;

1.3 BinlogThree modes of the log1.3.1 Row Level

The log is recorded in the form of each row of data being modified, and then the same data is modified on the slave side.

Advantage: In row level mode,Bin-log can not record the context-sensitive information of the executed SQL statement, just need to record which record has been modified and what to change. So the log content of row level will be very clear to record the details of each row of data modification, very easy to understand. There are no stored procedures or function in certain situations , and trigger calls and triggers cannot be copied correctly.

disadvantage:at the row level , all execution statements are recorded in the log when logged, which can result in a large amount of log content, such as an update statement:update Product set owner_member_id= ' B ' where owner_member_id= ' a ', after execution, the log does not record the event that corresponds to this update statement (MySQL record The Bin-log log in the form of an event, but rather the change of each record that this statement updates, so that it is logged as many times as the records are updated. Naturally, the amount of Bin-log logs will be very large. In particular, when executing statements such as ALTER TABLE , the amount of log generated is staggering, as MySQL has an ALTER TABLE Table structure change statements are handled in such a way that each record in the entire table needs to be changed, in effect rebuilding the entire table, and every record of that table is recorded in the log.

1.3.2 Statement Level

each SQL that modifies the data is recorded in the Bin-log of Master . slave The SQL process will parse the same SQL that sing Woo the original master side to execute again when copying .

Advantages:The advantages of statement level first is to solve the disadvantage of the row level , do not need to record each row of data changes, reduce bin-log log volume, save IOfor improved performance. Because it only needs to record the details of the statements executed on master, as well as contextual information when executing the statement.

Cons: Because he is the execution statement of the record, so, in order for these statements to be executed correctly on the slave side, it must also record some relevant information about each statement at the time of execution, that is, contextual information, to ensure that all statements are slave The end is executed with the same results as when it is executed at the master side. In addition, due to the rapid development of MySQL, a lot of new features continue to join, so that MySQL replication encountered a lot of challenges. When nature replicates, the more complex the content,the more likely the bug will be. At the statement level , there are a number of things that have been discovered that can occur as a MySQL replication problem, mainly when you use certain functions or functions when modifying data, such as sleep () functions are not copied correctly in some versions , and the last_insert_id () function is used in stored procedures , which can cause inconsistent slave and master ID , and so on. Because the row level is recorded on a per-row basis, no similar problem occurs.

1.3.3 Mixed

is actually the combination of the first two patterns. In Mixed mode,MySQL distinguishes between the log forms of treated records based on each specific SQL statement executed, that is, the statement and Row Choose between one. The statement level in the new version is still the same as before, just record the executed statement. In the new version of MySQL, the row level mode is also optimized, not all changes are recorded at the row level , as in the case of table structure changes will be statement pattern to record, if the SQL statement is indeed A statement that modifies data such as update or delete, then all row changes are recorded.

1.3.4How to choose to useBinlogthe Mode

1, Internet companies, the use of MySQL relatively few functions (stored procedures, triggers, functions)

Select the default statement mode , statement level mode

2. If you use MySQL 's special functions (stored procedures, triggers, functions), select Mixed mode.

3, the company if using the special features of MySQL (stored procedures, triggers, functions), data maximization consistent, at this time the best line mode.

1.3.5How to setBinlogthe Mode

Mysql> show global variables like '%binlog_format% '; +---------------+-----------+| variable_name | Value |+---------------+-----------+| Binlog_format | STATEMENT |+---------------+-----------+1 row in Set (0.00 sec)

q modify in config file

[mysqld]log-bin=mysql-bin#binlog_format= "STATEMENT" #binlog_format = "ROW" binlog_format= "MIXED"

q Direct Modification

Set global Binlog_format = ' STATEMENT ' Set global Binlog_format = ' ROW ' Set global Binlog_format = ' MIXED '

1.4 BinlogFile parsing ToolsMysqlbinlog

by default,Binlog is in binary format and cannot be viewed using the View Text tool commands, for example:cat,vi,find, etc.

1.4.1parse the entireLog-binfile

[Email protected] 3306]# Mysqlbinlog mysql-bin.000001

1.4.2parse the specified database

q -D only resolves the corresponding database

using the-D parameter, you can parse the database inside the corresponding Log-bin file as follows:

[Email protected] 3306]# mysqlbinlog-d Oldboy mysql-bin.000001

q -R data export to the specified file

to save the parsed result to another SQL file:

[Email protected] 3306]# mysqlbinlog-d Oldboy mysql-bin.000001-r oldboy.sql

Mysqlbinlog Tool Sub-Library export Binlog, if you use the-d parameter, then update the data, you must have used database to separatethe binlog of the specified library , For example:'

Use Oldboy;insert into student values (1, ' Oldboy ')

The following wording is not possible:

INSERT into oldboy.student values (2, ' Oldboy ')

1.4.3Intercept by Location

Mysqlbinlog mysqlbin.000001--start-position=365--stop-position=456-r pos.sql

Specify the start position without specifying the end position:

Mysqlbinlog mysqlbin.000001--start-position=365-r Pos.sql

Specify the end position without specifying a start position:

Mysqlbinlog mysqlbin.000001--stop-position=456-r Pos.sql

1.4.4Intercept by Time

Mysqlbinlog mysql-bin000020--start-datatime= ' 2017-10-16 17:14:15 '--stop-datetime= ' 2017-10-16 18:14:15 '-R time.sql

Specify a start time without specifying an end time:

Mysqlbinlog mysql-bin000020--start-datatime= ' 2017-10-16 17:14:15 '-R time.sql

Specify the end time and do not specify a start time:

Mysqlbinlog mysql-bin000020--stop-datetime= ' 2017-10-16 18:14:15 '-R time.sql

1.4.5 MysqlbinlogCommand Summary

1, the command function is to parse binlog into SQL statements (including location and point in time)

2.-D parameter splits Binlog according to the specified library (split single table Binlog can be filtered by SQL keyword)

3, through the position parameters intercept part binlog:--start-position=265--stop-position=345, accurate positioning to take part of the content.

4, through the time parameter interception part Binlog:--start-datetime= 180203 14:21:56--stop-datetime= 180203 14:21:56, Fuzzy fetching of content

5. -r file name, equivalent to redirect ">"

6, parsing the ROW level binlog Log Method

Mysqlbinlog--base64-output=decode-rows-v mysql-bin.000016

Mysqlbinlog--base64-output= "Decode-rows"--verbose mysql-bin.000004

2nd Chaptererror LogError log2.1 error logLog Introduction

the error log for MySQL logs theerror message that the MySQL service process Mysqld encountered during startup / shutdown or running.

2.2 Error Loglog function is turned on

to adjust the parameters in the configuration file :

[[email protected] 3306]# grep log-error my.cnf[mysqld]log-error =/data/3306/mysql_3306.err #<== Add log in MYSQLD configuration -error function

in the Start command, add:

Mysqld_safe--defaults-file=/data/3306/my.cnf--log-error=/data/3306/mysql_3306.err &

To view the error log:

Mysql> Show variables like '%log_error% '; +---------------+---------------------------+| variable_name | Value |+---------------+---------------------------+| Log_error | /data/3306/mysql_3306.err |+---------------+---------------------------+1 row in Set (0.00 sec)

3rd ChapterNormal query log3.1 General Query LogLog Introduction

General Query log: Logs client connection information and executed SQL statement information;

3.2 General Query Loglog function of khaki

to adjust the parameters in the configuration file :

[[email protected] 3306]# grep general_log my.cnf[mysqld]general_log = Ongeneral_log_file =/data/3306/data/mysql.log # <== add General_log function in MYSQLD configuration

4th ChapterSlow Query Log4.1 Slow query LogIntroduction

Slow query log (slow query log): Records SQL statements with execution time exceeding the specified value (long_query_time) .

4.2 Slow Query Loglog function is turned on

Long_query_time = 1log-slow-queries =/data/3306/slow.loglog_queries_not_using_indexes

The settings for the slow query log, for database optimization of SQL is very important.

4.3cut slow query log

Cut the slow query log by day and the script is as follows:

[email protected] scripts]# cat cut_slow_log.sh#/bin/bashcd/data/3306/&&/bin/mv slow.log.$ (date +%f) && Amp;mysqladmin-uroot-p123456-s/data/3306/mysql.sock Flush-log

to set a scheduled task for a cut log file:

[Email protected] scripts]# tail-2/var/spool/cron/root#cut mysql slow log00 xx * * */bin/sh/server/scripts/cut_slow_l Og.sh &>/dev/null

Introduction to MySQL database log