MySQL log management (III) _ MySQL

MySQL log management (3) reset master;

Purge master logs before '2017-05-22 01:00:00 ';

[Mysqld]

-- Expire_logs_days = 3

------------------------------------------------

1: binary log

Binary logs record all DDL (Data Definition Language) statements and DML (data operation language) statements, but do not record statements that contain data queries. The statement is saved as an "event" and describes the data change process. This log plays an extremely important role in data recovery in the event of a disaster.

2: log location and format

When the-log-bin [= file_name] option is enabled, mysqld writes SQL commands containing all the updated data to the log file. If the file_name value is not given, the default name is followed by _ bin. if the file name is provided but does not contain path strength, the file is written to the directory specified by the DATADIR parameter by default.

3: log reading

Because logs are stored in binary format and cannot be directly read, you need to use mysqlbinlog to view them. The syntax is as follows:

# Mysqlbinlog log_file

4: log Deletion

For busy OLTP systems, due to the large amount of logs produced every day, these logs will be a great waste of disk space if they are not cleaned up for a long time, regular log deletion is an important task for DBA to maintain the Mysql database. The following describes several common methods to delete logs.

(1 ):

Run the "reset master;" command to delete all binary logs. the new log number starts from "000001". The command is as follows:

Mysql> reset master;

(2 ):

Run "Purge master logs to 'MySQL-bin. * *** ', which removes all logs before the *** number, all logs numbered before the mysql-bin.000001

Mysql> purge master logs to 'MySQL-bin.000015;

The result shows that all logs before 000015 have been deleted.

(3 ):

Run the "purge master logs before 'yyyy-mm-dd hh24: min: SS'" command to delete the date "yyyy-mm-dd hh24: mi: all logs generated before "ss" are deleted from the following logs before "01:00:00"

Mysql> purge master logs before '2017-05-22 01:00:00 '';

(4 ):

Set the parameter-expire_logs_days = # (days). This parameter indicates the log expiration days. after the specified number of days, the log is automatically deleted, this will reduce the workload for DBA to manage logs.

# Vi/etc/my. cnf

[Mysqld]

-- Expire_logs_days = 3

In this way, logs generated three days ago will be deleted and automatically deleted by the system.