使用 PHP 导出 MySQL 数据库 [英] Export MySQL database using PHP

问题描述

我已经构建了一个 php/mysql (wamp) 应用程序并部署在本地工作站上.我的客户想要保存数据库并在他喜欢时恢复它.

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.

我找到了保存代码:

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

我的数据库是 utf8_general_ci 并且有英语、法语和意大利语我不需要复杂的代码，因为我不知道如何管理它们:-(

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

提前致谢.

推荐答案

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

Best way to export database using php script.

或添加特定表的第 5 个参数(数组):array("mytable1","mytable2","mytable3") 用于多个表

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

这篇关于使用 PHP 导出 MySQL 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！