Get rid of the era of staring at the screen to monitor MySQL

Get rid of the era of staring at the screen to monitor MySQL_PHP Tutorial

WBOY

Release： 2016-07-13 17:38:33

Original

1067 people have browsed it

This article mainly studies how to use Linux Shell script and Fetion API to realize automatic monitoring of MySQL database and SMS alarm, so that DBA can be freed from heavy and repetitive monitoring work and enjoy the sunshine and beach of Maldives while monitoring Beijing in real time. MySQL database.

With the rapid development of the Internet and informatization, the amount of data generated by business operations is increasing, so most companies choose to store important business data in databases. If the database system suddenly shuts down or other failures occur, it will inevitably affect the company's revenue, reputation, and even lead to user complaints. How to ensure the normal operation of the database system has always been a core concern of the company. This requires the DBA to monitor the various statuses of the database operation in real time, resolve failures before they occur, and eliminate hidden dangers in the cradle. This article describes how to use the "MySQL Monitor" Shell script to implement real-time monitoring of MySQL, discusses how to implement MySQL's SMS alarm function module, and combines a real application scenario to demonstrate how "MySQL Monitor" works.

　Background

　Problems encountered by Nanfeng Company

Nanfeng Company is an Internet company. The company has 10 servers in the computer room of District 1 of Beijing Netcom, 10 servers in the computer room of District 2, and 10 servers in the computer room of Shanghai Telecom District 1..., in view of Due to the importance of company data, the DBA must implement a series of monitoring measures to ensure the healthy operation of the database. It has set up 20 checkpoints including operating system disk space check, database table space check, database table status check, and dual-machine mutual backup status check. , what options do DBAs have to implement such a huge monitoring solution? Here are the traditional solutions:

This is an inefficient serial working method. The DBA must check 20 checkpoints of the database server one by one starting from 10:00 in the morning. It is very time-consuming. This traditional solution will make the DBA repeat it every day. Mechanized work, when there are hundreds of monitored servers, this traditional solution is a nightmare for the DBA. So how to monitor the database more effectively has become an urgent problem that the DBA needs to solve?

　Nanfeng Company’s solution

The smart MySQL needs to be controlled in a smart way. The DBA of Nanfeng Company has developed a "MySQL Monitor" solution, which makes full use of the advantages of "Shell script" + "Linux Cron timer service" + "China Mobile Fetion Free SMS API Interface". Only when there is a problem with the database checkpoint will a text message be sent to the DBA for alarm, realizing a truly unattended monitoring framework for a large number of database servers. The following is a brand-new solution:

This is an efficient parallel working method. In order to facilitate management, we first grouped the database servers. Each group of servers corresponds to a monitoring thread, and these monitoring threads are deployed on the "monitoring workstation". You must have noticed The "timeline" disappears because these monitoring threads run every 1 minute, and the "timeline" has no meaning. If everything is normal, these monitoring threads will only write logs and will not perform other operations; if an abnormality occurs on a certain database server, the "monitoring workstation" will send a text message reminder to the DBA terminal, so that the DBA can Problems are discovered and solved at once. Even if the DBA does not have a computer terminal around him, he can still understand the running status of the MySQL server in a timely manner