Solution to Mysql binlog log file being too large

The disk usage is too high. After checking the cause, it is found that the MySQL binlog file is too large.

Order

ls -l -h 

mysql-binlog is the binary log of the MySQL database, which is used to record the SQL statements (except data query statements) of users' operations on the database. You can use the mysqlbin command to view the contents of the binary log.

You can limit the output of binlog files by setting the my.cof configuration file.

1. Related binlog configuration

vim /etc/my.cof

[mysqld]
expire_logs_days = 3
#Set binlog cleanup time max_binlog_size = 100m
#binlog Each log file size binlog_cache_size = 4m
#binlog cache size max_binlog_cache_size = 512m
#Maximum binlog cache size

Restart mysql and see that only the logs for the past three days are retained

2. Advanced settings related to binlog

2.1 Change binlog mode

There are also three binlog modes: STATEMENT, ROW, and MIXED. The following are descriptions of these three formats:

STATMENT mode

In SQL statement-based replication (SBR), every SQL statement that modifies data is recorded in the binlog.

Advantages: There is no need to record every SQL statement and every row of data changes, so the binlog log will be relatively small, reducing disk IO and improving performance.

Disadvantages: In some cases, it may cause inconsistent data in the master-slave (such as problems with the sleep() function, last_insert_id(), and user-defined functions (udf)).

ROW mode

The context information of each SQL statement is not recorded. It is only necessary to record which data has been modified and how it has been modified.

Advantages: There will be no problem that the call and trigger of stored procedures, functions, or triggers cannot be correctly replicated in certain situations.

Disadvantages: A large amount of logs will be generated, especially when altering a table, which will cause the log to explode.

MIXED mode

Mixed-based replication (MBR): A combination of the above two modes. For general replication, STATEMENT mode is used to save binlogs. For operations that cannot be replicated in STATEMENT mode, ROW mode is used to save binlogs. MySQL selects the log saving method based on the executed SQL statement.
Modify the configuration file

[mysqld]
binlog_format = mixed
#Set log format

2.2 Related SQL Operation Binlog

show binary logs;                            
# View the existing details of binlog show variables like '%log%';            
# View the relevant configuration of log variables set global expire_logs_days = 3;    
# Set the binlog save time reset master;                                 
# Reset all binlogs, which is equivalent to deleting all binlogs. This operation has a great impact on the master-slave cluster because the master-slave assignment is based on binlog logs. PURGE {MASTER | BINARY} LOGS TO 'log_name'
PURGE {MASTER | BINARY} LOGS BEFORE 'date'
# Delete all binary logs in the log index before the specified log or date. MASTER and BINARY are synonyms PURGE MASTER LOGS TO 'binlog.000013'; 
#Purge binlog.000013 log PURGE MASTER LOGS BEFORE '2020-01-08 10:00:00';  
#Purge binlog logs before 2020-01-08 10:00:00 PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); 
# Clear the binlog log BEFORE 3 days ago. The date argument of the variable can be in the format of 'YYYY-MM-DD hh:mm:ss'.

This is the end of this article about how to solve the problem of Mysql binlog log file being too large. For more information about Mysql binlog log being too large, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!