2

I am looking for a PHP script which can check mysql is running or not in CENTOS / Linux VPS?

I have tried several method but its not working

First Method:

<?
$command = ('/etc/init.d/mysql restart');
if (! $dbh = mysql_connect('localhost','username','password') ){
$output = shell_exec($command);
echo "<pre>$output</pre>";
}
else
    echo"nothing happened";    
?>

Second Method:

<?php
// STOP + START = RESTART..

system('net stop "MySQL"'); /* STOP */
system('net start "MySQL"'); /* START */
?>

Both Methods dint not works for me in PHP..

Problem Is : these days my site having too much load of mysql, i tried several method but i am unable to stop its crashing. therefore i decide to make a PHP script to check weather mysql is running or not? If not then PHP script will perform Restart Mysql, or else print "My Sql is running". for this I've decided to set a Cron Job in PHP, that PHP script will monitor mysql is running or not. I am also looking to save the logs in the end.. to check how many times mysql got restart..... please someone find the fix of this issue.. and post here.. thanks in advance...

Shahid Hussain
  • 31
  • 1
  • 6
  • Have a look on [Restart mysql within php browser, permissions issue?](http://stackoverflow.com/questions/10588636/restart-mysql-within-php-browser-permissions-issue) and [sudo in php exec()](http://stackoverflow.com/questions/3173201/sudo-in-php-exec) – Mohit S Sep 04 '15 at 04:11
  • 2
    For the love of every device attached to the internet, do not configure PHP so that it can execute shell commands or external programs. – Eric J. Sep 04 '15 at 04:17
  • For Mysql load analysis you can use Mysql Slow Query Log https://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html – Ganesh Ghalame Sep 04 '15 at 04:40
  • Better way would be to handle it from Mysql side, I suggest you to use `mysqld_safe` for auto starting the mysql if it dies http://superuser.com/questions/611197/auto-restart-mysql-when-it-dies, rather than make mysql works using PHP its better if you analyse issue and tried to fix mysql and if won't possible then and then only go with your logic of restarting mysql from PHP – Ganesh Ghalame Sep 04 '15 at 04:47
  • Ok Ganesh, Thanks for the solution..but still i am looking for this option, because may be i have to use for other purpose – Shahid Hussain Sep 04 '15 at 04:53
  • 1
    @GaneshGhalame note that myself_safe will only restart mysqld successfully if MySQL is the problem -- which it usually isn't. If MySQL server is killed by the system due to a memory hungry web server consuming all available resources, mysqld_safe will discover that immediately and restart mysqld *if possible* but if insufficient memory persists, it correctly gives up, assuming something is more serious. If Apache is on the same machine, the problem is usually this: http://dba.stackexchange.com/questions/25077/mysql-innodb-crash-post-mortem – Michael - sqlbot Sep 04 '15 at 12:07
  • 1
    @Michael-sqlbot thanks for clarification – Ganesh Ghalame Sep 04 '15 at 12:53

4 Answers4

3

First of all, allowing MySQL to execute shell commands or executable programs is inviting a security disaster.

Secondly, if your site is hanging up because of too much load in MySQL, restarting MySQL from PHP is inviting a data loss disaster. Spend some time performance tuning your database. There is quite a bit of information about this on the internet.

You can configure your server (presuming you control the server and are not using virtual hosting) to allow read-only (!) access to the .pid file that is present while MySQL is running as suggested by @user1420752. You can check that it exists to see if MySQL is running. That does not ensure that it is at all responsive, though.

Alternatively, you can run just about the cheapest of all MySQL queries

SELECT 1

it will only run if MySQL is up, but does not require any IO to complete (see this).

There are quite a few monitoring solutions for operating systems (Windows/Linux) and your database. Some good ones are open source. I would suggest setting one up to notify you when CPU or IO utilization are high, or if the MySQL server stops, and focus your efforts on MySQL tuning.

Eric J.
  • 147,927
  • 63
  • 340
  • 553
2

You can achieve it by simple bash script. Schedule below script in cron on every 5 minutes or as per your requirement.

#!/bin/bash
EMAILID=yourmail@domain.com

date > /var/log/server_check.log

mysql -uroot -proot123 -e"SELECT USER FROM mysql.user LIMIT 1" >> /var/log/server_check.log 2>&1
if [ $? -eq 0 ]
    then
        echo -e "Hi,\\n\\nMy DB is running \\nThanks,\\nDatabase Admin" | mail -s "My DB is running" $EMAILID
        exit
else

        echo -e "Hi,\\n\\nMy DB is not running, so now starting it.\\nThanks,\\nDatabase Admin" | mail -s "My DB is not running" $EMAILID
                service mysqld start

fi

Further this is just a check but not solution, you should check your queries in slowlogs or your db configuration to get root cause of it and work on it.

Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
1

If your mysql service is block or stop running then, use following shell script to auto start your MySql service.

#!/bin/bash
now=$(date +"%T")
echo "in $(pgrep mysqld | wc -l)  auto mysql start $now"
if [[ $(pgrep mysqld | wc -l) == 0 ]];
then
    sudo -i | /etc/init.d/mysqld start
fi

Set Shell script in Cron job:

*/2 * * * * /bin/sh /home/auto_start_mysql.sh >> /home/cronlog.txt

For more detail check this URL: http://www.easycodingclub.com/check-mysql-running-or-not-using-shell-script-or-php-script/linux-centos/

easycodingclub
  • 323
  • 3
  • 5
0

Platform/version dependent, but how about:

$mysqlIsRunning = file_exists("/var/run/mariadb/mariadb.pid");
magnus
  • 4,031
  • 7
  • 26
  • 48
  • mariadb.pid is presumably specific to the MariaDB branch of MySQL... Also I would be very hesitant to configure my server to give PHP access to /var/run. – Eric J. Sep 04 '15 at 04:19