MySQL method to automatically clean binlog logs

Instructions:

Open MySQL If the binlog server is not set to automatically clean the logs, the binlog logs will be retained by default. Over time, the server disk space will be filled up by the binlog logs, causing an error in the MySQL database.

Use the following method to safely clear binlog logs

1. Clean the logs without master-slave synchronization

mysql -uroot -p123456 -e 'PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ),INTERVAL 5 DAY)';

#mysql Regularly clear the binlog from 5 days ago

mysql -u root -p #Enter the mysql console

reset master; Log

1, mysql -u root -p #Enter the slave server mysql console

show slave statusG; #Check which log is being read from the slave server. There are multiple slave servers. Select the earliest one as the target log.

2. Enter the master server mysql console

show master log; #Get a series of logs on the master server

PURGE MASTER LOGS TO 'binlog.000058'; #Delete the ones before binlog.000005, excluding binlog.000058

PURGE MASTER LOGS BEFORE '2016-06-22 13:00:00'; #Clear binlog logs before 2016-06-22 13:00:00
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); #Clear binlog logs 3 days ago

Three. Set up automatic cleaning of MySQL binlog logs

vi /etc/my.cnf #Edit configuration

expire_logs_days = 15 #自动删除15天前的日志。默认值为0，表示从不删除。
log-bin=mysql-bin #注释掉之后，会关闭binlog日志
binlog_format=mixed #注释掉之后，会关闭binlog日志

:wq! # Save and exit

Extended reading:

mysql> help purge;

Name: 'PURGE BINARY LOGS'

Description:

Syntax:

PURGE { BINARY | MASTER } LOGS

{ TO 'log_name' | BEFORE datetime_ expr }

The binary log is a set of files that contain information about data

modifications made by the MySQL server. The log consists of a set of

binary log files, plus an index file (see

http:// dev.mysql.com/doc/refman/5.5/en/binary-log.html).

The PURGE BINARY LOGS statement deletes all the binary log files listed

in the log index file prior to the specified log file name or date.

BINARY and MASTER are synonyms. Deleted log files also are removed from

the list recorded in the index file, so that the given log file becomes

the first in the list.

This statement has no effect if the server was not started with the

--log-bin option to enable binary logging.

URL: http://dev.mysql.com/doc/refman/5.5/en/purge-binary-logs.html

Examples:

PURGE BINARY LOGS TO 'mysql-bin.010';

PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

The following are the methods given by other netizens, you can refer to them

MYSQL master-slave replication uses RBR After changing the mode, the format of binlog is "ROW", which can solve many original key duplication problems.

In a busy master db On the server, the binlog log file grows very quickly. If it is not cleared regularly, the hard disk space will be filled up quickly.

Set up automatic cleaning of mysql Binlog log, configure my.cnf:

expire_logs_days = 10

Modify at runtime:

show binary logs;

show variables like '%log%';

set global expire_logs_days = 10;

You can use the corresponding backup strategy before clearing.

Manually delete the MySQL binlog logs 10 days ago:

PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);

show master logs;

MASTER and BINARY are synonyms.

Under normal circumstances, it is recommended to use MIXED binlog replication. Instructions in http://dev.mysql.com/doc/refman/5.1/en/open-bugs-general.html: Replication uses query-level logging: The master writes the executed queries to the binary log. This is a very fast, compact, and efficient logging method that works perfectly in most cases.

Attachment: Several modes of MYSQL replication

Starting from MySQL 5.1.12, the following three modes can be used to achieve:

–Statement-based replication replication, SBR),
– row-based replication (row-based replication, RBR),
– Mixed-based replication (MBR).
Correspondingly, there are three formats of binlog: STATEMENT, ROW, and MIXED. In MBR mode, SBR mode is the default.

The binlog format can be dynamically changed at runtime, except for the following situations:

. In the middle of a storage process or trigger

. NDB is enabled
. Try RBR for the current session mode, and the temporary table has been opened

If the binlog adopts the MIXED mode, the binlog mode will be automatically changed from SBR mode to RBR mode in the following situations.

. When a DML statement updates an NDB table

. When the function contains UUID()
. When 2 or more tables containing AUTO_INCREMENT fields are updated
. When executing any INSERT DELAYED statement
. When using UDF
. When the view must require the use of RBR, for example, when creating the view, UUID() is used Function

Set master-slave replication mode:
log-bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
binlog_format="MIXED"

You can also dynamically modify the binlog format at runtime. For example
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

The advantages and disadvantages of the two modes:

SBR Advantages:
Long history, mature skills
Binlog file is smaller
Binlog contains all database modification information, which can be used to audit the security of the database
Binlog can be used for real-time restoration, not just for replication
The master and slave versions can be different, and the slave server version can be higher than the master server version
SBR Disadvantages:
Not all UPDATE statements can be copied, especially when they contain uncertain operations.
Call UDF with non-deterministic factors There may be problems when copying. Statements using the following functions cannot be copied:
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (unless the –sysdate-is-now option is enabled at startup)
INSERT … SELECT Will generate more row-level locks than RBR
When copying an UPDATE that needs to perform a full table scan (no index is used in the WHERE statement), it needs to be larger than RBR Request more row-level locks
For InnoDB tables with AUTO_INCREMENT fields, INSERT statements will block other INSERTs Statement
For some complex statements, the resource consumption on the slave server will be more serious, and in RBR mode, it will only affect the changed record
Stored function (not stored process ) will also execute the NOW() function once when it is called. This can be a bad thing or a good thing
Determined UDF It also needs to be executed on the slave server
The data table must be almost consistent with the master server, otherwise it may cause replication errors
If errors occur when executing complex statements, more resources will be consumed

RBR Advantages:

Any situation can be replicated, which is the safest and most reliable for replication
The same as the replication skills of most other database systems
In most cases, if the table on the slave server has a primary key, replication will Much faster
Fewer row locks when copying the following statements:
* INSERT … SELECT
* INSERT
* containing the AUTO_INCREMENT field UPDATE without conditions or without modifying many records Or DELETE statement
Less locks when executing INSERT, UPDATE, DELETE statements
It is possible to use multi-threading to perform replication from the server
RBR Disadvantages:
The binlog is much larger
During complex rollbacks, the binlog will contain a large amount of data
Execute UPDATE on the main server statement, all changed records will be written to the binlog, while SBR will only write once, which will lead to frequent concurrent writing problems of the binlog
Large BLOB generated by UDF The value will cause replication to slow down
You cannot see from the binlog what statements were copied and written (encrypted)
When executing a pile of SQL statements on a non-transactional table, it is best to use SBR mode, otherwise it will easily lead to data inconsistency between the master and slave servers
In addition, the processing guidelines for changes in the tables in the system library mysql are as follows:
If using When INSERT, UPDATE, and DELETE directly operate the table, the log format is recorded according to the setting of binlog_format
If it is used If management statements such as GRANT, REVOKE, and SET PASSWORD are used to do this, SBR mode recording will be used no matter what.
Note: Using RBR After the mode is implemented, many primary key duplication problems that originally occurred can be solved. Example:
For insert into db_allot_ids select * from db_allot_ids This statement:
in BINLOG_FORMAT=STATEMENT In mode:
BINLOG log information is:
————————————–
BEGIN
/*!*/;
# at 173
#090612 16:05:42 server id 1 end_log_pos 288 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1244793942/*!*/;
insert into db_allot_ids select * from db_allot_ids
/*!*/;
————————————–

In BINLOG_FORMAT=ROW mode:

BINLOG log information is:
———————— —————–
BINLOG '
hA0yShMBAAAAMwAAAOAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRzAAIBAwAA
hA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA=
'/*!*/;
——————————————–

Steps to clear logs
1. Find log files
mysql> show binary logs;
+----------------+----------+
| Log_name | File_size |
+----------------+-----------+
| ablelee.000001 | 150462942 |
| ablelee.000002 | 125 |
| ablelee.000003 | 106 |
+----------------+----------+
2. Delete bin-log (delete the one before ablelee.000003 but does not include ablelee .000003)
mysql> purge binary logs to 'ablelee.000003';
Query OK, 0 rows affected (0.16 sec)

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

mysql> show binlog eventsG
****************************** * 1.row ***************************
Log_name: ablelee.000003
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 106
Info: Server ver: 5.1.26-rc-log, Binlog ver: 4
1 row in set (0.01 sec)
(ablelee.000001 and ablelee.000002 have been deleted)
mysql> show binary logs;
+----------------+----------+
| Log_name | File_size |
+----------------+-----------+
| ablelee.000003 | 106 |
+----------------+-----------+
1 row in set (0.00 sec)

(Other formats deleted!)
　 PURGE {MASTER | BINARY} LOGS TO 'log_name'
　 PURGE {MASTER | BINARY} LOGS BEFORE 'date'
Used to delete all binary logs listed in the log index before the specified log or date. These logs are also removed from the list recorded in the log index file so that the given log becomes first.
　For example:
　PURGE MASTER LOGS TO 'mysql-bin.010';
　PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';
Clear the binlog from 3 days ago
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
　The date argument of the BEFORE variable can be 'YYYY-MM-DD hh:mm:ss' format. MASTER and BINARY are synonyms.
　If you have an active slave server that is currently reading one of the logs you are trying to delete, this statement will not work and will fail with an error. However, if the slave is quiesced and you happen to clear one of the logs it wants to read, the slave cannot replicate after it is started. This statement can be safely run while the slave server is replicating. You don't need to stop them.
　To clear the logs, follow the steps below:
　1. On each slave, use SHOW SLAVE STATUS to check which log it is reading.
　2. Use SHOW MASTER LOGS gets a series of logs on the master server.
　3. Determine the oldest log among all slave servers. This is the target log. If all slave servers are up to date, this is the last log on the list.

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

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

The above is the content of MySQL’s method of automatically cleaning binlog logs. For more related articles, please Follow the PHP Chinese website (www.php.cn)!