Optimize Multiple MySQL Databases and Tables Automatically

Wrench and Screwdriver on KeyboardI’ve always been diligent about optimizing my clients’ MySQL database tables on a regular basis to reduce storage space and improve input/output efficiency. When I first started out, MyISAM was the database storage engine standard for MySQL, and  I used to do the table optimization manually, then phpMyAdmin came along and I began using its user interface, but that quickly became impractical as my client base grew. Some of my clients have multiple databases and each of those had multiple tables. I needed an automated solution. I came up with one using a combination of cron and PHP that has worked well for me for years. I had a client ask about the script I use, and I’ve decided to share it here.

My solution involves four main steps:

  1. Create a database user for the optimization operation.
  2. Add the database user to each database to be optimized.
  3. Create the PHP script to for the optimization.
  4. Create the cron job to run the PHP optimization script.

I’ll describe the steps using cPanel.

 Create a Database User

The first step is to create a database user for the optimization script to use. Sign in to cPanel and in the “Databases” section click on “MySQL Databases”. Scroll down to the “MySQL Users” section and add a new user. I usually just create a database user called “opt,” which makes it easy for me to keep track of the purpose of that database user.

Add the User to Each Database

After adding the “opt” user, scroll down to the “Add a User to a Database” section and add the “opt” user to each database you want to optimize. To run the “OPTIMIZE TABLE” database administration statement, all the database user will need is “SELECT” and “INSERT” permissions, so for optimal security, limit the user to those permissions for each database.

Create the PHP Script

The PHP script that optimizes the tables makes use of the “information_schema” table included with MySQL in cPanel/phpMyAdmin implementations. The information_schema table includes all the information required to get a list of the databases and corresponding table names to construct the OPTIMIZE TABLE MySQL statement generated by the script.

The script is as follows:

<?php
// ***** SET THE FOLLOWING *****
$host = 'localhost';
$dbuser = '';	// The database user you created.
$dbpass = '';	// The password you used for the database user.
$email = '';	// The complete email address where you want to receive messages regarding the script.
$domain = '';			// The domain for the account you're optimizing; e.g., yourdomain.com.

// The following can be used for debugging.
define('DEBUG_MODE', 0);	// off
//define('DEBUG_MODE', 1);	// on
// ***** END OF SETTINGS *****

// The next three constants are for the MailNotice function.
define('MAILNOTICE_TO', $email);
define('MAILNOTICE_FROM', 'noreply@' . $domain);
define('MAILNOTICE_REPLY_TO', 'noreply@' . $domain);

// ****** FUNCTIONS ******
function MailNotice($linenumber, $msg) {
	/* This function sends you an email notice if there is a problem with one of the
		operations in this script.
	*/
	// add the line number to the subject
	$subject = "MySQL Optimize Script Error --Line " . $linenumber;
		$reply = "From: " . MAILNOTICE_FROM . "\r\n" . "Reply-To: " . MAILNOTICE_REPLY_TO . "\r\n";
		mail(MAILNOTICE_TO, $subject, $msg, $reply);
}
// ***** END FUNCTIONS *****
try {
	$dbh = new PDO("mysql:host=$host", $dbuser, $dbpass);
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$sth = $dbh->prepare("SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' ORDER BY TABLE_SCHEMA, TABLE_NAME");
	$sth->execute();
	if ($sth->rowCount() > 0) {
		$sql = "OPTIMIZE TABLE ";
		while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
			if (DEBUG_MODE == 1) {
				echo '`' . $row['TABLE_SCHEMA'] . '`.`' . $row['TABLE_NAME'] . '`<br>';
			}
			$sql .= '`' . $row['TABLE_SCHEMA'] . '`.`' . $row['TABLE_NAME'] . '`, ';
		}
		$sql = substr($sql, 0, strlen($sql) - 2);
		if (DEBUG_MODE == 1) {
			echo 'sql = ' . $sql;
		} else {
			$sth = $dbh->prepare($sql);
			$sth->execute();
		}
	}
} catch(PDOException $e) {
	MailNotice(52, $e->getMessage() . ' sql = ' . $sql);
}
?>

If the script catches an exception when trying to optimize the database tables, it will email the error and the query it attempted to execute to the email address set in the script for debugging purposes.

To create the script in cPanel:

  1. Click the “Home” icon at the top left of the cPanel screen, then in the “Files” section, click the “File Manager” icon.
  2. I recommend saving the script to a directory outside of public_html so it won’t be accessible via the web. For the sake of this example, create a new directory in your /home/username directory called “scripts” by clicking the “New Folder” icon on the toolbar.
  3. Click on the newly created directory “scripts” to select it.
  4. Click “New File” icon on the toolbar and enter the name for your script; e.g., “mysql_optimize.php”, then click the “Create New File” button.
  5. Click the “mysql_optimize.php” file you created to select it, and then click the “Code Editor” icon on the toolbar.
  6. Copy the script above and paste it in the Code Editor, then fill out the host (usually, this will just be “localhost”), dbuser (the database user you created), dbpasss (the database user’s password), email (an email address the script can send you messages in case of an error with the script), and domain (the domain the script is sending the email from) at the top of the script.
  7. Click the “Save” button at the top right of the editor, then click “Close” to close the editor window.
  8. Click the “Change permissions” icon and set the permissions for the mysql_optimize.php file to 700.

Create the Cron Job

To run your script automatically, you need a cron job set up that calls the script. The exact command will differ server-to-server depending on the way the server has been set up; however, this basic example will work on most servers.

In cPanel:

  1. Click “Cron Jobs” in the Advanced section.
  2. In the Add a New Cron Job section, select a frequency from the “Common Settings” drop-down, or create your own using the user interface below it. For most databases, running the optimization script monthly will be adequate.
  3. In the “Command” text box, enter the following, substituting the user name of your cPanel account for “<username>” and adjusting the path as needed for your server: /usr/bin/php /home/<username>/scripts/mysql_optimize.php >/dev/null 2>&1
  4. Click “Add New Cron Job” to save your changes.

That’s it! The cron job will now run the script and optimize the database tables automatically.

Testing the Script

The script includes a debug mode that can be toggled on and off by commenting/uncommenting the corresponding lines of code on lines 10 and 11.

When in debug mode, the script will output the list of the database(s) and table(s) it collects as well as the query it prepares instead of executing the query.

To debug the script, save it to a public directory and execute it via a browser.