Advice on database backup practice & gzipping

[bunner_bob]

I've written a backup script which does the following:

• retrieves a list of all tables in the database
• reads each table and writes the fields (tab-delimited) into a gzipped file (one for each table)
• encodes those files into email attachments and then deletes the files
• sends a single email with multiple attachments

Really the only issue is I think it's kind of ugly to get an email with 10 attachments (for the 10 tables) and it might be nicer to just get one attachment - a gzipped folder containing the 10 text files.

I see no problem creating a directory and writing each table as an uncompressed text file to that directory. I'm not sure how to gzip the whole directory though. The various gzipping functions appear to work on a string, so I'd have to read the folder and its contents into a single string, which I'm not sure how to do. Can it be done?

Or - is it even necessary? Maybe it's no big deal to get backup emails with 10 attachments.

Or I suppose one more alternative is to write all the data from the tables to a single file, with some kind of divider between the tables. I can probably scope the way phpmyadmin creates dump files and just copy the way it does it.

Advice & comments?

Thanks,
Bob

[MarkR]

What is wrong with using mysqldump?

Seriously, a lot of php applications with their own "backup" facility have bugs in which causes it to generate corrupt backups. I recommend you don't create your own backup facility, but just use mysqldump, as it will work.

Also, assuming that a web page request won't time out (or user presses stop) during a backup is naive, and could lead to incomplete backups.

Basically just use mysqldump, it works. Run it from cron or similar. That is the way to go.

Mark

[bunner_bob]

Can't use cron - on a shared server. Also can't run shell scripts. Found this option for doing it from PHP - any problems with it?

<?php
$filename=´myTestDB´.date(´dmy´,time()).´.sql´;
$cmd=´mysqldump --user=root --password=test --opt myTestDB > "dbBackup/´.$filename.´"´;
passthru($cmd);
echo ´Db backup created to ´.$filename;
?>

• Bob

[bunner_bob]

mysqldump isn't working.

I tried this:

$dumpfile = "carepart_cpdb.sql";
  $dump_cmd = 'mysqldump --user=carepart_cpadmin --password=thepassword --opt carepart_cpdb > "/home/carepart/db_backup/"' . $dumpfile . '\"';
  $result = passthru($dump_cmd) or die("mysql error " . mysql_error());

And all I get is "mysql error" echoed, without any specifics about what kind of error it is.

So obviously the passthru() isn't working. Maybe it's some kind of restriction on my server.

Any other suggestions?

• Bob