Recommended:

  • phpclasses.org
  • jsclasses.org
  • jsmag.com
  • siteapps.com
  • View our reviews on Hot Scripts
  • JS Tutorial
  • scripts.com
  • securesignup.com




Recent Comments

Powered by Disqus




Back to articles

Making database backup and storing it in file

Here are two useful function, one makes a backup of specified database and stores it in file, other restores database from a backup file made by first function.

<?php
//backup function to create database backups files
//$con - database connection
//$db - database name
//$path - path where to save backup file
//$sep - custom seperator between querries
function backup($con, $db, $path = "./", $sep = "seperate here")
{
    //selecting database
    mysql_select_db($db) or die (mysql_error());
    //set your time zone to get precise timestamp
    date_default_timezone_set('Europe/Helsinki');
    //getting time stamp
    $date = date("Y-m-d-H-i-s");
    //creating file name
    $filepath = $path.$db."_".$date.".sql";
    //creating file
    $handle = fopen($filepath, "w");
    //getting all tales from database
    $tableres = mysql_query("SHOW TABLES FROM ".$db, $con);
    if(mysql_num_rows($tableres) > 0)
    {
        while($table = mysql_fetch_assoc($tableres))
        {
            //creating drop table if exists
            fwrite($handle, "DROP TABLE IF EXISTS `".
                            $table['Tables_in_'.$db]."`; rn");
            fwrite($handle, " rn");
            fwrite($handle, "-- ".$sep." rn");
            fwrite($handle, " rn");
            //creating create table if not exists
            fwrite($handle, "CREATE TABLE IF NOT EXISTS `".
                            $table['Tables_in_'.$db]."` ( rn");
            //getting information on table
            $fieldres = mysql_query("DESCRIBE ".$table['Tables_in_'.$db], $con);
            $f = '';
            $state = 1;
            $prime = 0;
            $keys = array();
            if(mysql_num_rows($fieldres) > 0)
            {
                while($field = mysql_fetch_assoc($fieldres))
                {
                    //checking if is primary key
                    if($field['Key'] == 'PRI')
                    {
                        $prime = $field['Field'];
                    }
                    //checkinf if index
                    else if($field['Key'] == 'MUL')
                    {
                        $keys[] = $field['Field'];
                    }
                    if($state)
                    {
                        $f = $f." `".$field['Field']."` ".$field['Type'];
                        if($field['Null'] == 'NO')
                        {
                            $f = $f." NOT NULL";
                        }
                        else if($field['Default'] != '')
                        {
                            $f = $f." default ".$field['Default'];
                        }
                        $f = $f." ".$field['Extra'];
                        $state--;
                    }
                    else
                    {
                        $f = $f.", rn `".$field['Field']."` ".$field['Type'];
                        if($field['Null'] == 'NO')
                        {
                            $f = $f." NOT NULL";
                        }
                        else if($field['Default'] != '')
                        {
                            $f = $f." default ".$field['Default'];
                        }
                        $f = $f." ".$field['Extra'];
                    }
                }
                //adding primiry key
                if($prime !== 0)
                {
                    $f = $f." , rn PRIMARY KEY  (`".$prime."`)";
                }
                //adding other keys
                foreach($keys as $key)
                {
                    $f = $f." , KEY `".$key."` (`".$key."`)";
                }
            }
            //getting table type
            $typeres = mysql_query("SHOW TABLE STATUS WHERE Name = '".
                                    $table['Tables_in_'.$db]."'", $con);
            $type = mysql_fetch_assoc($typeres);
            fwrite($handle, $f." rn");
            //getting charset
            $charset = explode("_", $type['Collation']);
            fwrite($handle, ") ENGINE=".$type['Engine']."  DEFAULT CHARSET=".
                            $charset[0]." AUTO_INCREMENT=".
                            $type['Auto_increment']."; rn");
            
            fwrite($handle, " rn");
            fwrite($handle, "-- ".$sep." rn");
            fwrite($handle, " rn");
            
            //getting all data from table
            $datares = mysql_query("SELECT * FROM ".$table['Tables_in_'.$db], $con);
            if(mysql_num_rows($datares) > 0)
            {
                $f = "INSERT INTO `".$table['Tables_in_'.$db]."` (";
                $fieldres = mysql_query("DESCRIBE ".$table['Tables_in_'.$db], $con);
                while($field = mysql_fetch_assoc($fieldres))
                {
                    $f = $f."`".$field['Field']."`, ";
                }
                fwrite($handle, substr($f, 0, strlen($f)-2).") VALUES rn");
                $f = '';
                while($data = mysql_fetch_assoc($datares))
                {
                    $f = $f."(";
                    foreach($data as $key => $value)
                    {
                        $value = mysql_real_escape_string($value);
                        $f = $f."'".$value."', ";
                    }
                    $f = substr($f, 0, strlen($f)-2);
                    $f = $f."), rn";
                }
                fwrite($handle, substr($f, 0, strlen($f)-4)."; rn");
                fwrite($handle, " rn");
                fwrite($handle, "-- ".$sep." rn");
                fwrite($handle, " rn");
            }
        }
    }
    //getting constraints
    $constres = mysql_query("SELECT * FROM information_schema.key_column_usage".
                            " WHERE table_schema = '".$db."'", $con);
    if(mysql_num_rows($constres) > 0)
    {
        while($constr = mysql_fetch_assoc($constres))
        {
            if($constr['CONSTRAINT_NAME'] != "PRIMARY")
            {
                fwrite($handle, " ALTER TABLE `".$constr['CONSTRAINT_SCHEMA'].
                                "` ADD CONSTRAINT `".$constr['CONSTRAINT_NAME'].
                                "` FOREIGN KEY (`".$constr['COLUMN_NAME'].
                                "`) REFERENCES `".$constr['REFERENCED_TABLE_NAME'].
                                "` (`".$constr['REFERENCED_COLUMN_NAME']."`);");
                fwrite($handle, " rn");
                fwrite($handle, "-- ".$sep." rn");
                fwrite($handle, " rn");
            }
        }
    }
    //closing file
    fclose($handle);
    //returning file path
    return $filepath;
}

//restore function to restore database from backup files
//$con - database connection
//$db - database name
//$filepath - path/to/filename.ext
//$sep - seperator, must be used the same, which was used in backup function
function restore($con, $db, $filepath, $sep = "seperate here")
{
    //selecting database
    mysql_select_db($db) or die (mysql_error());
    //getting contents of backup file
    $file = file_get_contents($filepath, FILE_USE_INCLUDE_PATH);
    //ecploding file into queries using seperator
    $queries = explode("-- ".$sep, $file);
    //free up memory ;)
    $file='';
    foreach($queries as $key => $value)
    {
        if($key != (sizeof($queries)-1))
        {
            //executing queries
            mysql_query($value, $con) or die (mysql_error());
        }
    }
}

//Example how to use
//connecting to database
$connection = mysql_connect("localhost", "root", "password");

//backuping and gettig backup file name
$file = backup($connection, "dbname");

//resoring database from backup
restore($connection, "dbname", $file);
?>

You may also be interested in:

Powered by BlogAlike.com

blog comments powered by Disqus