how to take backup of mysql database using php

Question

I need to export backup of the database using php when I click on the link. I searched so many references and created code. But when I execute this it displays error. Can anyone help me to get the solution? This is my code

<a href="back.php">BACKUP</a>

Back.php

<?php
include('../database.php');
   $dbhost = $_SERVER['SERVER_NAME'];
   $dbuser = 'root';
   $dbpass = '';
   $dbname='marketing';
   $backup_file = $dbname . date("Y-m-d-H-i-s") . '.gz';
   $command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass ". "$dbname | gzip > $backup_file";

   $sys=system($command);
if($sys)
{
    echo "succc";
}
else{
    echo "failed";
}


?>

Possible duplicate of [How to backup MySQL database in PHP?](https://stackoverflow.com/questions/2170182/how-to-backup-mysql-database-in-php) — E3Im, Sep 27 '18 at 06:52

score 3 · Accepted Answer · edited Sep 27 '18 at 11:01

You may use the following code pattern directly with your task

 <?php
    $dbhost = $_SERVER['SERVER_NAME'];
    $dbuser = 'root';
    $dbpass = '';
    $dbname = 'marketing';
    $connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
    $backupAlert = '';
    $tables = array();
    $result = mysqli_query($connection, "SHOW TABLES");
    if (!$result) {
        $backupAlert = 'Error found.<br/>ERROR : ' . mysqli_error($connection) . 'ERROR NO :' . mysqli_errno($connection);
    } else {
        while ($row = mysqli_fetch_row($result)) {
            $tables[] = $row[0];
        }
        mysqli_free_result($result);

        $return = '';
        foreach ($tables as $table) {

            $result = mysqli_query($connection, "SELECT * FROM " . $table);
            if (!$result) {
                $backupAlert = 'Error found.<br/>ERROR : ' . mysqli_error($connection) . 'ERROR NO :' . mysqli_errno($connection);
            } else {
                $num_fields = mysqli_num_fields($result);
                if (!$num_fields) {
                    $backupAlert = 'Error found.<br/>ERROR : ' . mysqli_error($connection) . 'ERROR NO :' . mysqli_errno($connection);
                } else {
                    $return .= 'DROP TABLE ' . $table . ';';
                    $row2 = mysqli_fetch_row(mysqli_query($connection, 'SHOW CREATE TABLE ' . $table));
                    if (!$row2) {
                        $backupAlert = 'Error found.<br/>ERROR : ' . mysqli_error($connection) . 'ERROR NO :' . mysqli_errno($connection);
                    } else {
                        $return .= "\n\n" . $row2[1] . ";\n\n";
                        for ($i = 0; $i < $num_fields; $i++) {
                            while ($row = mysqli_fetch_row($result)) {
                                $return .= 'INSERT INTO ' . $table . ' VALUES(';
                                for ($j = 0; $j < $num_fields; $j++) {
                                    $row[$j] = addslashes($row[$j]);
                                    if (isset($row[$j])) {
                                        $return .= '"' . $row[$j] . '"';
                                    } else {
                                        $return .= '""';
                                    }
                                    if ($j < $num_fields - 1) {
                                        $return .= ',';
                                    }
                                }
                                $return .= ");\n";
                            }
                        }
                        $return .= "\n\n\n";
                    }

                    $backup_file = $dbname . date("Y-m-d-H-i-s") . '.sql';
                    $handle = fopen("{$backup_file}", 'w+');
                    fwrite($handle, $return);
                    fclose($handle);
                    $backupAlert = 'Succesfully got the backup!';
                }
            }
        }
    }
    echo $backupAlert;
?>

Thanks for your reply..the code is working ...but there are some mistakes in the code. I found it and cleared it. The code is working now. and it will be more helpful if you give the explanation for the code — krishna, Sep 27 '18 at 10:26
Above script is very basic and simple script there have only query reading and writing method. 01) beginning of the script there is a connection variable to make a connection with database 02) through the variable got every table 03) using foreach loop split out the tables as seperate parts(single table can be gotten) 04) through the each split out table values we may easily split its all field(columns) values 05) and query are made for each tables dynamicly 06) created query is written into a file 07) file name and extentions are set on last lines of the script — Mohamed Faalil, Sep 27 '18 at 11:27
Could you tell me how can I save it on local machine and specify the folder like downloads — krishna, Sep 27 '18 at 12:02
Yah, It's very easy $handle = fopen("Downloads/{$backup_file}", 'w+'); You just insert the path before the $backup_file variable. for the example I have inserted the Downloads. Before inserting any folder name (path), please make sure which is exit or not. — Mohamed Faalil, Sep 27 '18 at 12:37
I added the folder name like this. But its not saving locally. it saves the file on the folder where code files are. I am asking is that how can I select the folder to save while its saving just like browser ask where to save while downloading — krishna, Sep 28 '18 at 06:03
Yes If you add just a folder name which is locating on your project existing place backup file will be gotten at the place. If you want to get the backup file into another place then you just give the path where you want get the backup file. such as following example : - $path = 'C:/Users/Mohamed Faalil -/Downloads/Documents'; $handle = fopen("{$path}/{$backup_file}", 'w+'); — Mohamed Faalil, Sep 28 '18 at 09:47
I needed to change isset to !is_null and put "$row[$j] = addslashes($row[$j]);" inside the if. — Guilherme Correa Teixeira, Feb 06 '20 at 04:09

score 2 · Answer 2 · answered Sep 27 '18 at 06:56

I use this Script as sheduled CRON task.

// Edit this section
$dbhost = "SERVER IP OR LOCALHOST";
$dbuser = "DB USER";
$dbpass = "DB PASSWORD";
$dbname = "DB NAME ";
$message = "E-MAIL MESSAGE TEXT";

// Don't need to edit below this section

function compress($filepath) {
    $zip = new ZipArchive();
    $file=$filepath.".zip";

    if($zip->open($file,1?ZIPARCHIVE::OVERWRITE:ZIPARCHIVE::CREATE)===TRUE) {
        // Add the files to the .zip file
        $zip->addFile($filepath);

        // Closing the zip file
        $zip->close();
    }
 }  

 ini_set('date.timezone', 'Europe/Budapest');
 $backupfile = $dbname.'_'.date("Y-m-d_H-i", time()).'.sql';
 system("mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname > $backupfile");
 compress($backupfile);

 // Send E-mail notification
 $sendto = "NAME <E-MAIL ADDRESS>";
 $sendfrom = "NAME <E-MAIL ADDRESS>";
 $sendsubject = "SUBJECT";
 // $message="This attachment contains the backup of your database.";
 $separator = md5(time());

 // attachment name
 $filename = $backupfile.".zip";

 // Open db file
 $file = fopen( $backupfile, "r" );
 // Read the file into a variable
 $size = filesize($backupfile);
 $content = fread( $file, $size);

 //$pdfdoc is PDF generated by FPDF
 $attachment = chunk_split(base64_encode(file_get_contents($filename)));

 // Define the main headers.
 $header = "From:$sendfrom\r\n";
 $header .= "MIME-Version: 1.0\r\n";
 $header .= "Content-Type: multipart/mixed; ";
 $header .= "boundary=$separator\r\n";
 $header .= "--$num\r\n";

 // Define the message section
 $header .= "Content-Type: text/plain\r\n";
 $header .= "Content-Transfer-Encoding:8bit\r\n\n";
 $header .= "$message\r\n";
 $header .= "--$separator\r\n";

 // Define the attachment section
 $header .= "Content-Type:  application/octet-stream; ";
 $header .= "name=\"$filename\"\r\n";
 $header .= "Content-Transfer-Encoding:base64\r\n";
 $header .= "Content-Disposition:attachment; ";
 $header .= "filename=\"$filename\"\r\n\n";
 $header .= "$attachment\r\n";
 $header .= "--$separator--";

 // Send email now
 mail( $sendto, $sendsubject, "", $header );

 // Delete the SQL and ZIP file from your server
 unlink($backupfile);
 unlink($filename);

 ?>

When I use this code the following errors occurs `Warning: fread(): Length parameter must be greater than 0 in on line 107 Warning: file_get_contents(marketing_2018-09-27_09-52.sql.zip): failed to open stream: No such file or directory in back.php on line 110 Notice: Undefined variable: num in on line 117 Warning: mail(): Multiple or malformed newlines found in additional_header in on line 135 Warning: unlink(marketing_2018-09-27_09-52.sql): Resource temporarily unavailable on line 138 Warning: unlink(marketing_2018-09-27_09-52.sql.zip): No such file or directory on line 139` — krishna, Sep 27 '18 at 07:58

bcperth · Answer 3 · 2018-09-27T10:19:12.033

If you add --verbose 2> output.txt to your command, it will spell out what is happening, line by line as below for example. Obviously you need to look at the contents of output.txt after the command.

-- Connecting to localhost...
-- Retrieving table structure for table users...
-- Sending SELECT query...
-- Retrieving rows...
-- Disconnecting from localhost...

Your full command would then be:

"mysqldump --opt -h $dbhost -u $dbuser -p $dbpass --verbose 2> output.txt". "$dbname | gzip > $backup_file"

Also it may not be correct to test $sys like you have done as an indicator of success, Instead use this form..

  system ( string $command, &$return_var);

and then $return will contain the return status of the executed command (mysqldump) which is better for testing actual success of the backup.

Your code might then look like:

int $return_var;
system ($command, &$return_var);

if ($return_var ==0){
    echo "success";
}
else{
    echo "failed";
}

also when I use system ( string $command, &$return_var); it displays nothing — krishna, Sep 27 '18 at 08:17
Ok I added some clarification. See if it works better now. Also check if "output.txt" is being created and look what it says. — bcperth, Sep 27 '18 at 10:20

score 0 · Answer 4 · answered May 27 '19 at 13:28

i changed a bit to support utf8

<title><?php echo "backup MySQL data - " . $_SERVER['SERVER_NAME'] ; ?></title>
<?php 

// ref. to https://stackoverflow.com/questions/52530833/how-to-take-backup-of-mysql-database-using-php
    $dbhost = 'localhost';
    $dbuser = 'root';
    $dbpass = '';
    $dbname = 'jackycms2019';
    $connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
    mysqli_set_charset($connection,"utf8");
    $backupAlert = '';
    $tables = array();
    $result = mysqli_query($connection, "SHOW TABLES");
    if (!$result) {
        $backupAlert = 'Error found.<br/>ERROR : ' . mysqli_error($connection) . 'ERROR NO :' . mysqli_errno($connection);
    } else {
        while ($row = mysqli_fetch_row($result)) {
            $tables[] = $row[0];
        }
        mysqli_free_result($result);

        $return = '';
        foreach ($tables as $table) {

            $result = mysqli_query($connection, "SELECT * FROM " . $table);
            if (!$result) {
                $backupAlert = 'Error found.<br/>ERROR : ' . mysqli_error($connection) . 'ERROR NO :' . mysqli_errno($connection);
            } else {
                $num_fields = mysqli_num_fields($result);
                if (!$num_fields) {
                    $backupAlert = 'Error found.<br/>ERROR : ' . mysqli_error($connection) . 'ERROR NO :' . mysqli_errno($connection);
                } else {
                    $return .= 'DROP TABLE ' . $table . ';';
                    $row2 = mysqli_fetch_row(mysqli_query($connection, 'SHOW CREATE TABLE ' . $table));
                    if (!$row2) {
                        $backupAlert = 'Error found.<br/>ERROR : ' . mysqli_error($connection) . 'ERROR NO :' . mysqli_errno($connection);
                    } else {
                        $return .= "\n\n" . $row2[1] . ";\n\n";
                        for ($i = 0; $i < $num_fields; $i++) {
                            while ($row = mysqli_fetch_row($result)) {
                                $return .= 'INSERT INTO ' . $table . ' VALUES(';
                                for ($j = 0; $j < $num_fields; $j++) {
                                    $row[$j] = addslashes($row[$j]);
                                    if (isset($row[$j])) {
                                        $return .= '"' . $row[$j] . '"';
                                    } else {
                                        $return .= '""';
                                    }
                                    if ($j < $num_fields - 1) {
                                        $return .= ',';
                                    }
                                }
                                $return .= ");\n";
                            }
                        }
                        $return .= "\n\n\n";
                    }

                    $backup_file = $dbname . '.sql';
                    $handle = fopen("{$backup_file}", 'w+');
                    fwrite($handle, $return);
                    fclose($handle);
                    $backupAlert = 'backup MySQL data completed !';
                }
            }
        }
    }
    echo $backupAlert;
?>

how to take backup of mysql database using php

4 Answers4