Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to deal with the fault that the Binlog log of the MySQL library of the production line is full of disk". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to deal with the fault that the Binlog log of the production line MySQL library is full of disk".

Fault description

This morning, the monitoring platform e-mail informed the production of a business system that the MySQL database storage space has reached 90%. Quickly get up and remotely connect to the database host, and found that the Master double main MySQL database 10.5.11.105 and 10.5.11.106 data directory / var/lib/mysql space utilization rate reached 97% and 96%.

Second, the process of problem analysis

First, query and confirm the master-slave relationship of the database.

Log in to the 10.5.11.105 and 10.5.11.106 databases and execute the statement: show slave status\ G; view.

Note that the binlog before the 10.5.11.106 database master-bin.000779 can be cleaned.

Execute the statement: show slave hosts\ G View server id.

It shows that Server_id:33333 is the slave library of 10.5.11.105.

Execute statement: show variables like 'server_id' can confirm.

3. Query whether binlog automatic cleaning is enabled.

Execute the following statement show variables like 'expire_logs_days'

Check the parameters and find that the binlog automatic cleaning function is not enabled.

4. View specific binlog files

Execute statement: show binary logs

You can see that there are a large number of binlog files.

5. Check the specific storage path of binlog

Execute statement: show variables like'% log_bin_basename%'; view.

VI. The root cause of the problem

After investigation, we found that the root cause of the problem is that the automatic cleaning function of binlog is not enabled, resulting in a large backlog of binlog.

7. Deal with problems

Clean up the binlog file. Clean up the binlog by confirming that the master and slave apply the latest binlog. Use the following command to leave a few more binlog when cleaning, and be careful not to manually rm binlog files.

1. Purge binary logs to_'binlog file name'; to clean up the binlog log.

2. Enable the function of automatically cleaning binlog logs (both master and slave libraries need to be executed)

Execute the statement:

Set global expire_logs_days=10

And add the following configuration to the configuration file of / etc/my.cnf:

Expire_logs_days=10

Note: the expiration time of binlog log can be customized according to the requirements of your own company. What I set here is 10 days!

At this point, I believe you have a deeper understanding of "how to deal with the failure of Binlog logs in the production line MySQL library full of disk". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue 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.