MySQL error log, binlog log, query log, slow query log simple introduction

Objective

The log of the database is a strong basis for helping the database administrator to track the various events that have occurred in the database before. MySQL provides error log, Binlog log (binary log), investigate log, slow query log. Here, I seek to solve the following question: What is the purpose of each log? How to control these logs? How do I use the information provided by these logs?

error Log1. Error log functionthe error log logs when MySQL starts and stops. and information about any severity errors that occur during server execution. When the database appears no matter what the failure causes the boot to fail. For example MySQL startup exception. we canCheck this log first. In MySQL, the error log log (and other logs) is not only stored in the file. Of course, it can also be stored in a table of data. As to the way of realization. The author is also studying ··2. Error log control and use1. Configuration by log-error=[File-name] to configure (in the MySQL configuration file), assume that no file_name,mysqld is specified using the error log named Host_name.err (host_ Name is the hostname). The log file is written to the folder specified in the DataDir (folder where the data is saved) by default. For example, I'm using the Wampserver integrated environment locallyAmong Log-error=d:/wamp/logs/mysql.log
For example with
Suppose I stare log-error (#log-error=d:/wamp/logs/mysql.log) and restart the server. You can view the error log file under the folder specified in DataDir
2. Viewing the error logformat of error log: Time [Error level] Error messageSuppose you feel that it is more troublesome to locate the error log by using the MySQL configuration file. You will be able to see where the error log is located by re-using the client command.using the command: Show variables like ' log_error ';
here is the MySQL boot log

Binary Log1. RoleThe binary log (also called the Binlog log) records all of the DDL (data definition Language) statements and DML (data manipulation language) statements. However, it does not contain data query statements. Statements are saved in the form of "events", which describe the process of describing data changes. The two main functions of the log are: Data recovery and data replication. Data recovery: MySQL itself has data backup and recovery capabilities.

For example, we make a backup of the data at 12:00 midnight every day.

Suppose one day, 13:00, there is a problem with the database. Causes the database content to be lost.

We are able to solve the problem through the binary log. The solution to this idea is. Be able to restore the data backup file of 12:00 midnight before the previous day to the database, and then use the binary log reply from 12:00 Midnight before the beginning of the previous operation of the database. data replication: MySQL supports data replication between master and slave servers, and through this function, it realizes the redundancy mechanism of database to ensure the availability of database and improve database de performance. MySQL is the transfer of data through binary logs.

The binary log content on the primary server is sent to the individual slave servers. and run on each of the servers from the server, thus guaranteeing the consistency of data between the master and slave servers. 2. Binary log control and use 1. Open by default, MySQL does not log binary logs. How can I turn on MySQL binary logging? We are able to control MySQL boot binary logging via MySQL configuration file. Start the MySQL binary log by changing the number of parameters Log-bin=[base_name].

MySQL logs the changed database contents to a log file in the name of base_name-bin.0000x. The bin represents binary. The suffix 00000x represents the sequence of binary log files, and each time MySQL is started, the log file sequence will take 1 of its own initiative. Suppose Base_name is undefined. MySQL will use the value set by the Pid-file parameter as the base name for the binary log file. For example, I set the Log-bin file name to Mybinlog. The mybinlog.00000x binary log file will be generated under the D:/wamp/bin/mysql/mysql5.6.17/data folder.

Binary log files such as
Check if the Bin-log log is turned on by using show variables like ' Log_bin '.

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

2. Viewing mysql binary logs is primarily for MySQL internal use. Not for the database administrator to read and use, therefore, binary logs and other logs an important difference is that the format of the binary file is not text format, its content can not be viewed directly through Notepad, in order to facilitate administrator management. MySQL provides the Mysqlbinlog tool to view binary log content.

Analogy: Mysqlbinlog D:\wamp\bin\mysql\mysql5.6.17\data\mybinlog.000003

Running results such as the following:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

Now let's do a test. See if the bin log records my actions to update the database.

For example, I changed the id1 of id2=2 in the datasheet T2 to 5. Then query the binary log file to see if my operation was recorded.

As a result and obviously, the binaries recorded my changes to the database, but also recorded that I was changing the data in that database, as for my query statement. It is not recorded.

3. Deletion of binary logsThe relatively busy system, because the daily generation of a large number of logs, these days are assumed to be unclear (or transferred) for a long time, will be a huge waste of disk space. So. Periodically deleting logs is an important part of the DBA's maintenance of the MySQL database. 1. Via the Reset Master commandRun the Reset Master command, which will delete all the Binlog logs. The new log file number starts at 000001. 2. Via Purege master logs to commandRemove all logs before the ' xxxxxx ' number by running purge master logs to ' base_name.xxxxxx '. Below I will delete all the logs before mybinlog.000003. For example, with:

3. Via purge master logs beffor ' time ' commandrunning purge master logs beffor ' time ' represents the deletion of all logs before ' time '. For example, delete all logs before 2016-04-01 00:00:00. Commands such as the following: Purge master logs beffor '2016-04-01 00:00:00';4. Set the number of references in the config file expire_logs_daysBy setting the number of parameters expire_logs_days=#, to specify the number of days the log expires, after a specified number of days, the log will be deleted voluntarily, this is my preferred way. For example, set Expire_logs_day=3, which represents 3 days after the initiative is deleted.

4. Binary log Important parameter description max_binlog_size: Specifies the maximum value of a single binary log file, assuming that the value is exceeded, resulting in a new binary log file with the suffix name +1. and recorded in the. index file.
binlog_cache_size: Buffer size sync_binlog: Indicates how many times the cache has not been written to sync to disk. Assuming that n is set to 1, the binary file is written in a synchronous write-to-disk manner.

The system default setting in MySQL is sync_binlog=0. That is, do not do whatever mandatory disk refresh instructions. The performance at this time is the best, but the risk is also the biggest. As soon as the system crash. All binlog information in the Binlog_cache will be lost. And when set to "1", it is the most secure, but the most performance loss of the settings. Because when set to 1. Even if the system is crash. There is also a maximum loss of a transaction that is not completed in Binlog_cache, which has no substantive impact on the actual data. binlog-do-db: The days of the database that need to be recorded, the default value is empty, which means that all library logs are synchronized to the binary log.
binlog-ignore-db: Days when you need to ignore which databases
log-slave-update: Configuration of Master-slave database is requiredBinglog_format: Optional values are statement (log logical SQL statements), row (record table row changes), mixed5. Using binary logs for data recoverythe hypothetical data exception was discussed earlier. To restore it to data at some point in time, relying only on binary is often not enough, IWe also need to back up the data before this point in time. for the sake of observation, I have now backed up my database, with data from table T1 such as the following:
from now on, I need to do something about the data, such as updating or inserting, after the operation. T1 data such as the

at this point. Suppose that something very unlucky happened. There was a hacker breaking in. Will my T1 table data all deleted, then how do I get the hacker to delete the previous data?

The first step: I need to restore my data to the data I backed up. Post-restore results such as the following:

Step Two: I need to use my binary log file to restore all data operations from the moment the data was backed up to the moment before the hacker attacked.Run: Mysqlbinlog D:\wamp\bin\mysql\mysql5.6.17\data
\mybinlog.000004Analyze binary logs We are now ' at 637 ', and our data has been hacked by hackers. All we need to do is to restore everything before the at 637 line. Ignore all subsequent actions.

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">
so we can run the following command to recover our data:mysqlbinlog D:\wamp\bin\mysql\mysql5.6.17\data\mybinlog.000004--stop-pos=637|mysql-uroot-p**dequan
and then we'll take a look at the data from our T1 table.
It is always done, but this also suggests that in order to restore the convenience of data, not only need to open the binary log, but also to save data on a regular basis. additions to the binary log:

1. We are also able to see which binary logs are currently available by using the show binary Logs command.

2. We are able to record events through show Binlog event log

Show Binlog events looks at all of the logged event. If you want to query a binary logging event, you can add in+ ' log name ' later. For example, with:

Query Log1. Function description The query log records the clientAll Statements。 Can specify its location by Log=[file_name]. As with other logs, assume that the file_name value is not specified. The log will be written to the DataDir folder, the default file name Division Host_name.log, which has a large impact on system performance. Generally does not open, in this. Not elaborate.
Slow Query Log1. Description of function The slow query log is the SQL statement log that records the full run time over the number of long_query_time (in seconds). In order to obtain a table lock, the waiting time is not counted as running time.

We are able to start the slow query log function via the log-slow-queries=[file_name] option. As with the previous log, assuming that file_name is not specified, the log folder is under the Datedir folder and the default name is Host_name-slow.log. 2. Slow query log read query slow query open status

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">
In the configuration file, such as the following code, open slow query # Open slow query
Slow_query_log=on
Slow_query_log_file=d:/wamp/logs/myslowquery.log
long_query_time=1.5

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">
(some places say through Log-slow-queries=[file_name] to specify the slow query log, but I tried, so start MySQL will error, maybe I am operating under the win system, Or maybe I'm using the Wampserver integrated environment to install MySQL, or may be using a different version number) to view the slow query time settings
Suppose you change the slow query time. Ability to use set long_query_time=1.5;
For ease of viewing effects. We changed the slow query time limit to 0.15. Then we write a time of more than 0.15 SQL, and finally look at the log, whether the SQL statement is logged.

Set the slow query time. and running the corresponding SQL statement

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

I ran a total of 3 SQL statements above, and now let's take a look at the slow query log, such as the following

It simply records the SQL statement in the query that has a longer query time.

MySQL error log, binlog log, query log, slow query log simple introduction