Inspiring

Question

Programmatic backup of MySQL data?

Forum|Forum|18 years ago
March 16, 2007
19 replies
2175 views

How can I do this with PHP?

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

Server side applications

This topic has been closed for replies.

N

Newsgroup_UserAuthor

Inspiring

I'm not out of the woods yet. Here's my code -

$query = mysql_query("select * from $table");
$num_fields = @mysql_num_fields($query);
$numrow = mysql_num_rows($query);

for ($i =0; $i<$numrow; $i++) {
$result .= "INSERT INTO ".$table." VALUES(";
for($j=0; $j<$num_fields; $j++) {
$row[$j] = mysql_fetch_assoc($query);
//$row[$j] = addslashes($row[$j]);

for ($x = 0; $x < count($row[$j]); $x++) {
$temp = str_replace("\n", "\\n", $row[$j][$x]);
$row[$j][$x] = $temp; }

if (isset($row[$j])) $result .= "\"$row[$j]\"" ; else $result .=
"\"\"";
if ($j<($num_fields-1)) $result .= ",";
}
$result .= ");\n";
}
return $result . "\n\n\n";
}

It's a runaway loop. Can you please take a look and see why that might be
happening?

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

"David Powers" <david@example.com> wrote in message
news:eth58o$3kh$1@forums.macromedia.com...
> Murray *ACE* wrote:
>> Sorry, David - I'm not sure I know what the antecedent of 'it' is. Do
>> you mean this?
>
> The line that I singled out at the top of my response:
>
> $row[$j] = ereg_replace("\n","\\n",$row[$j]);
>
> You cannot use ereg_replace() on an array. Moreover, you don't need a
> regular expression, which is why I used str_replace() instead.
>
> I haven't read through the entire script, but that was what was triggering
> your error.
>
> --
> David Powers, Adobe Community Expert
> Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
> Author, "PHP Solutions" (friends of ED)
> http://foundationphp.com/

N

Newsgroup_UserAuthor

Inspiring

Murray *ACE* wrote:
> Sorry, David - I'm not sure I know what the antecedent of 'it' is. Do you
> mean this?

The line that I singled out at the top of my response:

$row[$j] = ereg_replace("\n","\\n",$row[$j]);

You cannot use ereg_replace() on an array. Moreover, you don't need a
regular expression, which is why I used str_replace() instead.

I haven't read through the entire script, but that was what was
triggering your error.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

N

Newsgroup_UserAuthor

Inspiring

On Mon, 19 Mar 2007 22:30:13 +0100, Paul Westrich <nomadaNOSPAM@gmx.net>
wrote:

>Thanks for your advice. I additionally made a backup using
>phpMyAdmin but in my case there was not problem. My database
>is still rather small but I would be interested in learning
>how many records (or tables) a database may have before your
>script runs into problems. Any ideas?

The only problem I've run into so far was testing on a database with 18
tables containing 11,932 records totaling about 2.4 Mb it died with a
max execution time error.

Gary

N

Newsgroup_UserAuthor

Inspiring

> Replace it with this:

Sorry, David - I'm not sure I know what the antecedent of 'it' is. Do you
mean this?

Should I adjust $row($j) somehow to account for the appearance of the second
dimension?

for ($i =0; $i<$numrow; $i++) {
$result .= "INSERT INTO ".$table." VALUES(";
for($j=0; $j<$num_fields; $j++) {

// I added this next line since $row[$j] was never defined

$row[$j] = mysql_fetch_assoc($query);

//
$row[$j] = addslashes($row[$j]);
for ($x = 0; $x < count($row[$j]; $x++) {
$temp = str_replace("\n", "\\n", $row[$j][$x]);
$row[$j][$x] = $temp;
}
if (isset($row[$j])) $result .= "\"$row[$j]\"" ; else $result .=
"\"\"";
if ($j<($num_fields-1)) $result .= ",";
}
$result .= ");\n";
}
return $result . "\n\n\n";
}

$table1 = datadump ("tbl_images");
//$table2 = datadump ("table2");
//$table3 = datadump ("table3");

$content = $table1;

$file_name = "backup/MySQL_Database_Backup_".date("Y-m-d").".sql";
Header("Content-type: application/octet-stream");
Header("Content-Disposition: attachment; filename=$file_name");
echo $content;
exit;

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

"David Powers" <david@example.com> wrote in message
news:eth2bp$a1$1@forums.macromedia.com...
> Murray *ACE* wrote:
>> $row[$j] = ereg_replace("\n","\\n",$row[$j]);
>>
>> But this is throwing errors too (array to string conversion in line 24
>> (ereg_replace). I know I made a silly mistake, but what?
>
> $row is an array of fields. You cannot pass an array to ereg_replace().
>
> Replace it with this:
>
> for ($x = 0; $x < count($row[$j]; $x++) {
> $temp = str_replace("\n", "\\n", $row[$j][$x]);
> $row[$j][$x] = $temp;
> }
>
> --
> David Powers, Adobe Community Expert
> Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
> Author, "PHP Solutions" (friends of ED)
> http://foundationphp.com/

N

Newsgroup_UserAuthor

Inspiring

Murray *ACE* wrote:
> $row[$j] = ereg_replace("\n","\\n",$row[$j]);
>
> But this is throwing errors too (array to string conversion in line 24
> (ereg_replace). I know I made a silly mistake, but what?

$row is an array of fields. You cannot pass an array to ereg_replace().

Replace it with this:

for ($x = 0; $x < count($row[$j]; $x++) {
$temp = str_replace("\n", "\\n", $row[$j][$x]);
$row[$j][$x] = $temp;
}

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

N

Newsgroup_UserAuthor

Inspiring

On Mon, 19 Mar 2007 07:21:40 -0400, "Murray *ACE*"
<forums@HAHAgreat-web-sights.com> wrote:

>That's just perfect - thank you so much! A few tweaks, and it worked
>fine....

Glad to hear it. You're welcome.

Gary

N

Newsgroup_UserAuthor

Inspiring

In the second link that Steve posted, there is this function -

function datadump ($table) {

$result = "# Dump of $table \n";
$result .= "# Dump DATE : " . date("d-M-Y") ."\n\n";

$query = mysql_query("select * from $table");
$num_fields = @mysql_num_fields($query);
$numrow = mysql_num_rows($query);

for ($i =0; $i<$numrow; $i++) {
$result .= "INSERT INTO ".$table." VALUES(";
for($j=0; $j<$num_fields; $j++) {
// I added this next line since $row[$j] was never defined
$row[$j] = mysql_fetch_assoc($query);
//
$row[$j] = addslashes($row[$j]);
$row[$j] = ereg_replace("\n","\\n",$row[$j]);
if (isset($row[$j])) $result .= "\"$row[$j]\"" ; else $result .=
"\"\"";
if ($j<($num_fields-1)) $result .= ",";
}
$result .= ");\n";
}
return $result . "\n\n\n";
}

$table1 = datadump ("tbl_images");
//$table2 = datadump ("table2");
//$table3 = datadump ("table3");

$content = $table1;

$file_name = "backup/MySQL_Database_Backup_".date("Y-m-d").".sql";
Header("Content-type: application/octet-stream");
Header("Content-Disposition: attachment; filename=$file_name");
echo $content;
exit;

But this is throwing errors too (array to string conversion in line 24
(ereg_replace). I know I made a silly mistake, but what?

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

"Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
news:etglog$erf$1@forums.macromedia.com...
> Yes, in the warm shower, this occurred to me. So, I'll go for the
> solution that uses PHP instead of a SQL function, then....
>
> --
> Murray --- ICQ 71997575
> Adobe Community Expert
> (If you *MUST* email me, don't LAUGH when you do so!)
> ==================
> http://www.dreamweavermx-templates.com - Template Triage!
> http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
> http://www.dwfaq.com - DW FAQs, Tutorials & Resources
> http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
> ==================
>
>
> "David Powers" <david@example.com> wrote in message
> news:etfk5g$6bn$1@forums.macromedia.com...
>> Murray *ACE* wrote:
>>> OK - I'm still getting the error. Host tells me that the folder is wide
>>> open. Host also wonders about one's ability to write from a SQL server
>>> to a local file (it's windows, in case you were wondering).
>>>
>>> Here's the error -
>>> Access denied for user 'wildimages'@'%' (using password: YES)
>>
>> That's got nothing to do with write access, but user account access to
>> MySQL. You're trying to access from a different server, which isn't
>> allowed by the settings of the user account.
>>
>> --
>> David Powers, Adobe Community Expert
>> Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
>> Author, "PHP Solutions" (friends of ED)
>> http://foundationphp.com/
>
>

N

Newsgroup_UserAuthor

Inspiring

On Sun, 18 Mar 2007 23:40:29 -0500, Gary White
<reply@newsgroup.please> wrote:

>Okay. I played a bit more and found a couple of small possible issues.
>This will save it as a file on the server and in the same directory
>where the PHP file is running:

Nice work, Gary. I have nicked it for my own use! Thanks!
--
Steve Fleischer
steve at flyingtigerwebdesign dot com
Hong Kong

N

Newsgroup_UserAuthor

Inspiring

Yes, in the warm shower, this occurred to me. So, I'll go for the solution
that uses PHP instead of a SQL function, then....

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

"David Powers" <david@example.com> wrote in message
news:etfk5g$6bn$1@forums.macromedia.com...
> Murray *ACE* wrote:
>> OK - I'm still getting the error. Host tells me that the folder is wide
>> open. Host also wonders about one's ability to write from a SQL server
>> to a local file (it's windows, in case you were wondering).
>>
>> Here's the error -
>> Access denied for user 'wildimages'@'%' (using password: YES)
>
> That's got nothing to do with write access, but user account access to
> MySQL. You're trying to access from a different server, which isn't
> allowed by the settings of the user account.
>
> --
> David Powers, Adobe Community Expert
> Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
> Author, "PHP Solutions" (friends of ED)
> http://foundationphp.com/

N

Newsgroup_UserAuthor

Inspiring

On Sun, 18 Mar 2007 13:31:14 -0400, "Murray *ACE*"
<forums@HAHAgreat-web-sights.com> wrote:

>Yes, thanks, Gary. I'll give that a try - although I will say that I was
>slowly getting there. 8)

Okay. I played a bit more and found a couple of small possible issues.
This will save it as a file on the server and in the same directory
where the PHP file is running:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
" http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Database Backup</title>
</head>
<body>
<h1>Database Backup</h1>
<p>Please wait ...</p>
<?php

// configure these next four lines
$host='your host name';
$user='your user name';
$pass='your user password';
$db='the database to back up';
// done configuration

$connection=mysql_connect($host,$user,$pass)
or die(mysql_error());
mysql_select_db($db)
or die(mysql_error());

$tbls=mysql_query("SHOW TABLES")
or die(mysql_error());
$outfile='dbbackup-'.date('Y-m-d-His').'.sql';
$fp=fopen($outfile,'w')
or die('Unable to open output file.');
fwrite($fp,"# Database backup of $db\n# ".date('m/d/Y H:i:s')."\n\n");
while($tblrow=mysql_fetch_row($tbls)){
$table=$tblrow[0];
$query="SELECT * FROM `$table`";
$result=mysql_query($query)
or die(mysql_error());
if(mysql_num_rows($result)){
$f=mysql_num_fields($result);
$names=array();
$types=array();
for($i=0;$i<$f;$i++){
$field=mysql_fetch_field($result,$i);
$names[]=$field->name;
$types[]=$field->type;
}
$row_str='';
$rows=array();
while($row=mysql_fetch_row($result)){
for($i=0;$i<$f;$i++){
$row[$i]=mysql_real_escape_string($row[$i]);
if(strpos("|string|blob|date|datetime|timestamp|",$types[$i]))
$row[$i]="'$row[$i]'";
}
$rows[]="(".join(",",$row).")";
}
$rowstr=join(",\n",$rows);
$fields="`".join("`,`",$names)."`";
fwrite($fp,"# Empty table $table\nDELETE FROM `$table`;\n");
fwrite($fp,"\n# Insert values into $table\n");
fwrite($fp,"INSERT INTO `$table` ($fields)\nVALUES $rowstr;\n\n");
}
}
fclose($fp);
print "<p>Done!</p>\n<p><a href=\"$outfile\">View the File
$outfile</a></p>\n";
?>
</body>
</html>

Gary

N

Newsgroup_UserAuthor

Inspiring

Murray *ACE* wrote:
> OK - I'm still getting the error. Host tells me that the folder is wide
> open. Host also wonders about one's ability to write from a SQL server to a
> local file (it's windows, in case you were wondering).
>
> Here's the error -
> Access denied for user 'wildimages'@'%' (using password: YES)

That's got nothing to do with write access, but user account access to
MySQL. You're trying to access from a different server, which isn't
allowed by the settings of the user account.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

N

Newsgroup_UserAuthor

Inspiring

OK - I'm still getting the error. Host tells me that the folder is wide
open. Host also wonders about one's ability to write from a SQL server to a
local file (it's windows, in case you were wondering).

Here's the error -
Access denied for user 'wildimages'@'%' (using password: YES)

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

"Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
news:etelo4$t5t$1@forums.macromedia.com...
>I like cute. 8)
>
> The full code block now is -
>
> <?php require_once('../Connections/wildimages.php'); ?>
> <?php
>
> mysql_select_db($database_wildimages, $wildimages);
>
> $tableName = 'tbl_images';
> $backupFile = 'backup/images'.date("y-m-d").'.sql';
> $query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
> //echo $query;
>
> $result = mysql_query($query) or die(mysql_error());
>
> ?>
>
> In this case, however, cute has failed me and I can see that the error is
> a permissions one. Sigh....
>
> --
> Murray --- ICQ 71997575
> Adobe Community Expert
> (If you *MUST* email me, don't LAUGH when you do so!)
> ==================
> http://www.dreamweavermx-templates.com - Template Triage!
> http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
> http://www.dwfaq.com - DW FAQs, Tutorials & Resources
> http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
> ==================
>
>
> "David Powers" <david@example.com> wrote in message
> news:etel00$rsp$3@forums.macromedia.com...
>> Murray *ACE* wrote:
>>> What did I forget?
>>
>> A connection to MySQL?
>>
>> Although or die('Oops'); is cute, or die(mysql_error()); would tell what
>> the problem really is.
>>
>> --
>> David Powers, Adobe Community Expert
>> Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
>> Author, "PHP Solutions" (friends of ED)
>> http://foundationphp.com/
>
>

N

Newsgroup_UserAuthor

Inspiring

On Sun, 18 Mar 2007 09:56:18 -0400, "Murray *ACE*"
<forums@HAHAgreat-web-sights.com> wrote:

>I'm currently grieving! 8)
>
>Don't think you've seen the last of me....

Murr, you still having problems with this? This isn't thoroughly tested,
but you might give it a shot. As it is, it just displays in the browser.
I'm not sure of where you want to save it. Put it in a completely empty
page, no <html>, <head> or <body> tags and nothing above the opening
<?php line:

<?php
header("Content-type: text/plain");

$host='host name';
$user='user name';
$pass='password';
$db='database name';

$connection=mysql_connect($host,$user,$pass)
or die(mysql_error());
mysql_select_db($db)
or die(mysql_error());

$tbls=mysql_query("SHOW TABLES")
or die(mysql_error());

while($tblrow=mysql_fetch_row($tbls)){
$table=$tblrow[0];
$query="SELECT * FROM $table";
$result=mysql_query($query)
or die(mysql_error());
$f=mysql_num_fields($result);
$names=array();
$types=array();
for($i=0;$i<$f;$i++){
$field=mysql_fetch_field($result,$i);
$names[]=$field->name;
$types[]=$field->type;
}
$row_str='';
$rows=array();
while($row=mysql_fetch_row($result)){
for($i=0;$i<$f;$i++){
$row[$i]=mysql_real_escape_string($row[$i]);
if($types[$i]=="string")
$row[$i]="'$row[$i]'";
}
$rows[]="(".join(",",$row).")";
}
$rowstr=join(",\n",$rows);
$fields=join(",",$names);
print "# Backup of $table on ".date('m/d/Y')
." at ".date('h:i:s')."\n\n";
print "INSERT INTO $table ($fields)\nVALUES $rowstr;\n\n";
}
?>

Gary

N

Newsgroup_UserAuthor

Inspiring

David Powers wrote:
> gareth wrote:
>> Its pretty easy, using exec() to call mysqldump.
>
> I'm that a lot of hosts disable exec().

Er, fill in missing verb... I'm sure that...

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

N

Newsgroup_UserAuthor

Inspiring

I like cute. 8)

The full code block now is -

<?php require_once('../Connections/wildimages.php'); ?>
<?php

mysql_select_db($database_wildimages, $wildimages);

$tableName = 'tbl_images';
$backupFile = 'backup/images'.date("y-m-d").'.sql';
$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
//echo $query;

$result = mysql_query($query) or die(mysql_error());

?>

In this case, however, cute has failed me and I can see that the error is a
permissions one. Sigh....

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

"David Powers" <david@example.com> wrote in message
news:etel00$rsp$3@forums.macromedia.com...
> Murray *ACE* wrote:
>> What did I forget?
>
> A connection to MySQL?
>
> Although or die('Oops'); is cute, or die(mysql_error()); would tell what
> the problem really is.
>
> --
> David Powers, Adobe Community Expert
> Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
> Author, "PHP Solutions" (friends of ED)
> http://foundationphp.com/

Show more replies

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded