Shell Script : Backup MySql Databases In Compressed Format
This shell script will create dump of all databases from mysql server. Allow system administrator to have backups of all databases. The output would be in a tar.gz zipped format. Script is using mysqldump and tar command for this purpose.
Shell Script
#!/bin/bash
#Check if user input hostname, if not – exit from script
if [ "$#" = "0" ]; thenecho "Usage: `basename $0`
" exit 2
fi
#We use some variables:
#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}..."#This will get list all of databases, we use mysql command to login to server, and with awk get only
#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}... "#if database is not mysql we need to lock tables, to avoid problems when user during same time save
#something to db
if [ ${DB} != "mysql" ]; thenLOCKS="--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 #Now we will compress all dumps (stored in directory) to single file echo -n " Compressing files... " #with 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}echo "Dump of host ${MYSQL} finished"
Output
#./mysql_backup.sh localhost
Processing localhost...
Dumping mysql... Done
Dumping test... Done
Compressing files... Done
Dump of host localhost finished
#ls -la /data/
# to get list of files inside directory
total 152drwxrwxrwx 2 root root 4096 Mar 4 14:44 .
drwxr-xr-x 23 root root 4096 Mar 4 14:25 ..
-rw-r--r-- 1 yevhen yevhen 139361 Mar 4 14:44 mysql-localhost-20130304-144450.tar.gz
Unpack the tar.gz file
server# tar -zxvf mysql-localhost-20130304-144450.tar.gz
data/localhost-20130304-144450/
data/localhost-20130304-144450/test-20130304-144450.sql
data/localhost-20130304-144450/mysql-20130304-144450.sql
You might also like
Filed Under : SHELL SCRIPTS
Free Linux Ebook to Download
