I have used phpMyAdmin to administer all my MySQL databases, but one or two projects had really big databases, which I found difficult to develop from work, home and the internet simultaneously. I could export from phpMyAdmin without problem but I was having no luck in restoring because of timeouts.
The example attached will backup a database into several managable files, allowing me execute in phpMyAdmin without experiencing any browser timeouts. A full database restore function has also been included.
Each table is backed up to a structure file and several data files, all of which are sql queries. A log file is generated and used in the restore process. Text is also returned from the function, so it can be outputed to the browser if required.
Because of the size of some of the databases I use, I have also generated a paged restoration function, which will report after each file before moving on to the next (with a page refresh), again preventing timeouts and avoiding the appearance of failure when nothing is displayed.
Open the script and alter the database connection settings near the top of the code. Then uncomment the function you want to perform at the bottom.
Written by Matthew Waygood (mwwaygoo@hotmail.com) and submitted on www.weberdev.com, July 2004.
Script for backing up a mysql database to several sql query files, for easy transport and restoration.
Written as an alternative to phpMyAdmins' export for large databases, as timeouts can occur when
restoring.
There are 2 types of files, structure and data. The structure files contain a DROP table and CREATE
table queries and the data file contains several INSERT queries. This is to ensure that site backups
contain your database structure and data can be kept along with your PHP code. Useful when making major
alterations to websites.
To enable someone to use the files to restore a single table, each table has its own set of files.
If the tables are very large they are split into several numbered files. This is to stop utilities like
phpMyAdmin from timing out, when running the sql queries to restore the data.
A log file is generated of the files created, and is referenced when using the restore functions.
This is to ensure only the files created during the last backup are used.
FUNCTIONS
function run_mysql_query($sql_query, $method, $limit); Encompasses the whole connect,select,query database processes
$sql: standard single sql query
$method (OPTIONAL): method of returning select query results, REFERENCE, ASSOC, NUM or BOTH
$limit (OPTIONAL): seperate limit value applied to query, useful for paging results.
an update returns number of rows updated
an insert returns reference/key of new entry
a select return a reference like mysql_query, OR an array of results.
ASSOC uses field names as a key to the array - see myswl_fetch_assoc
NUM uses integer numbers as a key to the array - see mysql_fetch_row
BOTH uses both NUM and ASSOC values - see mysql_fetch_row
function backup_database_to_file($dir, $records_per_file)
$dir: relative directory to where the backup files will be saved. It is created if is doesnt exist when used in cron jobs, this can be combined with a date value to keep historic backups.
$records_per_file (OPTIONAL): number of rows stored in each file. The default is 1000 to keep the file size small, allowing saves to floppies and avoiding restore timeouts.
function restore_database_from_file($dir, $log_filename, $index)
$dir: same directory as used for a backup, so you can specify historic/automatic/manual backups
$log_filename: name of log file used to backup database, as it contains all the files created
$index (OPTIONAL): used by the page_restore function, its a reference to each file created in the backup log. ie first file is whole database structure "creating file: db_structure.sql" This is used when a browser timeout may occur when retoring a large database as output is only returned when the function has completed. You can use this to split the restore across several web pages.
function page_restore_database_from_file($dir, $log_filename)
$dir: same directory as used for a backup, so you can specify historic/automatic/manual backups
$log_filename: name of log file used to backup database, as it contains all the files created
$log_filename=$database['db_name'] . ".log"; // log of files generated
function run_mysql_query($query, $method="REFERENCE", $limit="")
{
// function for performing single simple queries to a mysql database.
// When using SELECT, by default a reference is returned like the standard mysql_query
// but if ASSOC,BOTH,NUM is specified as $method, then full set of results is returned in an array
// you may also specify a limit on the query, useful for paging results.
// If the query is an UPDATE or DELETE, the number of altered values is returned
// If the query is an INSERT, the Key of the new row is returned
// call using $results=array_from_mysql_query("SELECT * FROM table","3,6","REFERENCE");
// limit 3 means give me next 3
// limit 3,6 means ignore first 3 then give me next 6
// returned array is accessed as $result[$number_loop]['column_title'] where $number_loop starts from 0
global $database; // load external database connection values
// force $method to upper case
$method=strtoupper($method);
// Create database connection
$connection = mysql_connect($database['location'], $database['user'], $database['password'] ) or die ("Couldn't connect to database");
// Select the database
$db = mysql_select_db($database['db_name'], $connection) or die ("Couldn't select database");
// Execute SQL query and get result
$fetch = mysql_query($query, $connection) or die ("Couldn't execute query: ".$query."--\n");
if( (ereg("^UPDATE",$query)) || (ereg("^DELETE",$query)) )
{
// return the number of rows affected by this query
return mysql_affected_rows();
}
if(ereg("^INSERT",$query))
{
// return the reference of the newly inserted record
return mysql_insert_id();
}
if( (empty($method)) || ($method=="REFERENCE"))
{
// quering by reference, so return that reference
return $fetch;
}
// declare the array
$return = array();
switch ($method)
{
case "ASSOC":
$mysql_method=MYSQL_ASSOC;
break;
case "BOTH":
$mysql_method=MYSQL_BOTH;
break;
case "NUM":
$mysql_method=MYSQL_NUM;
break;
default:
return($return);
break;
}
// query requires an array of results to be returned
while ($row = mysql_fetch_array($fetch, $mysql_method))
{
$return[] = array_map("stripslashes", $row);
}
mysql_free_result($fetch);
return $return;
}
}
function backup_database_to_file($dir, $records_per_file=1000)
{
global $database; // load external database connection values
$progress=""; // log of progress returned to the user
$structure_log=""; // log of creating the database structure file
$data_log=""; // log of creating the data files
$total_files_produced=0; // number of files produced by this script (log,structure,data)
// create directory if it doesnt exist
if(!is_dir($dir))
{
mkdir($dir);
}
if(!ereg("/$", $dir))
{
$dir.="/";
}
// force records per file to 1000 if the value supplied is less than 100 or text
if( (!is_int($records_per_file)) || ($records_per_file<100) )
{
$records_per_file=1000;
}
// files used within this script
// use database name as log file, this will be used when restoring the data - so dont delete it
$log_filename=$database['db_name'].".log";
$log_file=$dir . $log_filename; // with path
// structure of whole database
$structure_file=$dir . $database['db_name'] . "-structure" . ".sql";
// all structure files start with this
$structure_file_head=$dir . $database['db_name'] . "-";
$structure_file_tail="-structure.sql"; // all data files end with this
// all data files start with this
$data_file_head=$dir . $database['db_name'] . "-";
$data_file_body="-data-";
$data_file_tail=".sql"; // all data files end with this
// open the log file for writing to while running this script
$log_handle = fopen($log_file, "wb");
if ($log_handle)
{
// log file was opened successfully, IE not locked by someone else doing backup/restore
$progress.="Successfully opened log file ".$log_filename."<br/>\n";
$total_files_produced++;
// lock it ourselves
clearstatcache();
if (flock($log_handle, LOCK_EX)) // ## don't do anything unless lock is successful
{
// file is locked
$progress.="Successfully locked log file<br/>\n";
$sql="SHOW TABLES";
$tables=run_mysql_query($sql, "NUM");
// if there are tables to process, then process them each in turn
if(sizeof($tables)>0)
{
// lock all the tables so an accurate snapshot can be taken
reset($tables);
$lock_table_sql = "LOCK TABLES ";
while(list($key, $value)=each($tables))
{
$lock_table_sql.= $tables[$key][0]." WRITE,";
}
$lock_table_sql=substr($table_list,0,-1);
run_mysql_query($lock_table_sql);
$progress.="All database tables locked<br/>\n";
// there are tables in the database
$progress.="There are ".sizeof($tables)." tables in the database<br/>\n";
// create headers for log file
$structure_log.="--------DATABASE STRUCTURE - drop/create query format\r\n\r\n";
$data_log.="--------DATABASE DATA - query format\r\n\r\n";
// open structure file for writing
$structure_handle = fopen ($structure_file, "w+");
$structure_log.=" creating file: ".$structure_file."\r\n";
$total_files_produced++;
reset($tables);
while(list($key, $value)=each($tables))
{
//// doing each table
$data_log.="\r\nTable ".$table_name."\r\n";
//// doing data
// reference for file. If data crosses mutliple files, use this as a reference.
$file_loop=1;
$file_loop_padded_string=str_pad($file_loop, 4, "0", STR_PAD_LEFT);
$data_file=$data_file_head . $table_name . $data_file_body . $file_loop_padded_string . $data_file_tail;
$row_iterator=0; // reset rows per file to 0
// unlock the tables as the procedure is complete
$sql="UNLOCK TABLES";
run_mysql_query($sql);
$progress.="All database tables unlocked<br/>\n";
}
else
{
// there are no tables to backup
$progress.="There are no tables in the database to do a backup<br/>\n";
}
}
else
{
// failed to lock log file
$progress.="Failed to lock log file<br/>\n";
}
}
else
{
// failed to open/create log file
$progress.="Failed to create log file, this may be locked by another process<br/>\n";
}
return $progress;
}
function restore_database_from_file($dir, $log_filename, $index=NULL)
{
global $database; // load external database connection values
$progress=""; // log of progress returned to the user
$total_files_processed=0; // number of files produced by this script (log,structure,data)
$file_list=array();
if(!ereg("/$", $dir))
{
$dir.="/";
}
// open the log file for writing to while running this script
$log_handle = fopen($dir.$log_filename, "r");
if($log_handle)
{
rewind($log_handle);
// log file was opened successfully, IE not locked by someone else doing backup/restore
$progress.="Successfully opened log file ".$log_filename."<br/>\n";
$total_files_processed++;
// lock it ourselves
clearstatcache();
if (flock($log_handle, LOCK_EX)) // ## don't do anything unless lock is successful
{
// file is locked
$progress.="Successfully locked log file<br/>\n";
// extract list of created files from the log
while (!feof($log_handle))
{
$buffer="";
$regs="";
$buffer = fgets($log_handle, 4096);
if(ereg("creating file: (.*)$", $buffer, $regs))
{
$file_list[]=trim($regs[1]);
}
}
if(sizeof($file_list)>0)
{
// there are some files to restore
$progress.="There are some files listed in the log to restore<br/>\n";
if(is_null($index))
{
reset($file_list);
while(list($key, $value)=each($file_list))
{
$progress.="Processing ".$file_list[$key]."<br/>\n";
$data_handle=fopen ($file_list[$key], "r");
while(!feof($data_handle))
{
$buffer= trim(fgets($data_handle, 4096));
while( (!feof($data_handle)) && (!ereg(".*;$", $buffer)) )
{
$buffer.= trim(fgets($data_handle, 4096));
}
if(ereg(".*;$", $buffer))
{
run_mysql_query($buffer);
}
}
fclose($data_handle);
}
}
else
{
if( (isset($file_list[$index])) && (!empty($file_list[$index])) )
{
$progress.="Processing ".$file_list[$index]."<br/>\n";
$data_handle=fopen($file_list[$index], "r");
while(!feof($data_handle))
{
$buffer= trim(fgets($data_handle, 4096));
while( (!feof($data_handle)) && (!ereg(".*;$", $buffer)) )
{
$buffer.= trim(fgets($data_handle, 4096));
}
if(ereg(".*;$", $buffer))
{
run_mysql_query($buffer);
}
}
fclose($data_handle);
flock($log_handle, LOCK_UN);
fclose($log_handle);
return $progress;
}
else
{
flock($log_handle, LOCK_UN);
fclose($log_handle);
return "Finished";
}
}
}
else
{
// there are no files to restore
flock($log_handle, LOCK_UN);
fclose($log_handle);
return "Failed. There are no files listed in the log to restore<br/>\n";
}
}
else
{
// failed to lock log file
$progress.="Failed to lock log file<br/>\n";
}
flock($log_handle, LOCK_UN);
}
else
{
// failed to open/create log file
return "Failed to open log file, it may be locked by another process or just doesnt exist<br/>\n";
}
fclose($log_handle);
// restore the database from the subdirectory backup and use the logfile as a source of the files used
//echo restore_database_from_file("./backup/", $log_filename);
// restore the database from the subdirectory backup and use the logfile as a source of the files used
// split the restore across several web pages to prevent the broswer from timing out
//echo page_restore_database_from_file("./backup/", $log_filename);
?>
matthew waygood wrote :1160
In order to use the paged restore, have just the page restore function in a script and it will do the rest. It will refresh the script and place index=0 in the current URL. The presence of an index value in the url will start the process and delete the entire database prior to restoring the structure then data.
There is no protection on this, and its meant to be run by the web developer in a protected admin area. So please be wary of running the restore on live sites, to prevent loss of data.
