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]

Re: PHP , MySQL Backup

..oO(Jerry Yang)

>I'm having issues with mysqldump

What issues?

Micha

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

Re: PHP , MySQL Backup

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

[Jerry Stuckle]

Re: PHP , MySQL Backup

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.
jstucklex@attgl obal.net
=============== ===

[Jerry Yang]

Re: PHP , MySQL Backup

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($GLOBAL S['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_arr ay($tables)) {
$i = $i['Tables_in_'.$d bname];

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

// Create DB code
$create = mysql_fetch_arr ay(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_escap e_string($k)."' ";
}

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

$gzip ? gzclose($fp) : fclose ($fp);


Any ideas ? Many Thanks

[Michael Fesser]

Re: PHP , MySQL Backup

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

Re: PHP , MySQL Backup

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]

Re: PHP , MySQL Backup

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

Re: PHP , MySQL Backup

On 22 Sep, 10:52, Michael Fesser <neti...@gmx.de wrote:

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

Re: PHP , MySQL Backup

Jerry Yang wrote:

On 22 Sep, 10:52, Michael Fesser <neti...@gmx.de wrote:

>.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.
jstucklex@attgl obal.net
=============== ===

[Jerry Yang]

Re: PHP , MySQL Backup

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]

Re: PHP , MySQL Backup

Jerry Yang wrote:

On 22 Sep, 10:52, Michael Fesser <neti...@gmx.de wrote:

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

Re: PHP , MySQL Backup

On Sep 22, 4:15 pm, r0g <aioe....@techn icalbloke.comwr ote:

Jerry Yang wrote:

On 22 Sep, 10:52, Michael Fesser <neti...@gmx.de wrote:

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

Re: PHP , MySQL Backup

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]

Re: PHP , MySQL Backup

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.
jstucklex@attgl obal.net
=============== ===