By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,664 Members | 1,656 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,664 IT Pros & Developers. It's quick & easy.

PHP , MySQL Backup

P: n/a
Hi
I'm having issues with mysqldump so need to create backups of my mysql
databases using PHP.

Can any one recommend a way to do this with out calling mysqldump ?

I did find one script and it worked on a small sample DB (1MB), but
fails on my db (75MB)

Thanks :)
Sep 20 '08 #1
Share this Question
Share on Google+
15 Replies


P: n/a
..oO(Jerry Yang)
>I'm having issues with mysqldump
What issues?

Micha
Sep 20 '08 #2

P: n/a
On Sep 20, 8:20*pm, Jerry Yang <jexxt...@gmail.comwrote:
Hi
I'm having issues with mysqldump so need to create backups of my mysql
databases using PHP.

Can any one recommend a way to do this with out calling mysqldump ?

I did find one script and it worked on a small sample DB (1MB), but
fails on my db (75MB)

Thanks :)
TinyMy or PHPMyAdmin.
On for failure on large databases - set_time_limit(0);
Sep 21 '08 #3

P: n/a
Jerry Yang wrote:
Hi
I'm having issues with mysqldump so need to create backups of my mysql
databases using PHP.

Can any one recommend a way to do this with out calling mysqldump ?

I did find one script and it worked on a small sample DB (1MB), but
fails on my db (75MB)

Thanks :)
Without knowing what's wrong with mysqldump, no. That's normally the
best way to take a dump of the database. If that fails, chances are
other attempts will fail, also (depending on what's wrong, of course).

I recommend you fix mysqldump. See comp.databases.mysql.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Sep 21 '08 #4

P: n/a
Thanks for the replies.

My host is running mysql 5.0.51a and mysqldump does return any data.
I've quite a few references to this on google.. so I need to find a
way around it !!

This php works for small db's... but fails on my main db:

function write($contents) {
if ($GLOBALS['gzip']) {
gzwrite($GLOBALS['fp'], $contents);
} else {
fwrite($GLOBALS['fp'], $contents);
}
}

mysql_connect ($dbserver, $dbuser, $dbpass);
mysql_select_db($dbname);

if ($gzip) {
$fp = gzopen($file, "w");
} else {
$fp = fopen($file, "w");
}

$tables = mysql_query ("SHOW TABLES");
while ($i = mysql_fetch_array($tables)) {
$i = $i['Tables_in_'.$dbname];

if (!$silent) {
echo "Backing up table ".$i."\n";
}

// Create DB code
$create = mysql_fetch_array(mysql_query ("SHOW CREATE TABLE ".
$i));

write($create['Create Table'].";\n\n");

// DB Table content itself
$sql = mysql_query ("SELECT * FROM ".$i);
if (mysql_num_rows($sql)) {
while ($row = mysql_fetch_row($sql)) {
foreach ($row as $j =$k) {
$row[$j] = "'".mysql_escape_string($k)."'";
}

write("INSERT INTO $i VALUES(".implode(",", $row).");\n");
}
}
}

$gzip ? gzclose($fp) : fclose ($fp);
Any ideas ? Many Thanks
Sep 21 '08 #5

P: n/a
..oO(Jerry Yang)
>Thanks for the replies.

My host is running mysql 5.0.51a and mysqldump does return any data.
I've quite a few references to this on google.. so I need to find a
way around it !!
References to what? What's the problem with mysqldump? Please be more
specific.

Micha
Sep 21 '08 #6

P: n/a
Hi
This seems to explain the issue with mysqldump.
http://bugs.mysql.com/bug.php?id=34192

Basically since my ISP has upgraded from 4.0.30 to 5.0.51a mysqldump
runs but does not produce any output. So I am having problems backing
up my database. My ISP has no plans to change the version of mysql at
the moment.

I tried the php script above and this works well on small databases,
it creates the output for large databases but they fail to restore.

I hope that makes sense..

Thanks
Sep 22 '08 #7

P: n/a
..oO(Jerry Yang)
>This seems to explain the issue with mysqldump.
http://bugs.mysql.com/bug.php?id=34192

Basically since my ISP has upgraded from 4.0.30 to 5.0.51a mysqldump
runs but does not produce any output.
Wouldn't this mean they only upgraded the client libraries, but not the
server? Why else would you have to use a 5.0 mysqldump on a 4.0 server?

Micha
Sep 22 '08 #8

P: n/a
On 22 Sep, 10:52, Michael Fesser <neti...@gmx.dewrote:
.oO(Jerry Yang)
This seems to explain the issue with mysqldump.
http://bugs.mysql.com/bug.php?id=34192
Basically since my ISP has upgraded from 4.0.30 to 5.0.51a mysqldump
runs but does not produce any output.

Wouldn't this mean they only upgraded the client libraries, but not the
server? Why else would you have to use a 5.0 mysqldump on a 4.0 server?

Micha
They have upgraded all of the mysql, not just the mysqldump.
The mysqldump seems to run fine, but on output is given..

Help Please :)
Sep 22 '08 #9

P: n/a
Jerry Yang wrote:
On 22 Sep, 10:52, Michael Fesser <neti...@gmx.dewrote:
>.oO(Jerry Yang)
>>This seems to explain the issue with mysqldump.
http://bugs.mysql.com/bug.php?id=34192
Basically since my ISP has upgraded from 4.0.30 to 5.0.51a mysqldump
runs but does not produce any output.
Wouldn't this mean they only upgraded the client libraries, but not the
server? Why else would you have to use a 5.0 mysqldump on a 4.0 server?

Micha

They have upgraded all of the mysql, not just the mysqldump.
The mysqldump seems to run fine, but on output is given..

Help Please :)
Get mysqldump fixed. But this isn't the correct newsgroup to be
following up on mysql questions.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Sep 22 '08 #10

P: n/a
Get mysqldump fixed. But this isn't the correct newsgroup to be
following up on mysql questions.
I agree this is the best solution.. but my ISP has no plans to do this
at the moment.
Any other ideas ?
Sep 22 '08 #11

P: n/a
r0g
Jerry Yang wrote:
On 22 Sep, 10:52, Michael Fesser <neti...@gmx.dewrote:
>.oO(Jerry Yang)
>>This seems to explain the issue with mysqldump.
http://bugs.mysql.com/bug.php?id=34192
Basically since my ISP has upgraded from 4.0.30 to 5.0.51a mysqldump
runs but does not produce any output.
Wouldn't this mean they only upgraded the client libraries, but not the
server? Why else would you have to use a 5.0 mysqldump on a 4.0 server?

Micha

They have upgraded all of the mysql, not just the mysqldump.
The mysqldump seems to run fine, but on output is given..

Help Please :)

You say the script dumps the data but then it can't be restored... how
are you trying to restore it? Via a web interface or via command line?
Have you looked inside the dump file? Is it syntactically broken in any
way? i.e. has it been truncated?

IF your only way of backing up an restoring is via the web then you're
going to run into PHP upload and download limits eventually. PHP My
admin generally wont allow you to restore files bigger than 2Mb
compressed and there may be limits on script execution time that will
prevent a large backup or restore.

The best way round these problems, assuming you can't get your host to
fix the _actual_ problem would be to divide and conquer i.e. modify your
script to dump/restore a table at a time or 1000 records at a time so as
not to run into these walls.
Roger.
Sep 22 '08 #12

P: n/a
On Sep 22, 4:15 pm, r0g <aioe....@technicalbloke.comwrote:
Jerry Yang wrote:
On 22 Sep, 10:52, Michael Fesser <neti...@gmx.dewrote:
.oO(Jerry Yang)
>This seems to explain the issue with mysqldump.
http://bugs.mysql.com/bug.php?id=34192
Basically since my ISP has upgraded from 4.0.30 to 5.0.51a mysqldump
runs but does not produce any output.
Wouldn't this mean they only upgraded the client libraries, but not the
server? Why else would you have to use a 5.0 mysqldump on a 4.0 server?
Micha
They have upgraded all of the mysql, not just the mysqldump.
The mysqldump seems to run fine, but on output is given..
Help Please :)

You say the script dumps the data but then it can't be restored... how
are you trying to restore it? Via a web interface or via command line?
Have you looked inside the dump file? Is it syntactically broken in any
way? i.e. has it been truncated?

IF your only way of backing up an restoring is via the web then you're
going to run into PHP upload and download limits eventually. PHP My
admin generally wont allow you to restore files bigger than 2Mb
compressed and there may be limits on script execution time that will
prevent a large backup or restore.

The best way round these problems, assuming you can't get your host to
fix the _actual_ problem would be to divide and conquer i.e. modify your
script to dump/restore a table at a time or 1000 records at a time so as
not to run into these walls.

Roger.
Previously using mysqldump resulted in an sql file of approx 75MB, yet
the php script produced a file of 175MB !! using the same data.
I can't see anything wrong with the large file, but it's not right at
that size !!

I normally use BigDump to upload the sql backup and have found this
works well.

Any ideas ?
Sep 22 '08 #13

P: n/a
r0g
Jerry Yang wrote:
<snip>
>The best way round these problems, assuming you can't get your host to
fix the _actual_ problem would be to divide and conquer i.e. modify your
script to dump/restore a table at a time or 1000 records at a time so as
not to run into these walls.

Roger.

Previously using mysqldump resulted in an sql file of approx 75MB, yet
the php script produced a file of 175MB !! using the same data.
I can't see anything wrong with the large file, but it's not right at
that size !!
Well not necessarily, it really depends on how many entries your dump
script is squeezing into each insert statement for example. There's many
ways of encoding the same data in SQL, some less efficient than others.

Have you got a local LAMP/WAMP setup? If so try loading it into a local
SQL DB. If it doesn't choke you're probably OK, but of course you should
take a look and check you have about the same number of records per
table as you have in your live DB etc.

Anyway, have a look at least, I'll keep my fingers crossed for you!

Roger.
Sep 22 '08 #14

P: n/a
Jerry Yang wrote:
>Get mysqldump fixed. But this isn't the correct newsgroup to be
following up on mysql questions.

I agree this is the best solution.. but my ISP has no plans to do this
at the moment.
Any other ideas ?
Find another host. If they won't fix this, how many other problems will
you have that they won't fix?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Sep 22 '08 #15

P: n/a
..oO(Jerry Yang)
>On 22 Sep, 10:52, Michael Fesser <neti...@gmx.dewrote:
>.oO(Jerry Yang)
>This seems to explain the issue with mysqldump.
http://bugs.mysql.com/bug.php?id=34192
>Basically since my ISP has upgraded from 4.0.30 to 5.0.51a mysqldump
runs but does not produce any output.

Wouldn't this mean they only upgraded the client libraries, but not the
server? Why else would you have to use a 5.0 mysqldump on a 4.0 server?

Micha

They have upgraded all of the mysql, not just the mysqldump.
OK, but then it can't be the bug you mentioned above.
>The mysqldump seems to run fine, but on output is given..

Help Please :)
Only your host can (and should!) fix this, because mysqldump is the
right and most flexible tool for this job.

Micha
Sep 22 '08 #16

This discussion thread is closed

Replies have been disabled for this discussion.