Blog

03/06/2019

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



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);
?>

<?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 vivek@server1.sxi.io:/var/www/html/cms/
Fire a browser and test it:
https://sxi.io/cms/db-mysql-test.php
Sample outputs when there is no error:

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

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);
?>

<?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 vivek@server1.sxi.io:/var/www/html/
Test it with your favorite web browser:
https://sxi.io/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

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

References

(adsbygoogle = window.adsbygoogle || []).push({});

Posted by: SXI ADMIN

The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.

20/08/2019

Start your Bitcoin Exchange with our Software

Start your Bitcoin Exchange with our Software Setup your white label bitcoin exchange right away. Or create your own cryptocurrency using...
14/08/2019

How to KVM, QEMU start or stop virtual machine from command line (CLI)

KVM or Kernel Based Virtual Machine is a popular virtualization technology. It allows you to run virtual guest machines over a host machine. To start...
14/08/2019

How to Docker backup Saving and restoring your volumes

Running a Docker volume backup First, we spin up a temporary container, and we mount the backup folder and the target Docker volume to this container....