Last active
September 16, 2023 00:36
-
-
Save ybucci/3fab819af949c371478540a15e119255 to your computer and use it in GitHub Desktop.
Backup MySQL 5.6 and transfer it over rsync
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 | |
| # Script will output dumps for all databases using seperate files | |
| # Created and modifiy by yuri.bucci@outlook.com 2021/08/29 | |
| # Linkedin https://www.linkedin.com/in/yuribucci/ | |
| # Mysql Host | |
| HOST="localhost" | |
| PORT="3306" | |
| USER="root" | |
| MYSQL_PWD="secret" | |
| # Compact "true" or "false" | |
| COMPACT="true" | |
| # Compact level "-1" for fast compression and less compression or "-9" for best compression and slowest method | |
| COMPACT_LEVEL="-9" | |
| COMPACT_PASSWORD="secret" | |
| # Backup Type "multiple" or "all" | |
| BACKUP_TYPE="multiple" | |
| # Exclude Databases from "all" | |
| ExcludeDatabases="Database|information_schema|performance_schema|mysql|sys" | |
| # Databases to backup, seperate by delimiter "," | |
| DATABASES="TESTE1" | |
| # Directory of Files | |
| OUTPUT_DIR="/backups/files" | |
| #Log Dir | |
| LOG_DIR="/backups/logs" | |
| # Delete files older than X Days | |
| MTIME="7" | |
| # Delete log files older than X Days | |
| MTIME_LOGS="7" | |
| # Rsync Transfer | |
| # Create ssh keys with "ssh-keygen" | |
| # and copy it to server with "ssh-copy-id USER@SERVER" | |
| RSYNC_HOST="hostname" | |
| RSYNC_PORT="22" | |
| RSYNC_USER="usuario" | |
| RSYNC_TMP="/tmp" | |
| RSYNC_DIR_TRANSFER="/home/backups/dados" | |
| # Email configuration | |
| EMAIL_FROM="email@domain.com" | |
| EMAIL_TO="email@domain.com" | |
| EMAIL_PASSWORD="Password" | |
| EMAIL_SMTP="smtp.gmail.com:587" | |
| ######################################################################## | |
| # Functions | |
| function displaytime { | |
| local T=$1 | |
| local D=$((T/60/60/24)) | |
| local H=$((T/60/60%24)) | |
| local M=$((T/60%60)) | |
| local S=$((T%60)) | |
| (( $D > 0 )) && printf '%d days ' $D | |
| (( $H > 0 )) && printf '%d hours ' $H | |
| (( $M > 0 )) && printf '%d minutes ' $M | |
| (( $D > 0 || $H > 0 || $M > 0 )) && printf 'and ' | |
| printf '%d seconds\n' $S | |
| } | |
| check_packages () | |
| { | |
| required_version=5.6.51 | |
| local_version=$(mysqldump --version | awk '{print $5}' | tr -d ',') | |
| pkgs='zip unzip rsync libio-socket-ssl-perl libnet-ssleay-perl sendemail gzip' | |
| install=false | |
| for pkg in $pkgs; do | |
| status="$(dpkg-query -W --showformat='${db:Status-Status}' "$pkg" 2>&1)" | |
| if [ ! $? = 0 ] || [ ! "$status" = installed ]; then | |
| install=true | |
| break | |
| fi | |
| done | |
| if "$install"; then | |
| apt-get update | |
| apt-get install -y $pkgs > /dev/null 2>&1 | |
| fi | |
| if [ "$local_version" != "$required_version" ] | |
| then | |
| apt-get update > /dev/null 2>&1 | |
| apt-get remove mysql*common* mysql-client* mysql-*client* -y > /dev/null 2>&1 | |
| apt-get autoremove -y > /dev/null 2>&1 | |
| wget https://downloads.mysql.com/archives/get/p/23/file/mysql-server_5.6.51-1debian9_amd64.deb-bundle.tar -P /tmp/ > /dev/null 2>&1 | |
| cd /tmp/ > /dev/null 2>&1 | |
| tar -xvf mysql-server_5.6.51-1debian9_amd64.deb-bundle.tar > /dev/null 2>&1 | |
| dpkg -i /tmp/mysql-common_5.6.51-1debian9_amd64.deb > /dev/null 2>&1 | |
| dpkg -i /tmp/mysql-community-client_5.6.51-1debian9_amd64.deb > /dev/null 2>&1 | |
| apt-get install -f -y > /dev/null 2>&1 | |
| rm -f /tmp/*.deb > /dev/null 2>&1 | |
| rm -f /tmp/mysql-server_5.6.51-1debian9_amd64.deb-bundle.tar > /dev/null 2>&1 | |
| fi | |
| } | |
| check_status_backup () | |
| { | |
| if [ "$RESULT" -ne 0 ] | |
| then | |
| echo "FAILED - DATABASE NOT FOUND" | |
| exit 1 | |
| elif [ "$retVal" -eq 0 ] | |
| then | |
| echo "SUCCESS" | |
| else | |
| echo "FAILED" | |
| fi | |
| } | |
| send_email () | |
| { | |
| sendemail \ | |
| -f "$EMAIL_FROM" \ | |
| -u "[BACKUP] | DATABASE [$DATABASE] | STATUS [$STATUS"] \ | |
| -t "$EMAIL_TO" \ | |
| -s "$EMAIL_SMTP" \ | |
| -o tls=yes \ | |
| -xu "$EMAIL_FROM" \ | |
| -xp "$EMAIL_PASSWORD" \ | |
| -a "$LOG_FILE" \ | |
| -o message-file="$LOG_FILE" > /dev/null | |
| } | |
| log_start () | |
| { | |
| LOG_FILE="$LOG_DIR/$DATABASE-$MY_DATE.log" | |
| START=$(date +%s) | |
| echo "" >> "$LOG_FILE" | |
| echo "##############################################################" >> "$LOG_FILE" | |
| echo "START DATE: $MY_DATE" >> "$LOG_FILE" | |
| echo "STARTING BACKUP OF DATABASE: $DATABASE" >> "$LOG_FILE" | |
| echo "##############################################################" >> "$LOG_FILE" | |
| echo "" >> "$LOG_FILE" | |
| } | |
| log_finish () | |
| { | |
| FILE_SIZE=$(stat -c %s "$FILE" | numfmt --to iec --format "%8.2f") | |
| TIME_TAKEN=$(displaytime "$DIFF") | |
| echo "" >> "$LOG_FILE" | |
| echo "##############################################################" >> "$LOG_FILE" | |
| echo "BACKUP FINALIZED OF DATABASE: $DATABASE" >> "$LOG_FILE" | |
| echo "END DATE: $MY_DATE" >> "$LOG_FILE" | |
| echo "TIME TAKEN OF EXECUTION $TIME_TAKEN" >> "$LOG_FILE" | |
| echo "SIZE OF BACKUP $FILE_SIZE" >> "$LOG_FILE" | |
| echo "##############################################################" >> "$LOG_FILE" | |
| echo "" >> "$LOG_FILE" | |
| } | |
| pre_dump () | |
| { | |
| MY_DATE=$(date +%Y-%m-%d-%H-%M-%S) | |
| LOG_FILE="$LOG_DIR/$DATABASE-$MY_DATE.log" | |
| log_start "$LOG_FILE" "$DATABASE" "$MY_DATE" | |
| START=$(date +%s) | |
| } | |
| post_dump() | |
| { | |
| MY_DATE=$(date +%Y-%m-%d-%H-%M-%S) | |
| END=$(date +%s) | |
| DIFF=$(echo "$END - $START" | bc) | |
| log_finish "$LOG_FILE" "$DATABASE" "$MY_DATE" "$DIFF" "$FILE" | |
| send_email "$DATABASE" "$STATUS" "$LOG_FILE" | |
| } | |
| msyql_dump_exec () | |
| { | |
| if [ "$BACKUP_TYPE" == "multiple" ]; then | |
| IFS=',' | |
| read -ra database_array <<< "$DATABASES" | |
| if [ "$COMPACT" == "true" ]; then | |
| for DATABASE in "${database_array[@]}"; | |
| do | |
| RESULT=$(mysql --defaults-extra-file="$credentialsFile" -P "$PORT" --batch --skip-column-names -e "SHOW DATABASES LIKE '"$DATABASE"';" | grep "$DATABASE" > /dev/null; echo "$?") | |
| FILE="$OUTPUT_DIR/$DATABASE-$MY_DATE.sql" | |
| pre_dump "$DATABASE" | |
| $MYSQLDUMP --defaults-extra-file=$credentialsFile -P $PORT --single-transaction --quick --skip-lock-tables --routines --events --default-character=utf8 --column-statistics=0 --verbose "$DATABASE" 2>"$LOG_FILE" > "$FILE" | |
| retVal=$? | |
| zip "$COMPACT_LEVEL" -e -j -m "$FILE".zip "$FILE" -P "$COMPACT_PASSWORD" > /dev/null 2>&1 | |
| STATUS=$(check_status_backup "$DATABASE" "$RESULT" "$retVal") | |
| FILE="$FILE.zip" | |
| post_dump "$STATUS" "$DATABASE" "$FILE" | |
| send_rsync "$FILE" | |
| done | |
| elif [ "$COMPACT" == "false" ]; then | |
| for DATABASE in "${database_array[@]}"; | |
| do | |
| RESULT=$(mysql --defaults-extra-file="$credentialsFile" -P "$PORT" --batch --skip-column-names -e "SHOW DATABASES LIKE '"$DATABASE"';" | grep "$DATABASE" > /dev/null; echo "$?") | |
| FILE="$OUTPUT_DIR/$DATABASE-$MY_DATE.sql" | |
| pre_dump "$DATABASE" | |
| $MYSQLDUMP --defaults-extra-file=$credentialsFile -P $PORT --single-transaction --quick --skip-lock-tables --routines --events --default-character=utf8 --column-statistics=0 --verbose "$DATABASE" 2>"$LOG_FILE" > "$FILE" | |
| retVal=$? | |
| STATUS=$(check_status_backup "$DATABASE" "$RESULT" "$retVal") | |
| post_dump "$STATUS" "$DATABASE" "$FILE" | |
| send_rsync "$FILE" | |
| done | |
| fi | |
| fi | |
| if [ "$BACKUP_TYPE" == "all" ]; then | |
| databases=`mysql --defaults-extra-file=$credentialsFile -e "SHOW DATABASES;" | tr -d "| " | egrep -v $ExcludeDatabases` | |
| if [ "$COMPACT" == "true" ]; then | |
| for DATABASE in $databases; do | |
| RESULT=$(mysql --defaults-extra-file="$credentialsFile" -P "$PORT" --batch --skip-column-names -e "SHOW DATABASES LIKE '"$DATABASE"';" | grep "$DATABASE" > /dev/null; echo "$?") | |
| FILE="$OUTPUT_DIR/$DATABASE-$MY_DATE.sql.gz" | |
| pre_dump "$DATABASE" | |
| $MYSQLDUMP --defaults-extra-file=$credentialsFile -P $PORT --single-transaction --quick --skip-lock-tables --routines --events --default-character=utf8 --column-statistics=0 --verbose "$DATABASE" 2>"$LOG_FILE" > "$FILE" | |
| retVal=$? | |
| zip "$COMPACT_LEVEL" -e -j -m "$FILE".zip "$FILE" -P "$COMPACT_PASSWORD" > /dev/null 2>&1 | |
| STATUS=$(check_status_backup "$DATABASE" "$RESULT" "$retVal") | |
| FILE="$FILE.zip" | |
| post_dump "$STATUS" "$DATABASE" "$FILE" | |
| send_rsync "$FILE" | |
| done | |
| elif [ "$COMPACT" == "false" ]; then | |
| for DATABASE in $databases; do | |
| RESULT=$(mysql --defaults-extra-file="$credentialsFile" -P "$PORT" --batch --skip-column-names -e "SHOW DATABASES LIKE '"$DATABASE"';" | grep "$DATABASE" > /dev/null; echo "$?") | |
| FILE="$OUTPUT_DIR/$DATABASE-$MY_DATE.sql" | |
| pre_dump "$DATABASE" | |
| $MYSQLDUMP --defaults-extra-file=$credentialsFile -P $PORT --single-transaction --quick --skip-lock-tables --routines --events --default-character=utf8 --column-statistics=0 --verbose "$DATABASE" 2>"$LOG_FILE" > "$FILE" | |
| retVal=$? | |
| STATUS=$(check_status_backup "$DATABASE" "$RESULT" "$retVal") | |
| post_dump "$STATUS" "$DATABASE" "$FILE" | |
| send_rsync "$FILE" | |
| done | |
| fi | |
| fi | |
| } | |
| send_rsync () | |
| { | |
| if [ "$COMPACT" == "true" ]; then | |
| rsync -avz $FILE $RSYNC_USER@$RSYNC_HOST:$RSYNC_DIR_TRANSFER/ -T $RSYNC_TMP --port="$RSYNC_PORT" | |
| fi | |
| if [ "$COMPACT" == "false" ]; then | |
| rsync -avz $FILE $RSYNC_USER@$RSYNC_HOST:$RSYNC_DIR_TRANSFER/ -T $RSYNC_TMP --port="$RSYNC_PORT" | |
| fi | |
| } | |
| ######################################################################## | |
| check_packages | |
| # Check if dir already exist if not create then | |
| [ ! -d $OUTPUT_DIR ] && mkdir -p $OUTPUT_DIR | |
| [ ! -d $LOG_DIR ] && mkdir -p $LOG_DIR | |
| ######################################################################## | |
| # System Variables | |
| MYSQL="$(which mysql)" | |
| MYSQLDUMP="$(which mysqldump)" | |
| GZIP="$(which gzip)" | |
| MY_DATE=$(date +%Y-%m-%d-%H:%M:%S) | |
| credentialsFile=/tmp/mysql-credentials.cnf | |
| echo "[client]" > $credentialsFile | |
| echo "user=$USER" >> $credentialsFile | |
| echo "password=$MYSQL_PWD" >> $credentialsFile | |
| echo "host=$HOST" >> $credentialsFile | |
| ######################################################################## | |
| find $OUTPUT_DIR/ -type f -mtime +$MTIME -name '*.gz' -exec rm -f {} \; | |
| find $OUTPUT_DIR/ -type f -mtime +$MTIME -name '*.gz' -exec rm -f {} \; | |
| find $LOG_DIR/ -type f -mtime +$MTIME -name '*.log' -exec rm -f {} \; | |
| msyql_dump_exec | |
| rm -f $credentialsFile |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment