Reading Time: 5 minutes

5,117 Views

Inside this article we will see how to backup mysql database using PHP. To create database backup we use many process like downloading database backup files from mysql workbench, taking backup via mysqldump, using terminal command, etc.

Here we will use mysqldump command to take database backup but we will use shell_exec() PHP function. The shell_exec() function is an inbuilt function in PHP which is used to execute the commands via shell and return the complete output.

Learn More –

Let’s get started.

Create an Application

Create a folder data-backup in your localhost directory. Make sure this folder should have sufficient permissions to read and write operations.

Method #1 – Using MySqldump

Inside this folder, create a file called index.php.

<?php 

$db_user = "root";
$db_pwd = "root";
$db_name = "php_shop";
$bkp_file_path = "/var/www/html/data.sql";

// create backup
shell_exec("mysqldump -u {$db_user} -p{$db_pwd} {$db_name} > {$bkp_file_path}");

MySql command to take backup (Syntax).

mysqldump -u [username] -p[password] [database_name] > [backup_file_name].sql

Command is for taking backup of database php_shop and backup will be saved into location /var/www/html/ and file name is data.sql.

It will backup entire database data with table structure.

Backup database table structure only –

Use the --no-data option.

mysqldump -u [username] -p[password] --no-data [database_name] > [backup_file_name].sql

Backup database table data only –

Use the --no-create-info option, or its shorthand -t.

mysqldump -u [username] -p[password] --no-create-info [database_name] > [backup_file_name].sql

Backup database few table –

mysqldump -u [username] -p[password] [database_name] table1 table2 table3 > [backup_file_name].sql

Method #2 – Using Queries

<?php
 
    $dbHost = 'localhost';
    $dbUsername = 'root';
    $dbPassword = 'root';
    $dbName = 'php_shop';
    $tables = '*';

    $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 

    //get all of the tables
    if($tables == '*'){
        $tables = array();
        $result = $db->query("SHOW TABLES");
        while($row = $result->fetch_row()){
            $tables[] = $row[0];
        }
    }else{
        $tables = is_array($tables)?$tables:explode(',',$tables);
    }

    foreach($tables as $table){
        $result = $db->query("SELECT * FROM $table");
        $numColumns = $result->field_count;

        $return .= "DROP TABLE $table;";

        $result2 = $db->query("SHOW CREATE TABLE $table");
        $row2 = $result2->fetch_row();

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

        for($i = 0; $i < $numColumns; $i++){
            while($row = $result->fetch_row()){
                $return .= "INSERT INTO $table VALUES(";
                for($j=0; $j < $numColumns; $j++){
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = preg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])){ 
                        $return .= '"'.$row[$j].'"' ; 
                    } else {
                        $return .= '""'; 
                    }
                    if ($j < ($numColumns-1)) {
                        $return.= ','; 
                    }
                }
                $return .= ");\n";
            }
        }

        $return .= "\n\n\n";
    }

    //save db file in same folder
    $handle = fopen('db-backup-'.time().'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);

It fetch all tables from database. It will read table structure and fetch data from it.

Application Testing

Open browser and hit this URL: http://localhost/data-backup/index.php

When we execute this program it will create mysql database backup to given path.

We hope this article helped you to learn How to Backup MySQL Database using PHP Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.