Logs in MySQL

Actually:

First, use Windows as the environment:

Logs in Windows

Overview of log file types:
1.
The error log records the problems 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 will be used.
# Log-error = D:/mysql_log_err.txt
2.
Query the Client Connection established by log records and the statements executed.
My. ini configuration information:
# Enter a name for the query log file. Otherwise a default name will be used.
# Log = D:/mysql_log.txt
3.
Statement used to update log records and change data. This log is not supported.
My. ini configuration information:
# Enter a name for the update log file. Otherwise a default name will be used.
# Log-update = D:/mysql_log_update.txt
4.
Binary logs record all statements for changing data. It is also used for replication.
My. ini configuration information:
# Enter a name for the binary log. Otherwise a default name will be used.
# Log-bin = D:/mysql_log_bin
5.
Slow logs record all queries whose execution time exceeds long_query_time seconds or where no index is used.
My. ini configuration information:
# Enter a name for the slow query log file. Otherwise a default name will be used.
# Long_query_time =1
# Log-Slow-queries = D:/mysql_log_slow.txt

 

 

 

Configuration in Linux

 

Code

In Linux:
SQL code

1. # Merge 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

# Commit in [mysqld] # log-error =/usr/local/MySQL/log/error. log =/usr/local/MySQL/log/MySQL. log long_query_time =2Log-Slow-queries =/usr/local/MySQL/log/slowquery. Log

Windows:
SQL code

1. # Merge 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"

# Commit in [mysqld] # log-error = "E:/progra ~1/Easyph ~1. 0b1/MySQL/logs/error. log "log =" E:/progra ~1/Easyph ~1. 0b1/MySQL/logs/MySQL. log "long_query_time =2Log-Slow-queries = "E:/progra ~1/Easyph ~1. 0b1/MySQL/logs/slowquery. log"

Enable slow Query
Long_query_time =2-- Indicates how long the SQL statement will be logged after execution, which is 2 seconds.
Log-Slow-queries =/usr/local/MySQL/log/slowquery. log -- Records slow statements returned by the query.

Log-queries-not-using-indexes = nouseindex. log -- it is a literal meaning that the query with no index is used in the log.

Log = mylog. log -- Record all execution statements

 

 

 

Log storage: by default, When enabled, all logs are stored in the datadir directory. if no name is specified, it will later host name. if the host name is songcomputer, the related log is songcomputer. log File.

 

 

Close and enable MySQL logs:

Run the following command to check whether logs are enabled:

· Mysql> show variables like 'Log _ % ';

 

 

If the value is off, the service is not enabled. write ini configuration information (my. INI is in the MySQL installation directory), and then remove the preceding "#"
And then restart the MySQL service. OK. The specified log file has been created. To stop the MySQL Log service, remove the corresponding configuration information in my. ini, that is
Yes.

 

 

>>>> Query slow logs

 

Manually read and modify slow logs

 

Show variables like 'long %'

Will get the slow log time

 

Set the slow log value

Set long_query_time =2; 

 

 

 

 

Focusing on binary files

 

Binary log:

From the overview, I can see that the log-bin of my. ini configuration information does not specify the file extension, because it is not used even if you specify the extension. When MySQL creates a binary log file
Create a file named "mysql_log_bin" and suffixed with ". Index". Then create a file named "mysql_log_bin"
". 000001" is a suffix. When the MySQL service restarts, one file with the suffix ". 000001" is added, and the suffix name is incremented by 1. If the log length exceeds
Max_binlog_size (1 GB by default) also creates a new log file. Use flush
Logs (MySQL Command) or execute mysqladmin-u-p flush-logs (Windows Command Prompt) will also create a new log file.

Since binary data is written, normal data cannot be seen when you open a file in Notepad. How can we view it?

Use the mysqlbinlog command in the bin directory, for example:

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 use SQL statements to view the binary file directory created by MySQL:

 

· Mysql> show Master logs;

 

View the status of the current binary file:

 

· Mysql> show Master status;

 

As for the accurate understanding of log files, you need to carefully read and deeply understand the log files. I will not describe them here!

Actually:

First, use Windows as the environment:

Logs in Windows

Overview of log file types:
1.
The error log records the problems 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 will be used.
# Log-error = D:/mysql_log_err.txt
2.
Query the Client Connection established by log records and the statements executed.
My. ini configuration information:
# Enter a name for the query log file. Otherwise a default name will be used.
# Log = D:/mysql_log.txt
3.
Statement used to update log records and change data. This log is not supported.
My. ini configuration information:
# Enter a name for the update log file. Otherwise a default name will be used.
# Log-update = D:/mysql_log_update.txt
4.
Binary logs record all statements for changing data. It is also used for replication.
My. ini configuration information:
# Enter a name for the binary log. Otherwise a default name will be used.
# Log-bin = D:/mysql_log_bin
5.
Slow logs record all queries whose execution time exceeds long_query_time seconds or where no index is used.
My. ini configuration information:
# Enter a name for the slow query log file. Otherwise a default name will be used.
# Long_query_time =1
# Log-Slow-queries = D:/mysql_log_slow.txt

 

 

 

Configuration in Linux

 

Code

In Linux:
SQL code

1. # Merge 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

# Commit in [mysqld] # log-error =/usr/local/MySQL/log/error. log =/usr/local/MySQL/log/MySQL. log long_query_time =2Log-Slow-queries =/usr/local/MySQL/log/slowquery. Log

Windows:
SQL code

1. # Merge 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"

# Commit in [mysqld] # log-error = "E:/progra ~1/Easyph ~1. 0b1/MySQL/logs/error. log "log =" E:/progra ~1/Easyph ~1. 0b1/MySQL/logs/MySQL. log "long_query_time =2Log-Slow-queries = "E:/progra ~1/Easyph ~1. 0b1/MySQL/logs/slowquery. log"

Enable slow Query
Long_query_time =2-- Indicates how long the SQL statement will be logged after execution, which is 2 seconds.
Log-Slow-queries =/usr/local/MySQL/log/slowquery. log -- Records slow statements returned by the query.

Log-queries-not-using-indexes = nouseindex. log -- it is a literal meaning that the query with no index is used in the log.

Log = mylog. log -- Record all execution statements

 

 

 

Log storage: by default, When enabled, all logs are stored in the datadir directory. if no name is specified, it will later host name. if the host name is songcomputer, the related log is songcomputer. log File.

 

 

Close and enable MySQL logs:

Run the following command to check whether logs are enabled:

· Mysql> show variables like 'Log _ % ';

 

 

If the value is off, the service is not enabled. write ini configuration information (my. INI is in the MySQL installation directory), and then remove the preceding "#"
And then restart the MySQL service. OK. The specified log file has been created. To stop the MySQL Log service, remove the corresponding configuration information in my. ini, that is
Yes.

 

 

>>>> Query slow logs

 

Manually read and modify slow logs

 

Show variables like 'long %'

Will get the slow log time

 

Set the slow log value

Set long_query_time =2; 

 

 

 

 

Focusing on binary files

 

Binary log:

From the overview, I can see that the log-bin of my. ini configuration information does not specify the file extension, because it is not used even if you specify the extension. When MySQL creates a binary log file
Create a file named "mysql_log_bin" and suffixed with ". Index". Then create a file named "mysql_log_bin"
". 000001" is a suffix. When the MySQL service restarts, one file with the suffix ". 000001" is added, and the suffix name is incremented by 1. If the log length exceeds
Max_binlog_size (1 GB by default) also creates a new log file. Use flush
Logs (MySQL Command) or execute mysqladmin-u-p flush-logs (Windows Command Prompt) will also create a new log file.

Since binary data is written, normal data cannot be seen when you open a file in Notepad. How can we view it?

Use the mysqlbinlog command in the bin directory, for example:

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 use SQL statements to view the binary file directory created by MySQL:

 

· Mysql> show Master logs;

 

View the status of the current binary file:

 

· Mysql> show Master status;

 

As for the accurate understanding of log files, you need to carefully read and deeply understand the log files. I will not describe them here!