How to log SQL logs in MySQL

Source: Internet
Author: User
Tags log log ultraedit

SQL Server has a SQL Profiler that can track SQL statements executed by the server in real time, which is useful when debugging errors in many cases. For example: Other people write complex code, production system, no debugging environment, no original code ...

Check the information, My SQL can use the following methods to track SQL statements, the following methods for the Windows platform as an example, Linux is similar:1 Configuring the My.ini file (in the installation directory, under Linux, the file is named My.cnffind the [mysqld] section to increase the configuration of the log, as shown in the following example:[Mysqld]
Log= "C:/temp/mysql.log"
Log_slow_queries= "C:/temp/mysql_slow.log"
long_query_time=1 log indicates the directory where logs are stored;log_slow_queries indicates the log directory of SQL logs with long execution time;Long_query_time Indicates how long the execution time is, Unit S. These configuration items should already exist under Linux, just commented out, and you can remove the annotations. But adding configuration items directly is OK. 2 Restart the MySQL service. Precautions :A Log directory must be present in advance or the log cannot be logged. There's a situation here too. C:/temp directory must already existB log file is the text of the Linux format, it is recommended to open with UltraEdit, converted to DOS format view (otherwise there is no line break, can not understand)The C service cannot delete the log file while it is in the startup state, or the SQL statement cannot be logged. D can not use UltraEdit directly to clear the contents of the file after saving, otherwise it will not be recorded. The service needs to be restarted if UltraEdit has saved. Bak and is logged to this file. E can be saved after clearing the text with Notepad, and the log can continue to be recorded. (Strange, not recommended)

if ($! = jQuery) {$ = Jquery.noconflict ();}

Practical point:

The first window is the environment:

Log under Window
Overview of log file types:
Error logging issues that occur when you start, run, or stop mysqld.
My.ini configuration information:
#Enter a name for the error log file. Otherwise a default name would be used.
Query logging establishes client connections and statements that are executed.
My.ini configuration information:
#Enter a name for the query log file. Otherwise a default name would be used.
#log =d:/mysql_log.txt
The update log records the statement that changes the data. The use of the log is not supported.
My.ini configuration information:
#Enter a name for the update log file. Otherwise a default name would be used.
The binary log records all statements that change data. Also used for replication.
My.ini configuration information:
#Enter a name for the binary log. Otherwise a default name would be used.
Slow logging all queries that have performed longer than long_query_time seconds or queries that do not use indexes.
My.ini configuration information:
#Enter a name for the slow query log file. Otherwise a default name would be used.
#long_query_time =1
#log-slow-queries= D:/mysql_log_slow.txt

Configuration under Linux

Under Linux:
SQL code

1. # Enter in [mysqld]
2. #log
3. Log-error=/usr/local/mysql/log/error.log
4. Log=/usr/local/mysql/log/mysql.log
5. long_query_time=2
6. log-slow-queries=/usr/local/mysql/log/slowquery.log

# Enter #log log-error=/usr/local/mysql/log/error.log log=/usr/local/mysql/log/mysql.log long_query_time=2 in [mysqld] log-slow-queries=/usr/local/mysql/log/slowquery.log

Under Windows:
SQL code

1. # Enter in [mysqld]
2. #log
3. log-error= "E:/progra~1/easyph~1.0b1/mysql/logs/error.log"
4. log= "E:/progra~1/easyph~1.0b1/mysql/logs/mysql.log"
5. long_query_time=2
6. log-slow-queries= "E:/progra~1/easyph~1.0b1/mysql/logs/slowquery.log"

# Enter #log log-error= "E:/progra~1/easyph~1.0b1/mysql/logs/error.log" log= "e:/progra~1/easyph~1.0b1/mysql/in [mysqld] Logs/mysql.log "long_query_time=2 log-slow-queries=" E:/progra~1/easyph~1.0b1/mysql/logs/slowquery.log "

Turn on Slow query
Long_query_time = 2-refers to how long the SQL executed will be log down, here is 2 seconds
log-slow-queries=/usr/local/mysql/log/slowquery.log--Log the query back to a slower statement

Log-queries-not-using-indexes = nouseindex.log--literally, log down without using the indexed query

Log=mylog.log--Record all execution statements

Log storage: By default, when turned on, all logs are stored in the DataDir directory. If you do not specify a name, it will be the name of the host name later. If the host name is Songcomputer, then the related log is the Songcomputer.log file.

MySQL Log off and on:
Use the following command to see if the log is enabled
Mysql>show variables like ' log_% ';

Where value is off, the service is not turned on, to enable only the My.ini configuration information to be written to (My.ini as the MySQL installation directory), and then remove the previous "#"
Restart the MySQL service. OK, you will now see that the specified log file has been created. Conversely, to stop the MySQL log service, simply remove the corresponding configuration information from the My.ini

>>>> corresponding use slow log query

Manually read slow logs and modify slow log times

Show variables like ' long% '
Will get a slow log time

To set the value of the slow log
Set long_query_time = 2;

Focus on the binary file

Binary log:

From the overview I can see that the log-bin of the My.ini configuration information does not specify a file name extension, because even if you specify it on the extension it is not used. When MySQL creates binary log files
, first create a file with a name of "Mysql_log_bin" and a ". Index" suffix, and then create a "mysql_log_bin" name to
". 000001" is the suffix of the file. When the MySQL service restarts a file with a suffix of ". 000001", the suffix name is incremented by 1, and if the log length exceeds
The upper limit of the Max_binlog_size (default is 1G) also creates a new log file, using flush
A new log file is also created by logs (MySQL command) or by performing mysqladmin–u–p flush-logs (Windows command Prompt).
Since the write is binary data, open the file with Notepad is not see the normal data, then how to view it?
Use the Mysqlbinlog command in the bin directory, such as:
Bin>mysqlbinlog d:/mysql_log/mysql_bin.000001
Bin>mysqlbinlog d:/mysql_log/mysql_bin.000002
Bin>mysqlbinlog d:/mysql_log/mysql_bin.000003
Bin>mysqlbinlog d:/mysql_log/mysql_bin.000004
Bin>mysqlbinlog d:/mysql_log/mysql_bin.000005
You can also view the binary file directories created by MySQL using the SQL statement:

Mysql> show master logs;

To view the current binary file status:

Mysql> Show master status;

As for the accurate understanding of log files, but also need readers to read carefully, deep understanding, here is no longer an Austrian!
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.