MySQL Export Database | Dump Database

Today’s tutorial is about How to Backup Mysql Database Using PHP. Hello friends recently i was working over a website and after finishing the website i gave it to my client and he was working over it. One day suddenly there was some issue in the server and my client lost his all the data. He is not having any backup also. So the server disaster becomes dooms day for my client.

Same can happen to you in many ways like hacking, server destroy or you run some wrong query and Booooommmm you lost your data. What you will do now?

Today’s tutorial is for saving your life from these issues. This tutorial How to Backup Mysql Database Using PHP can save your data and never let you down and allow you to focus on your other work. In this tutorial we will show you how you can save your data daily and download it your local PC or any safe place. In this tutorial we will use two files and one database dump so you can check it over your PC.

Files we use.

• Index.php
• backupClass.php
• Database dump

For database dump just download the attached zip file you will find the .sql database dump file.




Backup Class

In this class we are using only one function which will take 6 parameters including the database credentials, table names and backup file name. Here is the magic code

<?php
class backupClass {
    public function EXPORT_TABLES($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";
        for ($i = 0, $st_counter = 0; $i < $fields_amount;   $i++, $st_counter=0) {
            while($row = $result->fetch_row()){ //when started (and every after 100 command cycle):
                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 .=")";
                //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
                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-disposition: attachment; filename=\"".$backup_name."\"");
    return $content; 
    exit;
}
}

Above is the only single function which will help you to download all the tables with the data. How you will use this function and class. For this we have added the code to the index.php file which will help you to backup mysql database using php.

Index File

only a very small code to use

<?php
include_once 'backupClass.php';
$backupClass=new backupClass();

$host="localhost";
$user="root";
$password ="";
$dbName="webcam";
$backupName="TestDatabase.sql";
$tables=false; // Backup whole database tables if want specific than comment this line and uncomment the next line
//$tables=array("snapshot");   //backup specific tables only: array("mytable1","mytable2",...)   

echo $backupClass->EXPORT_TABLES($host, $user, $pass, $dbName, $tables, $backupName);
?>

You will see that we have used the $table variable 2 times. If you uncomment the first line than whole database will be downloaded. If you uncomment the 2nd line than only given table names will be downloaded. 2nd Line accept only array.

Hope you like our tutorial about How to Backup Mysql Database Using PHP. Please comment below if you found any issue.




[wpfilebase tag=file id=10 /]