Backing up MySQL database using PHP
Moderator: General Moderators
Backing up MySQL database using PHP
Is there a simple script I can use to retrieve a copy of a full MySQL database and download it to the user's computer via a single pushbutton?
Don't see why you couldn't use phpmyadmin... but whatever:
Anyway, google is your friend:
http://www.mt-soft.com.ar/2007/07/02/ph ... mysqldump/
Anyway, google is your friend:
http://www.mt-soft.com.ar/2007/07/02/ph ... mysqldump/
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
Some people like to add a backup/resoure function to their code that doesn't require the entire PHPMyAdmin to be installed. Or perform a cron based backup every night.
Here is a simple one that will create SQL files of the schema and data that can be used by programs like PHPMyAdmin to create and import the tables.
Here is a simple one that will create SQL files of the schema and data that can be used by programs like PHPMyAdmin to create and import the tables.
Code: Select all
<?php
// This program is free software; you can redistribute it and/or modify it
// under the terms of the GNU General Public License as published by the
// Free Software Foundation; either version 2 of the License, or (at your
// option) any later version.
//
// File: db_backup.php
header("Cache-Control: no-store, no-cache, must-revalidate");
// Full Database Backup
//Setup Variables
$path = ""; // Path to where the backup files will be stored
$dbhost = "localhost"; // Database Host URL
$dbuname = ""; // Database User Name
$dbpass = ""; // Database Password
// List of tables to backup from the database.
$dbtables = array();
$dbtables[] = "banned_servers";
$dbtables[] = "blog_comments";
$dbtables[] = "blog_data";
$dbtables[] = "blog_gallery";
$dbtables[] = "comments";
$dbtables[] = "current_history";
$dbtables[] = "history";
$dbtables[] = "messages";
$dbtables[] = "prefered_servers";
$dbtables[] = "profile";
$dbtables[] = "profile_news";
$dbtables[] = "server_list";
$dbtables[] = "server_updates";
$dbtables[] = "sessions";
function get_def($dbname, $table) {
global $conn, $fieldnames;
$count = 0;
$def = "";
$def .= "DROP TABLE IF EXISTS $table;\n";
$def .= "CREATE TABLE $table (\n";
$result = mysql_db_query($dbname, "SHOW FIELDS FROM $table",$conn) or die("Table $table not existing in database");
while($row = mysql_fetch_array($result)) {
$def .= " $row[Field] $row[Type]";
$fieldnames[$count] = $row['Field'];
$count++;
if ($row["Default"] != "") $def .= " DEFAULT '$row[Default]'";
if ($row["Null"] != "YES") $def .= " NOT NULL";
if ($row[Extra] != "") $def .= " $row[Extra]";
$def .= ",\n";
}
$def = ereg_replace(",\n$","", $def);
$result = mysql_db_query($dbname, "SHOW KEYS FROM $table",$conn);
while($row = mysql_fetch_array($result)) {
$kname=$row[Key_name];
if(($kname != "PRIMARY") && ($row[Non_unique] == 0)) $kname="UNIQUE|$kname";
if(!isset($index[$kname])) $index[$kname] = array();
$index[$kname][] = $row[Column_name];
}
while(list($x, $columns) = @each($index)) {
$def .= ",\n";
if($x == "PRIMARY") $def .= " PRIMARY KEY (" . implode($columns, ", ") . ")";
else if (substr($x,0,6) == "UNIQUE") $def .= " UNIQUE ".substr($x,7)." (" . implode($columns, ", ") . ")";
else $def .= " KEY $x (" . implode($columns, ", ") . ")";
}
$def .= "\n);";
return (stripslashes($def));
}
function get_content($dbname, $table, $namelist) {
global $conn, $encrypt, $path, $backup_encryption_type;
$fp = fopen ($path.$table . "_data.sql","w");
$content="";
$result = mysql_db_query($dbname, "SELECT * FROM $table",$conn);
while($row = mysql_fetch_row($result)) {
$insert = "INSERT INTO $table (" .$namelist . ") VALUES (";
for($j=0; $j<mysql_num_fields($result);$j++) {
if(!isset($row[$j])) $insert .= "NULL,";
else if($row[$j] != "") $insert .= "'".addslashes($row[$j])."',";
else $insert .= "'',";
}
$insert = ereg_replace(",$","",$insert);
$insert .= ");";
$content .= $insert."\n";
fwrite ($fp,$content);
$content = "";
}
fclose ($fp);
}
set_time_limit(0);
flush();
$conn = @mysql_connect($dbhost,$dbuname,$dbpass);
if ($conn==false)
die("password / user or database name wrong");
echo "<b>Starting Database Backup</b><br><br>";
flush();
foreach($dbtables as $tablename){
echo "Backing up table: $tablename<br>";
flush();
if(!file_exists($path . $tablename . "_table.sql"))
{
@unlink($path.$tablename . "_table.sql");
@unlink($path.$tablename . "_data.sql");
}
$cur_time=date("Y-m-d H:i");
unset($fieldnames);
$newfile = get_def($dbname,$tablename);
$fp = fopen ($path . $tablename . "_table.sql","w");
fwrite ($fp,$newfile);
fclose ($fp);
$namelist = "";
for($i = 0; $i < count($fieldnames); $i++){
$namelist .= $fieldnames[$i];
if($i != count($fieldnames) - 1)
$namelist .= ",";
}
get_content($dbname,$tablename, $namelist);
}
echo "<br><b>Database Backup Complete</b><br><br>";
$db->close();
?>- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm