cron job with mysqldump and gzip

NetHosted Customer Joined: 29 Jul 2005 Posts: 14

Trying to set up a cron job to backup a MYSQL DB, but I'm not sure how to compress it.

This works:

mysqldump --user=username --password=password myacc_dbname | mail -s "Database Backup" myemail@mydomain.com

But the DB is pretty big so I'd like to get it compressed, I've tried this:

mysqldump --user=username --password=password myacc_dbname | gzip -9 > dailybackup.sql.gz | mail -s "Database Backup" myemail@mydomain.com

But I just can't get it to work, any ideas where I've gone wrong or is this not possible on my account?

NetHosted Staff Joined: 24 Jun 2005 Posts: 1013

Hi,

I believe the problem here is at the point you are gzipping this it is writing it to disk, so it is no longer kept in the buffer or being passed to the mail command.

Regards,

Darryl

NetHosted Customer Joined: 29 Jul 2005 Posts: 14

This accounts disk space was almost full as well, could that have been affecting it?

Edit: scratch that, just deleted a load of files and tried again, still won't work.

Is there any way you can think of achieving this via a cron job?

NetHosted Staff Joined: 24 Jun 2005 Posts: 1013

Hi,

Using the -c flag for gzip should make it output to stdout so you can pipe this in, I believe the following syntax should work:

mysqldump --user=username --password=password myacc_dbname | gzip -9cf | mail -s "Database Backup" myemail@mydomain.com

Regards,

Darryl

NetHosted Customer Joined: 29 Jul 2005 Posts: 14

Thanks Darryl, awsome support as usual

Tried the above and it didn't work.

I tried adding a few timed cron jobs to do all this, I can back up the DB and gzip the file (ends up about 6MB), file downloads and extracts fine.

But when I try to mail it comes through unencoded.

/usr/sbin/sendmail email@domain.com < db_dailybackup.sql.gz

Tried using uuencode, but it seems to fail more often than not and creates a corrupt file.

NetHosted Staff Joined: 22 Mar 2004 Posts: 7017

Hi,

Perhaps mutt would work better for that last step, something like this should work:

NetHosted Customer Joined: 29 Jul 2005 Posts: 14

Thanks Andrew,

Tried mutt earlier, have just tried the above and I'm getting -

/bin/sh: mutt: command not found

NetHosted Staff Joined: 22 Mar 2004 Posts: 7017

Hi,

Please send in a ticket giving details of what account you are trying this on so I can investigate.

Thanks,

Andrew

NetHosted Customer Joined: 29 Jul 2005 Posts: 14

Thanks again guys, all working now

For anyone interested in the future, I've set up two cron jobs as below, first one runs once a day on the hour, the other a few minutes later:

Client Support	Community	Server Status	Contact Us	Client Login
Email Hosting	Website Hosting	Reseller Hosting	VPS Hosting	Dedicated Servers