仅使用 PHP 导出 MySQL 数据库

Export MySQL database using PHP only

提问by Daan

What would be a piece of PHP code for exporting a MySQL database to a .sqlfile?

将 MySQL 数据库导出到.sql文件的 PHP 代码是什么？

I don't have shell access so I can't use exec. I've found some codebut I looked through the code and this code is old and not well written. Most instructions I've found require shell access … the rest is outdated.

我没有 shell 访问权限，因此无法使用exec. 我找到了一些代码，但我查看了代码，发现这段代码很旧而且写得不好。我发现的大多数指令都需要 shell 访问……其余的已经过时了。

Thanks in advance.

提前致谢。

回答by Raju Dudhrejiya

Best way to export database using php script.

使用 php 脚本导出数据库的最佳方式。

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

或添加特定表的第 5 个参数（数组）：array("mytable1","mytable2","mytable3")对于多个表

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

回答by Funk Forty Niner

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 文件执行数据库备份查询。以下是使用 SELECT INTO OUTFILE 查询创建表备份的示例：

<?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:

要恢复备份，您只需要像这样运行 LOAD DATA INFILE 查询：

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

回答by Emil M

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

这个工具可能很有用，它是一个基于 PHP 的纯导出实用程序：https: //github.com/2createStudio/shuttle-export

回答by user3004356

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

在 *nix 系统中，使用 WHICH 命令显示 mysqldump 的位置，试试这个：

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

回答by Amir Iqbal

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

回答by Shobi

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

这是我的代码，这将备份 MySQL 数据库并将其存储在指定的路径中。

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

回答by Lord Zed

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

如果你没有phpMyAdmin，你可以在php CLI命令中编写例如登录到mysql并执行db dump。在这种情况下，您将使用shell_exec函数。

回答by Lord Zed

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

更新。修复了代码中的错误，在 $return .= 'INSERT INTO '.$table.'VALUES(';

回答by MStanley

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.

可以在https://github.com/tazotodua/useful-php-scripts找到用于导出和导入的整个项目。

回答by Mohammed Barham

You can use this command it works or me 100%

你可以使用这个命令它有效或我 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.exeis the path for mysqldump app , check on your pc.

注意：
C:\\wamp\\bin\\mysql\\mysql5.6.17\\bin\\mysqldump.exe是 mysqldump 应用程序的路径，请检查您的电脑。

-urootis -u{UserName}

-uroot是 -u{UserName}

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

如果您的数据库受密码保护，则在 -uroot 后添加此句子 -p{YourPassword}