My MySQL learning experience (15) Log

This article "My MySQL Learning experience (15)" will explain the MySQL log

The logs in MySQL are divided into 4 categories, and using these log files, you can see what's happening inside MySQL.

respectively is

1, error log: Log the MySQL service start, run, stop the MySQL service problems occurred error log

2. Query log: Record the established client connection and execute the statement gerenal log

3. Binary log: A statement that records all change data and can be used for data replication binary log

4. Slow query log: Records all queries with execution time exceeding long_query_time or queries that do not use indexes slow log

By default, all logs are created in the MySQL data directory. By refreshing the log, you can force MySQL to close and reopen the log file (or, in some cases, switch to

A new log). When you execute a FLUSH logs statement or perform mysqladmin flush-logs or mysqladmin refresh, the log is refreshed

If you use the MySQL replication feature, more log files can be maintained on the replication server, which is called the replacement log

Other logging capabilities can degrade the performance of MySQL databases. For example, in a MySQL database system with very frequent queries, if the common query log and the slow query log are turned on,

The MySQL database will take a lot of time to log. At the same time, the log consumes a lot of disk space

Binary log

Binary log is what we often say Binlog, the main record of MySQL database changes.

Binary logs are in a valid format and are transaction-safe in a way that contains all the information that is available in the update log.

The binary log contains information about the execution time of each statement that updates the database. He does not contain statements that do not modify any data, such as a SELECT statement

The maximum purpose of using the binary log is to recover the database most likely because the binary log contains all the updates made after the backup

1. Start and set up binary log

By default, the binary log is turned off, and you can start and set up the binary log by modifying the MySQL configuration file

There are several settings in the My.ini [mysqld] group that are related to the binary log:

log-bin[=PATH/[FILENAME]]
expire_logs_days=10
max_binlog_size=100M

Log-bin defines the binary log, path indicates the directory path where the log file resides;

filename Specifies the name of the log file, such as the full name of the file is filename.0001,filename.0002, etc.

In addition to the above file, there is a file that becomes Filename.index, the file content is a list of all logs, you can use Notepad to open the file

Filename.index the contents of the file, Joe is my computer name and there is currently only one Binlog file:. \joe-bin.000001

. \joe-bin.000001

Expire_logs_days defines the time that MySQL clears an expired log, that is, the number of days that the binary log is automatically deleted.

The default value is 0, which means "no automatic deletion". The file may be deleted when MySQL starts or refreshes the binary log

Max_binlog_size defines the size limit for a single file, and if the content size written by the binary log exceeds the given value, the log will scroll

(Close the current file and reopen a new log file.) You cannot set this variable to be greater than 1GB or less than 4096 bytes. The default value is 1GB

If you are using large transactions, the binary log file size may also exceed the defined size of max_binlog_size.

In the My.ini configuration file, under the [Mysqld] group, add the following parameters and parameter values

[mysqld]
log-bin
expire_logs_days=10
max_binlog_size=100M

Once added, close and restart the MySQL service process to open the binary log and then use the show variables statement to query the log settings

To view log settings using the show VARIABLES statement

Show VARIABLES like  '%log_% ';

You can see that the Log_bin is on,max_binlog_size to 104857600 bytes, and the conversion to MB is 100MB.

After MySQL restarts, you can see that the newly generated file suffix is. 000001 and. Index two files, the file name defaults to the host name

If you want to change the directory location of the log file, you can modify the Log-bin parameter in My.ini

For example:

[mysqld]log-bin= "D:\mysql\log\binlog"

After you close and restart the MySQL service, the new binary log will appear under the "D:\mysql\log\binlog" path

Tip: The database file should not be placed on the same disk as the log file, so that when the database file is damaged, you can use the log to recover the data

2. View the binary log

MySQL binary logs are often used. When MySQL creates a binary log file, it first creates a file with the name filename and the index suffix;

Then create a file with the filename name, with ". 000001" as the suffix. When the MySQL service restarts, a file with the suffix ". 000001" is incremented by one,

The suffix is incremented by 1, and a new log file is created if the log length exceeds the upper limit of max_binlog_size (1GB by default)

The show binary logs statement can view the current number of binary log files and file names. MySQL binary logs cannot be viewed directly, if you want to view the log contents,

Can be viewed through the Mysqlbinlog command

To view the number of binary log files and file names using the show binary logs statement

SHOW BINARY LOGS;

As you can see, there are currently two binary log files, because I restarted the MySQL service and the number of log files was the same as the number of MySQL services started.

Each time the MySQL service is started, a new log file will be generated

Viewing binary logs using Mysqlbinlog

Mysqlbinlog is a separate exe that needs to be executed on the command line we export the contents of the Binlog file to Binlog.txt

Mysqlbinlog  "D:\Program Files (x86) \mysql\mysql server5.5\data\joe-bin.000002" >c:\binlog.txt

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140731  7:49:30 server id 1  end_log_pos 107     Start: binlog v 4, server v 5.5.20-log created 140731  7:49:30 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG ‘
ioTZUw8BAAAAZwAAAGsAAAABAAQANS41LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACKhNlTEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
‘/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET [email protected]_COMPLETION_TYPE*/;

3. Delete binary log

MySQL binary logs can be configured for automatic deletion, while MySQL also provides a secure way to manually delete binary logs

Delete all binary log files using reset MASTER;

RESET MASTER;

Execute the statement, all binary logs will be deleted, MySQL will re-create the binary log, the new log file extension should be numbered again starting with 000001

Only delete part of the binary log files using purge MASTER LOGS;

PURGE MASTER LOGS;

The syntax is as follows

PURGE {MASTER | BINARY} LOGS to ' Log_name ' PURGE {MASTER | BINARY} LOGS before ' date '

The first method specifies the file name, and executing the command deletes all log files with a file name number that is smaller than the specified file name

The second method specifies a date, and executing the command deletes all log files before the specified date

Use purge MASTER LOGS; Delete all log files that were created earlier than binlog.000003

First of all, in order to demonstrate the statement operation process, to prepare multiple log files, the reader can perform multiple restarts of the MySQL service

For example, there are 10 log files

Execute Delete command

PURGE MASTER LOGS to "joe-bin.000003";

When execution is complete, use show binary logs; view binary log

You can see that joe-bin.000001 and joe-bin.000002 two log files were deleted.

Use PURGE MASTER LOGS to delete all log files created before March 30, 2013, execute the following command

PURGE MASTER LOGS before ' 20130330 '

After execution, the log files before March 30, 2013 are deleted, but the March 30, 2013 log is retained

4. Check the operation record in binary log

Show Binlog events;

For example, if you want to see the records in a binary log, but do not want to use Mysqlbinlog, you can use show Binlog events

For example, I want to see the contents of the ' joe-bin.000006 ' Binlog file, execute the following command

Show Binlog events in ' joe-bin.000006 ';

The contents are as follows

Log_name: joe-bin.000006
Pos: 202 
Event_type: Query 
Server_id: 1 
End_log_pos: 304 
Info: use `test`; insert into bin(name) values (‘orange‘) 

You can see what SQL commands the ' joe-bin.000006 ' this binlog file records

If you want to know when the Binlog file was created, you need to mysqlbinlog the tool to see

C:\ProgramData\MySQL\MySQL Server 5.5\data>mysqlbinlog mysql_bin.000001 
/*!40019 SET @@session.max_insert_delayed_threads=0*/; 
/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 
DELIMITER /*!*/; 
# at 4 
#131015 16:35:56 server id 1  end_log_pos 106   

Where 131015 is the log creation time, which is October 15, 2013

5. Restore the database using the binary log

If the MySQL server has binary logging enabled, you can use the Mysqlbinlog tool to start at a specified point in time when data is unexpectedly lost in the database

(for example, the last backup) until now, or another specified point in time, to recover data from the log

To recover data from a binary log, you need to know the path and file name of the current binary log file. Typically from a configuration file (i.e. my.cnf or My.ini, the file name depends on the MySQL

The server's operating system) find the path

Mysqlbinlog the syntax for recovering data is as follows:

mysqlbinlog [option] filename |mysql-uuser-ppass

option is optional and filename is the log file name

The more important two-pair option parameter is

--start-datetime 、--Stop-datetime

--start-position 、--stop--position

--start-date 、--Stop-date can specify the starting point and end point of the recovery database

--start-position 、--Stop--position can specify the starting and ending positions of the recovery data

Use Mysqlbinlog to restore the MySQL database to the July 2, 2014 15:27:48 state, execute the following command

Mysqlbinlog--stop-datetime= "2014-7-2 15:27:48" D:\mysql\log\binlog\binlog.000008 |mysql-u user-p Password

After the command executes successfully, all operations before 15:27:48 July 2, 2014 are restored according to the binlog.000008 log file.

This method is more effective for deleting data that is mistakenly manipulated.

6. Temporarily stop the binary log

If the configuration file configuration in MySQL starts the binary log, MySQL will always record the binary log, modify the configuration file, can stop the binary log,

However, the MySQL database needs to be restarted. MySQL provides the ability to temporarily stop the binary log. The SET sql_log_bin statement allows MySQL to pause or start the binary log

The syntax is as follows

SET Sql_log_bin={0|1}

Execute the following statement to pause the binary log

SET sql_log_bin=0;

Execute the following statement to restore the record binary log

SET sql_log_bin=1;

In fact, the Binlog file is a bit like the LDF file of SQL Server, everyone has saved the database operation log, can be based on this log to recover the database

But there are different, MySQL Binlog can not be opened, because the MySQL redo log is placed in the Ib_logfile file, and the undo log with the data files are put together

So this is very different from SQL Server.

When copying, MySQL must turn on the Binlog function, slave read binlog, and SQL Server's subscribers read the LDF file on the publisher

So just said: The Binlog file is somewhat similar to the LDF file of SQL Server

Error log

The error log file contains information about when Mysqld starts and stops, and when the server is running with any serious errors.

In MySQL, the error log is also very important, MySQL will start and stop the database information and some error messages logged in the error log

1. Starting and setting error logs

By default, the error log is logged to the database's data directory. If you do not specify a file name in the configuration file, the file name defaults to Hostname.err.

For example: MySQL is hosting a server host named Mysql-db, and the file name for logging error messages is Mysql-db.err. If flush LOGS is executed, the error log file is reloaded

The start and stop of the error log, as well as the log file name, can be configured by modifying My.ini (or MY.CNF). The configuration entry for the error log is log-error.

Under [mysqld], configure Log-error to start the error log. If you need to specify a file name, the configuration is as follows:

[Mysqld]log-error=[path/[file_name]]

Path is the directory path where the log files are located, and filename is the log file name. After modifying the configuration item, the MySQL service needs to be restarted before it takes effect

2. Check the error log

The error log can monitor the running state of the system, so as to detect faults and repair faults in time. The MySQL error log is stored as a text file, and you can use a text editor to directly

View MySQL error log

If you do not know the log file's storage path, you can use show variables to view the storage path for the error log.

The statements are as follows

Show variables like ' log_error ';

Use Notepad to view MySQL error logs

View the path to the error log through the statement above show variables like ' Log_error ', and then open the file with Notepad

We can see the error log contents as follows

View Code

3. Delete error log

The MySQL error log is stored as a text file in the file system and can be deleted directly

For previous versions of mysql5.5.7, flush logs can rename the error log file to Filename.err_old,

and create a new log file. But starting with mysql5.5.7, flush logs just re-opens the log file, does not do log backups and create operations.

If the log file does not exist, a new log file is created when MySQL starts or executes flush logs

After you delete the error log file in the running state, MySQL does not automatically create the log file. Flush logs When the log is reloaded, if the file does not exist,

is created automatically. So after deleting the error log, if you need to rebuild the log file, you need to execute the following command on the server side:

Mysqladmin-u root-p Flush-logs

or log in to the MySQL database at the client, execute the FLUSH logs statement

Flush logs;

Delete the Err file and rebuild the Log-error file with the Flush logs statement

Delete files manually

Manually execute flush logs;, err file restored

Open the Err file, there's nothing inside

General Query Log

The general query log records all user actions for MySQL, including starting and shutting down services, executing queries and updating statements, etc.

1. Start and set up the general query log

The MySQL server does not turn on the universal query log by default. If you need a generic query log, you can modify the My.ini or my.cnf configuration file to

Open. Add the Log option under the [mysqld] group of My.ini or MY.CNF

form as follows

[Mysqld]log[=path/[filename]]

Path is the directory path where the log files are located, and filename is the log file name. If you do not specify a directory and file name, the Universal query log is stored by default in the MySQL data directory.

The Hostname.log file. Hostname is the host name of the MySQL database

This adds the option log below [mysqld] and does not specify a parameter value later

[Mysqld]log

2. View the General query log

All operations used are logged in the Universal query log. You can see what the user is doing with MySQL by looking at the general query log. The General query log is

stored in a file system as a text file, you can use a text editor to open a common log file directly for viewing, and you can use Notepad under Windows

Vim, gedit, etc. can be used under Linux

Use Notepad to view the MySQL Universal query log

The file contents are as follows

E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.19-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
140801 23:39:33        1 Connect    [email protected] on 
            1 Query    SHOW VARIABLES
            1 Query    SHOW WARNINGS
            1 Query    select timediff( curtime(), utc_time() )
            1 Query    SHOW COLLATION
            1 Query    SET NAMES utf8
            1 Query    SET character_set_results=NULL
            1 Query    SELECT * FROM `emp`
140801 23:39:44        1 Query    SELECT * FROM `emp`
            1 Query    SELECT * FROM `emp`
140801 23:39:55        1 Query    USE test;

SELECT * FROM `emp`
            1 Init DB    test

You can see the MySQL boot information and the user root connection server and the record that executes the query statement

3. Delete the general query log

The universal query log is stored as a text file in the file system. General query logging all actions of the user

Therefore, in the case of user query, update frequently, the general query log will grow fast. DBAs can periodically delete older, common logs to conserve disk space

You can delete a common query log by deleting the log file directly. To reestablish a new log file, you can use the statement

Mysqladmin-flush logs

Delete log file directly

Execute Flush Logs

Log file was regenerated

Slow query log

A slow query log is a day that records queries longer than a specified time. The slow query log is primarily used to record query statements that have a long execution time

By slowly querying the log, you can find statements that take longer to execute, perform less efficiently, and then optimize

1. Start and set slow query log

In MySQL, the slow query log is turned off by default and can be opened via the log-slow-queries option in the config file My.ini or my.cnf, or in the MySQL service

Start with--log--slow-queries[=file_name] to start the slow query log. When you start a slow query log, you need to My.ini or my.cnf files

Configure the Long_query_time option to specify the logging threshold, if the query time of a query statement exceeds this value, the query process will be logged to the slow query log

File.

To turn on the slow query log in the My.ini or my.cnf file, configure the following:

[mysqld]

log-slow-queries[=path/[filename]]
long_query_time=n

Path is the directory path where the log files are located, and filename is the log file name. If you do not specify a directory and file name, it is stored in the data directory by default

The file name Hostname-slow.log,hostname is the host name of the MySQL server. The parameter n is the time value, in seconds.

If the Long-query_time option is not set, the default time is 10 seconds

Turn on slow query log

[mysqld]
log-slow-queries
long_query_time=1

2. View slow query log

MySQL's slow query log is stored as text and can be viewed directly using a text editor. In the slow query log, a query statement that takes a long time to execute is recorded.

Users can get less efficient query statements from the slow query log, which provides an important basis for query optimization.

View some parameters of the slow query log

Show variables like '%slow% ';

To view the contents of the slow query log file, open the Win7u-20130414z-slow.log file in the data directory using a text editor

E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.19-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
# Time: 140802  0:02:29
# [email protected]: root[root] @ localhost [::1]
# Query_time: 7.578125  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1406908949;
SELECT BENCHMARK (10000000,PASSWORD (‘newpwd‘));

You can see a slow query log recorded here. The account executing the statement is root @ localhost

Query time is query_time:7.578125 seconds

The query statement is SELECT BENCHMARK (10000000,password (' newpwd '));

The statement query time significantly exceeds the set value of 1 seconds, so it is logged in the slow query log file

Introduction to the benchmark function: http://database.51cto.com/art/201010/229366.htm

3. Delete Slow query log

As with the general query log, the slow query log can also be deleted directly. After deletion, without restarting the server, you need to perform a

Mysqladmin-u root-p Flush Logs

Rebuild the log file, or log on to the server to execute flush logs;

The official MySQL slow query log here has a flaw, that is, the query threshold can only be 1 seconds or more, if you want to set less than one second can do nothing

At this point, if you want to find the slow query SQL less than 1 seconds, you can use the Microslow-patch provided by Percona to break the limit and reduce the slow query time threshold to the millisecond level

Which logs should be opened normally

Logs can affect both MySQL performance and disk space. Therefore, if it is not necessary, you should open the log as little as possible.

Consider opening different logs depending on the environment you are using.

For example, a statement that optimizes query efficiency in the development environment can turn on slow query logging , or some SQL execution in the production environment is particularly slow or can be turned on

If the disk space is not sufficient, it can be turned on during peak hours, and then close the slow query log after capturing a slow SQL query

If you need to set up a replication environment, then you must open the binary log , if the data is particularly important also recommended to open the binary log, so that the database can be corrupted by the binary log

Salvage part of the data

General logs in either case, it is generally not recommended to open

Summarize

This article simply elaborated the MySQL log surface content, the MySQL log system is still quite perfect, hoped this article has the help to everybody

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o

2014-11-27 Supplemental Write transaction log process

My MySQL learning experience (15) Log