Created
August 25, 2015 18:20
-
-
Save necrogami/dde89b4ad9b27b9660f2 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
# Database Backup script. | |
# Created By: Mohammed Salih | |
# Senior System Administrator | |
# Date: 21/06/2007 | |
# Modified By: Anton Swartz | |
# Senior Systems Admin/Senior PHP Developer | |
# Date: 25/08/2015 | |
# | |
# Requires pixz Multi-threaded XZ compression Tool | |
# | |
# Database credentials | |
DB_USER="username" | |
#Please append password in the xxxxx section below, note that there is | |
# no space between -p and xxxxx | |
# Remove -p password for a blank password | |
DB_PASS="-p password" | |
# DATABASE Host | |
DB_HOST="-h localhost" | |
# Get list of Databases except the pid file | |
DBS_LIST=$(echo "show databases;"|mysql $DB_HOST -u $DB_USER $DB_PASS -N) | |
# Log file | |
BAKUP_LOG=./backup/log/db-backup.log | |
# Backup Base directory | |
BASE_BAK_FLDR=./backup/db | |
# Backup rotation period. | |
RM_FLDR_DAYS="+30" | |
DATE=$(date +%Y-%m-%d_%H-%M) | |
# From here, only edit if you know what you are doing. | |
index=0 | |
# Check if we can connect to the mysql server; otherwise die | |
#if [ ! "$(id -u -n)" = "mysql" ]; then | |
# echo -e "Error:: $0 : Only user 'mysql' can run this script" | |
# exit 100 | |
#fi | |
PING=$(mysqladmin ping $DB_HOST -u $DB_USER $DB_PASS 2>/dev/null) | |
if [ "$PING" != "mysqld is alive" ]; then | |
echo "Error:: Unable to connected to MySQL Server, exiting !!" | |
exit 101 | |
fi | |
# Backup process starts here. | |
# Flush logs prior to the backup. | |
mysql $DB_HOST -u $DB_USER $DB_PASS -e "FLUSH LOGS" | |
# Loop through the DB list and create table level backup, | |
# applying appropriate option for MyISAM and InnoDB tables. | |
for DB in $DBS_LIST; do | |
DB_BKP_FLDR=$BASE_BAK_FLDR/$DATE/$DB | |
[ ! -d $DB_BKP_FLDR ] && mkdir -p $DB_BKP_FLDR | |
# Get the schema of database with the stored procedures. | |
# This will be the first file in the database backup folder | |
#this was commented out in favor of each table having it's own create statement. | |
#mysqldump $DB_HOST -u $DB_USER $DB_PASS -R -d --single-transaction $DB | pxz -c > $DB_BKP_FLDR/000-DB_SCHEMA.sql.7z | |
index=0 | |
#Get the tables and its type. Store it in an array. | |
table_types=($(mysql $DB_HOST -u $DB_USER $DB_PASS -e "show table status from $DB" | \ | |
awk '{ if ($2 == "MyISAM" || $2 == "InnoDB") print $1,$2}')) | |
table_type_count=${#table_types[@]} | |
# Loop through the tables and apply the mysqldump option according to the table type | |
# The table specific SQL files will not contain any create info for the table schema. | |
# It will be available in SCHEMA file | |
while [ "$index" -lt "$table_type_count" ]; do | |
START=$(date +%s) | |
TYPE=${table_types[$index + 1]} | |
table=${table_types[$index]} | |
echo -en "$(date) : backup $DB : $table : $TYPE " | |
if [ "$TYPE" = "MyISAM" ]; then | |
DUMP_OPT="$DB_HOST -u $DB_USER $DB_PASS $DB --hex-blob --opt --tables " | |
else | |
DUMP_OPT="$DB_HOST -u $DB_USER $DB_PASS $DB --hex-blob --opt --single-transaction --tables" | |
fi | |
mysqldump $DUMP_OPT $table |pixz > $DB_BKP_FLDR/$table.sql.7z | |
index=$(($index + 2)) | |
echo -e " - Total time : $(($(date +%s) - $START))\n" | |
done | |
done | |
# Rotating old backup. according to the 'RM_FLDR_DAYS' | |
if [ ! -z "$RM_FLDR_DAYS" ]; then | |
echo -en "$(date) : removing folder : " | |
find $BASE_BAK_FLDR/ -maxdepth 1 -mtime $RM_FLDR_DAYS -type d -exec rm -rf {} \; | |
echo | |
fi |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment