PHP , MySQL Backup

[Jerry Yang]

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 :)

[Michael Fesser]

..oO(Jerry Yang)

>I'm having issues with mysqldump

What issues?

Micha

[=?ISO-8859-13?Q?Kristaps_K=FBlis?=]

On Sep 20, 8:20*pm, Jerry Yang <[e-mail]:

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);

[Jerry Stuckle]

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
==================

[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 !!

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

[Michael Fesser]

..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

[Jerry Yang]

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

[Michael Fesser]

..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

[Jerry Yang]

On 22 Sep, 10:52, Michael Fesser <[e-mail]:

.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 :)

[Jerry Stuckle]

Jerry Yang wrote:

On 22 Sep, 10:52, Michael Fesser <[e-mail]:

>.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
==================

[Jerry Yang]

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 ?

[r0g]

Jerry Yang wrote:

On 22 Sep, 10:52, Michael Fesser <[e-mail]:

>.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.

[Jerry Yang]

On Sep 22, 4:15 pm, r0g <[e-mail]:

Jerry Yang wrote:

On 22 Sep, 10:52, Michael Fesser <[e-mail]:

.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 ?

[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.

[Jerry Stuckle]

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
==================

[Michael Fesser]

..oO(Jerry Yang)

>On 22 Sep, 10:52, Michael Fesser <[e-mail]:

>.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