mysql_backups_using_replication

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
mysql_backups_using_replication [13.11.2025 08:56] – Pascal Sutermysql_backups_using_replication [13.11.2025 09:20] (current) – [Backup and Monitoring] Pascal Suter
Line 322: Line 322:
   0 */2 * * * /opt/dbbackup.sh >> /var/log/dbbackup.log 2>&1   0 */2 * * * /opt/dbbackup.sh >> /var/log/dbbackup.log 2>&1
 add the dbbackup.log to your logrotate config add the dbbackup.log to your logrotate config
 +
 +===== replica check script =====
 +here is a script to quickly check if the replica is working and up to date.. useful when messing around with the config for example or for monitoring purposes other than through the bakcup script: 
 +
 +<code bash checkReplica.sh>
 +#!/usr/bin/env bash
 +set -euo pipefail
 +
 +########################################
 +# Settings
 +########################################
 +
 +# Directory where your docker-compose.yml lives
 +COMPOSE_DIR="/path/to/your/project"         # <-- CHANGE THIS
 +
 +# Name of the replica service in docker-compose
 +REPLICA_SERVICE="mysql-replica"
 +
 +# Monitoring DB user (created on PRIMARY, replicated to replica)
 +MONITOR_USER="monitor"
 +MONITOR_PASSWORD="monitor-password"        # <-- CHANGE THIS
 +
 +########################################
 +# Colors
 +########################################
 +
 +if [ -t 1 ]; then
 +  RED="$(printf '\e[31m')"
 +  GREEN="$(printf '\e[32m')"
 +  YELLOW="$(printf '\e[33m')"
 +  BOLD="$(printf '\e[1m')"
 +  RESET="$(printf '\e[0m')"
 +else
 +  RED=""; GREEN=""; YELLOW=""; BOLD=""; RESET=""
 +fi
 +
 +########################################
 +# Helper
 +########################################
 +
 +# Extract "Value" from lines like:
 +# "  Some_Field: Value"
 +get_field() {
 +  local key="$1"
 +  printf '%s\n' "$STATUS" \
 +    | sed -n "s/^[[:space:]]*${key}:[[:space:]]*//p" \
 +    | head -n1
 +}
 +
 +########################################
 +# Main
 +########################################
 +
 +cd "${COMPOSE_DIR}"
 +
 +# Try SHOW REPLICA STATUS first (MySQL 8+), fall back to SHOW SLAVE STATUS (older)
 +STATUS=$(docker-compose -f "${COMPOSE_DIR}/docker-compose.yml" exec -T "${REPLICA_SERVICE}" \
 +  mysql -u"${MONITOR_USER}" -p"${MONITOR_PASSWORD}" -e "SHOW REPLICA STATUS\G" 2>/dev/null || true)
 +
 +if [[ -z "${STATUS}" ]]; then
 +  STATUS=$(docker-compose -f "${COMPOSE_DIR}/docker-compose.yml" exec -T "${REPLICA_SERVICE}" \
 +    mysql -u"${MONITOR_USER}" -p"${MONITOR_PASSWORD}" -e "SHOW SLAVE STATUS\G" 2>/dev/null || true)
 +fi
 +
 +if [[ -z "${STATUS}" ]]; then
 +  echo "${RED}${BOLD}ERROR:${RESET} No replication status found."
 +  echo "  - Is ${REPLICA_SERVICE} configured as a replica?"
 +  echo "  - Is MySQL running in that container?"
 +  exit 1
 +fi
 +
 +# IO / SQL thread status (new names first, fall back to old)
 +IO_Running=$(get_field "Replica_IO_Running")
 +if [[ -z "${IO_Running}" ]]; then
 +  IO_Running=$(get_field "Slave_IO_Running")
 +fi
 +
 +SQL_Running=$(get_field "Replica_SQL_Running")
 +if [[ -z "${SQL_Running}" ]]; then
 +  SQL_Running=$(get_field "Slave_SQL_Running")
 +fi
 +
 +# Seconds behind (new name first, fall back)
 +Seconds_Behind=$(get_field "Seconds_Behind_Source")
 +if [[ -z "${Seconds_Behind}" ]]; then
 +  Seconds_Behind=$(get_field "Seconds_Behind_Master")
 +fi
 +
 +# GTID sets – allow indent before the key
 +Executed_Gtid_Set=$(
 +  printf '%s\n' "${STATUS}" \
 +    | sed -n 's/^[[:space:]]*Executed_Gtid_Set:[[:space:]]*//p' \
 +    | head -n1
 +)
 +Retrieved_Gtid_Set=$(
 +  printf '%s\n' "${STATUS}" \
 +    | sed -n 's/^[[:space:]]*Retrieved_Gtid_Set:[[:space:]]*//p' \
 +    | head -n1
 +)
 +
 +# Errors (names are the same on old/new)
 +Last_IO_Error=$(get_field "Last_IO_Error")
 +Last_SQL_Error=$(get_field "Last_SQL_Error")
 +
 +########################################
 +# Determine overall status
 +########################################
 +
 +overall_color="${GREEN}"
 +overall_text="OK"
 +
 +if [[ "${IO_Running}" != "Yes" || "${SQL_Running}" != "Yes" ]]; then
 +  overall_color="${RED}"
 +  overall_text="ERROR"
 +elif [[ -z "${Seconds_Behind}" || "${Seconds_Behind}" == "NULL" ]]; then
 +  overall_color="${YELLOW}"
 +  overall_text="UNKNOWN_DELAY"
 +elif [[ "${Seconds_Behind}" =~ ^[0-9]+$ && "${Seconds_Behind}" -gt 300 ]]; then
 +  # More than 5 minutes behind -> warn
 +  overall_color="${YELLOW}"
 +  overall_text="LAGGING"
 +fi
 +
 +########################################
 +# Output
 +########################################
 +
 +echo
 +echo "${BOLD}Replica status for service '${REPLICA_SERVICE}':${RESET}"
 +echo "  Overall: ${overall_color}${overall_text}${RESET}"
 +echo
 +
 +echo "  IO thread:   ${IO_Running:-<unknown>}"
 +echo "  SQL thread:  ${SQL_Running:-<unknown>}"
 +
 +if [[ -z "${Seconds_Behind}" || "${Seconds_Behind}" == "NULL" ]]; then
 +  echo "  Delay:       (unknown / NULL)"
 +else
 +  echo "  Delay:       ${Seconds_Behind} seconds behind primary"
 +fi
 +
 +echo
 +echo "  Retrieved GTID set:"
 +echo "    ${Retrieved_Gtid_Set:-<none>}"
 +echo "  Executed GTID set:"
 +echo "    ${Executed_Gtid_Set:-<none>}"
 +echo
 +
 +if [[ -n "${Last_IO_Error}" && "${Last_IO_Error}" != " " ]]; then
 +  echo "${RED}  Last IO error:${RESET}"
 +  echo "    ${Last_IO_Error}"
 +  echo
 +fi
 +
 +if [[ -n "${Last_SQL_Error}" && "${Last_SQL_Error}" != " " ]]; then
 +  echo "${RED}  Last SQL error:${RESET}"
 +  echo "    ${Last_SQL_Error}"
 +  echo
 +fi
 +</code>
 +
  • mysql_backups_using_replication.txt
  • Last modified: 13.11.2025 09:20
  • by Pascal Suter