Automatically clear MySQL binlog logs and manually delete settings

The following articles mainly describe how to set the actual solution for automatically clearing MySQL binlog logs and manually deleting them. We all know that after MySQL Databases adopt the RBR mode from replication, the format of binlog is "ROW", which is mainly used to solve many primary key duplication problems.

On a busy master db server, MySQL binlog files are growing rapidly. If you clear them from time to time, the hard disk space will soon be filled.

Set to automatically clear MySQL binlog and configure my. cnf:

Expire_logs_days = 10

Modify at runtime:

 
 

  
  
show binary logs;   
  
  
show variables like '%log%';   
  
  
set global expire_logs_days = 10; 
 
 

The corresponding backup policy can be used before clearing.

Manually delete MySQL binlog 10 days ago:

 
 

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

Both MASTER and BINARY are synonyms.

Generally, we recommend that you use MIXED binlog replication. Using uses query-level logging: The master writes the executed queries to the binary logThis is a very fast, compact, and efficient logging method that works perfectly in most cases

Appendix: MySQL replication Modes

From MySQL 5.1.12, you can use the following three modes:

SQL statement-based replication ),

Row-based replication (RBR ),

Mixed-based replication (MBR ).

Correspondingly, there are three binlog formats: STATEMENT, ROW, and MIXED. In MBR mode, the default version is "SBR.

You can dynamically change the binlog format at runtime, except for the following situations:

Intermediate storage process or trigger

NDB Enabled

The current session has tried RBR mode and the temporary table has been enabled.

If binlog adopts the MIXED mode, the MySQL binlog mode is automatically changed from the SBR mode to the RBR mode in the following cases.

When the DML statement updates An NDB table

When the function contains UUID ()

When two or more tables with AUTO_INCREMENT fields are updated

When any insert delayed statement is row

When using UDF

When RBR must be used in a view, for example, the UUID () function is used to create a view.

Set the 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'; 
 
 

Advantages and disadvantages of the two modes:

Advantages of SBR:

Long history and mature skills

The binlog file is small.

Binlog contains all database modification information, which can be used to audit database security and other information.

MySQL binlog can be used for real-time restoration, not just for replication.

The Master/Slave versions can be different. The slave server version can be higher than the master server version.

Disadvantages of SBR:

Not all UPDATE statements can be copied, especially when there are uncertain operations.

Copying a UDF with uncertainties may also cause problems.

Statements that use 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 a full table scan (index is not used in the WHERE statement) is performed for replication, more row-level locks are required than RBR requests.

For InnoDB tables with AUTO_INCREMENT fields, the INSERT statement blocks other INSERT statements.

For some complex statements, resource consumption on the slave server is more serious, while in RBR mode, only the changed record will be affected.

When a stored function (not a Stored Procedure) is called, it also executes the NOW () function. This can be said to be a bad thing or a good thing.

The identified UDF must also be executed on the slave server.

Data Tables must be basically the same as those on the master server. Otherwise, a replication error may occur.

Execution of complex statements consumes more resources if an error occurs.

Advantages of RBR:

It can be copied in any situation, which is the most secure and reliable for replication.

Similar to the replication skills of most other Database Systems

In most cases, if a table on the slave server has a primary key, replication will be much faster.

When you copy the following statements, there are fewer row locks:

INSERT... SELECT

INSERT containing the AUTO_INCREMENT Field

There are no conditions attached or the UPDATE or DELETE statements for many records have not been modified

Less locks when executing INSERT, UPDATE, and DELETE statements

It is possible to use multiple threads to execute replication on the server.

Disadvantages of RBR:

Binlog is much larger

Complex rollback binlog contains a large amount of data.

When an UPDATE statement is executed on the master server, all the changed records will be written to the binlog, while the SBR will only write once, which will lead to frequent concurrent writes of the binlog.

The large BLOB value generated by the UDF causes the replication to slow down.

What statements have been copied (encrypted) cannot be seen from binlog)

When executing a stacked SQL statement on a non-transaction table, it is best to use the SBR mode. Otherwise, it is easy to cause data inconsistency between the master and slave servers.

In addition, the processing rules for table changes in the MySQL database are as follows:

If INSERT, UPDATE, and DELETE are used to operate the table directly, the log format is recorded according to the MySQL binlog_format setting.

If you use management statements such as GRANT, REVOKE, and set password, you can record them in the SBR mode in any case.

Note: The RBR mode can be used to handle many primary key duplication problems. Instance:

For the insert into db_allot_ids select from db_allot_ids statement:

In BINLOG_FORMAT = STATEMENT 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 '  
  
  
hA0yShMBAAAAMwAAAOAAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRzAAIBAwAA  
  
  
hA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA=  
  
  
'/*!*/; 
 
 

The above content is an introduction to setting up automatic cleanup of MySQL binlog logs and manual deletion methods. I hope you will find some gains.