Backup the MySql Database using PHP

 When we are running a website, usually people take backup of their database regularly.
For that they usually open MySql database using PhpMyAdmin and export the database in to a file.

In the below example. I am reading all the table name from  "my_db_name" DB. then  In the each table I am reading all the records. From the each records with the field name and the values, I am creating a insert query for that table.

This process is done recursively for all the table.
In the usage, I just displaying the database dumb.
You can modify it such that writing it into a file and downloading itautomatically.

I hope it would save a lot of time in doing your regular database backup activity.

<?php
define('DB_NAME','my_db_name');
class BackupDB{
    public function getTables() {
        $table_data = array();
        $sql = "SHOW TABLES FROM `" . DB_NAME. "`";
        $query = mysql_query($sql) or die(mysql_error());
        while($result = mysql_fetch_array($query))
        {
            if (isset($result['Tables_in_' . DB_NAME])) {
                    $table_data[] = $result['Tables_in_' . DB_NAME];
                }       
        }
       
        return $table_data;
    }
   
    public function backup() {
    $tables=$this->getTables();
        $output = '';

        foreach ($tables as $table) {
            $status = true;
            if ($status) {
                $output .= 'TRUNCATE TABLE `' . $table . '`;' . "\n\n";
           
                $sql = "SELECT * FROM `" . $table . "`";
                $query = mysql_query($sql) or die(mysql_error());
           
                while($result = mysql_fetch_array($query)) {
                    $fields = '';
                   
                    foreach (array_keys($result) as $value) {
                        $fields .= '`' . $value . '`, ';
                    }
                   
                    $values = '';
                   
                    foreach (array_values($result) as $value) {
                        $value = str_replace(array("\x00", "\x0a", "\x0d", "\x1a"), array('\0', '\n', '\r', '\Z'), $value);
                        $value = str_replace(array("\n", "\r", "\t"), array('\n', '\r', '\t'), $value);
                        $value = str_replace('\\', '\\\\',    $value);
                        $value = str_replace('\'', '\\\'',    $value);
                        $value = str_replace('\\\n', '\n',    $value);
                        $value = str_replace('\\\r', '\r',    $value);
                        $value = str_replace('\\\t', '\t',    $value);           
                       
                        $values .= '\'' . $value . '\', ';
                    }
                   
                    $output .= 'INSERT INTO `' . $table . '` (' . preg_replace('/, $/', '', $fields) . ') VALUES (' . preg_replace('/, $/', '', $values) . ');' . "\n";
                }
               
                $output .= "\n\n";
            }
        }
       
        return $output;   
    }
}
?>

Usage:
$objBackup    = new BackupDB ( );
echo $objBackup->backup();

Rollback is not working in PhpMyAdmin MySQL

Once I had problem, in rollback my changes in  MySQL.
I tried disabling autocomit. But problem was not resolved.

/* disable autocommit */
$mysqli->autocommit(FALSE);

$mysqli->rollback();
mysql_query("ROLLBACK");


Then finally, I found the actual problem.
By default in PhpMyadmin table type is MyISAM. Rollback will not work in MyISAM.

Then I changed the table in to InnoDB, It worked!!!