Preface

The log of the database is a powerful basis to help the database administrator track and analyze various events that have occurred in the database. Mysql provides error logs and binlog logs. (Binary log), investigation log, slow query log. Here, I seek to address the following questions: What is the purpose of each log? How to control these logs? How to use the information provided by these logs?

Error log

1. Error log function

Error The log records information about when MySQL is started and stopped, as well as any serious errors that occur while the server is running. When there is any failure in the database that prevents it from starting, For example, mysql starts abnormally, We cancheck this log first. In mysql, Error log logs (and other logs) can not only be stored in files, but of course can also be stored in data tables. As for the implementation method, the author is also studying... ##·

2. Error log control and use

1.Configuration

Pass log-error=[file-name] to configure (in the mysql configuration file), if file_name is not specified, mysqld uses the error log name host_name.err (host_name is the host name), and defaults to The log file is written to the directory specified by the parameter datadir (the directory where the data is saved).

For example, I use the WampServer integrated environment locally

where log-error= D:/wamp/logs/mysql.log

##As shown below

If I comment out log-error (#log-error=D:/wamp/logs/mysql.log) , restart the server, you can view the error log file in the directory specified by datadir

##2. View the error log

The format of the error log: time [error level] error message

If you find it troublesome to locate the error log location through the mysql configuration file, you can view the error log location through commands on the client

Use imperative: show variables like 'log_error';

#The following is the mysql startup log

##

Binary log

1. Function

Binary log (also called binlog log) records all DDL (data definition language) statements and DML (data manipulation language) statements, but does not include data query statements. Statements are saved in the form of "events" , which describes the process of data changes. The two main functions of this log are: data recovery and data replication.

Data recovery: MySQL itself has data backup and recovery functions. For example, we back up data at 12:00 midnight every day. If one day, at 13:00 pm, the database fails, causing the database content to be lost. We can solve this problem through binary logs. The solution is to restore the data backup file at 12:00 midnight of the previous day to the database first, and then use the binary log to restore the of the database from 12:00 midnight of the previous day to 13:00 of the current day. operate.

Data replication: MySQL supports the data replication function between master and slave servers, and uses this function to implement the redundancy mechanism of the database to ensure the availability of the database and improve the database Virtue performance. MySQL implements data transfer through binary logs. The binary log content on the master server will be sent to each slave server and executed on each slave server, thus ensuring data consistency between the master and slave servers.

2. Binary log control and use

1.Open

By default, mySQL does not record binary logs. How can I enable MySQL's binary logging function?

We can control MySQL to start the binary logging function through the MySQL configuration file. Start the MySQL binary log by modifying the parameter log-bin=[base_name]. mySQL will record the modified database content statements into a log file named base_name-bin.0000x, where bin represents binary and the suffix 00000x represents the order of the binary log file. Each time it is started, Mysql, the log file order will automatically increase by 1. If base_name is not defined, MySQL will use the value set by the pid-file parameter as the base name of the binary log file.

For example, if I name the log-bin file as mybinlog, it will be in D:/wamp/bin/mysql/mysql5 In the .6.17/data directory, generate the binary log file mybinlog.00000x.

##The binary log file is as shown below

Check whether the bin-log log is turned on by using show variables like'log_bin'.

##2.View

MySQL binary logs are mainly used internally by MySQL and are not intended for database administrators to read and use. Therefore, an important difference between binary logs and other logs is that binary logs The format of the file is not text format, and its content cannot be viewed directly through Notepad. In order to facilitate administrator management, MySQL provides the mysqlbinlog tool to view the binary log content.

##For example: mysqlbinlog D:\wamp\bin\mysql\mysql5.6.17\data\mybinlog.000003

The execution results are as follows:

Now let’s do a test to see if the bin log records me Operation of updating the database

For example, I change the id1 of the row with id2=2 in data table t2 to 5. Then I query the binary log file to see if my operations are recorded.

The results are obvious. The binary file records my operations of modifying the database, and also records that I modified the data in the database. As for my query statement, it is not recorded.

3. Deletion of binary logs

For a relatively busy system, since a large number of logs are generated every day, if these days are long, If the time is not clear (or transferred), it will cause a lot of waste of disk space. Therefore, regularly deleting logs is an important part of DBA's maintenance of MYSQL database.

1. Execute the reset master command through the reset master command

. This command will delete all binlog logs. New log file numbers start from 000001.

2. Delete 'xxxxxx' by executing purge master logs to'base_name.xxxxxx' 'All logs before number. Below I will delete all logs before mybinlog.000003.

As shown below:

3. Execute through purge master logs beffor 'time' command
purge master logs beffor 'time' means to delete all logs before 'time' . For example, to delete all logs before 2016-04-01 00:00:00, the command is as follows:

purge master logs before '

2016 -04-01 00:00:00';4. By setting the parameter expire_logs_days

## in the configuration file

#By setting the parameter expire_logs_days=#, you can specify the number of days for the log to expire. After the specified number of days, the log will be automatically deleted. This is my preferred method. For example, setting expire_logs_day=3 means that it will be automatically deleted after 3 days.

4. Binary log important parameter description

max_binlog_size: Specifies the maximum value of a single binary log file. If it exceeds This value will generate a new binary log file with a suffix of +1 and record it in the .index file.

##binlog_cache_size: cache area size

sync_binlog: Indicates that the cache is synchronized to the disk without writing to the cache many times. If N is set to 1, it means that the binary file is written to the disk synchronously. The system default setting in MySQL is sync_binlog=0, which means no mandatory disk refresh instructions are performed. At this time, the performance is the best, but the risk is also the greatest. Because once the system crashes, all binlog information in binlog_cache will be lost. When set to "1", it is the safest setting but has the greatest performance loss. Because when set to 1, even if the system crashes, at most one unfinished transaction in binlog_cache will be lost, without any substantial impact on the actual data.

##binlog-do-db: Which database days need to be recorded? The default value is empty, indicating that the All library logs are synchronized to the binary log.

##binlog-ignore-db: Days for which databases need to be ignored

##log-slave-update: Configuration required when building a master-slave database

binglog_format: Optional values ​​include statement (recording logical sql statements), row (recording table row changes), mixed

5. Use binary logs for data recovery

As discussed earlier, if the data is abnormal and you want to restore it to the data at a certain point in time, binary alone is often not enough. We What is also needed is the data that was backed up before this point in time.

In order to facilitate the observation of the effect, I have now backed up my database. At this time, the data in data table t1 is as follows:

From now on, I need to perform some operations on the data, such as update or insert operations. After the operation, the t1 data will be as shown below

##At this time, if something very unfortunate happens and a hacker breaks in, All the data in my t1 table has been deleted, so how do I get the data before the hacker deleted it?

#Step One: I need to restore my data To the data I backed up, the result after restoration is as follows:

##Second step: I need to use my binary log file to restore all data operations from the moment of data backup to the moment before being hacked

Execute: mysqlbinlog D:\wamp\bin\mysql\mysql5.6.17\data

\mybinlog.000004

Analyzing the binary log we found that at line 'at 637', our data was corrupted For hacker attacks, we only need to restore all operations before line 637 and ignore all operations after that.

##So we can execute the following command to restore our data:

mysqlbinlog D:\wamp\bin\mysql\mysql5.6.17\data\mybinlog.000004 --stop-pos=637|mysql -uroot -p**dequan

Then let’s check the data of our t1 table again

# Finally, we are done, but this also reminds us that in order to facilitate data recovery, not only Binary logging needs to be enabled and data must be saved regularly.

##Additional information about the binary log:

1. We can also view the current What other binary logs are there?

2. We can log events recorded through show binlog events

show binlog events views all logged events. If you want to query a certain binary log event, you can add in + 'log name' at the end, as shown below:

##Query log

1. Function description

The query log records

all statements from the client. You can specify its location through log=[file_name]. Like other logs, if the file_name value is not specified, the log will be written to the directory where datadir is located. The default file name is host_name.log. This log has a greater impact on system performance. Generally it will not be turned on, so I won’t go into details here.

Slow query log

1. Function description

The slow query log is Record all SQL statement logs whose execution time exceeds the parameter long_query_time (unit: seconds). The time spent waiting to obtain the table lock is not counted as execution time. We can enable the slow query log function through the log-slow-queries=[file_name] option. Like the previous log, if file_name is not specified, the log directory is in the datedir directory, and the default name is host_name-slow.log.

2. Slow query log reading

Query the slow query open status

Add the following code in the configuration file, Enable slow query

#Enable slow query

slow_query_log=ON
slow_query_log_file=D:/wamp/logs/myslowquery.log
long_query_time=1.5

(Some places say that the slow query log is specified through log-slow-queries=[file_name], but I tried it, and an error will be reported when starting mysql in this way. It may be that I am in win Operate under the system, or maybe my local mysql is installed using the WampServer integrated environment, or maybe the version used is different)

Check the slow query time setting

If you modify the slow query time, you can use set long_query_time=1.5;

In order to facilitate viewing the effect, we will change the slow query time limit is 0.15, then we write a sql with a time exceeding 0.15, and finally check the log to see whether the sql statement is recorded.

Set the slow query time and execute the corresponding sql statement

#I executed a total of 3 SQL statements above. Now let’s take a look. The slow query log is as follows

It only records the sql statement that takes a long time to query.

The above is the introduction of error log, binlog log, query log and slow query log in Mysql. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!