Posts Tagged ‘mysql dump script’

Linux – Mysql dump script that can be used with cronjob

October 8th, 2010

The following is a script that will log into mysql and make dumbs of the db’s while placing it in a dated folder.

The first part is the script that will create the folder, we will call it mkdatedir.sh

#!/bin/bash

if [ $1 ] ;  then
dir="$1";
else
dir="."
fi

export DATE=`date +%d:%m.%H:%M`
echo "$dir/$DATE"
mkdir "$dir/$DATE"

place this script in your desired folder. We will put it for now in “/var/lib/mysql/backupscripts/mkdatedir.sh”.

Once done create the backup script itself. We will call it “dumpdbs.sh”

The code is:

#!/bin/bash

# backup database script .

export MYSQL_PWD=PUTMYSQLADMINROOTPASSWORDHERE
#Location to dump backup, go into it
cd /var/lib/mysql/dumps
#calling on the creation directory script, create folder
export DATE=`/var/lib/mysql/backupscripts/mkdatedir.sh`
#go into folder
cd $DATE

for i in `mysql -u admin -e 'show databases'|grep -v Database` ; do
/bin/nice -n 10 mysqldump -u admin --opt $i | gzip -6 -> "$i.dump.gz";
done

Notice we used admin as the login here, you may need to use root. Once you tested and made sure this works. You can create a cron to backup on a regular basis.

if you found this helpful feel free to donate to:
ZEC: t1NQp1UuqQbmnXzazbLTSreS2AbaZpRBuTM
LTC: LZyNF1qkBUA7XFz83m5xwzGgmmj1owQn9d
BTC: 1PY95KFPTEJTR7f2NnSgaB6xB9pwDJkcJz