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 08:24] – [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 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.1763018697.txt.gz
  • Last modified: 13.11.2025 08:24
  • by Pascal Suter