Export MySQL database using PHP

Question

I've build a php/mysql (wamp) application and deployed on a local workstation. My customer wants to save db and restore it when he likes.

I've found this code for saving:

<?php


$DB_HOST = "localhost";
$DB_USER = "root";
$DB_PASS = "admin";
$DB_NAME = "dbname";

$con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

 
 $tables = array();

$result = mysqli_query($con,"SHOW TABLES");
while ($row = mysqli_fetch_row($result)) {
    $tables[] = $row[0];
}

$return = '';

foreach ($tables as $table) {
    $result = mysqli_query($con, "SELECT * FROM ".$table);
    $num_fields = mysqli_num_fields($result);

    $return .= 'DROP TABLE '.$table.';';
    $row2 = mysqli_fetch_row(mysqli_query($con, 'SHOW CREATE TABLE '.$table));
    $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";
    
}


$handle = fopen('backup.sql', 'w+');
fwrite($handle, $return);
fclose($handle);

echo "success";

?>

This code saves file in a default folder. What I need is to let user to decide where to save backup file or simply download it through browser. On the other hand user needs to restore from the file he wants so I need a 'browse' button to let him choose the file in any of his folder.

My database is utf8_general_ci and has english, french and italian language I don't need complex codes because I wouldn't know how to manage them :-(

Thanks in advance.

Yes I do but the customer wants to backup and restore his database at the website. — Daan, Mar 05 '14 at 10:48
something like: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html ? — Matthew Shine, Mar 05 '14 at 10:50

score 126 · Answer 1 · edited Sep 25 '15 at 01:16

126

Best way to export database using php script.

Or add 5th parameter(array) of specific tables: array("mytable1","mytable2","mytable3") for multiple tables

<?php 
    //ENTER THE RELEVANT INFO BELOW
    $mysqlUserName      = "Your Username";
    $mysqlPassword      = "Your Password";
    $mysqlHostName      = "Your Host";
    $DbName             = "Your Database Name here";
    $backup_name        = "mybackup.sql";
    $tables             = "Your tables";

   //or add 5th parameter(array) of specific tables:    array("mytable1","mytable2","mytable3") for multiple tables

    Export_Database($mysqlHostName,$mysqlUserName,$mysqlPassword,$DbName,  $tables=false, $backup_name=false );

    function Export_Database($host,$user,$pass,$name,  $tables=false, $backup_name=false )
    {
        $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); 
        }
        foreach($target_tables as $table)
        {
            $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        = (!isset($content) ?  '' : $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";
        }
        //$backup_name = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).".sql";
        $backup_name = $backup_name ? $backup_name : $name.".sql";
        header('Content-Type: application/octet-stream');   
        header("Content-Transfer-Encoding: Binary"); 
        header("Content-disposition: attachment; filename=\"".$backup_name."\"");  
        echo $content; exit;
    }
?>

edited Sep 25 '15 at 01:16

Protomen

9,471
9
57
124

answered Jul 21 '15 at 06:40

Raju Dudhrejiya

1,637
2
15
16

5

Useful if shell exec is not allowed by the host, thanks. – Blackbam Sep 14 '15 at 23:09
1

You also have to add the ` backtick, in case there are names used like match or set (which are keywords in mysql) – redestructa Nov 12 '15 at 13:13
i get error (errno: 150) – joshua pogi 28 Dec 07 '15 at 10:41
Hi Joshua pogi, can you please show me your error with details so I can checkd. – Raju Dudhrejiya Dec 09 '15 at 05:40
Joshua pogi can you please give me your error text. – Raju Dudhrejiya Apr 29 '16 at 09:22
Where add the 5th parameter? Pass to the function? or can I define to the begin in the 8th line: `$tables= "Your tables";`? – candlejack Oct 17 '16 at 13:10
2

@alessadro if you have only one table then add your table name on $tables = 'your_table_name' of if you want add multi table then pass array on $tables, like $tables = array('tbl_1','tbl_2','tbl_3'); on line no.8. no need to define any other parameter. – Raju Dudhrejiya Oct 19 '16 at 13:16
@RajuDudhrejiya got it! very useful! – candlejack Nov 05 '16 at 01:29
@RajuDudhrejiya for multiple tables not all database. i got full database backup with name $name.".sql" not get my $backup_name name as define. – Divyesh Jesadiya Nov 29 '16 at 13:32
1

How this works? – Davinder Kumar May 09 '17 at 11:02
@DavinderKumar copy above code and run you local system with your database credential, let me know if you need help for the same – Raju Dudhrejiya May 12 '17 at 07:07
1

@RajuDudhrejiya , Am not asking this, i have already used your solution. i am asking for the technical point how this working. What is logic. I review the code still lot of confusions. – Davinder Kumar May 12 '17 at 08:59
@DavinderKumar if still lot of confusions then use other solution, many solution are available in google, find best solution by your self without confusions. (Y) – Raju Dudhrejiya May 12 '17 at 10:50
@RajuDudhrejiya You are taking it negatively, i just want you to elaborate the code to me if you can. – Davinder Kumar May 18 '17 at 12:09
I know its quite late in the day, but can somebody tell me how to create a file in a local directory on server rather thane download the back DB back up ? – user3526204 May 22 '17 at 09:58
@user3526204 file_put_contents($backup_name, $content); (instead of headers and echo) – Rauli Rajande Sep 04 '17 at 16:15
1

One problem I've found with it, is that it doesn't retain data types. If I have a date column that allows NULL, it is exported as "" empty string, which when re-imported becomes 0000-00-00 rather than null. Yes, I could set a default value to counteract this but I don't have this issue with the exports produced by phpmyadmin. – Marc Feb 11 '19 at 19:20
If I have to update server url to local url, how can I replace during this export? – Aayush Dahal Aug 19 '19 at 09:28
@AayushDahal then you export first from the life after you need to replace your URL in any text editor in any Texteditor, I recommended Notepad++ let me know if you need more help – Raju Dudhrejiya Aug 23 '19 at 08:44
It's brilliant man! Thanks. – ehsan mohajeri Sep 04 '19 at 10:08
Great solution, thanks. But I'm not sure about the following line: ...for ($i = 0, $st_counter = 0; $i < $fieldCount; $i++, $st_counter = 0) {... What does it do? – Pjottur Dec 06 '19 at 11:34
Excellent, but I had the NULL value problem too. Any way to adjust for this? – Chico the Friendly Monkey Jun 05 '20 at 14:51
This won't work very well with large databases dues to memory limits. – Victor Marcoianu Jul 27 '21 at 13:18
@VictorMarcoianu Can you please share a screen-short of your error so I can help you – Raju Dudhrejiya Jul 28 '21 at 12:55
@RajuDudhrejiya There is not need for a screenshot, here's the error text: `Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 16384 bytes) in... ` Context: The database has around 300MB, PHP Memory limits are 512MB. I've also tried with 1024MB but to no effect. Thank you for taking the time. – Victor Marcoianu Jul 29 '21 at 13:12

score 22 · Answer 2 · answered Nov 15 '14 at 11:40

22

This tool might be useful, it's a pure PHP based export utility: https://github.com/2createStudio/shuttle-export

answered Nov 15 '14 at 11:40

Emil M

1,082
12
18

1

This solution works very well even if shell commands is disabled on server, i will be using this class into to my future projects, thank you so much for share! – Aztrozero Jul 12 '17 at 01:45
1

This tool is great but keep in mind there is no support for special charsets as `utf-8`. – Itay Ganor May 16 '18 at 09:45

score 21 · Answer 3 · edited Jul 04 '18 at 15:19

21

Try the following.

Execute a database backup query from PHP file. Below is an example of using SELECT INTO OUTFILE query for creating table backup:

<?php
$DB_HOST = "localhost";
$DB_USER = "xxx";
$DB_PASS = "xxx";
$DB_NAME = "xxx";

$con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($con->connect_errno > 0) {
  die('Connection failed [' . $con->connect_error . ']');
}

$tableName  = 'yourtable';
$backupFile = 'backup/yourtable.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysqli_query($con,$query);
?>

To restore the backup you just need to run LOAD DATA INFILE query like this:

<?php
$DB_HOST = "localhost";
$DB_USER = "xxx";
$DB_PASS = "xxx";
$DB_NAME = "xxx";

$con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($con->connect_errno > 0) {
  die('Connection failed [' . $con->connect_error . ']');
}

$tableName  = 'yourtable';
$backupFile = 'yourtable.sql';
$query      = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysqli_query($con,$query);
?>

edited Jul 04 '18 at 15:19

Barry

3,303
7
23
42

answered Mar 05 '14 at 14:48

Funk Forty Niner

74,450
15
68
141

1

Fred good answer. one question regarding taking backup. i can see i need to put table name manually is there any way i can automate that. i have more than 200 tables. Please advise. – Roxx Aug 20 '16 at 07:25
@CalculatingMachine You'd need some type of `foreach` loop. It's a good question which you should consider asking a question about. But do make a search first, there might be something already out there. I'd be glad to upvote it if you do; let me know if and when you do post a question. – Funk Forty Niner Aug 20 '16 at 13:03
Thanks for your comment Fred. Actually i searched very much and found some answers but those are incomplete. I know you from long time on SO and i know your answer are accurate. Voted up. and will keep in my mind regarding search and asking good question. Thanks again. – Roxx Aug 20 '16 at 16:55
You're very much welcome @CalculatingMachine and thanks, *cheers* – Funk Forty Niner Aug 20 '16 at 17:02
Caution with this method as it requires you have a FILE permission to run it. Bitnami Wordpress is an example of a hosting setup where this is not enabled by default. – Mike Morris - MBXSW May 11 '19 at 14:43

score 6 · Answer 4 · answered Mar 05 '14 at 10:53

6

In *nix systems, use the WHICH command to show the location of the mysqldump, try this :

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'test';
$mysqldump=exec('which mysqldump');


$command = "$mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname > $dbname.sql";

exec($command);
?>

answered Mar 05 '14 at 10:53

user3004356

870
4
16
49

1

I don't have shell access. – Daan Mar 05 '14 at 10:54
@Daan You have to have shell access or PHPMyAdmin...Without both how will you do – user3004356 Mar 05 '14 at 10:56
What will you do for a remote server – user3004356 Mar 05 '14 at 10:59
you may add flowing code at top: //Set encoding mysql_query("SET CHARSET utf8"); mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'"); – matinict Oct 20 '15 at 11:57
To make it work with my database credentials I modified this line: $command = "$mysqldump --opt -h\"$dbhost\" -u\"$dbuser\" -p\"$dbpass\" $dbname > $tempFile"; – P.O.W. Dec 28 '19 at 20:03

score 4 · Answer 5 · edited Feb 12 '18 at 12:56

Here is my code, This will backup MySQL database and store it in the specified path.

<?php
function backup_mysql_database($options){
$mtables = array(); $contents = "-- Database: `".$options['db_to_backup']."` --\n";

$mysqli = new mysqli($options['db_host'], $options['db_uname'], $options['db_password'], $options['db_to_backup']);
if ($mysqli->connect_error) {
    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

$results = $mysqli->query("SHOW TABLES");

while($row = $results->fetch_array()){
    if (!in_array($row[0], $options['db_exclude_tables'])){
        $mtables[] = $row[0];
    }
}

foreach($mtables as $table){
    $contents .= "-- Table `".$table."` --\n";

    $results = $mysqli->query("SHOW CREATE TABLE ".$table);
    while($row = $results->fetch_array()){
        $contents .= $row[1].";\n\n";
    }

    $results = $mysqli->query("SELECT * FROM ".$table);
    $row_count = $results->num_rows;
    $fields = $results->fetch_fields();
    $fields_count = count($fields);

    $insert_head = "INSERT INTO `".$table."` (";
    for($i=0; $i < $fields_count; $i++){
        $insert_head  .= "`".$fields[$i]->name."`";
            if($i < $fields_count-1){
                    $insert_head  .= ', ';
                }
    }
    $insert_head .=  ")";
    $insert_head .= " VALUES\n";        

    if($row_count>0){
        $r = 0;
        while($row = $results->fetch_array()){
            if(($r % 400)  == 0){
                $contents .= $insert_head;
            }
            $contents .= "(";
            for($i=0; $i < $fields_count; $i++){
                $row_content =  str_replace("\n","\\n",$mysqli->real_escape_string($row[$i]));

                switch($fields[$i]->type){
                    case 8: case 3:
                        $contents .=  $row_content;
                        break;
                    default:
                        $contents .= "'". $row_content ."'";
                }
                if($i < $fields_count-1){
                        $contents  .= ', ';
                    }
            }
            if(($r+1) == $row_count || ($r % 400) == 399){
                $contents .= ");\n\n";
            }else{
                $contents .= "),\n";
            }
            $r++;
        }
    }
}

if (!is_dir ( $options['db_backup_path'] )) {
        mkdir ( $options['db_backup_path'], 0777, true );
 }

$backup_file_name = $options['db_to_backup'] . " sql-backup- " . date( "d-m-Y--h-i-s").".sql";

$fp = fopen($options['db_backup_path'] . '/' . $backup_file_name ,'w+');
if (($result = fwrite($fp, $contents))) {
    echo "Backup file created '--$backup_file_name' ($result)"; 
}
fclose($fp);
return $backup_file_name;
}

$options = array(
    'db_host'=> 'localhost',  //mysql host
    'db_uname' => 'root',  //user
    'db_password' => '', //pass
    'db_to_backup' => 'attendance', //database name
    'db_backup_path' => '/htdocs', //where to backup
    'db_exclude_tables' => array() //tables to exclude
);
$backup_file_name=backup_mysql_database($options);

I think this is quite marvellous – anastymous May 04 '23 at 13:10 — anastymous, May 04 '23 at 13:10

score 3 · Answer 6 · answered Mar 04 '16 at 07:42

<?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);
?>

score 0 · Answer 7 · answered Mar 05 '14 at 10:51

0

If you dont have phpMyAdmin, you can write in php CLI commands such as login to mysql and perform db dump. In this case you would use shell_exec function.

answered Mar 05 '14 at 10:51

Lord Zed

750
7
28

I mentioned I don't have shell access and I do have phpMyAdmin. The customer wants to backup his database at his website. – Daan Mar 05 '14 at 10:52
You don't need one. just run this inside php file and see if it works. `` then check the root folder where the php file is and look for the test folder. the same way you can log into mysql with terminal sintax and extract the db to specific folder. I've done it before and the system I've tested it is Win7. – Lord Zed Mar 05 '14 at 15:35

score -1 · Answer 8 · edited Feb 23 '16 at 20:36

I would Suggest that you do the folllowing,

<?php

function EXPORT_TABLES($host, $user, $pass, $name, $tables = false, $backup_name = false)
{
    $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--Database: `" . $name . "`\r\n\r\n\r\n";
    foreach ($target_tables as $table)
    {
        $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') . ")__rand" . rand(1, 11111111) . ".sql";
    header('Content-Type: application/octet-stream');
    header("Content-Transfer-Encoding: Binary");
    header("Content-disposition: attachment; filename=\"" . $backup_name . "\"");
    echo $content;
    exit;
}
?>

The enitre project for export and import can be found at https://github.com/tazotodua/useful-php-scripts.

score -1 · Answer 9 · edited Oct 08 '19 at 14:25

I would Suggest that you do the folllowing,

<?php

$con = mysqli_connect('HostName', 'UserName', 'Password', 'DatabaseName');


$tables = array();

$result = mysqli_query($con,"SHOW TABLES");
while ($row = mysqli_fetch_row($result)) {
 $tables[] = $row[0];
}

$return = '';

foreach ($tables as $table) {
 $result = mysqli_query($con, "SELECT * FROM ".$table);
 $num_fields = mysqli_num_fields($result);

 $return .= 'DROP TABLE '.$table.';';
 $row2 = mysqli_fetch_row(mysqli_query($con, 'SHOW CREATE TABLE '.$table));
 $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";
 
}


$handle = fopen('backup.sql', 'w+');
fwrite($handle, $return);
fclose($handle);
echo "success";


?>

upd. fixed error in code, added space before VALUES in line $return .= 'INSERT INTO '.$table.'VALUES(';

Why are you dropping all tables??? – Liam Pillay Jun 29 '21 at 01:44 — Liam Pillay, Jun 29 '21 at 01:44

score -5 · Answer 10 · edited Mar 15 '15 at 20:15

-5

You can use this command it works or me 100%

exec('C:\\wamp\\bin\\mysql\\mysql5.6.17\\bin\\mysqldump.exe -uroot DatabaseName> c:\\database_backup.sql');

note:
C:\\wamp\\bin\\mysql\\mysql5.6.17\\bin\\mysqldump.exe is the path for mysqldump app , check on your pc.

-uroot is -u{UserName}

If your database is protected with password then add after -uroot this sentense -p{YourPassword}

edited Mar 15 '15 at 20:15

ProGM

6,949
4
33
52

answered Mar 15 '15 at 19:34

Mohammed Barham

1
1

This is rubbish – anastymous May 04 '23 at 13:11

Export MySQL database using PHP

10 Answers10

Linked

Related