MySQL database log management and maintenance _ MySQL

MySQL database log management and maintenance bitsCN.com

MySQL databases mainly have five types of logs: log-slow-queries, binary log (log-bin), and error log (log-error ), query Logs and update logs ). In the new MySQL version, the update log has been canceled. you can use binary logs to replace this function. This article focuses on slow query logs and binary logs.

Slow query log (log-slow-queries)

MySQL slow query refers to a query that consumes a large amount of time or does not use an index. MySQL can record both cases at the same time:

# Slow log storage path
Log-slow-queries = slow. log

# Queries over 2 seconds
Long_query_time = 2

# Queries without Indexes
Log-queries-not-using-indexes binary log (log-bin)

In MySQL, if binary logging is enabled, MySQL changes are recorded in the log file:
Mysql> show variables like 'log _ bin ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Log_bin | ON |
+ --------------- + ------- +
1 row in set

If you want to disable the bin log record, you can modify the configuration file my. ini, comment out the log-bin line, and restart the mysql service.

View the database bin log:
Mysql> show binary logs;
+ ------------------ + ----------- +
| Log_name | File_size |
+ ------------------ + ----------- +
| Mysql-bin.000001 | 946525 |
+ ------------------ + ----------- +
1 row in set

If there is no master-slave replication, you can reset the database logs and clear the previous log files in the following ways:
Mysql> reset master;
Query OK, 0 rows affected

However, if there is a replication relationship, PURGE should be used to clear the bin log:
Syntax:
Purge master logs to 'log _ name ';
Purge master logs before 'date ';

Deletes all binary logs listed in the log index before the specified log or date. These logs will also be deleted from the list of records in the log index file.

For example:
Purge master logs to 'MySQL-bin.010 ';
Purge master logs before '2017-06-23 15:00:00 ';

Clear binlog 3 days ago
Purge master logs before DATE_SUB (NOW (), INTERVAL 3 DAY); the date argument for the BEFORE variable can be in the 'yyyy-MM-DD hh: mm: SS' format.

If you have master-slave replication, pay attention to the following issues:

1. when the slave server is active and just reading one of the logs you are trying to delete, executing this command will not work, but will trigger an error.

2. the slave server is stopped. if you happen to clear one of the logs you want to read, the slave server cannot be copied after it is started.

3. the slave server is active and does not read the logs you are trying to delete. This command is safe and does not need to stop working on the slave server when executing this command.

To clear logs, follow these steps:

1. use show slave status on each SLAVE server to check which log it is reading.

2. use show master logs to obtain a series of LOGS on the MASTER server.

3. identify the earliest logs among all slave servers. This is the target log. If all slave servers are updated, this is the last log in the list.

4. back up all logs you want to delete. (This step is optional, but is recommended .)

5. clear all the logs, but not the target logs, because the slave server needs to synchronize with it

BitsCN.com