Record a MySQL explosion operation record

Explosion MySQL is the database of zabbix under the test environment. The MySQL environment was originally designed as a dual-master high-availability architecture MySQL. The data disks of both databases are 500G. If calculated at the magnitude of the existing nodes, 500G is quite rich.

On the same day, I received customer feedback that the test zabbix could not be opened. After investigation, it was found that the data of zabbix was full and the data could not be written. I am wondering that the 500G test environment should be more than enough. Why is there so much data? I checked the data directory file size of the database. Among them, the error log of the database has more than 3G. The binlog file that uses the largest space is the binlog file. A total of close to 300G of binlog files are generated. I ran to another MySQL to check the results and found that the databases on both sides were early There is no synchronization, and even the libraries on both sides are different. The main library also has a zabbix1 test library, and the secondary library does not even have a zabbix1 test library. It can be imagined how long it has not been synchronized.

Because the database cannot write data, it can still be accessed, but because the database is full, the operation will cause the database to be damaged, so the customer is asked to expand the hard disk first, and dare to operate the database after expanding the capacity by 50G. First use the command to connect to the database (the screenshot was not taken at that time, the following picture was made for the later test)

#mysql -uroot [email protected]

mysql> show binary logs; #Get a list of binlog files

Insert picture description here


mysql> show master status;

Insert picture description here


#View the binlog files currently being written mysql> purge binary logs to “mysql-bin.000012”; #Clear the binlog files before 12, corresponding to binlog The corresponding file in the storage directory will be deleted, and the corresponding name of the mysql-bin.index file will also be cleared

Insert picture description here


mysql> show variables like'expire_logs_days'; #View binlog automatic deletion rules

expire_logs_days = 0

0, which means unlimited

mysql> set global expire_logs_days = 3; #Dynamic setting of binlog log is only retained for 3 days

Insert picture description here


Modify the mysql configuration file my.cnf and permanently modify the binlog retention days.

Insert picture description here


Since the two MySQL are out of sync and the environment is a test environment, just restart MySQL.

Do the same for the other one, and perform synchronization configuration after both MySQL are normal.

In the real scenario, the storage days of binlog depends on your needs. Don't close MySQL at will when the MySQL database is full, especially when the hard disk cannot be expanded. Make sure that there is a certain amount of space for MySQL to operate.