Jan 04 2010

[Linux, PHP] PHP script to backup all databases

Category: Articles,Linux administration,PHPFractalizeR @ 1:20 am

Hello.

I’d like to share my script, which I use on some production servers for quick database backup using mysql tools. People say, that I should use bash for that, but I prefer PHP 🙂

Script uses mysqldump for dumping databases and gzip for compressing backups. rm is run to ensure backups are not stored for too long.

<?php
//GRANT SELECT,SHOW DATABASES,SHOW VIEW,LOCK TABLES ON *.* TO "backupuser"@localhost INDENTIFIED BY "XXXXXXXXXX"
 
//Change options below to fine-tune your copy
$user = 'backupuser';
$password = 'backupuserpass';
$backupPath = '/home/all/backups';
$maxdaysold = 7;
$dateStr = date("Y-m-d-h-i-s");
$databasesToSkip = array('mysql', 'information_schema', 'test');
$output = null;
$timeStarted = time();
 
mysql_connect('localhost', $user, $password) or die('Cannot connect to MySQL: '.mysql_error());
$sql = 'SHOW DATABASES';
$result = mysql_query($sql);
if(!$result) {
	die('Error getting database list: '.mysql_error());
}
$databases = array();
while ($row = mysql_fetch_assoc($result)) {
    $databases[] = $row['Database'];
}
 
foreach($databases as $database) {
	if(in_array($database, $databasesToSkip)) {
		continue;
	}
	exec("mysqldump --complete-insert --create-options --add-locks --disable-keys --extended-insert --quick --quote-names -u $user --password=$password $database|gzip --fast -c>{$backupPath}/$database.$dateStr.sql.gz", $output, $result);
	if($result) {
		echo("Error dumping database $backupPath: $result");
	}
}
 
//Deleting old backups
exec('find '.$backupPath.' -mtime '.$maxdaysold.' -name "*.sql.gz" -exec rm {} \\;', $output, $result);
if($result) {
	echo("Error removing old database backups!");
}
 
echo('Database backup succeeded. Time elapsed: '.(time() - $timeStarted).' sec.');
echo("\n");
<?php
//GRANT SELECT,SHOW DATABASES,SHOW VIEW,LOCK TABLES ON *.* TO “backupuser”@localhost INDENTIFIED BY “XXXXXXXXXX”

//Change options below to fine-tune your copy
$user = ‘backupuser’;
$password = ‘backupuserpass’;
$backupPath = ‘/home/all/backups’;
$maxdaysold = 7;
$dateStr = date(“Y-m-d-h-i-s”);
$databasesToSkip = array(‘mysql’, ‘information_schema’, ‘test’);
$output = null;
$timeStarted = time();

mysql_connect(‘localhost’, $user, $password) or die(‘Cannot connect to MySQL: ‘.mysql_error());
$sql = ‘SHOW DATABASES’;
$result = mysql_query($sql);
if(!$result) {
die(‘Error getting database list: ‘.mysql_error());
}
$databases = array();
while ($row = mysql_fetch_assoc($result)) {
$databases[] = $row[‘Database’];
}

foreach($databases as $database) {
if(in_array($database, $databasesToSkip)) {
continue;
}
exec(“mysqldump –complete-insert –create-options –add-locks –disable-keys –extended-insert –quick –quote-names -u $user –password=$password $database|gzip –fast -c>{$backupPath}/$database.$dateStr.sql.gz”, $output, $result);
if($result) {
echo(“Error dumping database $backupPath: $result”);
}
}

//Deleting old backups
exec(‘find ‘.$backupPath.’ -mtime ‘.$maxdaysold.’ -name “*.sql.gz” -exec rm {} \\;’, $output, $result);
if($result) {
echo(“Error removing old database backups!”);
}

echo(‘Database backup succeeded. Time elapsed: ‘.(time() – $timeStarted).’ sec.’);
echo(“\n”);

Tags: , , ,

8 Responses to “[Linux, PHP] PHP script to backup all databases”

  1. MKJ says:

    Thanks for this.

    Works brilliantly.

    Saved me a ton of work.

  2. MKJ says:

    Reason I’m asking is because I have to move a server. It would be handy to be able to import all the created backups – a few hundred – to the new server automatically one at a time without having to create each database myself first.

  3. MKJ says:

    I have been adding more and more sites, along with databases, to my server. I’m now getting a nasty error as if the browser is timing out (or something on my server is) when running the backup script. Using Opera I get this message ‘No data received: unable to load webpage because the server sent no data. I get similar errors with other browsers. Is there a way to keep the browser from timing out (if that is what is happening that is?).

  4. MKJ says:

    Not sure if I might have fixed this problem. I’ve disabled Zend OPcache in php.ini and now the script runs through completely. So far so good anyway.

Leave a Reply

You must be logged in to post a comment. Login now.