How to test PHP 7 and WordPress MySQL/MariaDB Database connectivity using a PHP script

Posted on in Categories , , , last updated August 15, 2017

I wanted to test if my PHP 7 and MySQL/MariaDB for WordPress are working. If not I wanted to see some error on screen or for my monitoring system. How to write a script for a quick PHP MySQL DB connection test for WordPress or any other database and send an email or text when site is down?

A PHP 7 + MySQL/MariaDB script for WordPress

The following script will use database/password info from WordPress config file itself. Create a script named db-mysql-test.php and put in wordpress directory where you will find a file named wp-config.php:

<?php
define('WEB_ID','l-cbz01');
require('wp-config.php');
$link = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
 
// failed
// die with an error
if (!$link) {
    // send HTTP/500 status code first
    header($_SERVER['SERVER_PROTOCOL'] . ' 500 Internal Server Error', true, 500);
    echo "<html><head><title>Failed at " .WEB_ID. "</title></head><body><pre>";
    echo "Web server: ". WEB_ID .  PHP_EOL;
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}
 
// succeeded and show message
echo "<html><head><title>Success at " .WEB_ID. "</title></head><body><pre>";
echo "Success: A proper connection to MySQL was made! The my_db database is great." . PHP_EOL;
echo "Host information: " . mysqli_get_host_info($link) . PHP_EOL;
mysqli_close($link);
?>

Upload the script using sftp/ftp client to your web server, run:
$ scp db-mysql-test.php [email protected]:/var/www/html/cms/
Fire a browser and test it:
https://www.cyberciti.biz/cms/db-mysql-test.php
Sample outputs when there is no error:

Fig.01: Success test output
Fig.01: Success test output

Sample outputs when there is an error like PHP 7 process not running or MySQL server is down:
Fig.02: Error: Unable to connect to MySQL status page
Fig.02: Error: Unable to connect to MySQL status page

A PHP 7 + MySQL/MariaDB script for custom database (any cms)

If you are not using WordPress or using something else, try the following script. You must set DB_HOST, DB_USER, DB_PASSWORD, and DB_NAME:

<?php
// set me
define('WEB_ID','l-cbz01');
define('DB_HOST', 'localhost');
define('DB_USER', 'vivek');
define('DB_PASSWORD', 'myPasswordHere');
define('DB_NAME', 'myDBNameHere');
$link = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
 
// failed
// die with an error
if (!$link) {
    // send HTTP/500 status code first
    header($_SERVER['SERVER_PROTOCOL'] . ' 500 Internal Server Error', true, 500);
    echo "<html><head><title>Failed at " .WEB_ID. "</title></head><body><pre>";
    echo "Web server: ". WEB_ID .  PHP_EOL;
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}
 
// succeeded and show message
echo "<html><head><title>Success at " .WEB_ID. "</title></head><body><pre>";
echo "Success: A proper connection to MySQL was made! The my_db database is great." . PHP_EOL;
echo "Host information: " . mysqli_get_host_info($link) . PHP_EOL;
mysqli_close($link);
?>

Save and upload the script using either ftp command or sftp command:
$ scp db-mysql-test.php [email protected]:/var/www/html/
Test it with your favorite web browser:
https://www.cyberciti.biz/cms/db-mysql-test.php

A note about sending a text/sms/email when script fails (optional)

You need to use third party monitoring service such as Pingdom or others. For example, you can edit your Pingdom test as follows:

Fig.03: Pingdom test
Fig.03: Pingdom test

I set “Check for string” to “Should not contain” to “Error: Unable to connect to MySQL” and click on Modify test button. A text string that must not be present (e.g. “Error: Unable to connect to MySQL”) in the HTML code of the page. If this text is found from the page, the site will be considered as down and you will an alert via text/sms or email as follows:

Fig.04: Pingdom DOWN alert email
Fig.04: Pingdom DOWN alert email

References

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

Leave a Comment