Script to back up a MySQL database (e.g. for WordPress blogs)

Jeff Atwood’s recent blog failure has probably served as a timely reminder to website owners across the ‘net – and I’m pleased to see that Jeff got his site back (some of us would be less lucky).  At the time, I tweeted this:

“RT @edbott: Every webmaster’s nightmare: http://bit.ly/7uKgK7 ^MW Feeling sorry for @codinghorror and thanking @alexcoles for my backups”

To which the response from my hosting provider came back as “er… what backups?!”.  Thankfully, that was an attempt at humour (either that or Alex had forgotten that he’d helped me set some backups last summer – it helps to be on first name terms with the guys who look after your Internet presence)!

In common with many blogs, markwilson.it runs on self-hosted WordPress and the key to its existence is a MySQL database.  Themes, etc. can be recreated… the data cannot (at least not easily).

This is where the following script comes in.  Run as a cron job on my host’s server, I have it set up to make a copy of my WordPress database and e-mail it to me (which means I have archive copies in my Google mail).  It took some tweaking (by Alex) to run without errors and I’d like to link to it’s original source but the link I have appears to be dead – I’ve left the original author’s comments intact though – hopefully he won’t mind:


#! /usr/bin/php -q
dev/null

*/

// configure your database variables below:
$dbhost = ‘localhost’; // Server address of your MySQL Server
$dbuser = ‘db_username’; // Username to access MySQL database
$dbpass = ‘db_password’; // Password to access MySQL database
$dbname = ‘db_name’; // Database Name

// Options You May Optionally Configure

$use_gzip = “no”; // Set to No if you don’t want the files sent in .gz format
$remove_sql_file = “yes”; // Set this to yes if you want to remove the .sql file after gzipping. Yes is recommended.
$remove_gzip_file = “yes”; // Set this to yes if you want to delete the gzip file also. I recommend leaving it to “no”

// Configure the path that this script resides on your server.

$savepath = “/home/username/wpbackup”; // Full path to this directory. Do not use trailing slash!

$send_email_w_file = “yes”; // Do you want this database backup sent to your email? Fill out the next 2 lines
$to = “emailaddress”; // Who to send the emails to
$from = “emailaddress”; // Who should the emails be sent from?
$senddate = date(“j F Y”);
$subject = “markwilson.it WordPress backup”; // Subject in the email to be sent.
$headers = “From: $from”;
if ($send_email_w_file == “yes”)
{
$message = “Attached is a MySQL dump of sitename dated $senddate.”;
// Brief Message.
}
else
{
$message = “Your MySQL database has been backed up and placed in $savepath.”; // Brief Message.
}

$use_ftp = “no”; // Do you want this database backup uploaded to an ftp server? Fill out the next 4 lines
$ftp_server = “localhost”; // FTP hostname
$ftp_user_name = “ftp_username”; // FTP username
$ftp_user_pass = “ftp_password”; // FTP password
$ftp_path = “/”; // This is the path to upload on your ftp server!

// Do not Modify below this line! It will void your warranty!

//———GZIP CLASS
class zipfile
{

var $datasec = array(); // array to store compressed data
var $ctrl_dir = array(); // central directory
var $eof_ctrl_dir = “\x50\x4b\x05\x06\x00\x00\x00\x00”; //end of Central directory record
var $old_offset = 0;

function add_dir($name)

// adds “directory” to archive – do this before putting any files in directory!
// $name – name of directory… like this: “path/”
// …then you can add files using add_file with names like “path/file.txt”
{
$name = str_replace(“\\”, “/”, $name);

$fr = “\x50\x4b\x03\x04”;
$fr .= “\x0a\x00”; // ver needed to extract
$fr .= “\x00\x00”; // gen purpose bit flag
$fr .= “\x00\x00”; // compression method
$fr .= “\x00\x00\x00\x00”; // last mod time and date

$fr .= pack(“V”,0); // crc32
$fr .= pack(“V”,0); //compressed filesize
$fr .= pack(“V”,0); //uncompressed filesize
$fr .= pack(“v”, strlen($name) ); //length of pathname
$fr .= pack(“v”, 0 ); //extra field length
$fr .= $name;
// end of “local file header” segment

// no “file data” segment for path

// “data descriptor” segment (optional but necessary if archive is not served as file)
$fr .= pack(“V”,$crc); //crc32
$fr .= pack(“V”,$c_len); //compressed filesize
$fr .= pack(“V”,$unc_len); //uncompressed filesize

// add this entry to array
$this -> datasec[] = $fr;

$new_offset = strlen(implode(“”, $this->datasec));

// ext. file attributes mirrors MS-DOS directory attr byte, detailed
// at http://support.microsoft.com/suppor…s/Q125/0/19.asp

// now add to central record
$cdrec = “\x50\x4b\x01\x02″;
$cdrec .=”\x00\x00″; // version made by
$cdrec .=”\x0a\x00″; // version needed to extract
$cdrec .=”\x00\x00″; // gen purpose bit flag
$cdrec .=”\x00\x00″; // compression method
$cdrec .=”\x00\x00\x00\x00”; // last mod time & date
$cdrec .= pack(“V”,0); // crc32
$cdrec .= pack(“V”,0); //compressed filesize
$cdrec .= pack(“V”,0); //uncompressed filesize
$cdrec .= pack(“v”, strlen($name) ); //length of filename
$cdrec .= pack(“v”, 0 ); //extra field length
$cdrec .= pack(“v”, 0 ); //file comment length
$cdrec .= pack(“v”, 0 ); //disk number start
$cdrec .= pack(“v”, 0 ); //internal file attributes
$ext = “\x00\x00\x10\x00”;
$ext = “\xff\xff\xff\xff”;
$cdrec .= pack(“V”, 16 ); //external file attributes – ‘directory’ bit set

$cdrec .= pack(“V”, $this -> old_offset ); //relative offset of local header
$this -> old_offset = $new_offset;

$cdrec .= $name;
// optional extra field, file comment goes here
// save to array
$this -> ctrl_dir[] = $cdrec;

}

function add_file($data, $name)

// adds “file” to archive
// $data – file contents
// $name – name of file in archive. Add path if your want

{
$name = str_replace(“\\”, “/”, $name);
//$name = str_replace(“\\”, “\\\\”, $name);

$fr = “\x50\x4b\x03\x04”;
$fr .= “\x14\x00”; // ver needed to extract
$fr .= “\x00\x00”; // gen purpose bit flag
$fr .= “\x08\x00”; // compression method
$fr .= “\x00\x00\x00\x00”; // last mod time and date

$unc_len = strlen($data);
$crc = crc32($data);
$zdata = gzcompress($data);
$zdata = substr( substr($zdata, 0, strlen($zdata) – 4), 2); // fix crc bug
$c_len = strlen($zdata);
$fr .= pack(“V”,$crc); // crc32
$fr .= pack(“V”,$c_len); //compressed filesize
$fr .= pack(“V”,$unc_len); //uncompressed filesize
$fr .= pack(“v”, strlen($name) ); //length of filename
$fr .= pack(“v”, 0 ); //extra field length
$fr .= $name;
// end of “local file header” segment

// “file data” segment
$fr .= $zdata;

// “data descriptor” segment (optional but necessary if archive is not served as file)
$fr .= pack(“V”,$crc); //crc32
$fr .= pack(“V”,$c_len); //compressed filesize
$fr .= pack(“V”,$unc_len); //uncompressed filesize

// add this entry to array
$this -> datasec[] = $fr;

$new_offset = strlen(implode(“”, $this->datasec));

// now add to central directory record
$cdrec = “\x50\x4b\x01\x02″;
$cdrec .=”\x00\x00″; // version made by
$cdrec .=”\x14\x00″; // version needed to extract
$cdrec .=”\x00\x00″; // gen purpose bit flag
$cdrec .=”\x08\x00″; // compression method
$cdrec .=”\x00\x00\x00\x00”; // last mod time & date
$cdrec .= pack(“V”,$crc); // crc32
$cdrec .= pack(“V”,$c_len); //compressed filesize
$cdrec .= pack(“V”,$unc_len); //uncompressed filesize
$cdrec .= pack(“v”, strlen($name) ); //length of filename
$cdrec .= pack(“v”, 0 ); //extra field length
$cdrec .= pack(“v”, 0 ); //file comment length
$cdrec .= pack(“v”, 0 ); //disk number start
$cdrec .= pack(“v”, 0 ); //internal file attributes
$cdrec .= pack(“V”, 32 ); //external file attributes – ‘archive’ bit set

$cdrec .= pack(“V”, $this -> old_offset ); //relative offset of local header
// echo “old offset is “.$this->old_offset.”, new offset is $new_offset
“;
$this -> old_offset = $new_offset;

$cdrec .= $name;
// optional extra field, file comment goes here
// save to central directory
$this -> ctrl_dir[] = $cdrec;
}

function file() { // dump out file
$data = implode(“”, $this -> datasec);
$ctrldir = implode(“”, $this -> ctrl_dir);

return
$data.
$ctrldir.
$this -> eof_ctrl_dir.
pack(“v”, sizeof($this -> ctrl_dir)). // total # of entries “on this disk”
pack(“v”, sizeof($this -> ctrl_dir)). // total # of entries overall
pack(“V”, strlen($ctrldir)). // size of central dir
pack(“V”, strlen($data)). // offset to start of central dir
“\x00\x00”; // .zip file comment length
}
}

//——— end class

$date = date(“Ymd-Hi”);
$filename = “$savepath/$dbname-$date.sql”;
passthru(“/usr/bin/mysqldump –opt -h$dbhost -u$dbuser -p$dbpass $dbname >$filename”);

if($use_gzip==”yes”){
$zipfile = new zipfile();

// add the subdirectory … important!
$zipfile -> add_dir($savepath);

// add the binary data stored in the string ‘filedata’
$file6 = fopen($filename,’rb’);
$filedata = fread($file6,filesize($filename));
fclose($file6);
//$filedata = “(read your file into $filedata)”;
$zipfile -> add_file($filedata, $filename);

// OR instead of doing that, you can write out the file to the loca disk like this:
$filename3 = $savepath.”/”.$dbname.”.”.$date.”_sql.tar.gz”;
$fd = fopen ($filename3, “wb”);
$out = fwrite ($fd, $zipfile -> file());
fclose ($fd);

//$zipline = “tar -czf “.$dbname.”.”.$date.”_sql.tar.gz “.$dbname.”-“.$date.”.sql”;
//echo shell_exec($zipline);
}

if($use_gzip==”yes”)
{
$filename2 = $filename3;
}
else
{
$filename2 = “$savepath/$dbname-$date.sql”;
}

if($send_email_w_file == “yes” )
{
$fileatt_type = filetype($filename2);
$fileatt_name = “”.$dbname.”-“.$date.”_sql”; // change to _sql.tar.gz if gzip file

// Read the file to be attached (‘rb’ = read binary)
$file = fopen($filename2,’rb’);
$data = fread($file,filesize($filename2));
fclose($file);

// Generate a boundary string
$semi_rand = md5(time());
$mime_boundary = “==Multipart_Boundary_x{$semi_rand}x”;

// Add the headers for a file attachment
$headers .= “\nMIME-Version: 1.0\n” .”Content-Type: multipart/mixed;\n” .” boundary=\”{$mime_boundary}\””;

// Add a multipart boundary above the plain message
$message = “This is a multi-part message in MIME format.\n\n” .”–{$mime_boundary}\n” .”Content-Type: text/plain; charset=\”iso-8859-1\”\n” .”Content-Transfer-Encoding: 7bit\n\n” .
$message . “\n\n”;

// Base64 encode the file data
$data = chunk_split(base64_encode($data));

// Add file attachment to the message
$message .= “–{$mime_boundary}\n” .”Content-Type: {$fileatt_type};\n” .” name=\”{$fileatt_name}\”\n” .”Content-Disposition: attachment;\n” .” filename=\”{$fileatt_name}\”\n” .”Content-Transfer-Encoding: base64\n\n” .
$data . “\n\n” .”–{$mime_boundary}–\n”;
}

// Send the message
$ok = @mail($to, $subject, $message, $headers);
if ($ok)
{
echo “Database backup created and sent! File name $filename2”;
}
else
{
echo “Mail could not be sent. Sorry!”;
}

if($use_ftp == “yes”)
{
$ftpconnect = “ncftpput -u $ftp_user_name -p $ftp_user_pass -d debsender_ftplog.log -e dbsender_ftplog2.log -a -E -V $ftp_server $ftp_path $filename2”;
shell_exec($ftpconnect);
echo “

$filename2 Was created and uploaded to your FTP server!

“;
}

if($remove_sql_file==”yes”)
{
exec(“rm -r -f $filename”);
}

if($remove_gzip_file==”yes”)
{
exec(“rm -r -f $filename2”);
}
?>

All you need to do is: save this on a web server somewhere; edit the variables at top of the script; and set up a cron job to run it at whatever schedule suits.

For many people, that should be enough to ensure that their WordPress installation is safe from any catastrophe (note: no warranty is implied and I’m not responsible if things do go wrong for you).  For me though, I have an additional consideration as my images are not in the database… maybe that will be the subject of a future blog post.  In the meantime, I can sleep soundly knowing that the database behind my site is backed up regularly.

Installing WordPress on a Mac: the aftermath (phpMyAdmin, databases, themes, plugins and fixing the tags)

Last week I wrote about installing WordPress on a Mac but I wanted to follow that up with a post on what happened next.

Installing phpMyAdmin

Installing WordPress is all very well, but it’s good to be able to manipulate the database. The command line mysql tools would have worked but a graphical interface (even an ugly one with bizarre icons) is often easier, so I installed phpMyAdmin as described by Nino Müller:

  • Download the latest version of phpMyAdmin (I used v3.1.2).
  • Extract the files to ~/Sites/phpmyadmin.
  • Copy config.sample.inc.php to config.inc.php and edit the Blowfish secret (the line which reads $cfg['blowfish_secret'] = ''; .
  • Navigate to http://localhost/~username/phpmyadmin and login.

Unfortunately, after attempting to logon, I was presented with an error message:

#2002 – The server is not responding (or the local MySQL server’s socket is not correctly configured)

Following The Vince Wadhwani’s advice at his Hackido site I typed mysqlconfig --socket to verify the socket is in use for MySQL (e.g. /tmp/mysql.sock) but I couldn’t find a config.default.php file (or the equivalent entry in my config.inc.php file) to adjust the socket. A post at Friends of ED suggested creating a symbolic link for the socket and it seemed to work for me:

sudo mkdir /var/mysql
sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

Following this I could log into phpMyAdmin (although I still have a warning message that phpMyAdmin cannot load the mcrypt extension – this doesn’t seem to be causing any problems though).

Importing a WordPress database

Using phpMyAdmin on my web host’s server, I exported the database from the live copy of markwilson.it and attempted to import it on the Mac. Unfortunately this didn’t work as my database was too large for PHP to upload – as confirmed by creating a file named ~/Sites/phpinfo.php containing <?php phpinfo(); ?>, then viewing it in a browser (http://localhost/~username/phpinfo.php) and looking for the upload_max_filesize variable.

Rather than messing around with my PHP configuration, I googled for the necessary commands and typed:

/user/local/mysql/bin/mysql -u root -p
drop database wordpressdatabasename;
source ./Downloads/wordpressdatabasename.sql
quit

At this point, the local copy of WordPress was running on the live database, but the links were all to the live site, so I used phpMyAdmin to edit the site URL in the wp_options table, changing it from http://www.markwilson.co.uk/blog to http://localhost/~username/blog.

Because the live copy of the site is on an old version of WordPress, browsing to http://localhost/~username/blog/wp-admin prompted me to upgrade the database, after which I could log in and edit the site settings (e.g. the blog address).

WordPress database upgrade

WordPress 2.7 Dashboard

Restoring the theme and plugins

At this point, although WordPress was running on a local copy of my live database, the normal theme was missing and the plugins were disabled (as they were not present). I copied them from the live server and, after selecting the theme and enabling the plugins saw something approaching normality, although there were a few plugins that required updating and I still couldn’t get rid of a particularly annoying database error:

WordPress database error: [Table ‘wordpressdatabasename.wp_categories’ doesn’t exist]
SELECT cat_name FROM wp_categories ORDER BY cat_name ASC

By disabling plugins one by one (I could also have grepped /~Sites/blog/wp-admin/wp-content/plugins for wp_categories), I found that the issue was in the Bad Behavior plugin that I use to ban IP addresses known to send spam.

Moving from categories to tags

When I first moved this site to WordPress, I used Dean Robinson’s Ultimate Category Cloud plugin to provide a tag cloud (at that time WordPress did not support tags). Over time, that because unmanageable and, although I still need to define a decent taxonomy for the site, the categories still have some value if they are converted to tags.

Over some tapas and drinks in the pub, my friend Alex Coles at ascomi and I had a look at the database structure and Alex came up with a quick SQL query to run against my WordPress database:

UPDATE wp_term_taxonomy SET taxonomy='post_tag' WHERE taxonomy='category'

That converted all of my categories to tags, but there were some I manually edited to return to categories (General – which was once called Uncategorised – and Site Notices) but for some reason, all the posts were recorded in a category of Uncatagorized. Some late night PHP coding (reminiscent of many nights at Uni’ – as Steve will no doubt remember – although in those days it was Modula-2, C, C++ and COBOL) resulted in a script to run through the database, identify all posts with a category of 17 (which in my database is the default category of “General”), put the post numbers into an array and then explicitly set the category as required, taking a note of the ones which have been changed so that they can be ignored from that point on:

<html>
<head>
</head>

<body>
<?php

// Connect to the WordPress database
$db_hostname = "localhost:/tmp/mysql.sock";
$db_username = "wordpressuser";
$db_password = "wordpresspassword";
$db_connect = mysql_connect($db_hostname, $db_username, $db_password) or die("Unable to connect to server.");
$db = mysql_select_db("wordpressdatabasename",$db_connect);

// Retrieve all objects including a category with the value of 17 (my default category)
$hascat = mysql_query("SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id = '17' ORDER BY object_id");
echo '<p>'.mysql_num_rows($hascat).' rows found with category</p>';

$correct_ids = array();

// Build a PHP array (not a MySQL array) containing the relevant object IDs for later comparison
while ($row = mysql_fetch_array($hascat))
{
$correct_ids[] = $row[0];
}
echo '<p>Array built. Length is '.count($correct_ids).'. First ID is '.$correct_ids[0].'.</p>';

// Retrieve every object
$result = mysql_query("SELECT * FROM wp_term_relationships ORDER BY object_id");
echo '<p>'.mysql_num_rows($result).' rows found total</p>';

// The magic bit!
// If the object is not in our previous array (i.e. the category is not 17)
// then add it to category 17 and put it in the array so it won't get added repeatedly
while ($row = mysql_fetch_array($result))
{
if (!in_array($row['object_id'],$correct_ids))
{
// Add to category 17
mysql_query("INSERT INTO wp_term_relationships (object_id,term_taxonomy_id,term_order) VALUES ('".$row['object_id']."','17','0')");
echo '<p>Alter database entry for object '.$row['object_id'].'.</p>';
// Add to the array so it is not flagged again
$correct_ids[]=$row['object_id'];
}
else echo '<p style="color:white; background-color:black">'.$row['object_id'].' ignored.</p>';
}

?>
</body>
</html>

Remaining issues

Permalinks don’t seem to work – it seems that Mac OS X does not support using .htaccess files by default and, whilst it’s possible to modify for the root folder it doesn’t seem to work for individual user sites. I’ll do some more digging and see if I can find a fix for that one.

WordPress also features the ability to automatically update plugins (and itself), but my installation is falling back to FTP access when I try to update it and making it work appears to be non-trivial. Again, I’ll take another look when I have more time.

Installing WordPress on a Mac

The software platform which markwilson.it runs on is in desperate need of an updated but there is only me to make it happen (supported by ascomi) and if I make a mistake then it may take some time for me to get the site back online (time which I don’t have!). As a result, I really needed a development version of the site to work with.

I thought that it would also be handy if that development version of the site would run offline – i.e. if it were served from a web server on one of my computers. I could run Windows, IIS (or Apache), MySQL and PHP but as the live site runs on CentOS, Apache, MySQL and PHP it makes sense to at least use something similar and my Mac fits the bill nicely, as a default installation of OS X already includes Apache and PHP.

I should note that there are alternative stacks available for running a web server on a Mac (MAMP and XAMPP are examples); however my machine is not a full web server serving hundreds of users, it’s a development workstation serving one user, so the built in tools should be fine. The rest of this post explains what I did to get WordPress 2.7 up and running on OS X 10.5.5.

  1. Open the System Preferences and select the Sharing pane, then enable Web Access.
  2. Web Sharing in OS X

  3. Test access by browsing to the default Apache website at http://computername/ and a personal site at http://computername/~username/.
  4. Download the latest version of MySQL Community Server (I used mysql-5.1.31-osx10.5-x86_64) and run the corresponding packaged installer (for me that was mysql-5.1.31-osx10.5-x86_64.pkg).
  5. After the MySQL installation is completed, copy MySQL.PreferencePane to /Library/PreferencePanes and verify that it is visible in System Preferences (in the Other group).
  6. MySQL Preferences in OS X

  7. Launch the MySQL preference pane and start MySQL Server (if prompted by the firewall to allow mysqld to allow incoming connections, allow this). Optionally, select automatic startup for MySQL.
  8. MySQL running in OS X

  9. Optionally, add /usr/local/mysql/bin to the path (I didn’t do this, as creating a .profile file containing export PATH="$PATH:/usr/local/mysql/bin" seemed to mess up my path somehow – it just means that I need to specify the full path when running mysql commands) and test access to MySQL by running /usr/local/mysql/bin/mysql.
  10. Enable PHP by editing /etc/apache2/httpd.conf (e.g. by running sudo nano /etc/apache2/httpd.conf) to remove the # in front of LoadModule php5_module libexec/apache2/libphp5.so.
  11. Test the PHP configuration by creating a text file named phpinfo.php containing <?php phpinfo(); ?> and browse to http://localhost/~username/phpinfo.
  12. With Mac OS X, Apache, MySQL and PHP enabled, start to work on the configuration by by running /usr/local/mysql/bin/mysql and entering the following commands to secure MySQL:
    drop database test;
    delete from mysql.user where user = '';
    flush privileges;
    set password for root@localhost = password('{newrootpassword}');
    set password for root@127.0.0.1 = password('{newrootpassword}');
    set password for 'root'@'{hostname}.local' = password('{newrootpassword}');
    quit
  13. Test access to MySQL. using the new password with /usr/local/mysql/bin/mysql -u root -p and entering newrootpassword when prompted.
  14. Whilst still logged in to MySQL, enter the following commands to create a database for WordPress and grant permissions (I’m not convinced that all of these commands are required and I do not know what foo is!):
    create database wpdatabasename;
    grant all privileges on wpdatabasename.* to wpuser@localhost identified by 'foo';
    set password for wpuser@localhost = old_password('wppassword');
    quit
  15. Download the latest version of WordPress and extract it to ~username/Sites/ (i chose to put my copy in a subfolder called blog, as it is on the live site).
  16. Configure WordPress to use the database created earlier by copying wordpressdirectory/wp_config_sample.php to wordpressdirectorywp_config.php and editing the following lines:
    define('DB_NAME', 'wpdatabasename');
    define('DB_USER', 'wpuser');
    define('DB_PASSWORD', 'wppassword');
    define('DB_HOST', 'localhost:/tmp/mysql.sock');
  17. Restart Apache using sudo apachectl restart.
  18. If WordPress is running in it’s own subdirectory, copy wordpressdirectory/index.php and wordpressdirectory/.htaccess to ~/Sites/ and then edit index.php so that WordPress can locate it’s environment and templates (require('./wordpressdirectory/wp-blog-header.php');).
  19. Browse to http://localhost/~username/wordpressdirectory/wp-admin/install.php and follow the “five minute WordPress installation process”.
  20. WordPress installation

  21. After installation, the dashboard for the new WordPress site should be available at http://localhost/~username/wordpressdirectory/wp-admin/.
  22. WordPress fresh out of the box (dashboard)

  23. The site may be accessed at http://localhost/~username/wordpressdirectory/.
  24. WordPress fresh out of the box

Credits

I found the following articles extremely useful whilst I was researching this post: