Page 1 of 1

Backing up MySQL database using PHP

Posted: Sat Aug 04, 2007 12:15 am
by trassalg
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?

Posted: Sat Aug 04, 2007 5:35 am
by SkyFlyer
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/

Posted: Sat Aug 04, 2007 2:55 pm
by trassalg
It's cause I won't be the one using the site, and the only user of the database is someone who has no programming knowledge... his knowledge ends at how to use a web browser to navigate.

Posted: Sat Aug 04, 2007 2:58 pm
by AKA Panama Jack
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.

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();
?>

Posted: Sun Aug 05, 2007 8:26 am
by superdezign
I may make use of that code (or at least the concept). I'd add dates or some sort of unique id to the the file names in case something went wrong and you didn't get a chance to get to the back-up in time.