Mysql log of the correct deletion method detailed _mysql

This paper describes the correct way to delete the MySQL log in detail. Share to everyone for your reference, specific as follows:

1. Find:

Mysql> show binary logs;
+ —————-+ ——— –+
| Log_name | file_size |
+ —————-+ ——— –+
| mysql-bin.000001 | 150462942 |
| mysql-bin.000002 |
| | mysql-bin.000003 | About Us |
+ —————-+ ——— –+

2. Delete Bin-log (before removing mysql-bin.000003 without mysql-bin.000003)

Mysql> purge binary logs to ' mysql-bin.000003 ';
Query OK, 0 rows affected (0.16 sec)

3. Query results (there is only one record now.)

Mysql> show Binlog events/g
*************************** 1. Row ***************************
Log_name: mysql-bin.000003
pos:4
event_type:format_desc
server_id:1 end_log_pos:106 info:server
: 5.1.26-rc-log, Binlog ver:4
1 row in Set (0.01 sec)
(mysql-bin.000001 and mysql-bin.000002 have been deleted)
mysql> Show binary logs;
+ —————-+ ——— –+
| Log_name | file_size |
+ —————-+ ——— –+
| mysql-bin.000003
| + —————-+ ——— –+
1 row in Set (0.00 sec)

(Use of other formatting removed!)

PURGE {MASTER | BINARY} LOGS to ' Log_name '
PURGE {MASTER | BINARY} LOGS before ' date '

Used to delete all binary logs that are listed in the log index before the specified log or date. The logs are also deleted from the list in the log index file, which is the first of the given logs.

For example:

PURGE MASTER LOGS to ' mysql-bin.010′;
PURGE MASTER LOGS before ' 2008-06-22 13:00:00′;

Clear the Binlog 3 days ago.

PURGE MASTER LOGS before Date_sub (now (), INTERVAL 3 day);

The date argument for the before variable can be in ' yyyy-mm-dd hh:mm:ss ' format. Master and binary are synonyms.

If you have an active secondary server that is currently reading one of the logs that you are trying to delete, this statement will not work, but will fail with an error. However, if the secondary server is inactive and you happen to clean up one of the logs that it wants to read, the secondary server cannot replicate after it is started. This statement is safe to run when the secondary server is replicating. You do not need to stop them.

To clean up the log, follow these steps:

1. On each subordinate server, use Show SLAVE status to check which log it is reading.

2. Use show master logs to obtain a series of logs on the primary server.

3. Determine the oldest log in all subordinate servers. This is the target log. If all the subordinate servers are updated, this is the last log on the list.

4. Make a backup of all the logs that you will delete. (This step is optional, but recommended.) ）

5. Clean all the logs, but not the target log.

In Contab settings:

Copy Code code as follows:

0 1 * * * * * mysql-uroot-e ' PURGE MASTER LOGS before Date_sub (now (), INTERVAL 3 day); ' `

More information about MySQL interested readers can view the site topics: "MySQL Log operation skills Daquan", "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL commonly used function large summary"

I hope this article will help you with the MySQL database meter.