Simple MySQL Database Backup

Like this blog? Consider exploring one of our sponsored banner ads...

This is the simplest way to backup MySQL databases and tables using the mysqldump command to generate an sql file. I will also show you how to restore the database or table from the generated sql file and demonstrate a simple loop to generate a separate mysqldump of each table in a given database. This makes it really easy to restore just the table(s) that you want.

So first let’s setup some global variables we will use later with some simple function calls. Database Host, database Name, Database User, Password and Path. Important to remember, Path needs to be writable by PHP and also be sure to escape and weird characters like $ in the fields.

// SETUP GLOBAL PARAMETERS
 
$params = array();
 
$params['host'] = 'localhost';
 
$params['database'] = 'collections';
 
$params['username'] = 'user007';
 
$params['password'] = 'asdf123';
 
$params['table']  = 'widgets';
 
$params['path']  = 'PATH/backup/';
// BACKUP A SINGLE TABLE
 
mysqlBackupTable($params);
// BACKUP AN ENTIRE DATABASE
 
mysqlBackupDatabase($params);
// RESTORE TABLE
 
mysqlRestoreTable($params);
// RESTORE DATABASE
 
mysqlRestoreDatabase($params);
// BACKUP ALL TABLES IN A DATABASE
 
$query = mysql_query("SHOW TABLES FROM `{$params['database']}`");
 
while($row = mysql_fetch_row($query)){
 
	$params['table'] = $row[0];
 
	mysqlBackupTable($params);	
 
}
// FUNCTION: BACKUP MYSQL TABLE TO A FILE
 
function mysqlBackupTable($params){
 
	$outfile = makeFileName($params['path'],$params['database'],$params['table']);
 
	$cmd = "mysqldump --user={$params['username']} --password={$params['password']} {$params['database']} --tables {$params['table']} > {$outfile}";
 
	system($cmd);
 
}
// FUNCTION: BACKUP MYSQL DATABASE TO A FILE
 
function mysqlBackupDatabase($params){
 
	$outfile = makeFileName($params['path'],$params['database']);
 
	$cmd = "mysqldump --user={$params['username']} --password={$params['password']} {$params['database']} > {$outfile}";
 
	system($cmd);
 
}
// FUNCTION: RESTORE MYSQL TABLE
 
function mysqlRestoreTable($params){
 
	$infile = makeFileName($params['path'],$params['database'],$params['table']);
 
	$cmd = "mysql --user={$params['username']} --password={$params['password']} {$params['database']} < {$infile}";
 
	system($cmd);
 
}
// FUNCTION: RESTORE MYSQL DATABASE
 
function mysqlRestoreDatabase($params){
 
	$infile = makeFileName($params['path'],$params['database']);
 
	$cmd = "mysql --user={$params['username']} --password={$params['password']} {$params['database']} < {$infile}";
 
	system($cmd);
 
}
// FUNCTION: NAMING CONVENTION FOR DUMP FILES
 
function makeFileName($path,$database,$table=''){
 
	return (!empty($table)) ? "{$path}{$database}@{$table}.sql" : "{$path}{$database}.sql" ;
 
}

About this entry

You’re currently reading “Simple MySQL Database Backup,” an entry on BRADINO

Published:
10.1.08 / 7pm
Category:
MySQL, PHP
Tags: