Building A Database-Server Monitor
I made a mistake. For the first time, I set up a Wordpress site for a client, and agreed to manage the hosting. I had played around with some Apache configuration settings, and deployed a couple projects to an Ubuntu production environment, but I had no real sysadmin experience. “This won’t matter,” I thought. “I won’t have to spend much time on this.”
I was wrong, of course. Not too long after deploying the site to a DigitalOcean droplet, I received an email from the client informing me that the site was down.
“Error establishing a database connection.”
I put on my boots and headlamp and dug my way down the rabbit hole. I had no idea what was causing the problem, but the logs (as well as the error message in the browser) indicated there was a problem with MySQL.
I played around with a few solutions but it was imperative that, in the meantime, I would be the first person to discover any future crashes.
And so, mysql-monitor was born: a MySQL-server monitoring script complete with real-time SMS status updates. The script tries to connect to the database at regular intervals and sends a text message to the admin if the connection fails.
How it works
- A script is executed by cron every minute
- The script initiates a connection to the database
- If the connection fails, suggesting that MySQL is inactive, the script will check the status value in a text file
- If the status value is not equal to 1 — suggesting that a message has not yet been sent for this particular crash — the script will execute a new Twilio text message
- Once the message is sent, the status is set to 1, in order to avoid repeating messages whilst the issue is being fixed. If the server is still down a minute later, when the script runs again, it will find the status to be 1, and no message will be sent
- If the connection to the database is successful, suggesting that MySQL is active, the status will be set to zero
The following packages must be installed on your server:
- PHP >= 5.3
- Twilio’s PHP library
- Sign up for a Twilio account here
- Get your Twilio phone number by following the instructions. This will serve as the number used to send out SMS
- Verify a recipient phone number. If you’re using a Twilio Trial account, you’ll only be able to send SMS messages to phone numbers that you’ve verified with Twilio. Phone numbers can be verified via your Twilio Console’s Verified Caller IDs
- Download the mysql-monitor directory to your server
- In the checkConnection.php file, insert your database connection credentials, your Twilio credentials and your message details
Run the script using a task scheduler
Create a cron job to execute your checkConnection.php script at regular intervals, ensuring up-to-date alerts if and when your MySQL server becomes inactive
- From the terminal, execute the ‘crontab -e’ command to open your user account’s crontab file
- Insert your cron job, like so
*/1 * * * * php /absolute/path/to/mysql-monitor/checkConnection.php >> /var/log/statusChecker.log 2>&1
- This section of the job provides a location to which logs can be sent:
>> /var/log/statusChecker.log 2>&1
- Be sure to create the statusChecker.log file so that you can keep track of your MySQL status history. Records of the events that occur each time the script runs will be kept inside this file
- To learn more about setting up a crontab file, visit Ubuntu’s Community Help Wiki.
The Coding Journey
Writing the code wasn’t a big deal. Twilio does all the SMS work — it’s really very handy.
Though it was a mistake to agree to manage the hosting, in doing so I found myself in the deep end, forced to become both more comfortable with the command line, and better at identifying bugs.
It also exposed me to cron, and gave me some experience integrating (albeit briefly) a third party library.