Home > Sci/Tech > WordPress SQL Database Backup and Optimization Without Plugins

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";

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!


My posts are not to serve as a replacement for recommendations provided by licensed physicians nor do they represent the opinions of Baylor College of Medicine or its affiliated institutions. Please read this site's Disclaimer and Terms of Use for more information.


Exit Pixel C – Enter iPad Pro

About a week ago, I sold my Pixel C and its keyboard for ~$500 on …


Your email address will not be published. Required fields are marked *