MySQL binary log file cleanup and management

1: Binary log

The binary log records all DDL(data definition Language) statements and DML(Data Manipulation language) statements, but does not record statements that include data queries. Statements are saved as "events," which describe the process of changing the data, which plays an extremely important role in recovering data during a disaster.

2: Location and format of the log

When started with the -log-bin[=file_name] option,mysqld writes the SQL command that contains all the updated data to the log file. If the file_name value is not given, the default name is the hostname followed by _bin, if the file name is given, but the path is not included, the files are written by default to the directory specified by the parameter datadir(Data directory)

3: Read the log

Since the log is stored in binary mode and cannot be read directly, it needs to be viewed with the mysqlbinlog tool, with the following syntax:

#mysqlbinlog Log_file

4: Deletion of logs

For more busy OLTP systems, because of the large daily production log, these logs can be a significant waste of disk space if they are not cleaned up for a long time, so periodically deleting logs is a DBA maintaining Mysql One of the most important things about the database, here are some common ways to delete logs

(1):

Perform "reset master;" command, the command will delete all binary logs, the new log number starts with "000001" and the command is as follows

Mysql>reset master;

(2):

Execute the "Purge master logs to ' mysql-bin.***** '" command, which removes all logs before the "* * * *" number, and the following removes the "mysql-bin.000001 "All the logs that were previously numbered

mysql>Purge master logs to ' mysql-bin.000015;

From the results, all logs before the number 000015 have been deleted

(3):

Execute "purge master logs before ' yyyy-mm-dd hh24:min:ss '" command, which deletes all logs generated before the date "yyyy-mm-dd hh24:mi:ss". All logs before the date "2010-05-22 01:00:00" are removed in the following

Mysql>purge master logs before ' 2010-05-22 01:00:00 ';

(4):

Set the parameter -expire_logs_days=# (days), This parameter means to set the expiration date of the log, and the log will be automatically deleted after the specified number of days, which will help reduce the amount of DBA Administration log.

#vi/etc/my.cnf

[Mysqld]

--expire_logs_days=3

In this way,3 days before the log will be deleted, the system automatically deleted

Set up automatic cleanup of MySQL binlog logs and manual removal methods

After MySQL master-slave Replication (replication) is in RBR mode, the format of the Binlog is "ROW", which solves many of the original key duplication problems that have occurred.
On a busy Master DB server, the Binlog log file is growing fast and the hard disk space is quickly filled if you do not clear it regularly.
Set the automatic cleanup MySQL binlog log, configure MY.CNF:
Expire_logs_days = 10
Modify at run time:
Show binary logs;
Show variables like '%log% ';
Set global expire_logs_days = 10;
The appropriate backup strategy can be used before purging.

Manually delete the MySQL binlog log 10 days ago:
PURGE MASTER LOGS before Date_sub (current_date, INTERVAL Day);
Show master logs;
Master and binary are synonyms.

In general, replication with mixed Binlog is recommended.

Http://dev.mysql.com/doc/refman/5.1/en/open-bugs-general.html

Description: 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, which works perfectly in the most cases.
Attached: Several modes of MySQL replication
The following transfers from http://steven1981.itpub.net/post/7967/485747
Starting with MySQL 5.1.12, the following three modes can be implemented:
– SQL statement-based replication (statement-based replication, SBR),
– Row-based replication (row-based replication, RBR),
– Mixed Mode replication (mixed-based replication, MBR).
Accordingly, there are three types of Binlog: statement,row,mixed. In MBR mode, the SBR mode is the default.

You can dynamically change the format of the Binlog at run time, in addition to the following scenarios:
. The middle of a stored procedure or trigger
. Enabled the NDB
. Current session trial RBR mode, and temporary table is open

If the Binlog uses the MIXED mode, the Binlog mode is automatically changed from SBR mode to RBR mode in the following cases.
. When a DML statement updates a NDB table
. When the function contains a UUID ()
. When 2 or more tables containing the Auto_increment field are updated
. When you line any INSERT DELAYED statement
. When using UDFs
. The view must require the use of RBR, such as establishing a view using the UUID () function

To 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 format of the Binlog at run time. 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, mature skills
Binlog files are small
Binlog contains all the database modification information, which can be used to audit the security of the database, etc.
Binlog can be used for real-time restores, not just for replication
Master-slave version can be different from server version can be higher than the primary server version
Disadvantages of SBR:
Not all UPDATE statements can be duplicated, especially if they contain indeterminate operations.
Replication may also be problematic when invoking a UDF with an indeterminate factor
Statements that use the following functions cannot be duplicated:
* Load_file ()
* UUID ()
* USER ()
* Found_rows ()
* Sysdate () (unless the –sysdate-is-now option is enabled at startup)
INSERT ... SELECT produces more row-level locks than RBR
Replication requires more row-level locks than RBR to perform a full-table scan (an UPDATE that is not applied to an index in a WHERE statement)
For InnoDB tables with auto_increment fields, INSERT statements block other INSERT statements
For some complex statements, the consumption of resources from the server will be more serious, and RBR mode will only affect the changed records.
A stored function (not a stored procedure) executes the now () function once it is called, which can be a bad thing or a good thing.
The identified UDF also needs to be executed from the server.
The data table must be almost consistent with the primary server, or it may cause replication to fail.
Executing complex statements consumes more resources if something goes wrong.

Advantages of RBR:
Any situation can be replicated, which is the safest and most reliable for replication
Same replication skills as most other database systems
In most cases, replication will be much faster if you have a primary key from a table on the server.
There are fewer row locks when copying the following statements:
* INSERT ... SELECT
* INSERT containing the auto_increment field
* UPDATE or DELETE statements with no strings attached or changes to many records
Fewer locks when executing insert,update,delete statements
Possible to perform replication from a server with multithreading
Disadvantages of RBR:
Binlog a lot bigger.
Complex rollback with a large amount of data in the Binlog
When an UPDATE statement is executed on the primary server, all changed records are written to Binlog, and SBR is written only once, which results in frequent binlog of concurrent write queries
Large BLOB values produced by UDFs can cause replication to slow
Cannot see from Binlog what statements are copied (encrypted)
When executing a stacked SQL statement on a non-transactional table, it is best to use SBR mode, otherwise it can easily result in data inconsistency of the master-slave server.
In addition, for the system library MySQL inside the table changes when the processing criteria are as follows:
If you are using the Insert,update,delete Direct Action table, the log format is recorded according to the Binlog_format setting
If the use of Grant,revoke,set PASSWORD and other management statements to do, then in any case, the use of SBR mode record.
Note: After using RBR mode, it can handle many original key duplication problems. Instance:
For INSERT INTO Db_allot_ids SELECT * from Db_allot_ids this statement:
In binlog_format=statement mode:
The 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:
The Binlog log information is:
—————————————–
BINLOG '
Ha0yshmbaaaamwaaaoaaaaaaaa8aaaaaaaaaa1nouwamzgjfywxsb3rfawrzaaibawaa
Ha0yshcbaaaanqaaabubaaaqaa8aaaaaaaeaav/8aqeaaad8aqeaaad8aqeaaad8aqeaaaa=
‘/*!*/;
—————————————–

This article is from the "XFICC" blog, make sure to keep this source http://xficc.blog.51cto.com/1189288/1586182

MySQL binary log file cleanup and management