MySQL backup

Script

#!/bin/bash

# usage: ./mysql_backup.sh <username> <password> <database_name>
if [ $# -ne 3 ]; then
  echo "Error: Missing required parameters"
  echo "Usage: $0 <username> <password> <database_name>"
  exit 1
fi

# configuration
MYSQL_HOST="<ip_address>"
MYSQL_PORT="3306"
MYSQL_USER="$1"
MYSQL_PASS="$2"
DATABASE_NAME="$3"
BACKUP_DIR="/mnt/backup"
BACKUP_FILE="$BACKUP_DIR/pms.sql"
TEMP_BACKUP_FILE="$BACKUP_DIR/backup_temp.sql"

# check if backup directory exists
if [ ! -d "$BACKUP_DIR" ]; then
  echo "Error: Backup directory $BACKUP_DIR does not exist"
  exit 1
fi

# check if backup directory is writable
if [ ! -w "$BACKUP_DIR" ]; then
  echo "Error: Backup directory $BACKUP_DIR is not writable"
  exit 1
fi

# test mysql connection before proceeding
mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SELECT 1;" "$DATABASE_NAME" >/dev/null 2>&1
if [ $? -ne 0 ]; then
  echo "Error: Cannot connect to MySQL database"
  exit 1
fi

# Remove old backup file if it exists
if [ -f "$BACKUP_FILE" ]; then
  rm "$BACKUP_FILE"
fi

# perform database dump with optimized settings for minimal locking
# --extended-insert:    Use multiple-row INSERT syntax for faster restores
# --lock-tables=false:  Don't lock tables (relies on single-transaction)
# --quick:              Retrieve rows one at a time rather than buffering entire result set
# --routines:           Include stored procedures and functions
# --single-transaction: Uses consistent read for InnoDB tables
# --triggers:           Include triggers
mysqldump \
  -h "$MYSQL_HOST" \
  -P "$MYSQL_PORT" \
  -u "$MYSQL_USER" \
  -p"$MYSQL_PASS" \
  --default-character-set=utf8mb4 \
  --extended-insert \
  --lock-tables=false \
  --no-tablespaces \
  --quick \
  --routines \
  --single-transaction \
  --triggers \
  "$DATABASE_NAME" > "$TEMP_BACKUP_FILE"

# check if mysqldump completed successfully
if [ $? -eq 0 ] && [ -s "$TEMP_BACKUP_FILE" ]; then

  # move temporary backup to final location
  mv "$TEMP_BACKUP_FILE" "$BACKUP_FILE"

  # return succesfull backup
  echo "Backup completed successfully: $BACKUP_FILE"
  echo "Backup size: $(du -h "$BACKUP_FILE" | cut -f1)"
  exit 0
else

  # clean up temporary file on failure
  if [ -f "$TEMP_BACKUP_FILE" ]; then
    rm "$TEMP_BACKUP_FILE"
  fi

  # return failed backup
  echo "Error: Database backup failed"
  exit 1
fi