MySQL Backup Databases In Compressed Format


#/bin/bash

Check if user input hostname, if not – exit from script
if [ “$#” = “0” ]; then
echo “Usage: basename $0
exit 2
fi

we save hostname that user input to MySQL variable
MYSQL=$1

Folder to keep backups
BACKUP_DIR=”/data”

we getting current timestamp, to use in filenames
NOW=date +%Y%m%d-%H%M%S

name of file will be HOSTNAME-CURRENT_TIMESTAMP
NAME=${MYSQL}-${NOW}

We use MySQL access Without Password
PASSWORD=””

This will create folder, where we will keep all dumps
mkdir ${BACKUP_DIR}/${NAME}

Telling user that we start to process hostnname
echo “Processing ${MYSQL}…”

Database name without description and database with name information_schema
DBS=echo "show databases;" | mysql --host=${MYSQL} --user=root --password=${PASSWD} | \
awk '{if ($1 != "Database" && $1 != "information_schema") {print $1}}'

Now we will process every database, we will create dump for each
for DB in ${DBS}; do

telling user that we starting to dump each database
echo -n ” Dumping ${DB}… ”

Something to Database
if [ ${DB} != “mysql” ]; then
LOCKS=”–lock-tables”
else
LOCKS=””
fi

Command that will Create Dump
mysqldump –host=${MYSQL} –user=root –password=${PASSWD} –force \
–allow-keywords –complete-insert –add-drop-table –add-locks –flush-logs \
${LOCKS} –result-file=${BACKUP_DIR}/${NAME}/${DB}-date +%Y%m%d-%H%M%S.sql ${DB} < /dev/null

Telling User that Process Done For this Database
echo “Done”
done
echo

Tar command we Compress Directory
tar -czf ${BACKUP_DIR}/mysql-${NAME}.tar.gz ${BACKUP_DIR}/${NAME} >/dev/null 2>&1
echo Done

Removing UNeeded Directory
rm -rf ${BACKUP_DIR}/${NAME}

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s