Shulou(Shulou.com)06/03 Report--

This article mainly introduces the Mysql binlog log file is too large how to do, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian with you to understand.

The disk suddenly reported an error and the utilization rate was too high. After investigating the cause, it was found that the binlog file of mysql took up too much.

Command

Ls-l-h

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

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

1. Related binlog configuration

Vim / etc/my.cof

[mysqld] expire_logs_days = setting binlog cleanup time max_binlog_size = 100m#binlog per 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 of the previous three days are kept.

2. Binlog related advanced settings 2.1 change binlog mode

There are also three modes of binlog: STATEMENT, ROW, and MIXED. These three formats are described below:

STATMENT mode

Based on the replication of SQL statements (statement-based replication, SBR), each sql statement that modifies the data is recorded in binlog.

Advantages: there is no need to record every SQL statement and every row of data changes, so that binlog will have fewer logs, reducing disk IO and improving performance.

Disadvantages: in some cases, it can lead to inconsistent data in master-slave (such as sleep () function, last_insert_id (), and user-defined functions (udf), etc.)

ROW mode

Instead of recording the context of each SQL statement, just record which piece of data has been modified and what it looks like.

Pros: there is no problem that the calls and triggers of stored procedures, or function, or trigger, can not be copied correctly in certain cases.

Disadvantages: a large number of logs will be generated, especially when alter table will cause the log to soar.

MIXED mode

Mixed mode replication (mixed-based replication, MBR): a mixture of the above two modes. General replication uses STATEMENT mode to save binlog, and for operations that cannot be replicated in STATEMENT mode, use ROW mode to save binlog,MySQL will choose the log preservation method according to the SQL statement executed.

Modify the configuration file

[mysqld] binlog_format = mixed# set log format 2.2 related SQL operations binlogshow binary logs; # View binlog existing details show variables like'% log%'; # View relevant configuration of log variable set global expire_logs_days = 3; # set binlog save time reset master # resetting all binlog is equivalent to deleting all binlog. This operation has a great impact on the master-slave cluster, because the master-slave assignment is based on PURGE {MASTER | BINARY} LOGS TO 'log_name'PURGE {MASTER | BINARY} LOGS BEFORE' date'# to delete all binary logs in the specified log or log index before the date. MASTER and BINARY are synonymous with PURGE MASTER LOGS TO 'binlog.000013'; # to clear the binlog.000013 log PURGE MASTER LOGS BEFORE' 2020-01-08 10 binlog.000013'; 00 DAY; # to clear the binlog log PURGE MASTER LOGS BEFORE DATE_SUB (NOW (), INTERVAL 3 DAY) before 2020-01-08 10:00:00; and # to clear the binlog log BEFORE 3 days ago. The date argument of the variable can be in 'YYYY-MM-DD hh:mm:ss' format. Thank you for reading this article carefully. I hope the article "what to do if the binlog log file in Mysql is too large" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.