WordPress SQL Database Backup and Optimization Without Plugins

In my pursuit for optimization without utilizing resource-hungry plugins, I’ve compiled the following PHP snippets to run as cron jobs on my Bluehost account.

Daily backup – requires the database name and its associated username/password. You’ll need a “backup” folder under public_html (or modify the save path accordingly in the code). Executing the script will create a GZipped backup archive of the specified database. I run this daily and save the file as the day of the week. Replace DB_USERNAME, DB_PASSWORD, and DB_NAME in the following script.

<?php $day=date("D");
$command="mysqldump -u DB_USERNAME -pDB_PASSWORD DB_NAME | gzip > ~/public_html/backup/$day.sql.gz";
system($command);?>

Note that there is no space between the “-p” flag and DB_PASSWORD.

Database optimization – optimizes all the databases linked to a username/password combination. Replace DB_USERNAME and DB_PASSWORD in the following script.

<?php $link = mysql_connect('localhost', 'DB_USERNAME', 'DB_PASSWORD');
if (!$link) {die('Could not connect: ' . mysql_error());}
$q= mysql_query("SHOW DATABASES") or die(mysql_error());
while ($dbName = mysql_fetch_array($q)) {
if ($dbName[0] != "information_schema") {echo " + Selecting " . $dbName[0] . "n";
$db_selected = mysql_select_db($dbName[0], $link);
if (!$db_selected) {die ('Can't use $dbName[0] : ' . mysql_error());}
$alltables = mysql_query("SHOW TABLES") or die(mysql_error());
while ($tableName = mysql_fetch_array($alltables)) {echo "   - Analyzing " . $tableName[0] . "n";
mysql_query("ANALYZE TABLE `".$tableName[0]."`") or die(mysql_error());
echo "   - Optimizing " . $tableName[0] . "n";
mysql_query("OPTIMIZE TABLE `".$tableName[0]."`") or die(mysql_error());}} 
else {echo " + Skipping " . $dbName[0] . "n";}}mysql_close($link);?>

I saved the scripts above as two different PHP files which I run through a Cron Job task (under CPanel).

/ramdisk/bin/php5 -q /path/to/your/script.php

Leave questions below!