MySQL shutdown and delete bin-log log detailed

Turn off Bin-log Log

When the Bin-log log is turned on, many mysql-bin.0000*-like files are generated and may take up a lot of hard disk space. For their own test machine or hard disk more nervous VPS, and do not need to do master,slave configuration, can turn off the log function completely.

The shutdown operation is simple, find the configuration file my.cnf, for Linux, generally by default in the/etc directory, open this file, use the pound (#) to comment out the following two configuration items.

The code is as follows	Copy Code
Log-bin=mysql-bin Binlog_format=mixed

Cases

Path to VI/ETC/MY.CNF or my.cnf

Modify the Log-bin=mysql-bin to #log-bin=mysql-bin

Restart MySQL to take effect.

When the configuration is modified, the MYSQLD service needs to be restarted before it can take effect.

You may receive an error while rebooting, "error 1186 (HY000): Binlog closed, cannot reset Master", the solution is to login to the MySQL command line to execute RESET master. The purpose of Reset master is to delete the previous Bin-log log file. This command is used sparingly in master and slave configuration environments.

Delete Bin-log Log

1. Find out what binary log files are currently available:

The code is as follows

Copy Code

Mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ |   mysql-bin.000001 | 1357315 | |       mysql-bin.000002 | 117 | |    mysql-bin.000003 | 404002 | |   mysql-bin.000004 | 2050722 | |    mysql-bin.000005 | 139103 | |     mysql-bin.000006 | 46702 | |       mysql-bin.000007 | 117 | |        mysql-bin.000008 | 98 | |       mysql-bin.000009 | 117 | |      mysql-bin.000010 | 1254 | |       mysql-bin.000011 | 117 | |  mysql-bin.000012 | 29394942 | |    mysql-bin.000013 | 422100 | |       mysql-bin.000014 | 117 | |       mysql-bin.000015 | 117 | |        mysql-bin.000016 | 98 | |       mysql-bin.000017 | 117 | |       mysql-bin.000018 | 117 | |    mysql-bin.000019 | 285300 | |    mysql-bin.000020 | 181229 | |        mysql-bin.000021 | 98 | +------------------+-----------+ Rows in Set (0.03 sec)

2. Delete Bin-log (delete all binary log files before mysql-bin.000018)

The code is as follows

Copy Code

Mysql> purge binary logs to ' mysql-bin.000018 '; Query OK, 0 rows affected (0.08 sec) Mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ |       mysql-bin.000018 | 117 | |    mysql-bin.000019 | 285300 | |    mysql-bin.000020 | 181229 | |        mysql-bin.000021 | 98 | +------------------+-----------+ 4 rows in Set (0.00 sec) Mysql> Show Binlog events; +------------------+-----+-------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ |   mysql-bin.000018 | 4 |         Format_desc |          1 | 98 | Server ver:5.0.45-log, Binlog ver:4 | |  mysql-bin.000018 | 98 |         Stop |         1 |                                       117 | | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ 2 rows in Set (0.01 sec)