55

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.

Improve this question
3
  • 3
    Yes I do but the customer wants to backup and restore his database at the website. Commented Mar 5, 2014 at 10:48
  • 1
    something like: dev.mysql.com/doc/refman/5.1/en/mysqldump.html ? Commented Mar 5, 2014 at 10:50
  • 3
    @MatthewShine he mentioned he does not have shell access Commented Mar 5, 2014 at 10:51

10 Answers 10

Reset to default
128

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;
    }
?>
Improve this answer
Sign up to request clarification or add additional context in comments.

24 Comments

Useful if shell exec is not allowed by the host, thanks.
You also have to add the ` backtick, in case there are names used like match or set (which are keywords in mysql)
@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.
How this works?
@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.
|
22

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

Improve this answer

2 Comments

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!
This tool is great but keep in mind there is no support for special charsets as utf-8.
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);
?>
Improve this answer

5 Comments

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.
@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.
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.
You're very much welcome @CalculatingMachine and thanks, cheers
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.
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);
?>
Improve this answer

5 Comments

I don't have shell access.
@Daan You have to have shell access or PHPMyAdmin...Without both how will you do
What will you do for a remote server
you may add flowing code at top: //Set encoding mysql_query("SET CHARSET utf8"); mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'");
To make it work with my database credentials I modified this line: $command = "$mysqldump --opt -h\"$dbhost\" -u\"$dbuser\" -p\"$dbpass\" $dbname > $tempFile";
4

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);
Improve this answer

1 Comment

I think this is quite marvellous
3 
<?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);
?>
Improve this answer

Comments

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.

Improve this answer

2 Comments

I mentioned I don't have shell access and I do have phpMyAdmin. The customer wants to backup his database at his website.
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.
-1

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.

Improve this answer

Comments

-1

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(';

Improve this answer

1 Comment

Why are you dropping all tables???
-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}

Improve this answer

1 Comment

This is rubbish

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.