Simple MySQL Database Backup

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