CODEWITHSUNDEEP
phpmysql
Roger
Roger

Reputation: 1693

MySQL data backup using PHP

I am here stuck with an problem and really need you people's help. I am trying to export mysql data using a PHP script. But the problem is an error which is below "Fatal error: Allowed memory size of 67108864 bytes exhausted....". I have seen some posts where they have suggested to change the php.ini file. But as I am hosted on a dedicated server I do not have that access. The table that I am trying to export has more the 2.2GB of data. I am here by posting the function that I am using to export those datas. Can you guys please help me in solving this issues?

<?php
set_time_limit(0);
backup_tables('hostname','username','password','databasename');

function backup_tables($host,$user,$pass,$name,$table = 'Table Name')
{

$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);

$result = mysql_query('SELECT * FROM '.$table);
$num_fields = mysql_num_fields($result);

$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
$return.= "\n\n".$row2[1].";\n\n";

for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysql_fetch_row($result))
{
$return.= 'INSERT INTO '.$table.' VALUES(';
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = ereg_replace("\n","\\n",$row[$j]);
if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
if ($j<($num_fields-1)) { $return.= ','; }
}
$return.= ");\n";
}
}
$return.="\n\n\n";
//save file
$handle = fopen('db-backup-'.time().'-'.($tables).'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}
?>

Upvotes: 2

Views: 3404

Answers (5)

Dmytro Suprunenko
Dmytro Suprunenko

Reputation: 46

hm.. i guess the best way to do this is using sql query "SELECT * INTO OUTFILE..." because php function "exec" can be forbidden by php.ini safe mode.

Upvotes: 0

symcbean
symcbean

Reputation: 48357

The table that I am trying to export has more the 2.2GB of data

And you're trying to load it all into a PHP array.

Even if you had enough memory, the performance would be appalling.

Write the rows fetched from the database as you read them...

while ($row = mysql_fetch_row($result)) {
   fputs($outfile, 'INSERT INTO '.$table.' VALUES(');
   ....
}

But a far better solution would be to use mysqldump.

update

I forgot to say that your method for escaping the output is wrong - use mysql_escape_string() not addslashes + ereg_replace() (and if you must use string relpacement functions, str_replace is much faster then [ep]reg_replace).

Upvotes: 1

TaZ
TaZ

Reputation: 743

Instead of collecting everything into (a huge) $return, move the fopen call to the start of the function and write every piece of result directly using fwrite instead of adding it to $return. The memory problem you're encountering is because $return takes up so much space.

Upvotes: 0

Aelios
Aelios

Reputation: 12137

Try to zip your backup sql statement with Zip->addFromString

http://php.net/manual/fr/book.zip.php

http://php.net/manual/fr/zip.examples.php

<?php

$zip = new ZipArchive();
$filename = "./backup.zip";

if ($zip->open($filename, ZIPARCHIVE::CREATE)!==TRUE) {
    exit("Could not open <$filename>\n");
}
$zip->addFromString("backup.txt", "sql statement");
$zip->close();
?>

Try to divide your backup into several zip file (for example : one table by zip)

Upvotes: 0

Fluffeh
Fluffeh

Reputation: 33502

I think that you are trying to remake a wheel here. Mysql has a fantastic mysqldump executable that will quickly, quietly and using few resources generate the file you are making. The best thing is that you can actually call it directly from within a php script using exec with something like this:

exec('mysqldump --user=... --password=... --host=... DB_NAME > /path/to/output/file.sql');

Upvotes: 5

Related Questions