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 |