Getting started with MySQL is simple-learning notes-Chapter 17th MySQL logs

Document directory

• 17.2.1 start and set binary logs
• 17.2.2. View binary logs
• 17.2.3 Delete binary logs
• 17.2.4. Restore the database using binary logs
• 17.2.5. temporarily disable the binary log function.
• 17.3.1. Start and set error logs
• 17.3.2. View error logs
• 17.3.3 delete error logs
• 17.4.1. Start and set general query logs.
• 17.4.2. View error logs
• 17.4.3. Delete common query logs.
• 17.5.1. Start and set slow query logs.
• 17.5.2. View slow query logs.
• 17.5.3 Delete slow query logs

17.1 log Introduction

Binary log

Error Log

General query log

Slow query log

 

17.2. binary log

Binary logs, also known as update logs, are used to record database changes. The binary log can be used to query the changes made in the MySQL database.

17.2.1 start and set binary logs

Disabled by default

# My. CNF (in Linux) or my. ini (in Windows)

[Mysqld]

Log-bin [= dir \ [filename]

 

Dir and filename can be unspecified, by default hostname-bin.number, while generating hostname-bin.index files

17.2.2. View binary logs

Mysqlbinlog filename. Number

 

17.2.3 Delete binary logs

1. Delete all binary logs

Reset master;

2. Delete binary logs by number

Purgemaster logs to 'filename. number'

Clear all binary files numbered less than number

3. Delete binary logs based on the Creation Time

Purgemaster logs to 'yyyy-mm-dd hh: mm: ss'

Delete

 

17.2.4. Restore the database using binary logs

Mysqlbinlog filename. Number | mysql-u root-P

Small number first restored

 

17.2.5. temporarily disable the binary log function.

Set SQL _log_bin = 0

17.3 Error Log

Error logs are the most commonly used logs in MySQL databases. Error logs are mainly used to record the enabling, disabling, and error information of the MySQL service.

17.3.1. Start and set error logs

The error log is enabled by default and cannot be disabled.

By default, error logs are stored in the data folder of the MySQL database. The error log file is usually named hostname. Err. Hostname indicates the Host Name of the MySQL server. The storage location of error logs can be set using the log-error option. Add the log-error option to the [mysqld] group of my. ini or my. CNF file. The format is as follows:

# My. CNF (in Linux) or my. ini (in Windows)

[Mysqld]

Log-error = DIR/[filename]

 

17.3.2. View error logs

Text editing/Viewer

 

17.3.3 delete error logs

In the MySQL database, you can use the mysqladmin command to enable new error logs. The syntax of the mysqladmin command is as follows:

Mysqladmin-u root-P flush-logs

After the command is executed, the database system automatically creates a new error log. The old error log is retained, but it has been renamed filename. Err-old.

 

 

17.4 general query logs

Common query logs are used to record all user operations, including starting and disabling MySQL services, update statements, and query statements.

17.4.1. Start and set general query logs.

By default, the general log query function is disabled.

You can use the log option of the my. CNF or my. ini file to enable common query logs. Add the log option to the [mysqld] group of my. CNF or my. ini file. The format is as follows:

# My. CNF (in Linux) or my. ini (in Windows)

[Mysqld]

Log [= dir \ [filename]

 

17.4.2. View error logs

Text editing/Viewer

 

17.4.3. Delete common query logs.

You can use the mysqladmin command to enable the new general query log. The new general query log directly overwrites the old query log and does not need to be deleted manually. The syntax of the mysqladmin command is as follows:

Mysqladmin-u root-P flush-logs

 

17.5 slow query logs

Slow query logs are used to record query statements whose execution time exceeds the specified time. Through slow query logs, you can find out which query statements have low execution efficiency for optimization.

 

17.5.1. Start and set slow query logs.

By default, the slow query log function is disabled.

You can use the log-Slow-queries option of the my. CNF or my. ini file to enable slow query logs. Use the long_query_time option to set the time value, in seconds. If the query time exceeds this time value, the query statement will be recorded in the slow query log. Add the log-Slow-queries option and long_query_time option to the [mysqld] group of my. CNF or my. ini file. The format is as follows:

# My. CNF (in Linux) or my. ini (in Windows)

[Mysqld]

Log-Slow-queries [= dir \ [filename]

Long_query_time = N

 

17.5.2. View slow query logs.

Text editing/Viewer

 

17.5.3 Delete slow query logs

The method for deleting slow query logs is the same as that for deleting common query logs. You can use the mysqladmin command to delete an object. You can also manually delete it. The syntax of the mysqladmin command is as follows:

Mysqladmin-u root-P flush-logs

After the command is executed, the command line prompts you to enter the password. After the correct password is entered, the deletion operation will be performed. The new slow query log directly overwrites the old query log and does not need to be deleted manually. The database administrator can also manually delete slow query logs. After deletion, restart the MySQL service. After restart, a new slow query log is generated. If you want to back up the old slow query log file, you can change the name of the old log file. Then restart the MySQL service.

 

17.6 Summary

Log Type	Configuration My. CNF or my. ini	Default Start	View	Delete
Binary Logs	[Mysqld] Log-bin [= dir \ [filename]	No	Mysqlbinlog Filename. Number	Reset master; Purge master logs 'Filename. number' Purge master logs 'Yyyy-mm-dd hh: mm: ss'
Error Logs	[Mysqld] Log-error [= dir \ [filename]	Yes	Text View/Editor	Mysqladmin-uroot-P flush-logs
General Query Logs	[Mysqld] Log [= dir \ [filename]	No	Text View/Editor	Mysqladmin-uroot-P flush-logs
Slow Query Logs	Log-Slow-queries [= dir \ [filename] Long_query_time = N	No	Text View/Editor	Mysqladmin-uroot-P flush-logs