[RESOLVED] DB Backup Script: Sorta Works...

Rodney_H_

Hi:

I modified a script that I found on this site. It works very well for the data, but for the database table's schema it gets a few things wrong, particularly with the field-type "ENUM".

Can someone help me 'tweak' this bad boy so I can call it a day??

Thanks.

<?php

function mysqlbackup($host,$dbname, $uid, $pwd, $output, $structure_only)  

{  

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

}





//include("mysqlbackup.h");  

//mysqlbackup("localhost","yerdatabase","yerusername","yerpassword","/home/sites/site90/web/backup/sqldata.txt", true);  
$file=$_SERVER['DOCUMENT_ROOT']."/proworkflow2/db_backup/sqldata.txt";
mysqlbackup("localhost","DB_name","DB_User","DB_Password",$file, false); 
?>

EDIT: Interestingly enough, this script is posted on PHPBuilder.com here:
http://phpbuilder.com/board/showthread.php?t=10321283&highlight=database+backup

as well as on CodeWalkers here:
http://codewalkers.com/seecode/46.html

(under two different authors' names ??)

mjax

Can you tell us WHAT exactly does not work as you expect it? So maybe a schema from one of your tables and the output this script produces for that table?

Rodney_H_

mjax wrote:
Can you tell us WHAT exactly does not work as you expect it? So maybe a schema from one of your tables and the output this script produces for that table?

Yes, mjax. Sorry.

I think everything works pretty well except the following line:


$field_type = mysql_field_type($result2, $j);

The "field type" is incorrect. I noticed there is a limitation because PHP returns its field type (string) rather than the MySQL field type (varchar, enum, etc...).

Is there a way that I can get the exact MySQL field type?

Thanks.

mjax

For ENUM fields, the returned type is 'string', but the mysql_field_flags() function will return 'enum'. This will still not give you the proper ENUM values.

Another route you could take is to use a MySQL query to fetch the fieldtypes and flags. This may be a lot easier for your particular situation.

For instance:

$result = mysql_query("DESCRIBE tablename");
while($field = mysql_fetch_array($result)) {
  echo "Field: {$field[Field]}\n";
  echo "Type: {$field[Type]}\n";
  echo "Null: {$field[Null]}\n";
  echo "Key: {$field[Key]}\n";
  echo "Default: {$field[Default]}\n";
  echo "Extra: {$field[Extra]}\n";
  echo "\n\n";
}

Rodney H. wrote:
Yes, mjax. Sorry.

I think everything works pretty well except the following line:
$field_type = mysql_field_type($result2, $j); 
The "field type" is incorrect. I noticed there is a limitation because PHP returns its field type (string) rather than the MySQL field type (varchar, enum, etc...).

Is there a way that I can get the exact MySQL field type?

Thanks.

Rodney_H_

mjax wrote:
For ENUM fields, the returned type is 'string', but the mysql_field_flags() function will return 'enum'. This will still not give you the proper ENUM values.

Another route you could take is to use a MySQL query to fetch the fieldtypes and flags. This may be a lot easier for your particular situation.

For instance:
$result = mysql_query("DESCRIBE tablename");
while($field = mysql_fetch_array($result)) {
  echo "Field: {$field[Field]}\n";
  echo "Type: {$field[Type]}\n";
  echo "Null: {$field[Null]}\n";
  echo "Key: {$field[Key]}\n";
  echo "Default: {$field[Default]}\n";
  echo "Extra: {$field[Extra]}\n";
  echo "\n\n";
}

Thanks, I will give this a whirl. It looks like what the script needs to function the way that I want and need.

Thanks again, mjax.

Rodney_H_

Thanks again, mjax.

With the "DESCRIBE" query, I am now able to completely rewrite the script to something even better, but that I hadn't know how to do until just now.

Thanks!!

Rodney_H_

One last question.

Is there a way with PHP to get the MySQL storage engine type? (MYISAM, INNODB, etc) ??

Thanks.

mjax

Try this:

$result = mysql_query("show table status");

This will return extended information about all tables in your database. It also includes the 'Engine' value for each table, such as 'MyISAM'.

Rodney_H_

Great!

Thank you so much for your help.

I appreciate it.