Binlog binary log mode and deletion method of MySQL/MariaDB

Sometimes you will find that binlog suddenly becomes very large, resulting in full disk partitions. At this time, you need to first clear binlog. When clearing binlog, if there is an active slave server, if the server 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. Of course, clearing binlog is only a temporary action. I should also find out what causes the sudden growth of binlog. The content of the binlog can be easily found.
I. Row Level
The log records each row of data in the modified form, and then modifies the same data on the slave side.
Advantage: in row level mode, bin-log does not record the context-related information of the executed SQL statement, but only needs to record the record that has been modified, to what it is. Therefore, the row-level log records the details of each row of data modification, which is easy to understand. In addition, stored procedures, functions, and trigger calls and triggers cannot be correctly copied in certain situations.
For a write operation, the row format binlog is divided into four parts, the imitation is: BEGIN-> Table_map-> Write_rows/Delete_rows/Update_rows-> COMMIT
Advantage: 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
* When executing INSERT, UPDATE, or DELETE statements without conditions or modifying many records, there are fewer locks;
It is possible to use multiple threads to execute replication on the server;
Disadvantage: when all statements executed at row level are recorded in logs, they are all recorded with modifications recorded in each line. This may produce a large amount of log content, for example, there is an update statement: update product set owner_member_id = 'B' where owner_member_id = 'a'. After execution, what is recorded in the log is not the event corresponding to this update statement (MySQL records the bin-log as an event), but the change of each record updated by this statement, in this way, many events are recorded and updated. Naturally, the amount of bin-log logs will be large. Especially when statements such as alter table are executed, the log volume generated is astonishing. Because MySQL treats alter table and other table structure change statements in a way that each record of the entire table needs to be changed. In fact, the whole table is rebuilt. Each record of the table is recorded in the log.
II. Statement Level
Each SQL statement that modifies the data is recorded in the bin-log of the master. When the server load balancer replicates, the SQL process parses the same SQL statement that was previously executed on the master to execute it again.
Advantages: statement level first solves the disadvantages of row level. It does not need to record the changes of each row of data, reduces the bin-log volume, saves IO, and improves performance. Because it only needs to record the details of the statements executed on the Master and the context information during statement execution.
Disadvantage: because it is a recorded execution statement, in order to make these statements run correctly on the slave end, it must also record some information about each statement during execution, that is, the context information to ensure that all the statements can get the same results as those executed on the master node when the slave terminal cup is executed. In addition, because MySQL is developing rapidly and many new features are constantly added, MySQL replication has encountered great challenges. The more complex the content involved during natural replication, the more common the bug is. In statement level, many cases have been found to cause MySQL Replication problems, mainly when some specific functions or functions are used for data modification, for example, the sleep () function cannot be copied in some versions, and the last_insert_id () function is used in the stored procedure, which may lead to an inconsistent id between the slave and the master. Since the row level records changes based on each row, no similar problems will occur.
III. Mixed Level
It is actually the combination of the first two modes. In Mixed mode, MySQL will differentiate the log format of the record based on each specific SQL Statement executed, that is, select one between Statement and Row. The Statment level in the new version is the same as before, and only the statements executed are recorded. The row level mode of the new MySQL squadron has also been optimized. Not all modifications are recorded by row level. For example, when the table structure is changed, it will be recorded in statement mode, if the SQL statement is indeed a statement that modifies data such as update or delete, changes to all rows will be recorded.
4. Parameters in the configuration file
Log-bin = mysql-bin
# Binlog_format = "STATEMENT"
# Binlog_format = "ROW" binlog_format = "MIXED:
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 ';
5. Delete and disable binlog
1. Disable binlog
# Modify the configuration file and comment on the following lines.
[Root @ LookBack ~] # Cat/etc/my. cnf | grep-E 'log [_-] bin | binlog_format | expire_logs_days '# This is a query statement
Log_bin = mysql-bin # whether binary logs are enabled
Binlog_format = mixed # binary log record mode
Expire_logs_days = 30 # Number of days when binary logs are automatically deleted. The default value is 0, indicating "no automatic deletion"
2. Delete the binlog correctly
Purge master logs to 'MySQL-bin.010 '; # clear MySQL-bin.010 LOGS
Purge master logs before '2017-07-19 13:50:42 '; # clear binlog logs before 2015 13:50:42
Purge master logs before DATE_SUB (NOW (), INTERVAL 3 DAY); # clear binlog logs before 3 days ago, the variable's date independent variable can be 'yyyy-MM-DD hh: mm: SS' format