Backup All Your MySQL Database With One Script

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

Okay i am not explaining what it MySQL, or what is SQL syntax. Now i just want to share with you a script to backup all the databases into compressed file. So you can backup your databases regularly easily to avoid data loss.

How to run/install it:

1. First download the script below.
2. Open the file and change the configuration, like username, password and target folder
3. Make it executable with chmod +x scriptname.sh
4. 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 happen with your server, your data will be safe. And last you can run this script with cron, so you can have your database backup regularly.

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