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.

Solution 1

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

Solution 2

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

Solution 3

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

Solution 4

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

Solution 5

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

View more solutions

Comments

[Daan]

I mentioned I don't have shell access and I do have phpMyAdmin. The customer wants to backup his database at his website.

[Daan]

I don't have shell access.

[user3004356]

@Daan You have to have shell access or PHPMyAdmin...Without both how will you do

[user3004356]

What will you do for a remote server

[Lord Zed]

You don't need one. just run this inside php file and see if it works. <?php shell_exec("mkdir test"); ?> 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.

[Blackbam]

Useful if shell exec is not allowed by the host, thanks.

[matinict]

you may add flowing code at top: //Set encoding mysql_query("SET CHARSET utf8"); mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'");

[redestructa]

You also have to add the ` backtick, in case there are names used like match or set (which are keywords in mysql)

[joshua pogi 28]

i get error (errno: 150)

[Raju Dudhrejiya]

Hi Joshua pogi, can you please show me your error with details so I can checkd.

[Raju Dudhrejiya]

Joshua pogi can you please give me your error text.

[Roxx]

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.

[Funk Forty Niner]

@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.

[Roxx]

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.

[Funk Forty Niner]

You're very much welcome @CalculatingMachine and thanks, cheers

[candlejack]

Where add the 5th parameter? Pass to the function? or can I define to the begin in the 8th line: $tables= "Your tables";?

[Raju Dudhrejiya]

@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.

[candlejack]

@RajuDudhrejiya got it! very useful!

[Divyesh Jesadiya]

@RajuDudhrejiya for multiple tables not all database. i got full database backup with name $name.".sql" not get my $backup_name name as define.

[Davinder Kumar]

How this works?

[Raju Dudhrejiya]

@DavinderKumar copy above code and run you local system with your database credential, let me know if you need help for the same

[Davinder Kumar]

@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.

[Raju Dudhrejiya]

@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)

[Davinder Kumar]

@RajuDudhrejiya You are taking it negatively, i just want you to elaborate the code to me if you can.

[user3526204]

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 ?

[Aztrozero]

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!

[Rauli Rajande]

@user3526204 file_put_contents($backup_name, $content); (instead of headers and echo)

[Itay Ganor]

This tool is great but keep in mind there is no support for special charsets as utf-8.

[Marc]

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.

[Mike Morris - MBXSW]

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.

[Aayush Dahal]

If I have to update server url to local url, how can I replace during this export?

[Raju Dudhrejiya]

@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

[ehsan mohajeri]

It's brilliant man! Thanks.

[Pjottur]

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?

[P.O.W.]

To make it work with my database credentials I modified this line: $command = "$mysqldump --opt -h\"$dbhost\" -u\"$dbuser\" -p\"$dbpass\" $dbname > $tempFile";

[Admin]

Excellent, but I had the NULL value problem too. Any way to adjust for this?

[Victor Marcoianu]

This won't work very well with large databases dues to memory limits.

[Raju Dudhrejiya]

@VictorMarcoianu Can you please share a screen-short of your error so I can help you

[Victor Marcoianu]

@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.