On some configurations,
mysqldump is not available.
So how does one backup his
database without
mysqldump, but only
PHP at hand?
Here is a workaround in pure PHP (just a little additional help from
tar and
zip - can be completely skipped).
This piece of script will create its own backup directories if they don't exist (yes it needs write rights to the destination directory), get a backup of the database (in chunks, not to break or stall the database), tar-zips the backups, and the cherry on top: it will delete old files - so they don't build up to use your whole disk space.
To run it, of course you need to configure it correctly with your database name, username, password, hostname, authorization password, and perhaps the paths to the directories to the backup output files.
If you're gonna use this piece of script, please drop me a line, i'll be happy to hear about it!
<?php
// set up a password access - so to prevent abuses
if ( $_GET['p']=='SOMe_Str0ng_Pa55word')
{
set_time_limit(60*6); // 6 minutes
$dbname = 'PuT_Your_DB_Name_Here'; // like wordpress
$user = 'PuT_Your_Username_Here'; // like root
$pass = 'PuT_Your_Password_Here'; // like 12345
$host = 'PuT_Your_DB_HOST_Here'; // like localhost or db.mysql.server.org
// check or create directory for DB backup
if ( !is_dir('/backup/DB') ) {
mkdir('/backup/DB');
chmod('/backup/DB', 0755);
}
// check or create directory for site backup
if ( !is_dir('/backup/html') ) {
mkdir('/backup/html');
chmod('/backup/html', 0755);
}
/************************************
CREATE A BACKUP OF THE DATABASE
************************************/
// check if a recent backup exists and clean up old files
$haveRecent = false;
$dp = opendir('/backup/DB');
while ( $fname = readdir($dp) )
{
if ( $fname && $fname!='.' && $fname!='..' )
{
if ( substr($fname, -11)=='.sql.tar.gz' && preg_match('/^[0-9]{14}_/i', $fname) && !preg_match('/[^a-z0-9\_\.]/i', $fname) )
{
$dt = substr($fname, 0, 8);
if ( $dt<=date("Ymd", time()-(86400*30*2) ) )
{
// delete all older than 90 days
@unlink('/backup/DB/'.$fname);
}
elseif ( (int)$dt>date("Ymd", time()-86400*15) )
{
// we have a recent backup (last 15 days)
$haveRecent = true;
}
}
}
}
//if ( !$haveRecent )
{
// dump the DB in the appropriate DB backup directory
$fn = '/backup/DB/'.date("YmdHis") . '_' . $dbname . '.sql';
$tfn = $fn . '.tar.gz';
if ( !is_file($fn) && !is_file($tfn) )
{
$MB = new mysqlBackup();
$MB->backup_tables($host, $user, $pass, $dbname, '*', $fn);
exec('tar -czf ' . $tfn . ' -C ' . dirname($tfn) . ' ' . basename($fn));
@unlink($fn);
}
}
/************************************
END CREATE A BACKUP OF THE DATABASE
************************************/
/************************************
CREATE A BACKUP OF THE WEBSITE FILES
************************************/
// check if a recent backup exists and clean up old files
$haveRecent = false;
$dp = opendir('/backup/html/');
while ( $fname = readdir($dp) )
{
if ( $fname && $fname!='.' && $fname!='..' )
{
if ( preg_match('/^[0-9]{14}\.tar\.gz$/', $fname) )
{
$dt = substr($fname, 0, 8);
if ( (int)$dt<=(int)date("Ymd", time()-(86400*30*2) ) )
{
// delete all older than 90 days
@unlink('/backup/html/'.$fname);
}
elseif ( (int)$dt>(int)date("Ymd", time()-86400*15) )
{
// we have a recent backup (last 15 days)
$haveRecent = true;
}
}
}
}
//if ( !$haveRecent )
{
// dump the DB in the appropriate DB backup directory
$fn = '/backup/html/' . date("YmdHis") . '.tar.gz';
if ( !is_file($fn) )
{
exec('tar -czf ' . $fn . ' -C / html');
}
}
/************************************
END CREATE A BACKUP OF THE WEBSITE FILES
************************************/
}
class mysqlBackup
{
//backup_tables('localhost','username','password','blog');
/* backup the db OR just a table */
function backup_tables($host, $user, $pass, $name, $tables = '*', $ofile ='')
{
$link = mysql_connect($host, $user, $pass) or die("could not connect");
mysql_select_db($name, $link);
//save file
$handle = fopen($ofile, 'w');
//get all of the tables
if($tables == '*')
{
$tables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
//cycle through
foreach($tables as $table)
{
$exhaustedTable = false; $iteration = 0; $pageSize = 1000; // to dump the tables in batches of 1000 lines
$colsRes = mysql_query('SHOW COLUMNS FROM `'.$table.'`');
$cols = array(); $colsStr = '';
while ( $colRow = mysql_fetch_row($colsRes) )
{
$cols[] = $colRow[0];
$colsStr.= ($colsStr!=''?',':'') . '`' . $colRow[0] . '`';
}
$num_fields = count($cols);
$n1 = ($num_fields-1);
fwrite($handle, 'DROP TABLE IF EXISTS `'.$table.'`;'. "\n");
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE `'.$table.'`'));
fwrite($handle, $row2[1].";\n");
while (!$exhaustedTable)
{
$result = mysql_query('SELECT * FROM `'.$table. '` LIMIT '.($iteration*$pageSize).','.$pageSize);
if ( mysql_num_rows($result) )
{
fwrite($handle, 'INSERT INTO `'.$table.'` (' . $colsStr . ') VALUES');
$nc = false;
while($row = mysql_fetch_row($result))
{
if ( $nc )
{
fwrite($handle, ', ');
}
fwrite($handle, '(');
for($j=0; $j<$num_fields; $j++)
{
if ( $row[$j] === NULL )
{
fwrite($handle, 'NULL');
}
elseif ( $row[$j]==='' )
{
fwrite($handle, '""');
}
else
{
$row[$j] = mysql_real_escape_string(stripslashes($row[$j]));
$row[$j] = preg_replace("/\n/m","\\n",$row[$j]);
fwrite($handle, '"'.$row[$j].'"');
}
if ($j<$n1)
{
fwrite($handle, ',');
}
}
fwrite($handle, ')');
$nc = true;
}
fwrite($handle, ";\n");
}
else
{
$exhaustedTable = true;
}
$iteration++;
}
}
fclose($handle);
}
}
?>
