take mysqldump and send email

Taking backups is like day-to-day task for every system admins. In this turotials we will tell you to take mysqldump and send email through linux system.
We can take mysql database backup using cron job and mysqldump command.

We created shell script called db-backup-email.sh. Which can be used on any linux based system. We tested this script on Red Hat,CentOS, Fedora, Debian and Ubuntu systems. It is working fine.

You can download this mysql backup shell script from here..

take mysqldump and send email

How to use this script?

use following command to take mysql backup.

/bin/sh /path-to-sh-script/db-backup-email.sh mysqlusername mysqlpassword database_name your_email@email.com

With Cron
You can put this script on cron job file on linux. following command will execute at night at 2.00 am.

0 2 * * * /bin/sh /path-to-sh-script/db-backup-email.sh mysqlusername mysqlpassword database_name your_email@email.com

In shell script I written following code.

#!/bin/sh

MYSQLUSER=$1; #mysqluser
MYSQLPASS=$2; #mysqlpass
DBNAME=$3; #DBname
EMAIL=$4; #email to send

mysqldump -u $MYSQLUSER -p $MYSQLPASS $DBNAME | gzip > /tmp/$DBNAME.`date +%Y-%m-%d-%H`.sql.gz && echo "the mysqldump has been completed- status received at `date +%Y-%m-%d-%H`"|mail -s mysqldump-status -a "/tmp/$DBNAME.`date +%Y-%m-%d-%H`.sql.gz" $EMAIL

What is mysqldump?
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

If you want to execute take dump only once than you can execute following mysqldump command.

mysqldump -u root -p sharetale | gzip > /tmp/serverDB.`date +%Y-%m-%d-%H`.sql.gz && echo "the mysqldump has been completed- status received at `date +%Y-%m-%d-%H`"|mail -s mysqldump-status -a "/tmp/serverDB.`date +%Y-%m-%d-%H`.sql.gz" purabdk@gmail.com

Many times E-mailing a database is a pretty bad idea, especially as it gets bigger and bigger, or security info is involved. But if your database is less than 5MB than you can send Database through email.

This script is for very small database. We just want to back it up every now and again in such a way that none technical members of the project can back up and restore.

There are some php scripts which can do same stuff but this script is much faster than other php script.

Published by

Purab

I am Purab from India, Software development is my profession and teaching is my passion. Programmers blog dedicated to the JAVA, Python, PHP, DevOps and Opensource Frameworks. Purab's Github Repo Youtube Chanel Video Tutorials Connect to on LinkedIn

Leave a Reply

Your email address will not be published.