I have a couple of sites hosted on digitalocean VPS with Nginx and Virtualmin, but from time to time I find the mysql down and the annoying message that database can’t be reached. I have tried finding why the MYSQL is down but till now no luck no error message in the logs.
For my sites to not be down for hours I have created a shell script that is checking the mysql processes and in case are down it is starting the service again. I have added it in crontab and it is checking the service from 5 to 5 minutes with a log. I needed something simple that would start mysql in the case is down. Nothing fancy works well with CentOS 6 and 7.
Below are the steps to have the shell script running in crontab that is checking the mysql status and brings it up if down.
Step 1: Open terminal and login to the server
You can use putty or a similar ssh problem to access the server via SSH. Without SSH the script can’t be created.
Step 2: Create the sh script
The script and the log would be placed in /home. Just do the below:
cd /home nano sqlrestart.sh
Step 3: Create the shell instruction
I am checking for the programs to see if the mysql processes are up. In my case 2 processes are up, so the script in case not 2 processes are up would trigger the start:
To check the number:
[root@ns1 home]# pgrep mysql | wc -l 2
#!/bin/bash UP=$(pgrep mysql | wc -l); if [ "$UP" -ne 2 ]; then echo "MySQL is down."; /sbin/service mysql start else echo "All is well."; fi
#!/bin/bash UP=$(pgrep mysql | wc -l); if [ "$UP" -ne 2 ]; then echo "MySQL is down."; /sbin/service mysqld start else echo "All is well."; fi
Step 4: Save the file
Once you are done with the script press CTRL + x and you will be asked to save the changes. Type Y and hit enter. You will be returned to terminal and msqlrestart.sh file will be created in the root directory.
Step 5: Give execution permissions
To be able to run the script would need to have the execution permission, to do this do:
chmod +x sqlrestart.sh
Step 6: Add it in crontab
Now you just need to do is to added in the crontab so it can be run from 5 to 5 min to check the status:
export EDITOR=nano crontab -e
-add the script at the end:
*/5 * * * * /home/sqlrestart.sh >> /home/sqlrestart.log 2>&1
You can save it as in the step 4.
Now you just need to do is to test it. Just stop the
Just stop the mysql with:
/sbin/service mysqld stop
CentOS 7 :
/sbin/service mysql stop
After wait for couple of minutes and check the /home/sqlrestart.log
This is what I have done on my server to prevent the downtime caused by mysql not beeing started or in case a crash happens. It works perfect for me. In case you want a different approach where the status is checked and not the processes you can check: