Creating a CronJob - MySQL DB backup

[woldie]

Hi,

I'm trying to create a cron job to backup my database, I've been searching around for examples but can anyone assist me on this?

Here is the code at the moment which I run on the command line:

mysqldump --opt -v -uusername -p dbname > mysqlbackup270804.sql

As you can see I create a file name according to date so it should create this 'on the fly'. Ideally I would like to do a backup around 23:00 everyday, also perhaps use gunzip to save diskspace.

Many Thanks

Woldie.

[RonPK]

The command line command date is useful here. Try 'man date' for all the options. Use it in your cron line with backticks.

mysqldump [bla] ¦ gzip > [path]/backup_`date +%d`.sql.gz

creates a file called backup_01.sql.gz , 01 being today's day number. This also puts the output through gzip (the ¦ should be a full pipe).

For your date format something like %d%m%y might work.

[woldie]

Thanks RonPK for the feedback, I'll give it go, much appreciated for this.

:o)

[woldie]

Hi,

I've got a small problem, my cron job did not work.

I checked the /var/logs directory and edited the cron file to see the output, and this is what I got,

Sep 1 23:00:00 www CROND[16775]: (root) CMD (/usr/bin/mysqldump --opt -u user -ppass ¦ gzip > /home/sites/domain/web/mysqlbackup_`date +)

As you can see the date bit did not work, I think this is the problem?

Here is the script I wrote,

00 23 * * * /usr/bin/mysqldump --opt -u user -ppass ¦ gzip > /home/sites/domain/web/mysqlbackup_`date +%d%m%y`.sql.gz

Any ideas?

Many Thanks

W.

[RonPK]

Ah yes, sorry about that, I pasted my line from a script. If you run the command directly inside the cron job, you need to escape the % with a \ or it will be interpreted as a newline.

[woldie]

Thanks again for responding RonPK.

This is the script I've got:-

00 09 * * * /usr/bin/mysqldump --opt -u user -ppassword ¦ gzip > /home/sites/domain/web/mysqlbackup_`date +\d\m\y`.sql.gz

This does work, however, this is the file name which it outputs:-

mysqlbackup_dmy.sql.gz

As you can see it hasn't populated with the correct date.

Much appreciated thanks.

:o)

[woldie]

Oh yes, what I did unzipped the file and this is what I got, it didn't quite workout.

Thanks

/usr/bin/mysqldump Ver 8.22 Distrib 3.23.54, for redhat-linux-gnu (i386)
By Igor Romanenko, Monty, Jani & Sinisa
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table
Usage: /usr/bin/mysqldump [OPTIONS] database [tables]
OR /usr/bin/mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR /usr/bin/mysqldump [OPTIONS] --all-databases [OPTIONS]

-A, --all-databases Dump all the databases. This will be same as
--databases with all

etc.......

[RonPK]

The date-part should look like this:
`date +\%d\%m\%y`

The backslash should tell the cron daemon to ignore the special meaning of the % character. The % then should get passed on to the date command.

It looks like cron only starts /usr/bin/mysqldump, without all the options. I'm not sure about this, but maybe it will work if you put the whole line, or just the mysqldump part, between quotes.

[woldie]

Hi RonPK,

Right, I've cracked it, all thanks to you obviously!

What I did miss out was the name of the database in which it could dump the data.

Cheers

:o)

[SkyDog]

Is there an advantage to doing a database dump rather than just backing up the data directery itself? I've always just tarred and zipped the entire data directory.

[py9jmas]

If the database is running when you copy the raw data files, you are almost certain to get a corrupt backup. The database keeps stuff in memory to improve performance. What is flushed to disk when you copy the file will probably be inconsistant and out of date.

But of course, you have tested your backups haven't you?

[SkyDog]

If the database is running

LOL, of course not. I guess that's an advantage of doing the dump, you can leave the db running.

[woldie]

Hi Guys,

I've got a problem, and I don't understand it at all.

What I have is the following cron job code to do a mysqldump. What I did is chmod 755 so that it executes at a given time shown below.

30 09 * * * /usr/bin/mysqldump --opt -uusername -ppassword dbname ¦ gzip > /home/sites/domain.com/web/mysqlbackup_`date +%d%m%y`.sql.gz

But after it has executed I look at the file size and its around 20k, where as if I run the code on the command line the file size is around is 12meg which looks right to. So I don't understand this, it works on the command line but it doesn't when its executed as a cron job.

Any reasons why?

Thanks again.

Woldie

[RonPK]

Woldie, have you tried any of the suggestions that were made earlier in this thread? Like escaping the %-characters?