生成数据库备份时"PHP Fatal error: Allowed memory size of 134217728 bytes exhausted"

Question

当我尝试在文本文件中备份数据库时，我得到了子j。

function backup_tables($backup_filename, $tables = '*')
{
    $conf = new JConfig();
    $dbhost = $conf->host;
    $dbuser = $conf->user;
    $dbpassword = $conf->password;
    $dbname = $conf->db;
    $link = mysql_connect($dbhost, $dbuser, $dbpassword);
    mysql_select_db($dbname, $link) or die(mysql_error());
    $return = "drop database if exists `$dbname`;nncreate database `$dbname`;nnuse `$dbname`;nn";
    $return .= "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;nn";
    $return .= "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;nn";
    $return .= "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;nn";
    $return .= "/*!40101 SET NAMES utf8 */;nn";
    $handle = fopen($backup_filename, 'w+');
    fwrite($handle, $return); $return = "";
    // get all of the tables
    if ($tables == '*') {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while ($row = mysql_fetch_row($result)) {
            $tables[] = $row[0];
        }
    } else {
        $tables = is_array($tables) ? $tables : explode(',', $tables);
    }
    // cycle through
    foreach ($tables as $table) {
        $result = mysql_query('SELECT * FROM ' . $table);
        $num_fields = mysql_num_fields($result);
        $return .= 'DROP TABLE IF EXISTS `' . $table . '`;';
        $return .= "nn" . mysql_fetch_row(mysql_query('SHOW CREATE TABLE `' . $table . '`;'))[1] . " DEFAULT CHARSET=cp1251;nn";
        while ($row = mysql_fetch_row($result)) {
            $return .= 'INSERT INTO ' . $table . ' VALUES(';
            for ($i = 0; $i < $num_fields; $i++) {
                $row[$i] = str_replace("n", "\n", addslashes($row[$i]));
                $return .= '"' . (isset($row[$i])? $row[$i] : '') . '"';
                if ($num_fields - $i - 1) {
                    $return .= ',';
                }
            }
            $return .= ");n";
            fwrite($handle, $return); $return = "";
        }
        if($return) {
            fwrite($handle, $return);
            $return .= "nnn";
        }
    }
    fclose($handle);
}

此函数在某处存在内存泄漏之外运行良好。它创建一个文件~30 MiB并挂起并出现上述错误。在文件生成过程中，httpd 进程的内存使用量均匀增加。还有一个：世代挂在一张大桌子上（包含日志），但我认为这无关紧要，因为信息是逐行写入的。

Answer 1

还有一个：一代人挂在一张大桌子上（包含一根日志）， 但我认为这无关紧要，因为信息是逐行写的。

实际上这就是原因：我应该使用mysql_unbuffered_query而不是mysql_query。现在这个函数看起来像这样：

function backup_tables($backup_filename, $tables = '*')
{
    $conf = new JConfig();
    $dbhost = $conf->host;
    $dbuser = $conf->user;
    $dbpassword = $conf->password;
    $dbname = $conf->db;
    $link = mysql_connect($dbhost, $dbuser, $dbpassword);
    mysql_select_db($dbname, $link) or die(mysql_error());
    $return = "drop database if exists `$dbname`;nncreate database `$dbname`;nnuse `$dbname`;nn";
    $return .= "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;nn";
    $return .= "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;nn";
    $return .= "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;nn";
    $return .= "/*!40101 SET NAMES utf8 */;nn";
    $handle = fopen($backup_filename, 'w+');
    fwrite($handle, $return); $return = "";
    // get all of the tables
    if ($tables == '*') {
        $tables = array();
        $result = mysql_query("SHOW TABLES");
        while ($row = mysql_fetch_row($result)) {
            $tables[] = $row[0];
        }
    } else {
        $tables = is_array($tables) ? $tables : explode(',', $tables);
    }
    // cycle through
    foreach ($tables as $table) {
        $return .= "DROP TABLE IF EXISTS `$table`;";
        $return .= "nn" . mysql_fetch_row(mysql_query("SHOW CREATE TABLE `$table`;"))[1] . " DEFAULT CHARSET=cp1251;nn";
        $result = mysql_unbuffered_query("SELECT * FROM `$table`");
        $num_fields = mysql_num_fields($result);
        while ($row = mysql_fetch_row($result)) {
            $return .= "INSERT INTO `$table` VALUES(";
            for ($i = 0; $i < $num_fields; $i++) {
                $row[$i] = str_replace("n", "\n", addslashes($row[$i]));
                $return .= '"' . (isset($row[$i])? $row[$i] : '') . '"';
                if ($num_fields - $i - 1) {
                    $return .= ',';
                }
            }
            $return .= ");n";
            fwrite($handle, $return); $return = "";
        }
        if($return)
            fwrite($handle, $return);
        $return = "nnn";
    }
    fclose($handle);
}

Answer 2

这里的 PHP 答案是增加最大内存大小，如果不是同时增加最大执行时间。

除了重新创建 mysqldump 命令的练习之外，还有理由从 PHP 代码中执行此操作吗？

你最好使用 mysqldump 或类似 Holland http://hollandbackup.org/的东西来单独浏览和转储每个表。

Answer 3

当前答案使用已弃用的函数。执行此操作的新方法是使用 mysqli：：use_result。

就我而言，我在尝试将大型sql表写入文件中时遇到了耗尽内存错误。这是我如何使用它。

$conn = new mysqli("localhost", "my_user", "my_password", "my_db");
$sql = 'SELECT row1, row2 from table';
$fp = fopen('output.json', 'w');
if ($conn->multi_query($sql)) {
    do {
        if ($result = $conn->use_result()) {
            while ($row = $result->fetch_row()) {
              $row1 = $row[0];
              $row2 = $row[1];
              $item = array('row1'=>$row1, 'row2'=>$row2);  
              fwrite($fp, json_encode($item));
            }
            $result->close();
        }
    } while ($conn->more_results() && $conn->next_result());
}
fclose($fp);
$conn->close();