This is a simple mysql backup script.The script can be used manually as well as in crontab.
Edit the below given script and provide the information required in the script.
The script will take the backup in .sql format later it will compress the .sql file and in end send the mail about the status of mysql backup.
(1) For getting mail from Server install mail command [mailx(in Red Hat/CentOS) or mail-utils in Ubuntu/Debian]
yum install mailx (For Red Hat and CentOS) OR apt-get install mail-utils (For Debian and Ubuntu)
(2) Create directory /root/database-backup/
mkdir -p /root/database-backup/
(3)Below given is the MySQL backup script
Note: Edit the values in bash script as per requirement
Create a file called db-backupscript.sh
And paste the below contents
#!/bin/bash # mysql backup script # Author: sharad # 4-Sept-2013 # MYSQLUSER=Mysql-user PASSWORD=Mysql-user-Passw0rd DBNAME=Database-Name ### Give MYSQL Server IP address or FQDN, For eg. 127.0.0.1 or localhost MYSQLHOST=127.0.0.1 BACKUPPATH=/root/database-backup/$DBNAME-`date +%F-%H%M%S` #You can give multiple email id in MAILTO variable by using comma (,) for eg. MAILTOfirstname.lastname@example.org,email@example.com MAILTOfirstname.lastname@example.org mysqldump -h$MYSQLHOST -u$MYSQLUSER -p$PASSWORD $DBNAME > $BACKUPPATH ### compressing the file gzip $BACKUPPATH FILESIZE=$( du -sh $BACKUPPATH.gz ) if [ ! -f $BACKUPPATH.gz ]; then echo "$BACKUPPATH.gz File not found!, Database Name: $DBNAME" | mail -s "$DBNAME backup failed" $MAILTO else echo "$BACKUPPATH.gz File found,Database Name: $DBNAME, Actual size after compression is $FILESIZE " | mail -s "DBNAME database backup is done" $MAILTO fi
(4) Give execute permission to script and should be executable and access by owner of file
chmod 700 db-backupscript.sh
Nice script, Instead of the variable name backuppath, you can give it dbname , it becomes less confusing later