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
Next revision		 Previous revision
mysql_backups_using_replication [13.11.2025 02:01] – [mysql backups using replication] Pascal Sutermysql_backups_using_replication [13.11.2025 09:20] (current) – [Backup and Monitoring] Pascal Suter
Line 3: Line 3:
  
 in my case i had a docker-compose.yml which contains the php web app and a mysql server service. i first copied the service in the ''docker-compose.yml'' file to a ''mysql-replica'' service, so that the respective sections look like this:  in my case i had a docker-compose.yml which contains the php web app and a mysql server service. i first copied the service in the ''docker-compose.yml'' file to a ''mysql-replica'' service, so that the respective sections look like this: 
-<code>+<code yaml>
   mysql:   mysql:
 #    image: mysql:8.0 #    image: mysql:8.0
Line 40: Line 40:
  
 for the primary server, make sure the config contains the following settings:  for the primary server, make sure the config contains the following settings: 
-<code>+<code conf>
 [mysqld] [mysqld]
 server-id = 1 server-id = 1
Line 58: Line 58:
  
 and on the replica the settings should look like this:  and on the replica the settings should look like this: 
-<code>+<code conf>
 [mysqld] [mysqld]
 server-id = 2 server-id = 2
Line 64: Line 64:
 relay_log = relay-bin relay_log = relay-bin
  
-# keep binlog on replica too (useful for cascading replication, backups, etc.) +# disable binary logging as we don't need this on the replica 
-log_bin = mysql-bin +skip-log-bin
-binlog_format = ROW+
  
 +# GTID functionality is needed for replication
 gtid_mode = ON gtid_mode = ON
 enforce_gtid_consistency = ON enforce_gtid_consistency = ON
-log_slave_updates = ON 
  
 # make it read-only for safety # make it read-only for safety
Line 77: Line 76:
  
 # don't auto-start replication until we finish setup # don't auto-start replication until we finish setup
 +# then comment this out
 skip_slave_start = ON skip_slave_start = ON
 </code> </code>
Line 154: Line 154:
  
 # Directory where your docker-compose.yml lives # Directory where your docker-compose.yml lives
-COMPOSE_DIR="/path/to/your/project"   # <-- CHANGE THIS+COMPOSE_DIR="/opt/buchmann-prod"   # <-- CHANGE THIS
  
 # Name of the replica service in docker-compose # Name of the replica service in docker-compose
Line 168: Line 168:
 MAX_BACKUPS=24 MAX_BACKUPS=24
  
-# Uptime Kuma +# Uptime Kuma entity ID 
-UPTIME_SERVER="my.server.net"         # <-- CHANGE THIS +ENTITY_ID="2N88TDy5YeCX7n3cJD5Wok9DNZhdRlw6"       # <-- CHANGE THIS
-ENTITY_ID="YOUR_ENTITY_ID_HERE"       # <-- CHANGE THIS (create new monitor of type "push" and copy the id from there)+
  
 # Monitoring DB user (created on the PRIMARY, replicated to replica) # Monitoring DB user (created on the PRIMARY, replicated to replica)
 MONITOR_USER="monitor" MONITOR_USER="monitor"
-MONITOR_PASSWORD="monitor-password"   # <-- CHANGE THIS or move to env+MONITOR_PASSWORD="jookeg4ahr0eidienaiGhe8nieGo1u"   # <-- CHANGE THIS or move to env
  
 # State file to track last Executed_Gtid_Set # State file to track last Executed_Gtid_Set
Line 211: Line 210:
  
   io_running=$(printf '%s\n' "${status}" | awk -F: '/Slave_IO_Running/ {gsub(/^[ \t]+/, "", $2); print $2}')   io_running=$(printf '%s\n' "${status}" | awk -F: '/Slave_IO_Running/ {gsub(/^[ \t]+/, "", $2); print $2}')
-  sql_running=$(printf '%s\n' "${status}" | awk -F: '/Slave_SQL_Running/ {gsub(/^[ \t]+/, "", $2); print $2}')+  sql_running=$(printf '%s\n' "${status}" | awk -F: '/Slave_SQL_Running:/ {gsub(/^[ \t]+/, "", $2); print $2}')
   seconds_behind=$(printf '%s\n' "${status}" | awk -F: '/Seconds_Behind_Master/ {gsub(/^[ \t]+/, "", $2); print $2}')   seconds_behind=$(printf '%s\n' "${status}" | awk -F: '/Seconds_Behind_Master/ {gsub(/^[ \t]+/, "", $2); print $2}')
-  executed_gtid=$(printf '%s\n' "${status}" | awk -F: '/Executed_Gtid_Set/ {sub(/^[ \t]+/, "", $2); print $2}') +  executed_gtid=$( 
 +  printf '%s\n' "${status}" | \ 
 +    sed -n 's/^ *Executed_Gtid_Set:[[:space:]]*//p' 
 +  )
   echo "[$(date)] Slave_IO_Running=${io_running}, Slave_SQL_Running=${sql_running}, Seconds_Behind_Master=${seconds_behind}"   echo "[$(date)] Slave_IO_Running=${io_running}, Slave_SQL_Running=${sql_running}, Seconds_Behind_Master=${seconds_behind}"
  
Line 255: Line 256:
   msg="${msg// /_}"   msg="${msg// /_}"
  
-  local url="https://${UPTIME_SERVER}/api/push/${ENTITY_ID}?status=${status}&msg=${msg}&ping=${walltime}"+  local url="https://uptime.dalco.ch/api/push/${ENTITY_ID}?status=${status}&msg=${msg}&ping=${walltime}"
  
   echo "[$(date)] Sending uptime ping: ${url}"   echo "[$(date)] Sending uptime ping: ${url}"
Line 294: Line 295:
 docker-compose stop "${REPLICA_SERVICE}" docker-compose stop "${REPLICA_SERVICE}"
  
-# 3) Create tar.bz2 backup+# 3) Create tar.gz backup
 TIMESTAMP="$(date +%Y%m%d-%H%M%S)" TIMESTAMP="$(date +%Y%m%d-%H%M%S)"
-BACKUP_FILE="${BACKUP_DIR}/mysql-replica-${TIMESTAMP}.tar.bz2"+BACKUP_FILE="${BACKUP_DIR}/mysql-replica-${TIMESTAMP}.tar.gz"
  
-echo "[$(date)] Creating tar.bz2 from ${REPLICA_DATA_DIR} -> ${BACKUP_FILE}..." +echo "[$(date)] Creating tar.gz from ${REPLICA_DATA_DIR} -> ${BACKUP_FILE}..." 
-tar -cjf "${BACKUP_FILE}" -C "${REPLICA_DATA_DIR}" .+tar -czf "${BACKUP_FILE}" -C "${REPLICA_DATA_DIR}" .
  
 echo "[$(date)] Backup archive created: ${BACKUP_FILE}" echo "[$(date)] Backup archive created: ${BACKUP_FILE}"
Line 305: Line 306:
 # 4) Rotate old backups # 4) Rotate old backups
 echo "[$(date)] Rotating old backups (keeping last ${MAX_BACKUPS})..." echo "[$(date)] Rotating old backups (keeping last ${MAX_BACKUPS})..."
-mapfile -t BACKUPS < <(ls -1t "${BACKUP_DIR}"/mysql-replica-*.tar.bz2 2>/dev/null || true)+mapfile -t BACKUPS < <(ls -1t "${BACKUP_DIR}"/mysql-replica-*.tar.gz 2>/dev/null || true)
  
 if (( ${#BACKUPS[@]} > MAX_BACKUPS )); then if (( ${#BACKUPS[@]} > MAX_BACKUPS )); then
Line 316: Line 317:
 echo "[$(date)] Backup finished successfully." echo "[$(date)] Backup finished successfully."
 </code> </code>
 +**NOTE** you may want to use gz instead of bz2 in case it takes too long
 +
 +here is the cron job: 
 +  0 */2 * * * /opt/dbbackup.sh >> /var/log/dbbackup.log 2>&1
 +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.1762995697.txt.gz
  • Last modified: 13.11.2025 02:01
  • by Pascal Suter