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);
?>
Upload the script using sftp/ftp client to your web server, run:
$ scp db-mysql-test.php vivek@server1.cyberciti.biz:/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
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);
?>
Save and upload the script using either ftp command or sftp command:
$ scp db-mysql-test.php vivek@server1.cyberciti.biz:/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
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
PHP 5/7 mysqli_connect()
PHP 7/5 require()
WordPress WP_CONFIG file
