I have been following on your question from beginning, I did my research and found your question any way interesting.
Most of the solutions for backuping mysql via php code are out of date and have problems with nowadays environment. Therefore I was curious to find if there was any updated and useful once. My plan was if I could not find one then I wanted to develop one.
But I found a solution and it works. I have test it by exporting and re-importing tables. And I think you will like it.
Before going to the code, I have following following remarks:
- With respect to David Walsh the code and tutorial you are following is from 2008 using MySQL which is deprecated now. The code below uses perhaps MySQLi.
- When you run the code, it will deliver you a downloadable backup file. You can pass the file to a safe remote destination like server, cloud storage (dropbox, etc).
- To run the backup function periodically, and depending on your OS, environemnt, strategy and how you want to take that approach. In most cases CRON is the solution to schedule it to run every evening once or some thing like that, there is a punch for tutorial and helpful information regarding that online.
- Be a ware of this function will only make backup on localhost, as most host provider has blocked for remote mysql access (so this function can not take backup of remote mysql tables).
- In the beginning and for testing it is fine to use root username, but when that works, then I suggest you create a username and password specific access to the table you want take backup from.
Note: the code is found on this page with respect to the code owner, I added extra line of code just to make it work for the OP.
Very important note:
Depending on your server resources & capacity and your database size, executing such code might take time, specially and typically if you have a shared hosting, therefore you need to extend your execution time up to 300 seconds (5 minutes) and the backup should be taken in low load hours.
And worst case you might need to ask your host provider to extend execution time if that part is prevented by code.
The working code
<?php
define("MAX_EXECUTION_TIME", 100); // seconds
$timeline = time() + MAX_EXECUTION_TIME;
EXPORT_TABLES('localhost', 'root', '', 'blog');
function EXPORT_TABLES($host, $user, $pass, $name, $tables = false, $backup_name = false)
{
$mysqli = new mysqli($host, $user, $pass, $name);
$mysqli->select_db($name);
$mysqli->query("SET NAMES 'utf8'");
$queryTables = $mysqli->query('SHOW TABLES');
while ($row = $queryTables->fetch_row())
{
$target_tables[] = $row[0];
}
if ($tables !== false)
{
$target_tables = array_intersect($target_tables, $tables);
}
try
{
foreach ($target_tables as $table)
{
$result = $mysqli->query('SELECT * FROM ' . $table);
$fields_amount = $result->field_count;
$rows_num = $mysqli->affected_rows;
$res = $mysqli->query('SHOW CREATE TABLE ' . $table);
$TableMLine = $res->fetch_row();
$content = (!isset($content) ? '' : $content) . "\n\n" . $TableMLine[1] . ";\n\n";
for ($i = 0, $st_counter = 0; $i < $fields_amount; $i ++, $st_counter = 0)
{
while ($row = $result->fetch_row())
{ //when started (and every after 100 command cycle):
if ($st_counter % 100 == 0 || $st_counter == 0)
{
$content .= "\nINSERT INTO " . $table . " VALUES";
}
$content .= "\n(";
for ($j = 0; $j < $fields_amount; $j ++)
{
$row[$j] = str_replace("\n", "\\n", addslashes($row[$j]));
if (isset($row[$j]))
{
$content .= '"' . $row[$j] . '"';
} else
{
$content .= '""';
}
if ($j < ($fields_amount - 1))
{
$content .= ',';
}
}
$content .= ")";
//every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
if ((($st_counter + 1) % 100 == 0 && $st_counter != 0) || $st_counter + 1 == $rows_num)
{
$content .= ";";
} else
{
$content .= ",";
}
$st_counter = $st_counter + 1;
}
}
$content .= "\n\n\n";
}
} catch (Exception $e)
{
echo 'Caught exception: ', $e->getMessage(), "\n";
}
$backup_name = $backup_name ? $backup_name : $name . "___(" . date('H-i-s') . "_" . date('d-m-Y') . ")__rand" . rand(1, 11111111) . ".sql";
header('Content-Type: application/octet-stream');
header("Content-Transfer-Encoding: Binary");
header("Content-disposition: attachment; filename=\"" . $backup_name . "\"");
echo $content;
exit;
}
?>
