Clear binlog logs in MySQL

In the following articles, we will first describe how to set the actual solution for automatically clearing MySQL binlog diaries and manually deleting data. We all know how to replicate MySQL Data libraries (replication) after accepting the RBR mode, the binlog format is "ROW". Its primary utility is to deal with many primary key repeated problems that were originally presented.

On a busy master db server, MySQL binlog log files are growing rapidly. If the root is broken from time to time due to instability, the hard disk space will soon be filled up.

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

Expire_logs_days = 10

Modify at runtime:

You can accept the corresponding backup policy before root disconnection.

Manually delete the MySQL binlog diary 10 days ago:

MBSTER and BINBRY are synonyms.

Generally, it is recommended to apply the 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: STBTEMENT, ROW, and MIXED. In MBR mode, the default version is "SBR.

You can dynamically change the binlog pattern during running, except for the following situations:

The storage process or the middle of the triggered Device

NDB Enabled

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

If binlog accepts 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 the automatic _ INDREMENT field are updated

When any insert delbyed statement is executed

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:

It can also dynamically modify the binlog pattern at runtime. For example

The two modes have their own advantages and disadvantages:

Advantages of SBR:

Long history and mature skills

The binlog file is small.

The binlog contains all the modification information of the numeric database, which can be used to review the security of the numeric database.

MySQL binlog can be used for timely restoration, not only for replication

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

The following error occurs in the process of creating an HTTP protocol:

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

When the timer has a UDF with uncertainties, copying may also cause problems.

The statements using the following functions cannot be copied:

SYSDBTE () (unless the sysdate-is-now option is enabled at startup)

INSERT... SELEDT will generate more row-level locks than RBR.

When copying a UPDBTE that requires a full table scan (which is not applied to the index in the WHERE statement), more row-level locks are required than RBR requests.

For InnoDB tables with the automatic _ INDREMENT field, the INSERT statement blocks other INSERT statements.

For some large statements, resource consumption on the slave server will be more serious, while in RBR mode, it will only affect the generation of the changed records

A storage function (not a storage process) is called and a NOW () function is executed. This is both a bad thing and a good thing.

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

The value table must be almost the same as the master server. Otherwise, a replication error may occur.

If an error occurs when a large statement is executed, more resources will be consumed.

Advantages of RBR:

Replication can be performed in any situation, which is the safest and most reliable for replication.

The same is true for most other numeric 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 containing the automatic _ INDREMENT Field

UPDBTE or DELETE statements with no additional premise or with no modification to many records

Less locks when executing INSERT, UPDBTE, and DELETE statements

It is possible to accept multiple threads from the server to execute replication.

Incorrect RBR:

Binlog is much larger

Binlog contains a large number of values during rollback.

When the UPDBTE statement is executed on the master server, all the changed record cities are written to the binlog, while the SBR only writes once, which leads to frequent concurrent binlog writing problems.

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

You cannot see what statements have been copied (encrypted) from the statement in binlog)

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

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

If you accept INSERT, UPDBTE, and DELETE operations on tables, the log pattern is recorded based on the MySQL binlog_format setting.

If you accept management statements such as GRBNT, REVOKE, and set pbssword, you can accept records in the SBR mode in any case.

Note: After accepting the RBR mode, it can handle many primary key repetition problems that were originally presented. Instance:

For the insert into db_allot_ids select from db_allot_ids statement:

In BINLOG_FORMBT = STBTEMENT mode:

The BINLOG log information is as follows:

In BINLOG_FORMBT = ROW mode:

The BINLOG log information is as follows:

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