This is a function that backs up the structure and data of a MySQL database and represents them as a large text file of SQL statements. Doesn't require special disk access permissions, it pulls everything through the connection handle itself. Output can be sent transfered via FTP (i.e. by transfering the text file) or by HTTP (i.e. by direct output to the invoking browser).
By : woodys
<?
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.
// **************
// IMPORTANT: If you use this function, for personal or commercial use, AND you feel an overwhelming sense of gratitude that someone actually took the time and wrote it,
// immediately go to your paypal account and send me $10 with a small comment of how and how much it helped! Set the payment recipient to woodystanford@yahoo.com .
// **************
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>
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More Database Code Articles
More By Codewalkers
 developerWorks - FREE Tools!
|
You'll get answers to many questions and more from David Barnes, Lead Evangelist for IBM Emerging Internet Technologies. David will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial version of WebSphere Business Modeler Advanced V6.1.1, IBM’s premier business process modeling and analysis tool for business users that offers process modeling, simulation, and analysis capabilities. IBM WebSphere Business Modeler helps you visualize, understand, and document business processes for continuous improvement.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial version of IBM Rational Business Developer V7.1. Rational Business Developer offers rapid and simplified development of business applications and services through Enterprise Generation Language (EGL) tools, generating Java or mainframe solutions while shielding developers from technical complexities.
FREE! Go There Now!
|
|
|
|
Secure your Web applications with IBM Rational AppScan Standard Edition V7.7, previously known as Watchfire AppScan. This Web application security testing tool automates vulnerability assessments and scans and tests for common Web application vulnerabilities. Visit IBM developerWorks to download a free trial of IBM Rational AppScan Standard Edition V7.7.
FREE! Go There Now!
|
|
|
|
Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available.
FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference on November 29 at 1:00 pm ET to participate in an interactive discusssion with Grady Booch around architecture and reuse. Get your questions answered!
FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference on December 6 at 1:00 pm ET to participate in an agile application development discussion and get your questions answered on using IBM Rational Method Composer in a distributed environment.Get your questions answered!
FREE! Go There Now!
|
|
|
|
Join this webcast to discover the key requirements for successful change and release management. Learn how to extend your .NET environment to improve productivity and collaboration, and address core problems afflicting team development. In this webcast, we’ll review typical challenges faced by customers and how to resolve them with the IBM Rational Change and Release Management solution, including Rational ClearCase, Rational ClearQuest and Rational Build Forge. Replay is available for 9 months.
FREE! Go There Now!
|
|
|
|
The Eclipse community is constantly working to extend Eclipse's functionality. In this webcast, learn about some of the most important and feature-rich projects under development. From multi-language support to plug-in development, tune in to see what Eclipse is capable of now.
FREE! Go There Now!
|
|
|
|
In this webcast, you'll get an introduction to the eXtreme Transaction Processing (XTP) features of WebSphere Extended Deployment and the common architectural traits required by XTP applications. See how WebSphere Extended Deployment's ObjectGrid feature provides a state-of-the-art infrastructure for hosting XTP applications.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
