Backing up your site and files is a Must, and many servers have back-up options available to them. But one thing that is forgotten far too often when backing up your site, is backing up your database.

With a blog, like this and most wordpress blogs, the only part of the site to regularly change is the database. The site itself rarely changes other than the few uploaded photos.

Every Post, Comment, Trackback, registration all modifies the database. I like to take mywordpress DB from this online Blog, and dump in to my local copy so I have content, and comments/trackbacks where I test plugins/designs/hacks.

So here is a simple PHP script that will back up a whole database, and save the output as either a .sql file, or a compressed gunzip archive to a specified directory.

function mysql_backup($type,$host, $user, $password, $table, $path) {
	global $day;
	if($type == "gz"){
		// Gunzipp Output (.gz)
		$backup = $path.$table.'_'.$day.'.gz';
		exec(
			sprintf(
				'/usr/local/mysql/bin/mysqldump --host=%s --user=%s --password=%s %s --quick --lock-tables --add-drop-table gzip > %s',
				$host,
				$user,
				$password,
				$table,
				$backup
			)
		);
	}elseif($type == "sql"){
		// SQL Outout (.sql)
		$backup = $path.$table.'_'.$day.'.sql';
		exec(
			sprintf(
				'/usr/local/mysql/bin/mysqldump --host=%s --user=%s --password=%s %s --quick --lock-tables --add-drop-table > %s',
				$host,
				$user,
				$password,
				$table,
				$backup
			)
		);
	}
	return true;
}

Here is an example of how to use it.
In a PHP file.

$day = date("l"); // Lowercase L
mysql_backup('sql','localhost', 'SQL_Username', 'SQL_Password', 'table', '/root/path/to/mysql/data/backups/');

The Function perametors are in order;

'sql' or 'gz'         -> Type of backup.
'localhost'          -> mySQL Server path
'SQL_Username' -> mySQL Username with all read/write privileges
'SQL_Password'  -> mySQL Password
'table'               -> mySQL Table to backup

// $day is set to name the backup,  I use 'l' to name the backup the full day ( monday, tuesday.. sunday)

Here is a full example to back up multiple databases, If used with a CRON, you can get a 7 day rotation of any number of databases.

< ?php

$day = date('l');

function mysql_backup($type,$host, $user, $password, $table, $path) {
	global $day;
	if($type == "gz"){
		// Gunzipp Output (.gz)
		$backup = $path.$table.'_'.$day.'.gz';
		exec(
			sprintf(
				'/usr/local/mysql/bin/mysqldump --host=%s --user=%s --password=%s %s --quick --lock-tables --add-drop-table gzip > %s',
				$host,
				$user,
				$password,
				$table,
				$backup
			)
		);
	}elseif($type == "sql"){
		// SQL Outout (.sql)
		$backup = $path.$table.'_'.$day.'.sql';
		exec(
			sprintf(
				'/usr/local/mysql/bin/mysqldump --host=%s --user=%s --password=%s %s --quick --lock-tables --add-drop-table > %s',
				$host,
				$user,
				$password,
				$table,
				$backup
			)
		);
	}
	return true;
}

foreach(array("sheldons_blog","rogers_blog","janes_blog") as $table){
	if(mysql_backup('sql','localhost', 'SQL_Username', 'SQL_Password', $table, '/root/path/to/mysql/data/backups/')){
		echo('Table backed up. '.$table.'_'.$day.'.sql
'."\n");
	}else{
		die('Error backing up table.' .$table);
	}
}
?>

If you are planning on passing the information from a form, I suggest using PHPs escapeshellarg.

On line 8 of the function you will see I have stated the full path my mySQL, in most cases you will just need

mysqldump

or it may vary depending on your server setup, on one of my servers ‘mysqldump’ works, on this server I need

/usr/local/mysql/bin/mysqldump