Simple MySQL log operation command _ MySQL

This article mainly introduces the log Operation Commands of MySQL, which focuses on the log deletion methods of MySQL. For more information, see 1. first confirm whether your log is enabled

MySQL>show variables like 'log_bin'; 

If ON is enabled, the log file is in the data directory of the MySQL installation directory.

2. how to know the current log

MySQL> show master status; 

3. use MySQLbinlog to view binary log files

shell>MySQLbinlog mail-bin.000001 

Or

shell>MySQLbinlog mail-bin.000001 | tail 

4. correctly delete MySQL BIN-LOG operations

In mysql, a large number of log files, such as mysq-bin.000001, are generated. these are binary files, if normal logs do not have master-slave configuration, you can directly use the reset master to delete the logs. this method is very simple,
If there is no master-slave replication, you can reset the database logs by reset master to clear the previous log files:

mysql> reset master;

You can also configure it in my. cnf.

expire_logs_days = 3

The number of days that binary logs are automatically deleted. Here, logs is automatically cleared three days ago.

The default value is 0, indicating "not automatically deleted ".

Example

# By File: remove logs before mysql-bin.000354, excluding mysql-bin.000354MYSQL> purge binary logs to 'MySQL-bin.000354'; Query OK, 0 rows affected (0.16 sec) # by time: delete the log MYSQL> purge binary logs before '2017-11-10 00:00:00 '; # by time: check the logs generated three days ago: MYSQL> purge master logs before date_sub (now (), interval 3 day); automatically clear logs: # modify my. cnf file configuration bin-log expiration time expire-logs-days = 7max-binlog-size = 268435456

If you are a master-slave mysql log file, refer to the following method

// Before deleting logs, check the log files currently used by the master and slave servers, // log on to the mysql terminal of the server to delete the log # mysql-u root-pxxxxx // check the status of the replication master server Mysql> show master status + ------------------ + ----------- + ------------ + logs + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + -------------------- + ----------- + ------------------------------------------------ + | mysql-bin.000097 | 541677824 | www | test, mysql, information_schema | + ------------------ + ----------- + -------------- + -------------------------------------- + // Copy the log file currently in use by the master server: mysql-bin.000097 // Check the replication slave server status Mysql> show slave statusG // The replication master server log file that is currently being used by the slave server is: mysql-bin.000103 // the log file currently in use is 000097, and all I need to do is delete all the logs before 00095 (reserved logs for recent days) mysql> purge master logs to 'MySQL-bin.000095; # ll/usr/local/mysql/var // from the results, we found that all logs before 000097 have been deleted.