无需mysqldump即可将大型数据库导出到文件，内存占用低 [英] Export large database to file without mysqldump, low memory footprint
问题描述
可能的重复:
内存使用情况从数据库导出到 php 中的 csv
我正在导出一个大型数据库，并想知道降低内存占用的最佳方法.
I am exporting a large database and wanted to know the best way to make it have a low memory footprint.
我意识到我必须在有时间限制并使用低内存的循环中执行此操作，一次提取 100 行并将信息保存到文件中，然后重定向以从它完成的位置开始一个新的循环上一个周期.
I realize that I must do this in cycles that have a time limit and use low memory, fetching say 100 rows at a time and saving the information to the file then redirect to start a new cycle starting from where it finished on the previous cycle.
我想知道将数据缓冲到文件并且不会耗尽内存的最佳方法是什么，目前脚本将所有数据作为字符串获取，然后在完成从数据库中获取所有行后保存到文件.有时它会耗尽内存，因此需要修复.
I am wondering whats the best way buffer the data to file and not run out of memory, at present the script gets all data as a string then saves to file when it has finished getting all rows from the database. Some times it runs out of memory, hence the need to fix.
我是否对从数据库获取的数据使用 fwrite() 而不是放入 var 或使用临时文件?如果我使用临时文件，我什么时候合并/重命名到备份文件中?
Do I use fwrite() on the data fetched from the database instead of putting into a var or use a temp file? If I use a temp file when do I merge/rename into the backup file?
基本上什么是脚本将数据库数据导出到文件而不会出现错误致命错误:PHP 允许内存大小耗尽"的最佳方法?
Basically what is the best way for the script to export the database data into a file without getting the error "Fatal Error: PHP Allowed Memory Size Exhausted"?
function backup_tables($host, $user, $pass, $db, $tables = '*')
{
set_time_limit(0);
$mysqli = new mysqli($host,$user,$pass, $db);
if ($mysqli->connect_errno)
{
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$return = '';
$return .= "--\n";
$return .= "-- Database: `$db`\n";
$return .= "--\n\n";
$return .= "-- --------------------------------------------------------\n\n";
$numtypes = array(
'tinyint',
'smallint',
'mediumint',
'int',
'bigint',
'float',
'double',
'decimal',
'real'
);
// get all of the tables
if ($tables == '*')
{
$tables = array();
$result = $mysqli->query('SHOW TABLES');
while ($row = $result->fetch_row())
{
$tables[] = $row[0];
}
$result->close();
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
for ($z = 0; $z == 0; $z++)
{
echo $z.'<br>';
// cycle through tables
foreach ($tables as $table)
{
//
$typesarr = array();
$result = $mysqli->query("SHOW COLUMNS FROM `".$table."`");
while ($row = $result->fetch_assoc())
{
$typesarr[] = $row;
}
$result->close();
#echo '<h2>'.$table.'</h2>';
#print("<pre>" . print_r($typesarr, true). "</pre>");
// table structure dump
$return .= "--\n";
$return .= "-- Table structure for table `$table`\n";
$return .= "--\n\n";
$return.= 'DROP TABLE IF EXISTS `'.$table.'`;'."\n\n";
$result = $mysqli->query("SHOW CREATE TABLE `".$table."`");
$row = $result->fetch_array();
$return.= $row[1].";\n\n";
$result->close();
// table data dump
$return .= "--\n";
$return .= "-- Dumping data for table `$table`\n";
$return .= "--\n\n";
$result = $mysqli->query("SELECT * FROM `".$table."`");
$num_fields = $result->field_count;
if ($result->num_rows > 0)
{
// put field names in array and into sql insert for dump
$fields_str = '';
$fields = array();
$finfo = $result->fetch_fields();
foreach ($finfo as $val)
{
$fields_str .= '`'.$val->name.'`, ';
$fields[] = $val->name;
}
$fields_str = '('.rtrim($fields_str, ', ').')';
$return.= 'INSERT INTO `'.$table.'` '.$fields_str.' VALUES'."\n";
// cycle through fields and check if int for later use
for ($i = 0; $i < $num_fields; $i++)
{
// strip brackets from type
$acttype = trim(preg_replace('/\s*\([^)]*\)/', '', $typesarr[$i]['Type']));
$acttype = explode(' ', $acttype);
// build array, is field int or not
if (is_numeric(array_search($acttype[0], $numtypes)))
{
$numflag[$i] = 1;
}
else
{
$numflag[$i] = 0;
}
}
}
$x = 0;
$num_rows = $result->num_rows;
// cycle through table rows
while($row = $result->fetch_row())
{
$x++;
// cycle through rows fields
for($j=0; $j<$num_fields; $j++)
{
if (isset($row[$j]) and $j === 0) { $return .= '('; }
// field data has value or not NULL
if (isset($row[$j]))
{
// field data dump (INT)
if ($numflag[$j]==1)
{
#echo '(INT) '. $fields[$j].' = '.$row[$j].'<br>';
$return.= $mysqli->real_escape_string($row[$j]);
}
else
{
// field data dump values (empty string, NULL and INT)
$return.= "'".$mysqli->real_escape_string($row[$j])."'";
#echo $fields[$j]." = '".$mysqli->real_escape_string($row[$j])."'<br>";
}
}
else
{
// field data dump (NULL)
if (is_null($row[$j]))
{
$row[$j] = 'NULL';
#echo '(NULL) '. $fields[$j].' = '.$row[$j].'<br>';
$return.= $row[$j];
}
else
{
// field data dump (empty string)
$return.= "''";
}
}
if ($j<($num_fields-1)) { $return.= ', '; }
}
if ($x<$num_rows) { $return.= "),\n"; } else { $return .= ");\n"; }
#echo '<br>';
}
#echo 'Rows: '.$rows.'<br>';
#echo 'Iterations: '.$x.'<br>';
$return.="\n-- --------------------------------------------------------\n\n";
}
}
$result->close();
//save file
$handle = fopen('/db-backup-'.time().'.sql','a');
fwrite($handle,$return);
fclose($handle);
}
欢迎举例
推荐答案
将 MYSQLI_USE_RESULT 作为 resultmode 参数添加到 mysqli::query 调用以一次遍历结果一行，而不将它们全部转移到 PHP 中.另请参阅 mysqli::use-result.
Add MYSQLI_USE_RESULT as the resultmode parameter to the mysqli::query call in order to iterate over the results one row at a time, without transferring them all to PHP in one bunch. See also the documentation for mysqli::use-result.
将每一行直接写入输出文件，避免使用 $result 变量.结合上面的内容，这会导致每行都从服务器获取并写入文件，因此 PHP 不必一次存储多于一行.
Write each line directly to the output file, avoiding the $result variable. Combined with the above, this can lead to each row being fetched from the server and written to file, so PHP won't have to store more than one row at a time.
这篇关于无需mysqldump即可将大型数据库导出到文件，内存占用低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
