Here's a quick code snippet I just wrote to dump and compress (gzip) all mysql databases on a server into separate files using PHP and PEAR.
<?php
// include PEAR DB library
require_once('DB.php');
// define the DSN in an array
// NOTE: user must have access to all databases
$dsn = array(
'phptype' => 'mysql',
'username' => 'YOURUSER',
'password' => 'YOURPASSWORD',
'hostspec' => 'HOSTNAME', // localhost?
);
// instantiate a PEAR DB object
$DB =& DB::connect($dsn);
// check for an error
if (DB::isError($DB)) die($DB->getMessage());
// set the DB fetch mode to associative
$DB->setFetchMode(DB_FETCHMODE_ASSOC);
// define sql statement
$sql = "show databases";
// fetch sql result
$databases = $DB->getAll($sql);
// loop through results
foreach ($databases as $index => $result) {
// define the mysqldump command
$command = "mysqldump -u " . $dsn['username'] . " "
. "-p" . $dsn['password'] . " "
. "-h " . $dsn['hostspec'] . " "
. $result['Database'] . " | gzip > "
. $result['Database'] . ".sql.gz";
// execute command
`$command`;
}
?>
