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

I was woken up by the phone early in the morning. The database of a certain project in the cloud was all hung up and couldn't be started (I slept too much and didn't hear the alarm message). I was so scared.

It was said on the phone that all the mysql database master libraries could not be started, but the slave database was normal, so it was suspected that the master database had gone to connect with other Aliyun master libraries. These databases were previously migrated from Aliyun to the idc computer room, so he made this judgment.

Quickly turn on the computer, connect * *, log in to one of the database servers, and try to start the mysql service by executing the following command

[root@bbsmysql121 backup] # mysqld_safe-user=mysql &

Failed to start, try another database server, or failed. Considering that all the databases cannot be started, it can be preliminarily determined that it may be caused by the problem of the database host.

The underlying design of the database is the virtualization of two physical nodes, plus a physical machine for backup. Among them, the virtual machine of one physical machine does the mysql master library, and the virtual machine of the other physical machine does the mysql slave library.

First give up troubleshooting in the virtual machine, quickly log in to the host system. Next, check out the problem from two aspects.

Virtual background management system

It is found that the storage is full and the problem is very serious.

ü ssh logs in to the host system debian

[6885005.756183] Buffer I/O error on dev dm-16, logical block 34667776, lost async page write

[6885005.757292] Buffer I/O error on dev dm-16, logical block 34667792, lost async page write

[6885005.758210] Buffer I/O error on dev dm-16, logical block 34667808, lost async page write

[6885005.759079] Buffer I/O error on dev dm-16, logical block 34667824, lost async page write

[6885005.759922] Buffer I/O error on dev dm-16, logical block 34667840, lost async page write

[6885005.760723] Buffer I/O error on dev dm-16, logical block 34667856, lost async page write

Syslog / var/log/messages found a large number of disk io errors.

Summing up the above findings, it is almost certain that there is something wrong with the disk: one is that the storage space designated by proxmox is full, and the other is a disk io error. Once you know what the problem is, there are two options: fix the error or promote the slave library to the master library. Considering the standby problem, try to repair the main library as much as possible, but it really can't be repaired, so use the second set of solutions (upgrade slave library).

Free up disk space

Why is the disk space full? Someone should have done something on the virtual machine, and it may be that each virtual machine does the same thing, causing the host disk space to fill up quickly. Log in to any virtual machine running the mysql database and execute the command

Df-h

When logging on to other servers, partition / dev/sdb1 is also used more than 90%. Enter the directory / data and run the following instructions to check the directory space usage:

[root@cumysql121 data] # du-hs *

4.0K backup

59G db_pkg

59G mysql_db

[root@cumysql121 data] # cd backup

[root@cumysql121 backup] # du-hs *

Boy, there are several directories of more than 50 gigabytes (I deleted them when I wrote this article, and there is no record). From the directory name, these files should be automatically generated by the backup database. Regardless of it, delete it first.

Someone must have done an automatic task in the system. Check it with the command crontab-l, and sure enough, you have found something:

#! / bin/bash

/ usr/local/xtrabackup/bin/innobackupex-defaults-file=/etc/my.cnf-user=root-passwor='+N4dohask+MsLhG' / data/backup/

Find / data/backup/*-mtime + 1-exec rm-fr {}\

~

At first glance, there is no problem with this script, and then look carefully, the last line is the symbol "~", there is a problem! The intention of the script writer is to back up the database once a day and then delete the historical backup data from the previous day so that the disk is not full.

But there are two fatal problems, which are described here.

Backup policy error

There is a special backup system, and data should be backed up to that system instead of locally.

Means error

After the backup script has been written, it should be executed manually to verify its correctness. Instead of finishing it, just throw it on it.

Fix disk error

Contact the computer room urgently and ask the technician to connect the KVM over to the host. In case the system cannot boot, you can view it remotely or enter the single-user mode for repair operations such as fsck.

Ssh hosts the system debian, confirms that the stuffed disk space is released, and then executes reboot to restart the system. After a few minutes, the system boots normally.

Subsequent operation

Check the system log, there is no disk io error, create directories and files, normal; start each virtual machine, start the database on it, are normal.

Inform all personnel to check whether it is normal or not from the business level. In a moment, a lot of recovery information came from the text message, and I felt much more secure. Needless to say, it was the sa of the project side who did this without notifying anyone.

Tell him in private, explain this to others yourself, and that if you do anything risky in the future, you'd better inform each other.

The above is the editor to introduce to you to kill a bunch of mysql database, only need such a shell script to explain the integration, hope to help you, if you have any questions, please leave me a message, the editor will reply to you in time. Thank you very much for your support to the website!

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.