backup-mysql-all-db

#!/bin/bash
LANG=C

# Variables
FTP_HOST=""
FTP_USER=""
FTP_PASS=""
DAYS_TO_KEEP=30
BACKUP_PATH="/backup/$(hostname)/sql"

CMD_DIR=$(dirname $0)
CMD_NAME=$(basename $0)
NEW_TMP_DIR="/tmp/${CMD_NAME}"
MY_CNF="/root/.my.cnf"

# Create necessary directories
mkdir -p "${NEW_TMP_DIR}" 
mkdir -p "${BACKUP_PATH}"

# Lockfile check
if [ -e ${NEW_TMP_DIR}/lock ]; then
    echo "Already running with lockfile ${NEW_TMP_DIR}/lock"
    exit 1
else
    touch ${NEW_TMP_DIR}/lock
fi

# Cleanup function
function cleanup {
    rm -rf "$NEW_TMP_DIR"
}

# Trap cleanup on exit and SIGTERM
trap 'cleanup' EXIT SIGTERM

# Retrieve MySQL credentials
USER=$(grep -m 1 'user' ${MY_CNF} | awk '{print $3}')
PASS=$(grep -m 1 'password' ${MY_CNF} | awk '{print $3}')

# Function to get Seconds_Behind_Master
get_seconds_behind_master() {
    mysql -u"$USER" -p"$PASS" -e "SHOW SLAVE STATUS\G;" | grep 'Seconds_Behind_Master' | awk '{print $2}'
}

# Wait until Seconds_Behind_Master is 0
while true; do
    SECONDS_BEHIND=$(get_seconds_behind_master)
    if [ "$SECONDS_BEHIND" -eq 0 ]; then
        echo "Replication caught up, Seconds_Behind_Master is 0."
        break
    else
        echo "Seconds_Behind_Master is $SECONDS_BEHIND, waiting..."
        sleep 5 # Wait for 5 seconds before checking again
    fi
done

# Set the DAY variable after replication has caught up
DAY=$(date +%y%m%d-%H-%M)

# Backup operations
mysql -u "$USER" -p"$PASS" -e "STOP SLAVE;"
mysql -u "$USER" -p"$PASS" -e "SHOW MASTER STATUS\G;" > "${BACKUP_PATH}/${DAY}-master-status.txt"
mysql -u "$USER" -p"$PASS" -e "SHOW SLAVE STATUS\G;" > "${BACKUP_PATH}/${DAY}-slave-status.txt"
mysql -u "$USER" -p"$PASS" -e "SHOW DATABASES" | while read DBNAME; do
    echo "Dumping $DBNAME"
    nice -n19 ionice -c 3 mysqldump --lock-tables --skip-extended-insert --single-transaction --flush-privileges --routines --triggers -u "$USER" -p"$PASS" "$DBNAME" | nice -n19 ionice -c 3 gzip > "${BACKUP_PATH}/${DAY}-${DBNAME}.sql.gz"
done
mysql -u "$USER" -p"$PASS" -e "START SLAVE;"

# Clean up old backups
find ${BACKUP_PATH}/* -type f -ctime +${DAYS_TO_KEEP} -exec nice -n19 ionice -c 3 rm -f {} \;

# Transfer all to FTP
if [ "$FTP_HOST" != "" ]; then
    trickle -u 8096 -d 8096 nice -n19 ionice -c 3 lftp -c "set ftp:list-options -a; set ssl:verify-certificate no; open ftp://$FTP_USER:$FTP_PASS@$FTP_HOST; lcd ${BACKUP_PATH}; mkdir -p ${BACKUP_PATH}; cd ${BACKUP_PATH}; mirror --reverse --delete --verbose"
fi