Php Programming Code Examples
Php > Database Related Code Examples
MySQL Database Backup
MySQL Database Backup
<?
function mysqlbackup($host,$dbname, $uid, $pwd, $output, $structure_only)
{
//this function creates a text file (or output to a HTTP connection), that when parsed through MYSQL's telnet client, will re-create the entire database
//Parameters:
// $host: usually "localhost" but depends on where the MySQL database engine is mounted
// $dbname : The MySQL database name
// $uid : the database's username (not your account's), leave blank if none is required
// $pwd : the database's password
// $output : this is the complete filespec for the output text file, or if you want the result SQL to be sent back to the browser, leave blank.
// $structure_only : set this to true if you want just the schema of the database (not the actual data) to be output.
if (strval($output)!="") $fptr=fopen($output,"w"); else $fptr=false;
//connect to MySQL database
$con=mysql_connect("localhost",$uid, $pwd);
$db=mysql_select_db($dbname,$con);
//open back-up file ( or no file for browser output)
//set up database
out($fptr, "create database $dbname;\n\n");
//enumerate tables
$res=mysql_list_tables($dbname);
$nt=mysql_num_rows($res);
for ($a=0;$a<$nt;$a++)
{
$row=mysql_fetch_row($res);
$tablename=$row[0];
//start building the table creation query
$sql="create table $tablename\n(\n";
$res2=mysql_query("select * from $tablename",$con);
$nf=mysql_num_fields($res2);
$nr=mysql_num_rows($res2);
$fl="";
//parse the field info first
for ($b=0;$b<$nf;$b++)
{
$fn=mysql_field_name($res2,$b);
$ft=mysql_fieldtype($res2,$b);
$fs=mysql_field_len($res2,$b);
$ff=mysql_field_flags($res2,$b);
$sql.=" $fn ";
$is_numeric=false;
switch(strtolower($ft))
{
case "int":
$sql.="int";
$is_numeric=true;
break;
case "blob":
$sql.="text";
$is_numeric=false;
break;
case "real":
$sql.="real";
$is_numeric=true;
break;
case "string":
$sql.="char($fs)";
$is_numeric=false;
break;
case "unknown":
switch(intval($fs))
{
case 4: //little weakness here...there is no way (thru the PHP/MySQL interface) to tell the difference between a tinyint and a year field type
$sql.="tinyint";
$is_numeric=true;
break;
default: //we could get a little more optimzation here! (i.e. check for medium ints, etc.)
$sql.="int";
$is_numeric=true;
break;
}
break;
case "timestamp":
$sql.="timestamp";
$is_numeric=true;
break;
case "date":
$sql.="date";
$is_numeric=false;
break;
case "datetime":
$sql.="datetime";
$is_numeric=false;
break;
case "time":
$sql.="time";
$is_numeric=false;
break;
default: //future support for field types that are not recognized (hopefully this will work without need for future modification)
$sql.=$ft;
$is_numeric=true; //I'm assuming new field types will follow SQL numeric syntax..this is where this support will breakdown
break;
}
//VERY, VERY IMPORTANT!!! Don't forget to append the flags onto the end of the field creator
if (strpos($ff,"unsigned")!=false)
{
//timestamps are a little screwy so we test for them
if ($ft!="timestamp") $sql.=" unsigned";
}
if (strpos($ff,"zerofill")!=false)
{
//timestamps are a little screwy so we test for them
if ($ft!="timestamp") $sql.=" zerofill";
}
if (strpos($ff,"auto_increment")!=false) $sql.=" auto_increment";
if (strpos($ff,"not_null")!=false) $sql.=" not null";
if (strpos($ff,"primary_key")!=false) $sql.=" primary key";
//End of field flags
if ($b<$nf-1)
{
$sql.=",\n";
$fl.=$fn.", ";
}
else
{
$sql.="\n);\n\n";
$fl.=$fn;
}
//we need some of the info generated in this loop later in the algorythm...save what we need to arrays
$fna[$b]=$fn;
$ina[$b]=$is_numeric;
}
out($fptr,$sql);
if ($structure_only!=true)
{
//parse out the table's data and generate the SQL INSERT statements in order to replicate the data itself...
for ($c=0;$c<$nr;$c++)
{
$sql="insert into $tablename ($fl) values (";
$row=mysql_fetch_row($res2);
for ($d=0;$d<$nf;$d++)
{
$data=strval($row[$d]);
if ($ina[$d]==true)
$sql.= intval($data);
else
$sql.="\"".mysql_escape_string($data)."\"";
if ($d<($nf-1)) $sql.=", ";
}
$sql.=");\n";
out($fptr,$sql);
}
out($fptr,"\n\n");
}
mysql_free_result($res2);
}
if ($fptr!=false) fclose($fptr);
return 0;
}
function out($fptr,$s)
{
if ($fptr==false) echo("$s"); else fputs($fptr,$s);
}
?>
Copy the above function into a file called "mysqlbackup.h" and invoke it with the following script:
<html>
<?php
include("mysqlbackup.h");
mysqlbackup("localhost","yerdatabase","yerusername","yerpassword","/home/sites/site90/web/backup/sqldata.txt", true);
?>
The database's structure has been saved to "/home/sites/site90/web/backup/sqldata.txt" FTP download it at your convenience.
</html>
