Shulou(Shulou.com)06/01 Report--

The following is about the characteristics and use of three kinds of logs in MySQL database. The secret of the text is that it is close to the topic. So, no gossip, let's read the following directly, I believe you will benefit from reading the characteristics of the three kinds of logs in MySQL database and using this article.

1.1 mysql tool mysqlbinlog

The function of the mysqbinlog tool is to parse the binary binlog log contents of mysql and parse the binary log into SQL statements that can be executed in the MySQL database.

1.2 what is the binlog log for MySQL?

The following file under the MySQL data directory is the binlog log of mysql

Mysql-bin.000001mysql-bin.000002mysql-bin.000003mysql-bin.000004mysql-bin.000005mysql-bin.000006mysql-bin.000007mysql-bin.000008. . . . . .. . Tip: in order to generate binlog, you must turn on the log-bin function [root@db01 3306] # greplog-bin / data/3306/my.cnflog-bin = / data/3306/mysql-bin1.3 mysql binlog log function [root@db01 3306] # vim / data/3306/my.cnflog-bin = / data/3306/mysql-bin.

1.4 what is the purpose of mysql's binlog log?

Mysql's binlog log is used to record mysql internal additions and deletions and other updates to the mysql database (changes to data). Statements for database queries, such as those at the beginning of show,select, will not be recorded by binlog logs. For master-slave replication of the database, as well as incremental recovery.

Test questions:

In the MySQL database, with regard to the binlog log, the following statement is correct-(A)

A: with sufficient binlog logs and regular completeness, we can recover single-table data at any point in time.

B: take mysql master-slave synchronization as an example, all operations of the master database are recorded in binlog.

C: take the master-slave synchronization of mysql as an example, all query operations of the master database will be recorded in binlog.

D:binlog cannot be viewed through cat and vi, but can be viewed through gedit.

1.5 practice of parsing binlog logs with mysqlbinlog tool

By default, binlog logs are in binary format and cannot be viewed using commands of the view text tool, such as cat, vi

[root@db01 3306] # file / data/3306/mysql-bin.000001/data/3306/mysql-bin.000001: MySQL replication log

Parse the binlog log of the specified library

Example: use the mysqlbinlog-d parameter to parse the binlog log of the specified library

[root@db01 3306] # oldboy / data/3306/mysql-bin.000001-r oldboy.sql [root@db01 3306] # ll oldboy.sql-rw-r--r-- 1 root root 4731 Aug 31 04:36 oldboy.sql [root@db01 3306] # cat oldboy.sql

Conclusion: if the mysqlbinlog tool exports binlog from a sub-library, if you use the-d parameter, you must have usedatabase when updating the data in order to identify the binlog of the specified library, for example:

Use oldboy;insert into test values (1)

The following way of writing is not good.

Nsert into oldboy.test values (1)

Official information

Intercept by location: precise

Mysqlbinlog mysql-bin.000003 365 456 pos.sql

# description: the end of the specified file is "# at 365"-- "# at 456" in the binlog file;-r is the specified file, which is equivalent to redirection. If the command specifies that the beginning does not specify the end of the file, it starts from the beginning of the file, if you do not specify the start.

Intercept by time: blur, not allowed

Mysqlbinlog mysql-bin.000003-'2016-10-8 12 12 12' 2016-10-8 12 V 20 20'- r time.sql

# Note: if the time is under # at456 in the binlog file, even if only the start time is specified to the end of the file, and only the end is specified, it starts from the beginning;-r is the specified file

1.6 mysqlbinlog Command Summary

Mysqlbinlog command:

1. Parse the binlog log into a SQL statement (including location and time point).

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

3. Through the position parameters to intercept part of the binlog:--syart-position=365-stop-position=456,; to accurately locate part of the content.

4. Through the time parameter to intercept part of binlog:-stsrt-datetime='2016-10-8 12-12

5.-r specifies the file name, which is equivalent to redirection.

6. The method of parsing ROW level binlog Log

2. MySQL Database Service Log 2.1Error Log (error.log) introduction and adjustment

1. Error log (error.log) introduction

MySQL's error log records error messages encountered during startup / shutdown or running of the MySQL service process mysql.

two。 Error log (error.log) practice

Method 1: adjust the method in the configuration file, of course, you can add startup parameters at startup

[mysqld_safe] log-error=/data/3306/mysql_oldboy3306.err

Method 2: start the MySQL command and add:

Mysql_safe-- detaults-file=/data/3306/my.cnf &

You can also see it in the database.

Mysql > show variables like'% log_error%' +-+-+ | Variable_name | Value | +-+-+ | log _ error | | +-+-- + 1 row in set (0.00 sec)

If the mysql database cannot get up the troubleshooting steps

1. Back up the log file, then empty the log file, and restart the database to see the error.

two。 If it is multi-instance, then the directory permissions under the multi-instance directory chown-R mysql.

3. Check to see if there is a user who manages the mysql database.

2.2 introduction and adjustment of general query log (genera log) (not used at work)

1. Introduction to General query Log (generalquery log)

General query log (general querylog), record client connection information and executed SQL statement information (add, delete, change and query, all records). Do not use it at work, it will consume IO performance

two。 General query log generalquery log) adjustment

Mysql > show variables like 'general_log%' +-+ | Variable_name | Value | +-+- -+ | general_log | ON | | general_log_file | / data/3306/data/db01.log | +-+-- + 2 rows in set (0.00 sec)

Temporary effect:

Mysql > set global general_log_file ='/ data/3306/data/db01.log'; Query OK, 0 rows affected (0.00 sec) mysql > show variables like 'general_log%' +-+ | Variable_name | Value | +-+- -+ | general_log | OFF | | general_log_file | / data/3306/data/db01.log | +-+-- + 2 rows in set (0.00 sec)

Permanently effective (configured in configuration file):

[root@db01 3306] # grep gene / data/3306/my.cnfgeneral_log = on general_log_file = / data/3306/data/db01.log

An example of an actual general query log:

2.3 introduction and adjustment of slow query log (slow query log)

1. Slow query log description:

Slow query log, which records SQL statements whose execution time exceeds a specified value

two。 Adjustment of slow query log

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

The setting of slow query is very important for database SQL optimization.

[root@db01 /] # egrep "quer" / data/3306/my.cnf | tail-3long_query_time = 1log-slow-queries = / data/3306/slow.loglog_queries_not_using_indexes

Optimize the solution by using slow query

1. Enable slow query parameters

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

two。 Slow query log cutting script

[root@db01 /] # vim / uroot scripts / root@db01 scripts / var/spool/cron/root# cut mysql slow log00 scripts / bin/sh / server/scripts/cut_slow_log.sh > / dev/null 2 > / dev/null 2 > & & 1

3. Use the tool mysqlsla to analyze slow queries and send mailboxes to relevant personnel regularly.

Use explain to optimize the basic flow of SQL statements (select statements) *

Reason: when there is a problem with the website, the access will be slow, and more than 2 seconds in the database will be a very slow query!

Idea: when users are slow to access data, they have to think about whether it is caused by a full query, so we need to find a slow query, and there are two cases of finding a slow query. The first is to use the following emergency site crawling method in case of emergency. Enter the mysql database to find the slow query, and then build an index. See explain to check whether the slow query statement has been indexed. If there is no index to build the index, the second kind is not so urgent, you can pay attention to the load, cpu and other information at ordinary times, and check when you find something abnormal, these are all by modifying the configuration file to input the slow query statement into sllow.log, and then through the analysis tool for analysis, in the form of e-mail to developers and dba personnel to let them intervene to deal with.

Troubleshooting method: first check whether the web load is high, whether the storage pressure is high, as well as database load and disk IO, as well as cpu and other reasons. If the load is high, it is usually caused by the slow query of the database. The following is the slow query of the database:

(1) the method of slow query SQL statement

Find slow queries in the database (emergency processing)

Mysql > show full processlist +-+-- + | Id | User | Host | db | Command | Time | State | Info | | +-+ | 9 | root | localhost | oldboy | Query | 0 | NULL | show full processlist | +-+-- | -+-+ 1 row in set (0.00sec)

Description: if the database is providing external access, when the number of visits is large. There are many statements to execute this command continuously, and it is recommended that it be executed twice every two seconds. When the same command is found to appear continuously, it may be a slow query statement.

Query slow queries on the command line (daily processing)

[root@db01 ~] # mysql-uroot-poldboy123-S / data/3306/mysql.sock-e "showfull processlist;" | egrep-vi "sleep" Id User Host db Command Time State Info11 root localhost NULL Query 0 NULL showfull processlist

Description: this command can be said to be prepared in advance; whether it is important or not urgent, analyze the slow query log

Configure parameter record slow query statement

Long_query_time = 2 log_queries_not_using_indexes log-slow-queries = / data/3306/slow.log

(2) after catching the slow query, use the query statement to check the execution of the index (to see if you have walked the index)

Explain select * from test where name='oldgirl'\ Gexplain select * from test where name='oldgirl'\ G

Tip: the results of these two commands are the same! SQL_NO_CACHE is to prevent caching.

(3) Index the conditional columns that need to be indexed.

Large tables cannot be indexed during peak hours, and 3 million records are up to standard.

(4) analyze the man query tool mysqlsla (send email every morning).

Cut slow query log

1.mv cutting, reload process (same principle as mginx log cutting)

[root@db01 3306] # vim / data/3306/my.cnf log-slow-queries = / data/3306/slow.log [root@db01 3306] # / data/3306/mysql restart Restarting MySQL...Stoping MySQL...Starting MySQL... [root@db01 3306] # ll slow.log-rw-rw---- 1 mysqlmysql 380 Aug 27 15:59 slow.log [root@db01 3306] # mv / data/3306/slow.log / opt/$ (date +% F) _ slow.log [root@db01 3306] # ll / opt/-rw-rw---- 1 mysqlmysql 190 Aug 27 16:26 2016-08-27_slow.log [root@db01 3306] # mysqladmin-uroot-poldboy123-S / data/3306/mysql.sock flush-logs [root@db01 3306] # ll / data/3306/slow.log-rw-rw---- 1 mysqlmysql 190 Aug 27 16:28 / data/3306/slow.log

two。 Scripting (scheduled tasks)

Mv / data/3306/slow.log/opt/$ (date +% F) _ slow.logmysqladmin-uroot-poldboy123-S/data/3306/mysql.sock flush-logs

Tip: you can script the above commands, cut them every day, and analyze them with appropriate tools.

Is there anything you don't understand about the characteristics and use of the three kinds of logs in the above MySQL database? Or if you want to know more about it, you can continue to follow our industry information section.

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.