How can I back up an entire MySQL table (into a back.sql file)? I don't mean from phpmyadin. I want to be able to back up from the Control Panel that I'm building. I don't know how, not even a theory. Any ideas?
If possible, I also want to be able to use that back up when something is wrong or so (something like resetting). Get what I mean? lol
Thanks,
-Nay
Backing up MySQL table
Moderator: General Moderators
have a look at this, this is code that has been taken from phpMyAdmin
Replace following variables:
databaseserver = The machine on which your database is residing.
username = database username
password = database password
databasename = the name of database to backup.
Code: Select all
<?
$target="file";
if($target=="file")
{
header('Content-Type: application/octetstream');
header('Content-Disposition: filename="backup.sql"');
$asfile="download";
}
$crlf="\r\n";
$link = mysql_connect("localhost", "root", "");
$database = mysql_select_db("newobs");
$dbname = "newobs";
$dump_buffer="";
$tables = mysql_query("show tables from $dbname");
$num_tables = mysql_num_rows($tables);
if($num_tables == 0)
{
echo "# No Tables Found";
exit;
}
$dump_buffer.= "# DatabaseBackup $crlf";
$dump_buffer.= "# Backup made:$crlf";
$dump_buffer.= "# ".date("F j, Y, g:i a")."$crlf";
$dump_buffer.= "# Database: $dbname$crlf";
$dump_buffer.= "# Backed up tables : $dbname $crlf";
$i = 0;
while($i < $num_tables)
{
$table = mysql_tablename($tables, $i);
//echo $table . "<br>";
$dump_buffer.= "# --------------------------------------------------------$crlf";
$dump_buffer.= "$crlf#$crlf";
$dump_buffer.= "# Table structure for table '$table'$crlf";
$dump_buffer.= "#$crlf$crlf";
$db = $table;
$dump_buffer.= get_table_def($table, $crlf,$dbname).";$crlf";
$dump_buffer.= "$crlf#$crlf";
$dump_buffer.= "# Dumping data for table '$table'$crlf";
$dump_buffer.= "#$crlf$crlf";
$tmp_buffer="";
get_table_content($dbname, $table, 0, 0, 'my_handler', $dbname);
$dump_buffer.=$tmp_buffer;
$i++;
$dump_buffer.= "$crlf";
}
echo $dump_buffer;
exit;
function get_table_def($table, $crlf,$dbname)
{
$schema_create = "DROP TABLE IF EXISTS $table;$crlf";
$db = $table;
$schema_create .= "CREATE TABLE $table ($crlf";
$result = mysql_query("SHOW FIELDS FROM " .$dbname."."
. $table) or die();
while($row = mysql_fetch_array($result))
{
$schema_create .= " $row[Field] $row[Type]";
if(isset($row["Default"]) && (!empty($row["Default"]) || $row["Default"] == "0"))
$schema_create .= " DEFAULT '$row[Default]'";
if($row["Null"] != "YES")
$schema_create .= " NOT NULL";
if($row["Extra"] != "")
$schema_create .= " $row[Extra]";
$schema_create .= ",$crlf";
}
$schema_create = ereg_replace(",".$crlf."$", "", $schema_create);
$result = mysql_query("SHOW KEYS FROM " .$dbname."." .
$table) or die();
while($row = mysql_fetch_array($result))
{
$kname=$row['Key_name'];
$comment=(isset($row['Comment'])) ? $row['Comment'] : '';
$sub_part=(isset($row['Sub_part'])) ? $row['Sub_part'] : '';
if(($kname != "PRIMARY") && ($row['Non_unique'] == 0))
$kname="UNIQUE|$kname";
if($comment=="FULLTEXT")
$kname="FULLTEXT|$kname";
if(!isset($index[$kname]))
$index[$kname] = array();
if ($sub_part>1)
$index[$kname][] = $row['Column_name'] . "(" . $sub_part . ")";
else
$index[$kname][] = $row['Column_name'];
}
while(list($x, $columns) = @each($index))
{
$schema_create .= ",$crlf";
if($x == "PRIMARY")
$schema_create .= " PRIMARY KEY (";
elseif (substr($x,0,6) == "UNIQUE")
$schema_create .= " UNIQUE " .substr($x,7)." (";
elseif (substr($x,0,8) == "FULLTEXT")
$schema_create .= " FULLTEXT ".substr($x,9)." (";
else
$schema_create .= " KEY $x (";
$schema_create .= implode($columns,", ") . ")";
}
$schema_create .= "$crlf)";
if(get_magic_quotes_gpc()) {
return (stripslashes($schema_create));
} else {
return ($schema_create);
}
}
function get_table_content($db, $table, $limit_from = 0, $limit_to = 0,$handler)
{
// Defines the offsets to use
if ($limit_from > 0) {
$limit_from--;
} else {
$limit_from = 0;
}
if ($limit_to > 0 && $limit_from >= 0) {
$add_query = " LIMIT $limit_from, $limit_to";
} else {
$add_query = '';
}
get_table_content_fast($db, $table, $add_query,$handler);
}
function get_table_content_fast($db, $table, $add_query = '',$handler)
{
$result = mysql_query('SELECT * FROM ' . $db . '.' . $table . $add_query) or die();
if ($result != false) {
@set_time_limit(1200); // 20 Minutes
// Checks whether the field is an integer or not
for ($j = 0; $j < mysql_num_fields($result); $j++) {
$field_set[$j] = mysql_field_name($result, $j);
$type = mysql_field_type($result, $j);
if ($type == 'tinyint' || $type == 'smallint' || $type == 'mediumint' || $type == 'int' ||
$type == 'bigint' ||$type == 'timestamp') {
$field_num[$j] = true;
} else {
$field_num[$j] = false;
}
} // end for
// Get the scheme
if (isset($GLOBALS['showcolumns'])) {
$fields = implode(', ', $field_set);
$schema_insert = "INSERT INTO $table ($fields) VALUES (";
} else {
$schema_insert = "INSERT INTO $table VALUES (";
}
$field_count = mysql_num_fields($result);
$search = array("\x0a","\x0d","\x1a"); //\x08\\x09, not required
$replace = array("\\n","\\r","\Z");
while ($row = mysql_fetch_row($result)) {
for ($j = 0; $j < $field_count; $j++) {
if (!isset($row[$j])) {
$values[] = 'NULL';
} else if (!empty($row[$j])) {
// a number
if ($field_num[$j]) {
$values[] = $row[$j];
}
// a string
else {
$values[] = "'" . str_replace($search, $replace, addslashes($row[$j])) . "'";
}
} else {
$values[] = "''";
} // end if
} // end for
$insert_line = $schema_insert . implode(',', $values) . ')';
unset($values);
// Call the handler
$handler($insert_line);
} // end while
} // end if ($result != false)
return true;
}
function my_handler($sql_insert)
{
global $crlf, $asfile;
global $tmp_buffer;
if(empty($asfile))
$tmp_buffer.= htmlspecialchars("$sql_insert;$crlf");
else
$tmp_buffer.= "$sql_insert;$crlf";
}
function faqe_db_error()
{
return mysql_error();
}
function faqe_db_insert_id($result)
{
return mysql_insert_id($result);
}
?>databaseserver = The machine on which your database is residing.
username = database username
password = database password
databasename = the name of database to backup.
mMm.......I'm still a little O_o about the code. I need to read through it again.
btw, it's odd asking this but what's
I've been learning PHP for a few months now and I still don't know what is that..........what's that dot in front of the equal sign for? O_o
-Nay
btw, it's odd asking this but what's
Code: Select all
.=-Nay
Code: Select all
$var = "Hello";
$var .= " World";
print $var;
// prints Hello WorldMark
An alternative to the very useful code that was posted by Bech100 is the mysqldump command in which you can dump 1, or some, or all of your tables at the same time, seperating the contents and the structure.
First mysqldump line will dump all row information for your database and store it in "OutputFile.name"
Second Line will dump the table creation information (no row info) and store it in "OutputFile.name"
Third line will dump table creation information PLUS row information and store it in "OutputFile.name"
Code: Select all
mysqldump -e -n -t -u YourUserName -pYourPasswd DATABASE їtables]
mysqldump -d -e -u YourUserName -pYourPasswd DATABASE
mysqldump -e -u YourUserName -pYourPasswd DATABASE
<?php // exampleDumpDb.php //
ob_start();
system( 'mysqldump -d -e -u userOne -pmyPasswd MyFirstDB' );
$mysqlDumpContents = ob_get_contents();
ob_end_clean();
// save the contents of the dump for later restoration
$fp = fopen('/some/dir/mysqlDump.sql','w+');
fwrite( $fp , $mysqlDumpContents );
fclose( $fp );
?>Second Line will dump the table creation information (no row info) and store it in "OutputFile.name"
Third line will dump table creation information PLUS row information and store it in "OutputFile.name"