Backup All Your MySQL Database With One Script

MySQL is a relational database management system (RDMS), and one of the most powerful DBMSes. Most websites including this blog use MySQL technology behind them. Since it’s free and always bundled with PHP and Linux, it is often called LAMP (Linux, Apache, MySQL, and PHP). If you know a little about programming in PHP, MySQL is a skill you must learn.

Okay I am not going to explain what MySQL is, or what SQL syntax is. Now I just want to share with you a script to back up all the databases into a compressed file. So you can back up your databases regularly and easily to avoid data loss.

How to run/install it:

First download the script below.
Open the file and change the configuration, like username, password and target folder
Make it executable with chmod +x scriptname.sh
Run it with ./scriptname.sh

Here is the script:

#!/bin/bash

PATH=/usr/sbin:/sbin:/bin:/usr/bin

MyUSER="Your Username Here"
MyPASS="Your Password Here"
MyHOST="localhost" #Usually 'localhost', but if you know what you are doing, please change it

MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

DEST="/backup" #destination folder, /backup is default folder, you need to create it first
MDB="$DEST/mysql" #i put all database backup under mysql folder, you can change it to anyname

HOST="$(hostname)"

NOW="$(date +"%d-%m-%Y")"
FILE=""

DBS=""

IGGY="test mysql"

DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

for db in $DBS
do
	skipdb=-1
	if [ "$IGGY" != "" ];
	then
	for i in $IGGY
		do
			[ "$db" == "$i" ] && skipdb==1 || :
		done
	fi

	if [ "$skipdb" == "-1" ] ; then
		FILE="$MDB/$db.$HOST.gz"
		$MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS --complete-insert $db | $GZIP -9 > $FILE
                echo "Backup $FILE.....DONE"
	fi
done

Or download it here.

And also you can automatically send it to email or another backup server, so you don’t have to worry if something happens with your server; your data will be safe. And lastly you can run this script with cron, so you can have your database backed up regularly.

I will write about how to run it with cron and how to automatically send it to another server. So stay tuned 🙂