Cron job - mysqldump

[pmartin]

Hoping to find some help here...How do i setup a cron job to run this command everynight and ftp it off the box:

mysqldump -u root -p --all-databases > dbdump.sql

I appreciate the help...I have never setup a cron job before so the more details the better.

Thanks!

 

[jroper]

Hi

Put your commands in a shell script.

Log into webmin, and there you can configure and build a cronjob to launch your shell script.

Joe

 

[jmullinix]

If you want it to run a 4:02 am local time, you can create a shell script with the commands you need and drop that script into the /etc/cron.daily folder. That way you don't have to muck around in the crontab file.

 

[Snapdragon]

Here is my MySQL backup cron job. Edit it as you see fit.

I suggest you made a backup user account that only has enough read privs to complete the backup. Sorry, I cannot tell you what they are as this job has come from a box that was formatted a couple weeks ago.

This job will keep a rotation of mysql backups for seven days. First in, first out. It also optimizes the databases daily before backup.

--lock-tables is a highly suggested switch to avoid DB changes during backup.

#!/bin/sh

# optimize live databases
mysqlcheck -A --optimize -uadmin_backup -ppasswordgoeshere

# rotate stored database backups
cd /mnt/backup/db/
rm -rf *.sql.6.gz
rename .sql.5.gz .sql.6.gz *.sql.5.gz
rename .sql.4.gz .sql.5.gz *.sql.4.gz
rename .sql.3.gz .sql.4.gz *.sql.3.gz
rename .sql.2.gz .sql.3.gz *.sql.2.gz
rename .sql.1.gz .sql.2.gz *.sql.1.gz
rename .sql.gz   .sql.1.gz *.sql.gz

# backup live databases
mysqldump -uadmin_backup -ppasswordgoeshere --lock-tables asterisk > /mnt/backup/db/asterisk_`date +%Y%m%d%H%M`.sql
mysqldump -uadmin_backup -ppasswordgoeshere --lock-tables asteriskcdrdb > /mnt/backup/db/asteriskcdrdb_`date +%Y%m%d%H%M`.sql
mysqldump -uadmin_backup -ppasswordgoeshere --lock-tables mysql > /mnt/backup/db/mysql_`date +%Y%m%d%H%M`.sql
mysqldump -uadmin_backup -ppasswordgoeshere --lock-tables information_schema > /mnt/backup/db/information_schema_`date +%Y%m%d%H%M`.sql

# compress newly made database backups
gzip /mnt/backup/db/*.sql

exit 0