Automatic Database(MySQL) backup

Automatic Database(MySQL) backup

Create automatic database(MySQL) backup by php script into a .sql file in Windows platform.

MySQL is one of the most popular open source database management system for the development of interactive Websites. If a site has sensitive data in a MySQL database,then it needs to take backup of

the information, so that it can be restored in case of any disaster .

In this article, we will focus on how to get automatic backup solution of the database (MySQl) by php script in windows system. For this please find the following php script.

<?php 

$db_host = localhost ; // Here enter the databse host name.
$db_user = root ; // Here enter the username to access your database.
$db_pass = 'mindfire' ; // Here enter the password to access your database.
$db_name = 'test_db' ; // Here enter the database name, of which you want to take the backup.

// call the backup_db function.
backup_db($db_host,$db_user,$db_pass,$db_name);


/**
* @desc : create database backup (.sql file) of the database
* @param : $db_host(string),$db_user(string),$db_pass(string),$db_name(string),$tables(by default it will take '*' for all tables in db), $drop(bool)
* @return : void
**/

function backup_db($db_host,$db_user,$db_pass,$db_name,$tables = '*',$drop = true)
{
    // link to the database by above given authentication.
    $link = mysql_connect($db_host,$db_user,$db_pass);
    
    if(mysql_select_db($db_name,$link))
    {
        //get all of the tables
        if($tables == '*')
        {
            // store all the table name in $tables array.
            $tables = array();
            $result = mysql_query('SHOW TABLES');

            while($row = mysql_fetch_row($result))
            {
                $tables[] = $row[0];
            }

        }
        else
        {
            $tables = is_array($tables) ? $tables : explode(',',$tables);
        }

        //cycle through
        foreach($tables as $table)
        {
            $result = mysql_query('SELECT * FROM '.$table);
            $fields_num = mysql_num_fields($result);

            $return.= 'DROP TABLE '.$table.';';
            $res = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
            
            $return.= "\n\n".$res[1].";\n\n";

            for ($index = 0; $index < $fields_num; $index++) 
            {

                while($row = mysql_fetch_row($result))
                {
                    $return.= 'INSERT INTO '.$table.' VALUES(';

                    for($j=0; $j<$fields_num; $j++) 
                    {
                        $row[$j] = addslashes($row[$j]);
                        $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                        if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                        if ($j<($fields_num-1)) { $return.= ','; }
                    } // end of innermost for loop

                    $return.= ");\n";
                } // end of while loop

            } // end of for loop.

            $return.="\n\n\n";
        } // end of outer for loop.

        //save the file in the desitred location.
        $handle = fopen('backup/db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
        fwrite($handle,$return);
        fclose($handle);
    }
} // end of the function

?>

When this above php script is run manually, it will create the database backup(in the format of .sql file) of your given database. But we need to run it automatic, so that it will create database

backup. In this article, I am going to discuss how to make this script to run automatic in windows platform.

For this you need to create a “.bat” file which is going to run the above php script through command prompt. In my example, I have created a backup.bat file to run the backup.php(the php script

which creates database backup).

In the backup.bat file, we need to link the php.exe file and the php script file(which is going to run : backup.php). Please check the following code, that should be placed in the bacup.bat file.

@echo off
C:\xampp\php\php.exe -f D:\Projects\php\Live\tested_app\backup_databse_php\backup.php

In the above code, echo off will clear the command prompt window at the time of execution. Then you need to give the physical path of the php.exe file and the php script file that to be
run through command prompt. Now you need to open the “Scheduled Tasks” in your system (All Programs -> Accessories -> System Tools -> Scheduled Tasks ). Or you can open it through the command

prompt by typing “Control schedtasks”.

Here by right click, you can create a new “Scheduled Task” . Then you have to set the properties of your scheduled task. Right click on the scheduled task(that you created) and set the path of the
.bat file(backup.bat) in the Run field . Here you can also set password to run this file. Then please select the “Scheduled” tab in the properties window. Here you can schedule the process as the

basis of daily, weekly, monthly, once, at system start up, at logon and when idle.

As per your schedule, the command prompt runs the given .bat file(backup.bat) and the backup.php file is automatically gets executed and makes the back up of your database(MySQL) in a .sql file.