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 configurationvim /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 binlog2.1 Change binlog modeThere 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. [mysqld] binlog_format = mixed #Set log format 2.2 Related SQL Operation Binlogshow 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:
|
<<: Detailed explanation of JavaScript object conversion to primitive value
>>: Detailed explanation of HTML style tags and related CSS references
What I want to share today is to use native JS to...
This article shares with you how to use Vue to im...
When we develop a web project with Django, the te...
I recently installed Ubuntu 20.04 and found that ...
Open the centos yum folder Enter the command cd /...
Project Background Recently, there is a project w...
1. Add the following code to http{} in nginx.conf...
The isnull() function cannot be used as a substit...
Table of contents Cause of the incident Use Node ...
Table of contents Steps to create TCP in Linux Se...
Preface: Sometimes in a route, the main part is t...
This article mainly introduces the relevant conte...
Table of contents 1. Schematic diagram of group q...
【Historical Background】 I have been working as a ...
1. Why write this article? You must have read a l...