Solution to Mysql binlog log file being too large

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 

insert image description here

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

insert image description here

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!

You may also be interested in:
  • Two ways to correctly clean up mysql binlog logs
  • Detailed explanation of MySQL database binlog cleanup command
  • How to automatically clean up MySQL binlog logs
  • Detailed explanation of mysql binlog binary log
  • How to view mysql binlog (binary log)
  • MySQL uses binlog logs to implement data recovery
  • How to open MySQL binlog log
  • Detailed explanation of MySQL binlog usage

<<:  Detailed explanation of JavaScript object conversion to primitive value

>>:  Detailed explanation of HTML style tags and related CSS references

Recommend

JS implements dragging the progress bar to change the transparency of elements

What I want to share today is to use native JS to...

Vue implements a search box with a magnifying glass

This article shares with you how to use Vue to im...

Detailed steps to start the Django project with nginx+uwsgi

When we develop a web project with Django, the te...

Ubuntu 20.04 connects to wifi (2 methods)

I recently installed Ubuntu 20.04 and found that ...

CentOS7 configuration Alibaba Cloud yum source method code

Open the centos yum folder Enter the command cd /...

Implementation of Webpack3+React16 code splitting

Project Background Recently, there is a project w...

How to configure nginx to limit the access frequency of the same IP

1. Add the following code to http{} in nginx.conf...

A complete example of implementing a timed crawler with Nodejs

Table of contents Cause of the incident Use Node ...

Introduction to the process of creating TCP connection in Linux system

Table of contents Steps to create TCP in Linux Se...

vue.js Router nested routes

Preface: Sometimes in a route, the main part is t...

Analyzing the troublesome Aborted warning in MySQL through case studies

This article mainly introduces the relevant conte...

A brief analysis of MySQL's WriteSet parallel replication

【Historical Background】 I have been working as a ...

JavaScript uses promise to handle multiple repeated requests

1. Why write this article? You must have read a l...