Here's a couple PHP functions for dumping MySQL databases. You'll
doubtless need to fix the comment lines after email clients wrap them.
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);
}
> I haven't finished implementing this yet, but what I'm working on is to
> store the schema, table by table, as separate files. These can be
> generated by phpMyAdmin's Export page or you can use mysqldump from the
> command line. In addition, I'll store the data for some tables that have
> mostly static info, using the "extended insert" format (a row of data
> per text line). SVN will then be able to diff any changes in the CREATE
> TABLE statements or the data lines. However, all this will be mostly for
> reference, since I may make the table changes using phpMyAdmin or a
> straight SQL ALTER TABLE statement against the development database,
> export the new schema into my working copy, which I then can commit and
> merge into the trunk, but I still have to apply those changes to the
> production database.
>
> Regards,
>
> Joe
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
> For additional commands, e-mail: users-help@subversion.tigris.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Received on Fri Dec 17 05:28:22 2004