The method for MySQL to automatically clear binlog, mysqlbinlog

The method for MySQL to automatically clear binlog, mysqlbinlog

Note:

If the server that enables MySQL binlog is not set to automatically clear logs, binlog logs are retained by default for a long time, and the server disk space is full of binlog logs, leading to MySQL database errors.

Use the following method to safely clear binlog logs:

1. Clear logs without master-slave Synchronization

Mysql-uroot-p123456-e 'purge master logs before DATE_SUB (NOW (), INTERVAL 5 DAY )';

# Mysql regularly clears the binlog five days ago

Mysql-u root-p # enter the mysql Console

Reset master; # reset binlog

Ii. safely clear binlog logs under MySQL master-slave Synchronization

1. mysql-u root-p # enter the mysql console on the slave server
Show slave status \ G; # Check which log is being read from the slave server. Multiple slave servers are available. Select the earliest log as the target log.
2. log on to the mysql console of the master server.

Show master log; # obtain a series of logs on the master server
Purge master logs to 'binlog. 000000'; # Delete binlog.000005, excluding binlog.000058
Purge master logs before '2017-06-22 13:00:00 '; # Clear binlog logs before 2016 13:00:00
Purge master logs before DATE_SUB (NOW (), INTERVAL 3 DAY); # Clear binlog LOGS 3 days ago

Iii. set to automatically clear MySQL binlog

Vi/etc/my. cnf # edit Configuration

Expire_logs_days = 15 # logs generated 15 days ago are automatically deleted. The default value is 0, indicating that it is never deleted. After log-bin = mysql-bin # is commented out, binlog binlog_format = mixed # is disabled.

: Wq! # Save and exit

Additional 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

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

-- 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 '2017-04-02 22:46:26 ';

The methods provided by other netizens are as follows.

After MYSQL master-slave replication (replication) adopts the RBR mode, the binlog format is "ROW", which can solve many primary key duplication problems.
On a busy master db server, binlog log files are growing rapidly. If the files are cleared 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 log. This 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 RBR mode is used in the current session and the temporary table is enabled.

If binlog adopts the MIXED mode, the binlog mode is automatically changed from the SBR mode to the RBR mode in the following situations.
. 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.
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
* The UPDATE or DELETE statements for many records are not attached with conditions or 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 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'
Bytes
Ha0yshcbaaaanqaaabubaaaqaa8aaaaaeaav/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA =
'/*! */;
--------------

Log cleanup procedure
1. Search for log files
Mysql> show binary logs;
+ ---------------- + ----------- +
| Log_name | File_size |
+ ---------------- + ----------- +
| Ablelee.20.01 | 150462942 |
| Ablelee.000002 | 125 |
| Ablelee.000003 | 106 |
+ ---------------- + ----------- +
2. Delete the bin-log (before deleting ablelee.000003, but without ablelee.000003)
Mysql> purge binary logs to 'ablelee. 123456 ';
Query OK, 0 rows affected (0.16 sec)

3. query results (only one record is available now .)

Mysql> show binlog events \ G
* *************************** 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 deleted formats are used !)
PURGE {MASTER | BINARY} logs to 'Log _ name'
PURGE {MASTER | BINARY} logs before 'date'
Deletes all binary logs listed in the log index before the specified log or date. These logs will also be deleted from the list of records in the log index file, so that the given log becomes the first.
For example:
Purge master logs to 'mysql-bin.010 ';
Purge master logs before '2017-06-22 13:00:00 ';
Clear binlog 3 days ago
Purge master logs before DATE_SUB (NOW (), INTERVAL 3 DAY );
The date argument for the BEFORE variable can be in the 'yyyy-MM-DD hh: mm: ss' format. Both 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 does not work, but fails with an error. However, if the slave server is slave and you happen to have cleared one of the logs it wants to read, the slave server cannot be copied after it is started. This statement can be run safely when the slave server is being copied. You do not need to stop them.
To clear logs, follow these steps:
1. Use show slave status on each SLAVE server to check which log it is reading.
2. Use show master logs to obtain a series of LOGS on the MASTER server.
3. Identify the earliest logs among all slave servers. This is the target log. If all slave servers are updated, this is the last log in the list.

4. Back up all the logs you want to delete. (This step is optional, but is recommended .)

5. Clear all logs, but not the target logs.