Richard Korts
asked on
php script to dump MySQL tables
Is it possible to write a php script to "dump" selected tables of a MySQL database?
I want the resultant file to be a .sql file that could be easily restored (manually) if need be.
I want the resultant file to be a .sql file that could be easily restored (manually) if need be.
I used this script on my hosting package, it worked well. Check out
I had permission to run as cron.
I had permission to run as cron.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I've used this...
<?php // RAY_mysql_backup_dump.php
error_reporting(E_ALL);
date_default_timezone_set("America/Chicago");
// PATTERN OF THE NAME OF THE BACKUP FILES
// 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';
// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB CONNECTION: ";
echo "<br/> $errmsg <br/>";
die();
}
// GET A LIST OF THE DATA BASES ON THIS CONNECTION
// MAN PAGE: http://php.net/manual/en/function.mysql-list-dbs.php
if (!$db_list = mysql_list_dbs($db_connection))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB LIST: ";
echo "<br/> $errmsg <br/>";
die();
}
// ITERATE OVER THE LIST OF NAMES TO MAKE AN ARRAY
while ($row = mysql_fetch_object($db_list))
{
$db_names[] = $row->Database ;
}
// ANYTHING IN POST DATA YET?
if (empty($_POST["d"]))
{
// NOTHING POSTED - ASK CLIENT TO CHOOSE THE DATA BASE
echo "<form method=\"post\">\n";
echo "BACK UP A DATA BASE:<br/>";
foreach ($db_names as $db_name)
{
echo "<input type=\"radio\" name=\"d\" value=\"$db_name\">$db_name <br/>\n";
}
echo "<input type=\"submit\" />\n";
echo "</form>\n";
die();
}
// THERE IS A RADIO BUTTON IN $_POST
if (!in_array($_POST["d"], $db_names)) die("ERROR: DATABASE {$_POST["d"]} NOT FOUND");
$db_name = $_POST["d"];
// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB SELECTION: ";
echo "<br/> $errmsg <br/>";
die('NO DATA BASE');
}
// SET THE NAME OF THE BACKUP WITH A TIMESTAMP
$bkup = 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';
$fp = fopen($bkup, "w");
// GET THE LIST OF TABLES
$sql = "SHOW TABLES";
$res = mysql_query($sql);
if (!$res) die( mysql_error() );
if (mysql_num_rows($res) == 0) die( "NO TABLES IN $db_name" );
while ($s = mysql_fetch_array($res))
{
$tables[] = $s[0];
}
// ITERATE OVER THE LIST OF TABLES
foreach ($tables as $table)
{
// WRITE THE DROP TABLE STATEMENT
fwrite($fp,"DROP TABLE `$table`;\n");
// GET THE CREATE TABLE STATEMENT
$res = mysql_query("SHOW CREATE TABLE `$table`");
if (!$res) die( mysql_error() );
$cre = mysql_fetch_array($res);
$cre[1] .= ";";
$txt = str_replace("\n", "", $cre[1]); // FIT EACH QUERY ON ONE LINE
fwrite($fp, $txt . "\n");
// GET THE TABLE DATA
$data = mysql_query("SELECT * FROM `$table`");
$num = mysql_num_fields($data);
while ($row = mysql_fetch_array($data))
{
// MAKE INSERT STATEMENTS FOR ALL THE VALUES
$txt = "INSERT INTO `$table` VALUES(";
for ($i=0; $i < $num; $i++)
{
$txt .= "'".mysql_real_escape_string($row[$i])."', ";
}
$txt = substr($txt, 0, -2);
fwrite($fp, $txt . ");\n");
}
}
// ALL DONE
fclose($fp);
// SHOW THE LINK TO THE BACKUP FILE
echo "<br/>BACKUP OF $db_name CREATED HERE:\n";
echo "<br/><a href=\"$bkup\">$bkup</a>\n";
And this:<?php // RAY_mysql_backup_restore.php
error_reporting(E_ALL);
// PATTERN OF THE NAME OF THE BACKUP FILES
// 'RAY_backups/mysql' . date('Ymd\THis') . $db_name . '.txt';
// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";
// FIND THE BACKUP FILES
if (!$my_dir = scandir('backups',1)) die('NO BACKUP DIRECTORY FOUND');
// IF NOTHING POSTED YET
if (empty($_POST))
{
// ASK CLIENT TO CHOOSE ONE FOR RESTORE
$x = FALSE;
echo "<form method=\"post\">\n";
echo "RESTORE A DATA BASE:<br/>";
foreach ($my_dir as $my_file)
{
if (!ereg('^mysql', $my_file)) continue;
echo "<input type=\"radio\" name=\"d\" value=\"$my_file\">$my_file <br/>\n";
$x = TRUE;
}
if (!$x) die('NO BACKUP FILES FOUND');
echo "<input type=\"submit\" />\n";
echo "</form>\n";
die();
}
// THERE IS A RADIO BUTTON IN $_POST
if (!in_array($_POST["d"], $my_dir)) die("ERROR: FILE {$_POST["d"]} NOT FOUND");
// THE NAME OF THE BACKUP FILE
$fname = $_POST["d"];
// GET THE NAME OF THE DB FROM THE BACKUP FILE NAME
$db_name = $fname;
$db_name = ereg_replace("^mysql", '', $db_name);
$db_name = ereg_replace("\.txt$", '', $db_name);
$db_name = substr($db_name, 15); // LENGTH 15 = strlen("date('Ymd\THis')")
// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB CONNECTION: ";
echo "<br/> $errmsg <br/>";
}
// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB SELECTION: ";
echo "<br/> $errmsg <br/>";
die('NO DATA BASE');
}
// LOAD THE BACKUP DATA QUERIES
$sqls = file('RAY_backups/' . $fname);
// ITERATE OVER THE QUERIES TO RELOAD THE DATA
foreach ($sqls as $sql)
{
if (!$res = mysql_query($sql))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>QUERY FAIL: ";
echo "<br/>$sql <br/>";
die($errmsg);
}
}
And for a single table backup on the same data base...<?php // RAY_mysql_backup_table.php
error_reporting(E_ALL);
echo "<pre>\n";
// A FORM-ACTIVATED TABLE BACKUP
// RESPONSE TO EE QUESTION http://www.experts-exchange.com/Database/MySQL/Q_25465037.html?cid=1749
// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??"; // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB CONNECTION: ";
echo "<br/> $errmsg <br/>";
}
// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB SELECTION: ";
echo "<br/> $errmsg <br/>";
die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES
// IF THE CLIENT HAS CHOSEN A TABLE FROM OUR FORM
if (!empty($_POST["dbt"]))
{
// CLEAN NAMES FOR THE TABLES
$dbt = mysql_real_escape_string($_POST["dbt"]);
$dbt_bkup = $dbt . 'BCK';
// REMOVE ANY OLD BACKUP DATA (AND IGNORE QUERY ERRORS THAT MAY OCCUR)
$sql = "DROP TABLE $dbt_bkup";
var_dump($sql);
mysql_query($sql);
// CREATE A NEW BACKUP TABLE WITH THE STRUCTURE OF THE ORIGINAL TABLE
$sql = "CREATE TABLE $dbt_bkup LIKE $dbt";
var_dump($sql);
mysql_query($sql) or die( mysql_error() );
// POPULATE THE NEW BACKUP TABLE WITH THE DATA FROM THE ORIGINAL TABLE
$sql = "INSERT INTO $dbt_bkup SELECT * FROM $dbt";
var_dump($sql);
mysql_query($sql) or die( mysql_error() );
// ALL DONE
echo "$dbt HAS BEEN BACKED UP IN $dbt_bkup" . PHP_EOL;
}
// GIVE THE CLIENT A FORM TO CHOOSE THE TABLE TO BACKUP
if (empty($_POST["dbt"]))
{
// RUN A QUERY TO GET A LIST OF TABLES ON THIS DB
$t = mysql_query("SHOW TABLES") or die( mysql_error() );
// IF NO TABLES
if (mysql_num_rows($t) == 0)
{
die('NO TABLES');
}
// GET AN ARRAY OF TABLES WITH THE TABLE NAME AS THE INDEX
while ($show_tables = mysql_fetch_array($t))
{
$my_tables[] = $show_tables[0];
}
// SORT THE NAMES
natcasesort($my_tables);
// PRODUCE THE FORM TO CHOOSE THE TABLE
echo "<form method=\"post\">" . PHP_EOL;
echo "CHOOSE TABLE TO BACKUP" . PHP_EOL;
foreach ($my_tables as $my_table)
{
// GET THE NUMBER OF ROWS FOR THIS TABLE
$t = mysql_query("SELECT COUNT(*) AS n FROM $my_table") or die( mysql_error() );
$r = mysql_fetch_assoc($t);
$n = $r["n"];
// PRODUCE A RADIO BUTTON FOR THE CLIENT TO CHOOSE A TABLE
echo "<input type=\"radio\" name=\"dbt\" value=\"$my_table\" />$my_table ($n ROWS)" . PHP_EOL;
}
// END OF SELECTION FORM
echo "<input type=\"submit\" value=\"BACKUP NOW\" />" . PHP_EOL;
echo "</form>" . PHP_EOL;
}
HTH, ~Ray
If you have shell or command line access, you can use the 'mysqldump' program.