Cron job for daily database backup


#1

Does anyone have a cron job command for backing up a database that you can share?

I’ve tried repeatedly but can’t come up with a command that works.

Here’s my most recent (failed) attempt:

mysqldump --opt --user=username --password=pw --host=mysql.hostname.com database_name > backup_file_name.sql

(Where username, pw, mysql.hostname.com and database_name are placeholders for the real values.)

I’d appreciate whatever help anyone can supply.

FYI, some tables in my database recently were emptied (I don’t know how), and there was no database backup when I clicked the restore button, even though the database has been around since 2012. Fortunately, I had a fairly recent manual backup. Having a daily backup on hand would ease my mind.

Thanks.


#2

This is what I use:

/usr/bin/mysqldump -hDBHOST.EXAMPLE.COM -uDB_USER -pDB_PASSWORD DB_NAME > backup_file_name.sql

Change all the upper case to your database information. I’d suggest a full path for the output, like /home/MYUSER/backup_file_name.sql


#3

Thanks, Scott! I’ll give it a try.


#4

In order to avoid having to ask myself the question of what the current directory and path are in a cron job, I simply write everything out in full:

#!/bin/bash export TZ="/usr/share/zoneinfo/UTC" now=$(date +"%y%m%d-%H%M") /usr/bin/mysqldump --opt --single-transaction --user=DBUSER --password=PASSWORD --host=HOSTNAME DBNAME | /bin/gzip -7 > /home/USER/temp/DBNAME-$now.sql.gz /bin/gzip -cd /home/USER/temp/DBNAME-$now.sql.gz | tail -n 1 | /usr/bin/mutt -s "DBNAME $now" -a /home/USER/temp/DBNAME-$now.sql.gz -- BACKUPS@EXAMPLE.COM

The reason for sending the last line of the .sql file as the body of the email message is that if all has gone well it will say “-- Dump completed on DATE TIME”