13Answers
Answer
      • 2
    • in php, were dealing with users, so most likely we will make use of the automated backup using php scripts.
    • Please note that the pure-PHP solutions here below make the assumption that the order of the tables is not important. If you have foreign keys in your database, the order of the restore IS important. But I didn't find a solution to that specific problem... MysqlDump do that, and phpmyadmin also. Although they are not pure PHP solutions.
    • I also had a situation and I couldn't find a tool to satisfy me. So for the sake of backup/restore MySQL data from PHP I have made a program that can compress the data into a zip file that you can download. Later you can upload and restore the full database. You can find it on my Github page github.com/JoshyFrancis/mysql_backup_restore_php. Now this repository is archived. This is more an introduction to how to backup data into a zip file in a cross-platform way. And I am now on the process of developing a better solution. Soon you can find it on my github repositories.
      • 2
    • @jehon The table information_schema.referential_constraints can be analyzed to determine the correct order. If there cannot be a correct order (because of circular dependencies), then you'd have to follow mysqldump's approach of dropping the foreign key check, adding all the data, and then adding the foreign keys at the end.

While you can execute backup commands from PHP, they don't really have anything to do with PHP. It's all about MySQL.

I'd suggest using the mysqldump utility to back up your database. The documentation can be found here : http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html.

The basic usage of mysqldump is

mysqldump -u user_name -p name-of-database >file_to_write_to.sql

You can then restore the backup with a command like

mysql -u user_name -p <file_to_read_from.sql

Do you have access to cron? I'd suggest making a PHP script that runs mysqldump as a cron job. That would be something like

<?php

$filename='database_backup_'.date('G_a_m_d_y').'.sql';

$result=exec('mysqldump database_name --password=your_pass --user=root --single-transaction >/var/backups/'.$filename,$output);

if($output==''){/* no output is good */}
else {/* we have something to log the output here*/}

If mysqldump is not available, the article describes another method, using the SELECT INTO OUTFILE and LOAD DATA INFILE commands. The only connection to PHP is that you're using PHP to connect to the database and execute the SQL commands. You could also do this from the command line MySQL program, the MySQL monitor.

It's pretty simple, you're writing an SQL file with one command, and loading/executing it when it's time to restore.

You can find the docs for select into outfile here (just search the page for outfile). LOAD DATA INFILE is essentially the reverse of this. See here for the docs.

  • 57
Reply Report

using PHP function:

EXPORT_DATABASE("localhost", "user", "pass", "db_name" );

updated function code at github.

  • 30
Reply Report
    • i done it my self i just commented $content .= " INSERT INTO ".$table." VALUES"; and it produced only structure not insert queries
      • 2
    • We prefer that answers stand on their own, not require us to follow a link to github. Your previous version that included the code here is better. If you want to have a postscript pointing to github, that would be fine.
      • 2
    • @Teepeemm I can't agree with you. Code snippets should be posted within answers, but the updatable code / functions, better on github, where it will be maintained better (because MYSQL standards are updated too, like when I migrated code from MySql to MySqli, which i could never done if that code was embedded here.

Based on the good solution that provided by tazo todua, I've made some of changes since mysql_connect has deprecated and not supported in new php version. I've used mysqli_connect instead and increased the performance of inserting values to the database:

<?php

/**
* Updated: Mohammad M. AlBanna
* Website: MBanna.info
*/


//MySQL server and database
$dbhost = 'localhost';
$dbuser = 'my_user';
$dbpass = 'my_pwd';
$dbname = 'database_name';
$tables = '*';

//Call the core function
backup_tables($dbhost, $dbuser, $dbpass, $dbname, $tables);

//Core function
function backup_tables($host, $user, $pass, $dbname, $tables = '*') {
    $link = mysqli_connect($host,$user,$pass, $dbname);

    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
        exit;
    }

    mysqli_query($link, "SET NAMES 'utf8'");

    //get all of the tables
    if($tables == '*')
    {
        $tables = array();
        $result = mysqli_query($link, 'SHOW TABLES');
        while($row = mysqli_fetch_row($result))
        {
            $tables[] = $row[0];
        }
    }
    else
    {
        $tables = is_array($tables) ? $tables : explode(',',$tables);
    }

    $return = '';
    //cycle through
    foreach($tables as $table)
    {
        $result = mysqli_query($link, 'SELECT * FROM '.$table);
        $num_fields = mysqli_num_fields($result);
        $num_rows = mysqli_num_rows($result);

        $return.= 'DROP TABLE IF EXISTS '.$table.';';
        $row2 = mysqli_fetch_row(mysqli_query($link, 'SHOW CREATE TABLE '.$table));
        $return.= "\n\n".$row2[1].";\n\n";
        $counter = 1;

        //Over tables
        for ($i = 0; $i < $num_fields; $i++) 
        {   //Over rows
            while($row = mysqli_fetch_row($result))
            {   
                if($counter == 1){
                    $return.= 'INSERT INTO '.$table.' VALUES(';
                } else{
                    $return.= '(';
                }

                //Over fields
                for($j=0; $j<$num_fields; $j++) 
                {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = str_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                    if ($j<($num_fields-1)) { $return.= ','; }
                }

                if($num_rows == $counter){
                    $return.= ");\n";
                } else{
                    $return.= "),\n";
                }
                ++$counter;
            }
        }
        $return.="\n\n\n";
    }

    //save file
    $fileName = 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql';
    $handle = fopen($fileName,'w+');
    fwrite($handle,$return);
    if(fclose($handle)){
        echo "Done, the file name is: ".$fileName;
        exit; 
    }
}
  • 13
Reply Report
      • 2
    • there are 4 loops: 1) foreach: over tables 2) for $i:??? 3) while:over rows 4) for $j: over columns. I can't see how the for $i is necessary
    • I've tested the function, and the for $i loop (below the //Over tables comment) is indeed unnecessary. Running this code with and without this for loop results in the same outcome. (Of course you have to keep the contents of the loop)

If you want to backup a database from php script you could use a class for example lets call it MySQL. This class will use PDO (build in php class which will handle the connection to the database). This class could look like this:

<?php /*defined in your exampleconfig.php*/
define('DBUSER','root');
define('DBPASS','');
define('SERVERHOST','localhost');
?>

<?php /*defined in examplemyclass.php*/
    class MySql{
        private $dbc;
        private $user;
        private $pass;
        private $dbname;
        private $host;

        function __construct($host="localhost", $dbname="your_databse_name_here", $user="your_username", $pass="your_password"){
            $this->user = $user;
            $this->pass = $pass;
            $this->dbname = $dbname;
            $this->host = $host;
            $opt = array(
               PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
               PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
            );
            try{
                $this->dbc = new PDO('mysql:host='.$this->host.';dbname='.$this->dbname.';charset=utf8', $user, $pass, $opt);
            }
            catch(PDOException $e){
                 echo $e->getMessage();
                 echo "There was a problem with connection to db check credenctials";
            }
        } /*end function*/


        public function backup_tables($tables = '*'){  /* backup the db OR just a table */
            $host=$this->host;
            $user=$this->user;
            $pass=$this->pass;
            $dbname=$this->dbname;
            $data = "";
            //get all of the tables
            if($tables == '*')
            {
                $tables = array();
                $result = $this->dbc->prepare('SHOW TABLES'); 
                $result->execute();                         
                while($row = $result->fetch(PDO::FETCH_NUM)) 
                { 
                    $tables[] = $row[0]; 
                }
            }
            else
            {
                $tables = is_array($tables) ? $tables : explode(',',$tables);
            }
            //cycle through
            foreach($tables as $table)
            {
                $resultcount = $this->dbc->prepare('SELECT count(*) FROM '.$table);
                $resultcount->execute();
                $num_fields = $resultcount->fetch(PDO::FETCH_NUM);
                $num_fields = $num_fields[0];

                $result = $this->dbc->prepare('SELECT * FROM '.$table);
                $result->execute();
                $data.= 'DROP TABLE '.$table.';';

                $result2 = $this->dbc->prepare('SHOW CREATE TABLE '.$table);    
                $result2->execute();                            
                $row2 = $result2->fetch(PDO::FETCH_NUM);
                $data.= "\n\n".$row2[1].";\n\n";

                for ($i = 0; $i < $num_fields; $i++) 
                {
                    while($row = $result->fetch(PDO::FETCH_NUM))
                    { 
                        $data.= 'INSERT INTO '.$table.' VALUES(';
                        for($j=0; $j<$num_fields; $j++) 
                        {
                            $row[$j] = addslashes($row[$j]); 
                            $row[$j] = str_replace("\n","\\n",$row[$j]);
                            if (isset($row[$j])) { $data.= '"'.$row[$j].'"' ; } else { $data.= '""'; }
                            if ($j<($num_fields-1)) { $data.= ','; }
                        }
                        $data.= ");\n";
                    }
                }
                $data.="\n\n\n";
            }
            //save filename
            $filename = 'db-backup-'.time().'-'.(implode(",",$tables)).'.sql';
            $this->writeUTF8filename($filename,$data);
        /*USE EXAMPLE
           $connection = new MySql(SERVERHOST,"your_db_name",DBUSER, DBPASS);
           $connection->backup_tables(); //OR backup_tables("posts");
           $connection->closeConnection();
        */
        } /*end function*/


        private function writeUTF8filename($filenamename,$content){  /* save as utf8 encoding */
            $f=fopen($filenamename,"w+"); 
            # Now UTF-8 - Add byte order mark 
            fwrite($f, pack("CCC",0xef,0xbb,0xbf)); 
            fwrite($f,$content); 
            fclose($f); 
        /*USE EXAMPLE this is only used by public function above...
            $this->writeUTF8filename($filename,$data);
        */
        } /*end function*/


        public function recoverDB($file_to_load){
            echo "write some code to load and proccedd .sql file in here ...";
        /*USE EXAMPLE this is only used by public function above...
            recoverDB("some_buck_up_file.sql");
        */
        } /*end function*/


        public function closeConnection(){
            $this->dbc = null;
        //EXAMPLE OF USE 
        /*$connection->closeConnection();*/
        }/*end function*/


    } /*END OF CLASS*/
    ?>

Now you could simply use this in your backup.php:

include ('config.php');
include ('myclass.php');
    $connection = new MySql(SERVERHOST,"your_databse_name_here",DBUSER, DBPASS);
    $connection->backup_tables(); /*Save all tables and it values in selected database*/
    $connection->backup_tables("post_table"); /*Saves only table name posts_table from selected database*/
    $connection->closeConnection();

Which means that visiting this page will result in backing up your file... of course it doesn't have to be that way :) you can call this method on every post to your database to be up to date all the time, however, I would recommend to write it to one file at all the time instead of creating new files with time()... as it is above.

Hope it helps and good luck ! :>

  • 5
Reply Report

From the answer of @DevWL, I got "Undefined offset ..." at

if ($j<($num_fields-1)) { $data.= ','; }

I made some changes to:

  • preserve relationships (foreign keys)
  • use Transactions
  • remove the uneedy $num_fields

class DBbackup {
 public $suffix;
 public $dirs;
 protected $dbInstance;
 public function __construct() {
   try{
    $this->dbInstance = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, 
    $username, $password);
  } catch(Exception $e) {
    die("Error ".$e->getMessage());
  }
   $this->suffix = date('Ymd_His');
 }

 public function backup($tables = '*'){
   $output = "-- database backup - ".date('Y-m-d H:i:s').PHP_EOL;
   $output .= "SET NAMES utf8;".PHP_EOL;
   $output .= "SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';".PHP_EOL;
   $output .= "SET foreign_key_checks = 0;".PHP_EOL;
   $output .= "SET AUTOCOMMIT = 0;".PHP_EOL;
   $output .= "START TRANSACTION;".PHP_EOL;
   //get all table names
   if($tables == '*') {
     $tables = [];
     $query = $this->dbInstance->prepare('SHOW TABLES');
     $query->execute();
     while($row = $query->fetch(PDO::FETCH_NUM)) {
       $tables[] = $row[0];
     }
     $query->closeCursor();
   }
   else {
     $tables = is_array($tables) ? $tables : explode(',',$tables);
   }

   foreach($tables as $table) {

     $query = $this->dbInstance->prepare("SELECT * FROM `$table`");
     $query->execute();
     $output .= "DROP TABLE IF EXISTS `$table`;".PHP_EOL;

     $query2 = $this->dbInstance->prepare("SHOW CREATE TABLE `$table`");
     $query2->execute();
     $row2 = $query2->fetch(PDO::FETCH_NUM);
     $query2->closeCursor();
     $output .= PHP_EOL.$row2[1].";".PHP_EOL;

       while($row = $query->fetch(PDO::FETCH_NUM)) {
         $output .= "INSERT INTO `$table` VALUES(";
         for($j=0; $j<count($row); $j++) {
           $row[$j] = addslashes($row[$j]);
           $row[$j] = str_replace("\n","\\n",$row[$j]);
           if (isset($row[$j]))
             $output .= "'".$row[$j]."'";
           else $output .= "''";
           if ($j<(count($row)-1))
            $output .= ',';
         }
         $output .= ");".PHP_EOL;
       }
     }
     $output .= PHP_EOL.PHP_EOL;

   $output .= "COMMIT;";
   //save filename

   $filename = 'db_backup_'.$this->suffix.'.sql';
   $this->writeUTF8filename($filename,$output);
 }


 private function writeUTF8filename($fn,$c){  /* save as utf8 encoding */
   $f=fopen($fn,"w+");
   # Now UTF-8 - Add byte order mark
   fwrite($f, pack("CCC",0xef,0xbb,0xbf));
   fwrite($f,$c);
   fclose($f);
 }

}

And usage example:

$Backup = new DBbackup();
$Backup->backup();

This works great on MySQL 10.1.34-MariaDB , PHP : 7.2.7

  • 2
Reply Report

Take a look here! It is a native solution written in php. You won't need to exec mysqldump, or cope with incomplete scripts. This is a full mysqldump clone, without dependencies, output compression and sane defaults.

Out of the box, mysqldump-php supports backing up table structures, the data itself, views, triggers and events.

MySQLDump-PHP is the only library that supports:

  • output binary blobs as hex.
  • resolves view dependencies (using Stand-In tables).
  • output compared against original mysqldump. Linked to travis-ci testing system (testing from php 5.3 to 7.1 & hhvm)
  • dumps stored procedures.
  • dumps events.
  • does extended-insert and/or complete-insert.
  • supports virtual columns from MySQL 5.7.

You can install it using composer, or just download the php file, and it is as easy as doing:

use Ifsnop\Mysqldump as IMysqldump;

try {
    $dump = new IMysqldump\Mysqldump('database', 'username', 'password');
    $dump->start('storage/work/dump.sql');
} catch (\Exception $e) {
    echo 'mysqldump-php error: ' . $e->getMessage();
}

All the options are explained at the github page, but more or less are auto-explicative:

$dumpSettingsDefault = array(
    'include-tables' => array(),
    'exclude-tables' => array(),
    'compress' => Mysqldump::NONE,
    'init_commands' => array(),
    'no-data' => array(),
    'reset-auto-increment' => false,
    'add-drop-database' => false,
    'add-drop-table' => false,
    'add-drop-trigger' => true,
    'add-locks' => true,
    'complete-insert' => false,
    'databases' => false,
    'default-character-set' => Mysqldump::UTF8,
    'disable-keys' => true,
    'extended-insert' => true,
    'events' => false,
    'hex-blob' => true, /* faster than escaped content */
    'net_buffer_length' => self::MAXLINESIZE,
    'no-autocommit' => true,
    'no-create-info' => false,
    'lock-tables' => true,
    'routines' => false,
    'single-transaction' => true,
    'skip-triggers' => false,
    'skip-tz-utc' => false,
    'skip-comments' => false,
    'skip-dump-date' => false,
    'skip-definer' => false,
    'where' => '',
    /* deprecated */
    'disable-foreign-keys-check' => true
);
  • 1
Reply Report
    • i had problems, due to lock tables while restoring . i fixed by passing argument 'add-locks' => false ( using pivotal cloud foundry oracle mysql service )

for using Cron Job, below is the php function

public function runback() {

    $filename = '/var/www/html/local/storage/stores/database_backup_' . date("Y-m-d-H-i-s") . '.sql';

    /*
     *  db backup
     */

    $command = "mysqldump --single-transaction -h $dbhost -u$dbuser -p$dbpass yourdb_name > $filename";
    system($command);
    if ($command == '') {
        /* no output is good */
        echo 'not done';
    } else {
       /* we have something to log the output here */
        echo 'done';
    }
}

There should not be any space between -u and username also no space between -p and password. CRON JOB command to run this script every sunday 8.30 am:

>> crontab -e

30 8 * * 7 curl -k https://www.websitename.com/takebackup
  • 0
Reply Report

@T.Todua's answer. It's cool. However, it failed to backup my database correctly. Hence, I've modified it. Please use like so: Backup_Mysql_Db::init("localhost","user","pass","db_name","/usr/var/output_dir" ); Thank you.

 <?php            
/**========================================================+
 *                                                         +
 * Static class with functions for backing up database.    +
 *                                                         +
 * PHP Version 5.6.31                                      +
 *=========================================================+*/ 
class Backup_Mysql_Db
{ 
private function __construct() {}  
/**Initializes the database backup
 * @param String $host mysql hostname
 * @param String $user mysql user
 * @param String $pass mysql password
 * @param String $name name of database
 * @param String $outputDir the path to the output directory for storing the backup file
 * @param Array $tables (optional) to backup specific tables only,like: array("mytable1","mytable2",...)  
 * @param String $backup_name (optional) backup filename (otherwise, it creates random name) 
 * EXAMPLE: Backup_Mysql_Db::init("localhost","user","pass","db_name","/usr/var/output_dir" );  
*/  
public static function init($host,$user,$pass,$name, $outputDir, $tables=false, $backup_name=false)
{
    set_time_limit(3000); 
    $mysqli = new mysqli($host,$user,$pass,$name); 
    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }
    //change database to $name
    $mysqli->select_db($name);  
    /* change character set to utf8 */
    if (!$mysqli->set_charset("utf8")) 
    {
        printf("Error loading character set utf8: %s\n", $mysqli->error);
        exit();
    }  
    //list all tables in the database
    $queryTables = $mysqli->query('SHOW TABLES'); 
    while($row = $queryTables->fetch_row()) 
    { 
        $target_tables[] = $row[0];  
    }
    //if user opted to backup specific tables only
    if($tables !== false) 
    { 
        $target_tables = array_intersect( $target_tables, $tables); 
    }  
    date_default_timezone_set('Africa/Accra');//set your timezone
    //$content is the text data to be written to the file for backup
    $content = "-- phpMyAdmin SQL Dump\r\n-- version 4.7.4". //insert your phpMyAdmin version 
           "\r\n-- https://www.phpmyadmin.net/\r\n--\r\n-- Host: ".$host.
           "\r\n-- Generation Time: ".date('M d, Y \a\t h:i A',strtotime(date('Y-m-d H:i:s', time()))).
           "\r\n-- Server version: ".$mysqli->server_info.
           "\r\n-- PHP Version: ". phpversion();
    $content .= "\r\n\r\nSET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\r\nSET AUTOCOMMIT = 0;\r\nSTART TRANSACTION;\r\nSET time_zone = \"+00:00\";\r\n\r\n\r\n/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\r\n/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\r\n/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\r\n/*!40101 SET NAMES utf8mb4 */;\r\n\r\n--\r\n-- Database: `".
                $name."`\r\n--\r\nCREATE DATABASE IF NOT EXISTS `".
                $name."` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;\r\nUSE `".
                $name."`;"; 
    //traverse through every table in the database
    foreach($target_tables as $table)
    {
        if (empty($table)){ continue; } 
        $result = $mysqli->query('SELECT * FROM `'.$table.'`');     
        //get the number of columns
        $fields_amount=$result->field_count;
        //get the number of affected rows in the MySQL operation
        $rows_num=$mysqli->affected_rows;   
        //Retrieve the Table Definition of the existing table 
        $res = $mysqli->query('SHOW CREATE TABLE '.$table); 
        $TableMLine=$res->fetch_row(); 
        $content .= "\r\n\r\n-- --------------------------------------------------------\r\n\r\n"."--\r\n-- Table structure for table `".$table."`\r\n--\r\n\r\n";
        //if the table is not empty
        if(!self::table_is_empty($table,$mysqli))  
        {   $content .= $TableMLine[1].";\n\n";//append the Table Definition 
            //replace, case insensitively
            $content =str_ireplace("CREATE TABLE `".$table."`",//wherever you find this
                                    "DROP TABLE IF EXISTS `".$table."`;\r\nCREATE TABLE IF NOT EXISTS `".$table."`",//replace with that
                                    $content);//in this  
            $content .= "--\r\n-- Dumping data for table `".$table."`\r\n--\r\n"; 
            $content .= "\nINSERT INTO `".$table."` (".self::get_columns_from_table($table, $mysqli)." ) VALUES\r\n".self::get_values_from_table($table,$mysqli); 
        } 
        else//otherwise if the table is empty
        {
            $content .= $TableMLine[1].";";  
            //replace, case insensitively
            $content =str_ireplace("CREATE TABLE `".$table."`",//wherever you find this
                                    "DROP TABLE IF EXISTS `".$table."`;\r\nCREATE TABLE IF NOT EXISTS `".$table."`",//replace with that
                                    $content);//in this  
        } 
    }
    $content .= "\r\n\r\n/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\r\n/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\r\n/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;";
    date_default_timezone_set('Africa/Accra');
    //format the time at this very moment and get rid of the colon ( windows doesn't allow colons in filenames)
    $date = str_replace(":", "-", date('jS M, y. h:i:s A.',strtotime(date('Y-m-d H:i:s', time())))); 
    //if there's a backup name, use it , otherwise device one
    $backup_name = $backup_name ? $backup_name : $name.'___('.$date.').sql'; 
    //Get current buffer contents and delete current output buffer
    ob_get_clean();     
    self::saveFile($content, $backup_name, $outputDir); 
    exit; 
}

/** Save data to file. 
 * @param String $data The text data to be stored in the file 
 * @param String $backup_name The name of the backup file 
 * @param String $outputDir (optional) The directory to save the file to. 
 * If unspecified, will save in the current directory. 
 * */ 
private static function saveFile(&$data,$backup_name, $outputDir = '.') 
{
    if (!$data)
    {
        return false; 
    } 
    try 
    { 
        $handle = fopen($outputDir . '/'. $backup_name , 'w+');
        fwrite($handle, $data);
        fclose($handle);
    } catch (Exception $e) 
    {
      var_dump($e->getMessage());
      return false;
    }
    return true;
}
/**Checks if table is empty
 * @param String $table table in mysql database
 * @return Boolean true if table is empty, false otherwise   
*/ 
private static function table_is_empty($table,$mysqli)
{
    $sql = "SELECT * FROM $table";
    $result = mysqli_query($mysqli, $sql);  
    if($result) 
    {    
        if(mysqli_num_rows($result) > 0)
        {  
            return false; 
        }
        else 
        {    
            return true; 
        }
    }
    return false;  
}
/**Retrieves the columns in the table 
 * @param String $table table in mysql database
 * @return String a list of all the columns in the right format    
*/ 
private static function get_columns_from_table($table, $mysqli)
{ 
    $column_header = "";
    $result = mysqli_query($mysqli, "SHOW COLUMNS FROM $table");  
    while($row = $result->fetch_row()) 
    { 
        $column_header .= "`".$row[0]."`, "; 
    }
    //remove leading and trailing whitespace, and remove the last comma in the string
    return rtrim(trim($column_header),','); 
} 

/**Retrieves the values in the table row by row in the table 
 * @param String $table table in mysql database
 * @return String a list of all the values in the table in the right format    
*/
private static function get_values_from_table($table, $mysqli)
{
    $values = ""; 
    $columns = [];
    //get all the columns in the table
    $result = mysqli_query($mysqli, "SHOW COLUMNS FROM $table");  
    while($row = $result->fetch_row()) 
    { 
        array_push($columns,$row[0] ); 
    } 

    $result1 = mysqli_query($mysqli, "SELECT * FROM $table");  
    //while traversing every row in the table(row by row)
    while($row = mysqli_fetch_array($result1))
    {   $values .= "("; 
        //get the values in each column
        foreach($columns as $col)
        {               //if the value is an Integer
            $values .=  (self::column_is_of_int_type($table, $col,$mysqli)? 
                    $row["$col"].", "://do not surround it with single quotes
                    "'".$row["$col"]."', "); //otherwise, surround it with single quotes 
        }   
        $values = rtrim(trim($values),','). "),\r\n";  
    }  
    return rtrim(trim($values),',').";"; 
}

/**Checks if the data type in the column is an integer
 * @param String $table table in mysql database
 * @return Boolean true if it is an integer, false otherwise. 
*/
private static function column_is_of_int_type($table, $column,$mysqli)
{
    $q = mysqli_query($mysqli,"DESCRIBE $table");  
    while($row = mysqli_fetch_array($q)) 
    {
        if ($column === "{$row['Field']}")
        {
            if (strpos("{$row['Type']}", 'int') !== false) 
            {
                return true;
            }
        } 
    }
    return false; 
} 

}

  • 0
Reply Report

try this 100% working.

<?php
function dbbackup($host,$user,$pass,$name,$tables=false, $backup_name=false)
{ 
set_time_limit(3000); 
$mysqli = new mysqli($host,$user,$pass,$name); 
$mysqli->select_db($name); 
$mysqli->query("SET NAMES 'utf8'");
$queryTables = $mysqli->query('SHOW TABLES'); 
while($row = $queryTables->fetch_row()) 
{ 
    $target_tables[] = $row[0]; 
}   
if($tables !== false) 
{
    $target_tables = array_intersect( $target_tables, $tables); 
} 
$content = "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\r\nSET time_zone = \"+00:00\";\r\n\r\n\r\n/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\r\n/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\r\n/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\r\n/*!40101 SET NAMES utf8 */;\r\n--\r\n-- Database: `".$name."`\r\n--\r\n\r\n\r\n";
foreach($target_tables as $table)
{
    if (empty($table))
    {
        continue;
    } 
    $result = $mysqli->query('SELECT * FROM `'.$table.'`');     $fields_amount=$result->field_count;  $rows_num=$mysqli->affected_rows;     $res = $mysqli->query('SHOW CREATE TABLE '.$table); $TableMLine=$res->fetch_row(); 
    $content .= "\n\n".$TableMLine[1].";\n\n";   $TableMLine[1]=str_ireplace('CREATE TABLE `','CREATE TABLE IF NOT EXISTS `',$TableMLine[1]);
    for ($i = 0, $st_counter = 0; $i < $fields_amount;   $i++, $st_counter=0) 
    {
        while($row = $result->fetch_row())  
        {
            if ($st_counter%100 == 0 || $st_counter == 0 )  
            {
                $content .= "\nINSERT INTO ".$table." VALUES";
            }
            $content .= "\n(";    
            for($j=0; $j<$fields_amount; $j++)
            {
                $row[$j] = str_replace("\n","\\n", addslashes($row[$j]) ); 
                if (isset($row[$j]))
                {
                    $content .= '"'.$row[$j].'"' ;
                } 
                else
                {
                    $content .= '""';
                }
                if ($j<($fields_amount-1))
                {
                    $content.= ',';
                }  
            }
            $content .=")";
            if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num) 
            {
                $content .= ";";
            }
            else
            {
                $content .= ",";
            }
            $st_counter=$st_counter+1;
        }
    } $content .="\n\n\n";
}
$content .= "\r\n\r\n/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\r\n/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\r\n/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;";
$backup_name = $backup_name ? $backup_name : $name.'___('.date('H-i-s').'_'.date('d-m-Y').').sql';
ob_get_clean(); 
header('Content-Type: application/octet-stream'); 
header("Content-Transfer-Encoding: Binary"); 
header('Content-Length: '. (function_exists('mb_strlen') ? mb_strlen($content, '8bit'): strlen($content)) );   
header("Content-disposition: attachment; filename=\"".$backup_name."\""); 
echo $content; exit;
}
dbbackup("host", "user", "password", "database" );
?>

check on github

  • 0
Reply Report
      • 1
    • It really seems like people keep copying the same code on this page, leading to the same question: What is the point of the for loop using $i? The loop using $j is already looping over the fields.

Try out following example of using SELECT INTO OUTFILE query for creating table backup. This will only backup a particular table.

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';

   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $table_name = "employee";
   $backup_file  = "/tmp/employee.sql";
   $sql = "SELECT * INTO OUTFILE '$backup_file' FROM $table_name";

   mysql_select_db('test_db');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not take data backup: ' . mysql_error());
   }

   echo "Backedup  data successfully\n";

   mysql_close($conn);
?>
  • 0
Reply Report

A solution to take the backup of your Database in "dbBackup" Folder / Directory

<?php
error_reporting(E_ALL);

/* Define database parameters here */
define("DB_USER", 'root');
define("DB_PASSWORD", 'root');
define("DB_NAME", 'YOUR_DATABASE_NAME');
define("DB_HOST", 'localhost');
define("OUTPUT_DIR", 'dbBackup'); // Folder Path / Directory Name
define("TABLES", '*');

/* Instantiate Backup_Database and perform backup */
$backupDatabase = new Backup_Database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

$status = $backupDatabase->backupTables(TABLES, OUTPUT_DIR) ? 'OK' : 'KO';
echo "Backup result: " . $status;

/* The Backup_Database class */
class Backup_Database {

    private $conn;

    /* Constructor initializes database */
    function __construct( $host, $username, $passwd, $dbName, $charset = 'utf8' ) {
        $this->dbName = $dbName;
        $this->connectDatabase( $host, $username, $passwd, $charset );
    }


    protected function connectDatabase( $host, $username, $passwd, $charset ) {
        $this->conn = mysqli_connect( $host, $username, $passwd, $this->dbName);

        if (mysqli_connect_errno()) {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
            exit();
        }

        /* change character set to $charset Ex : "utf8" */
        if (!mysqli_set_charset($this->conn, $charset)) {
            printf("Error loading character set ".$charset.": %s\n", mysqli_error($this->conn));
            exit();
        }
    }


    /* Backup the whole database or just some tables Use '*' for whole database or 'table1 table2 table3...' @param string $tables  */
    public function backupTables($tables = '*', $outputDir = '.') {
        try {
            /* Tables to export  */
            if ($tables == '*') {
                $tables = array();
                $result = mysqli_query( $this->conn, 'SHOW TABLES' );

                while ( $row = mysqli_fetch_row($result) ) {
                    $tables[] = $row[0];
                }
            } else {
                $tables = is_array($tables) ? $tables : explode(',', $tables);
            }

            $sql = 'CREATE DATABASE IF NOT EXISTS ' . $this->dbName . ";\n\n";
            $sql .= 'USE ' . $this->dbName . ";\n\n";

            /* Iterate tables */
            foreach ($tables as $table) {
                echo "Backing up " . $table . " table...";

                $result = mysqli_query( $this->conn, 'SELECT * FROM ' . $table );

                // Return the number of fields in result set
                $numFields = mysqli_num_fields($result);

                $sql .= 'DROP TABLE IF EXISTS ' . $table . ';';
                $row2 = mysqli_fetch_row( mysqli_query( $this->conn, 'SHOW CREATE TABLE ' . $table ) );

                $sql.= "\n\n" . $row2[1] . ";\n\n";

                for ($i = 0; $i < $numFields; $i++) {

                    while ($row = mysqli_fetch_row($result)) {

                        $sql .= 'INSERT INTO ' . $table . ' VALUES(';

                        for ($j = 0; $j < $numFields; $j++) {
                            $row[$j] = addslashes($row[$j]);
                            // $row[$j] = ereg_replace("\n", "\\n", $row[$j]);
                            if (isset($row[$j])) {
                                $sql .= '"' . $row[$j] . '"';
                            } else {
                                $sql.= '""';
                            }
                            if ($j < ($numFields - 1)) {
                                $sql .= ',';
                            }
                        }

                        $sql.= ");\n";
                    }
                } // End :: for loop

                mysqli_free_result($result); // Free result set

                $sql.="\n\n\n";
                echo " OK <br/>" . "";
            }
        } catch (Exception $e) {
            var_dump($e->getMessage());
            return false;
        }

        return $this->saveFile($sql, $outputDir);
    }


    /* Save SQL to file @param string $sql */
    protected function saveFile(&$sql, $outputDir = '.') {
        if (!$sql)
            return false;

        try {
            $handle = fopen($outputDir . '/db-backup-' . $this->dbName . '-' . date("Ymd-His", time()) . '.sql', 'w+');
            fwrite($handle, $sql);
            fclose($handle);

            mysqli_close( $this->conn );
        } catch (Exception $e) {
            var_dump($e->getMessage());
            return false;
        }
        return true;
    }

} // End :: class Backup_Database

?>
  • 0
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions