Need suggestion for a backup script..

your_shadow03 · 02-19-2014, 10:53 AM

I have written a backup script for Mysql database as shown below:

Code:

#!/bin/bash
# Robert Feb-14
# backup each mysql db into a different file, rather than one big file
# as with --all-databases - will make restores easier

USER1="user1"
USER2="user2"
USER3="user3"
PASSWORD1=""
PASSWORD2=""
PASSWORD3=""
db1="database1"
db2="database2"
db3="database3"
OUTPUTDIR="/var/lib/psa/dumps/domains/logic.tv/custom-database                                                                                       e-backup"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

# clean up any old backups - save space
#rm "$OUTPUTDIR/*bak" > /dev/null 2>&1

# get a list of databases
#databases=`$MYSQL --user=$USER1 --password=$PASSWORD1 \
# -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
#
# dump each database in turn
#for db in $databases; do
#    echo $db
$MYSQLDUMP --force --opt --user=$USER1 --password=$PASSWORD1 \
    --databases $db1 > "$OUTPUTDIR/$db1.bak"

$MYSQLDUMP --force --opt --user=$USER2 --password=$PASSWORD2 \
    --databases $db2 > "$OUTPUTDIR/$db2.bak"
$MYSQLDUMP --force --opt --user=$USER3 --password=$PASSWORD3 \
    --databases $db3 > "$OUTPUTDIR/$db3.bak"

The above code is working fine and dumping it to the required folder.All I need is adding date and time to the filename and sending mail notification in case the database backup is successfully performed. Can anyone suggest?

schneidz · 02-19-2014, 10:56 AM

man date. e.g.:

Code:

$MYSQLDUMP --force --opt --user=$USER1 --password=$PASSWORD1 \
    --databases $db1 > "$OUTPUTDIR/$db1`date +%Y-%j-%s`.bak"

colucix · 02-19-2014, 10:57 AM

Use command substitution inside the filename, e.g.

Code:

"$OUTPUTDIR/$db1.$(date +%F).bak"

produces

Code:

/var/lib/psa/dumps/domains/logic.tv/custom-database/database1.2014-02-19.bak

your_shadow03 · 02-19-2014, 11:23 AM

colucix & sch,
Thanks. It works great.

your_shadow03 · 02-19-2014, 11:32 AM

Any idea how to add email notification if the backup is successfully performed?

sjreilly · 02-19-2014, 11:52 AM

Why not just use automysqlbackup?
It does email notification as well as daily, weekly and monthly backups.

schneidz · 02-19-2014, 11:56 AM

Quote:

Originally Posted by your_shadow03

Any idea how to add email notification if the backup is successfully performed?

man mail
using an if statement you can check the result of the previous command ($!).

your_shadow03 · 02-19-2014, 12:04 PM

I dont think automysqlbackup takes care of individual database backup. Does it?

your_shadow03 · 02-19-2014, 12:06 PM

Sch,
I implemented it through echo "Backup Performed" | mail -s "Subject" <myemailID>
It works but mail comes from root@<hostname>
How shall I make it come from different name other than root. Like administrator@<hostname>

your_shadow03 · 02-19-2014, 12:20 PM

sjrelly,

I read through the script. It does take daily backup. I am trying to see which option to enable for daily backup.

schneidz · 02-19-2014, 12:56 PM

Quote:

Originally Posted by your_shadow03

Sch,
I implemented it through echo "Backup Performed" | mail -s "Subject" <myemailID>
It works but mail comes from root@<hostname>
How shall I make it come from different name other than root. Like administrator@<hostname>

run command as another user:

Code:

su - schneidz -c "ls -l /home/schneidz"

sjreilly · 02-20-2014, 03:07 AM

Yes, you can select which databases to back up (or all) with the DBNAMES variable