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 00:13] – [Backup and Monitoring] 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 126: Line 126:
 you can also see the the executed and retrieved Gtid dataset, they should update as the master is writing data to the db.  you can also see the the executed and retrieved Gtid dataset, they should update as the master is writing data to the db. 
  
 +now remove the ''skip_slave_start = ON'' line or comment it out in the ''conf/mysql-replica/replica.cnf'' file and restart the container, then re-check if it is still syncing. 
 ===== Backup and Monitoring ===== ===== Backup and Monitoring =====
 now lets create a backup script. this script will first check if the replication is still running and updated before it shuts down the replica container and creates a tar.gz file of the database data directory. on success, it will notify a uptime kuma push monitor.  now lets create a backup script. this script will first check if the replication is still running and updated before it shuts down the replica container and creates a tar.gz file of the database data directory. on success, it will notify a uptime kuma push monitor. 
Line 153: 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 167: 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 210: 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 254: 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 293: 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 304: 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 315: 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.1762989188.txt.gz
  • Last modified: 13.11.2025 00:13
  • by Pascal Suter