Monitoring PHP based websites can be difficult because not all hosting providers provide Unix shell access. Usually monitoring software can be installed on the server running the website database, but there are times when that is not possible. Adding PHP code to query the database statistics allows Bronshae to monitor PHP database based websites.
The first step is to determine the type of data for collection from the database. Information like the number of sessions, database size, uptime, and responsiveness are commonly collected. The number of database sessions can be found by querying the "Threads_connected" variable within MYSQL that represents the number of active connections. More information and other Server Status Variables can be found on the MySQL Server Status Variables page. The database uptime can be determined from the same set of Server Status Variables. The size of the database is calculated from the INFORMATION_SCHEMA.TABLES table. The responsiveness of the database is calculated by measuring the total time to collect the information.
The collected data will be placed into name value pairs like the following:
The Bronshae App Status Object will read the PHP output and then convert the name value pairs to an attribute. The collected data is then available for trend analysis or for adding to an operation. The PHP script is available at http://www.bronshae.com/images/articles/HowToMonitorPHPWebSiteDB/mysqlstats.php. After downloading the script, the mysqlstats.php file should be placed in a directory on the web server. For this example, I have put the script in a directory called /var/www/protected.
Protecting Data Access
To protect the page from access by others, it is necessary to setup Apache authentication by using an .htpasswd and .htauth file. The htpasswd command is used to generate the .htpasswd file used by Apache. The command will prompt for a password and then store the encrypted password in the .htpasswd file.
% htpasswd -c /var/www/protected/.htpasswd user1
The .htaccess file is placed in the same protected directory as .htpasswd. More information about generating htaccess password can be found at Generate htaccess password (htpasswd) from the command line.
AuthName "Protected login"
require user user1
Test the access to the protected directory by entering the URL http://website/protected in a browser. There should be a prompt for the username and password before access is given.
The next step is to configure a Bronshae App Status Object to read the output from our mysqlstats.php script.
1. Visit Configure -> Monitors
2. Choose your host that will execute the App Status Object.
3. Add a Status Object called app.App. For this example, I left the name field blank.
4. Click on the Add button
5. Specify the URL, username, password, and name for the protected site. I am using the name MySQLDB. Note that I am also using https to the protected page.
6. Click on the Update button
After the app.MySQLDB Status Object is updated, the host will begin to query the remote website for information. Visiting either the Status by Host page -> Apps or the Status By Object -> app.MySQLDB will show the data being collected.
By adding an App Status Object, Bronshae can monitor vital website db characteristics like number of database sessions, responsiveness, database availability, and database size. This same technique of using an App Status Object with name value pairs can be used with other types of applications too.