Staging Cluster

Support Status Test Case
Production 1
Version 3.0.13

A staging cluster is a child cluster of a production cluster containing 3 nodes, one replica is detached from replication and proxies track the the status of the standalone node to enable dev team to test on production data

Creating a staging or a child cluster (since 3.1.44)

A staging cluster and a child cluster of a cluster are created in one call, on the same replication-manager as the parent, on OpenSVC or Kubernetes:

  • Dashboard: cluster menu Provision > New Staging Cluster or New Child Cluster. The plan is shown first, or why it is refused.
  • API: POST /api/clusters/{parent}/actions/create-staging or create-child, body {"name", "dbImage", "dbCount", "confirm"}; confirm false returns the plan.
  • MCP: cluster-create-staging, cluster-create-child.
Staging Child
Use the dev team tests on production data test a migration (another release) under the parent's real writes
Default name <parent>-staging <parent>-child
Databases 3 2
Own replication-source-name staging child
After the seed one replica detached: 1 Master, 1 Slave, 1 StandAlone, the proxy tracking the StandAlone every node keeps replicating

What the creation does:

  1. Checks the parent first: it must hold a logical backup (mysqldump or mydumper) on its backup server. Without it nothing is created: take a logical backup of the parent first.
  2. Creates the cluster with the parent's orchestrator, database image (unless another release is given), DBU per node, proxy type and proxy credential, logical backup tool, and the parent's monitoring and replication accounts (same name, same password), the parent as multisource head cluster. Since 3.1.45:
    • its GTID domain is the CRC32 of its own name, like any cluster (it was the parent's
      • 1: every child of a parent shared one domain). A plan whose domain is the parent's is refused: choose another name;
    • its databases are sized from the DBU before they are provisioned (1 DBU = 1 core, 4 GB, 20 GB, 1000 IOPS): 2 DBU per node give 2 cores, 8 GB, 40 GB and 2000 IOPS, so innodb_io_capacity_max 2000 (it kept the defaults, 300 IOPS, whatever the DBU).
  3. Bootstraps a regular master-slave under its own replication-source-name (staging or child).
  4. Restores the parent's backup on its master, data only: the system tables of its own release are kept, no account is loaded from the parent. Its replicas receive the restore through replication (see The restore on the master). Its master then replicates from the parent's master under the parent's replication-source-name, the same name on both sides: the two names must differ. Its position combines the backup's position in the parent's domain with its own current position in its own domain.
  5. For a staging cluster, detaches one replica as the staging server, once that replica has applied all the master wrote (see below).

The progress is the state CINF0014 on the new cluster; WARN0239 tells the step where it stopped and why. A cluster whose creation stopped is kept: fix the cause and reseed it from its parent, or drop it.

Rights: cluster-staging (staging) or cluster-replication (child) on the parent, and the right to create a cluster.

Reseeding a staging or a child cluster from its parent (since 3.1.45)

Bootstrap Staging From Parent (API POST /api/clusters/{staging}/actions/staging-reseed-from-parent) reloads an existing staging cluster with the parent's data of now:

  1. Stops the master's replication from the parent: its position in the parent's GTID domain stops moving.
  2. Takes a fresh logical backup of the parent when the last one is behind that position. A reseed never takes the parent's domain back: the master already holds in its binary log the later transactions of the parent it replicated, and with gtid_strict_mode it refuses to write an older one after them (error 1950). The fresh backup is past that position, the parent's domain only moves forward. A new cluster holds none of the parent's domain: its creation does not need it. MariaDB.
  3. Restores that backup on the master and points its replication from the parent at the backup's position, as at the creation.
  4. Refreshes the staging server: a replica that applied all the master wrote is detached as the new staging server, and the former staging server is reloaded from the staging master's own backup, taken after its restore, never from the parent's: it holds the restored data and the master's position in every domain, so the former staging server does not replay the restore through replication.

The restore on the master

A backup restored on a master with the binary log on goes to every replica through its binary log. While it runs (since 3.1.45):

  • The binary logs stay bounded: on MariaDB 11.4 and later, max_binlog_total_size is held at 2G on the master and its replicas, and slave_connections_needed_for_purge at 0 on the replicas (with the default 1, a server with no replica of its own never purges); MariaDB purges by itself. The values come back at the end: the plan's max_binlog_total_size under the dynamic config, the one found otherwise.
  • The restore pauses on each binary log rotation until every replica applied the rotated binary log with no delay (Seconds_Behind_Master 0). A replica whose replication is stopped, or delayed on purpose (hosts-delayed, MASTER_DELAY), does not take part. WARN0254 names the replica the restore waits for. There is no timer: a restore can take days.
  • Semi-sync is paused on the master (rpl_semi_sync_master_enabled, rpl_semi_sync_source_enabled on MySQL and Percona 8.0.26 and later) and set back on at the end, only when it was on: the pause already holds the restore for the replicas, and every commit of the restore waited for an acknowledgement (a 858 MB backup took 16m51s with it, 12m37s without).
  • The restore ends once every replica reached the master's position read when the restore wrote its last transaction, and a staging replica is detached only then. A replica whose applied position does not move for 90 s, a broken replication or an SQL error ends that wait.

Cancelling a restore: a running logical reseed, from the parent or from the cluster's own backup, single file or splitdump, stops with GET /api/clusters/{cluster}/servers/{server}/actions/job-cancel/reseedmysqldump, also while it is paused on a rotation. The binary log limit and semi-sync come back at once, without waiting for the replicas. The data is then incomplete: the server's replication stays stopped, WARN0252 says so until a reseed of that server ends. A reseed from the parent refused before anything was loaded (no parent backup, no parent master, a reseed already running) starts the master's replication from the parent again.

Outside a restore, prov-db-binlog-total-size-pct (default 25) gives max_binlog_total_size as that share of prov-db-disk-size, never under 2G (template variable %%ENV:SVC_CONF_ENV_MAX_BINLOG_TOTAL_SIZE%%, MariaDB 11.4 and later): the binary logs bounded by the plan, not only by prov-db-expire-log-days, once the replication ruleset of the configurator sets it.

4.4.12.1 Staging config important setup :

## 4.4.12.2 To be change when proxy tracking state is enable
haproxy = false

## 4.4.12.3 Specify an other domain for staging
prov-db-domain = "3"

## 4.4.12.4  Specify an other source of replication for the staging cluster
replication-source-name = "staging"

## 4.4.12.5 Point the production cluster to enable mutti-tiers cluster master prod to master staging extra replication source
replication-multisource-head-cluster="prod-cluster"

## 4.4.12.6 Proxy Upgrade script
db-servers-state-change-script = "/data/repman/script/database_state_change.sh"

## 4.4.12.7 To enable ssh to database and proxy hosts, use ssh-keygen ssh-copy-id for first deployment
onpremise-ssh = true
onpremise-ssh-credential = "root:"
onpremise-ssh-private-key = "/root/.ssh/id_rsa_preprod"
scheduler-jobs-ssh = true

## 4.4.12.8  To automate logical backup  
monitoring-scheduler = true

4.4.12.9 Lesson learns:

  • Production and staging monitoring and replication users should have same name same password so that 2 similar users does not get different password in staging after an initial restore from production
  • Mydumper dist version test your restore

4.4.12.10 Todo :

  • New cluster variable --topology-staging bool
  • New cluster variable --topology-staging-refresh-script
  • New cluster variable --topology-staging-post-detach-script ( obfuscation data do whatever have to be done in db)
  • Cluster menu api call to trigger: Staging refresh

Api call should trigger similar script that will comes embedded in share

#!/bin/bash

# Variables
REPLICATION_MANAGER_USER="admin"
REPLICATION_MANAGER_PASSWORD="xxx"
REPLICATION_MANAGER_URL="https://repman-01:10005"
REPLICATION_MANAGER_CLUSTER_NAME="staging"
REPLICATION_MANAGER_HOST_NAME=$1
REPLICATION_MANAGER_HOST_PORT="3306"
NB_SLAVES=0

##### This bloc is for getting the replication-manager token
GET="wget -q --no-check-certificate -O- --header Content-Type:application/json"
AUTH_DATA="{\"username\": \"$REPLICATION_MANAGER_USER\", \"password\": \"$REPLICATION_MANAGER_PASSWORD\"}"
TOKEN=$($GET --post-data "$AUTH_DATA" --header Accept:text/html $REPLICATION_MANAGER_URL/api/login)

function get {
    $GET --header Accept:application/json --header "Authorization: Bearer $TOKEN" "$@"
}

# Counting the slaves, depending, we will play one of the two following scenarios
NB_SLAVES=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/slaves | jq length)
echo $NB_SLAVES

# Scenario 1 : 2 slaves, then we will stop the replication on one that will be the "staging"  
if [ $NB_SLAVES -eq 2 ]; then
  echo "picking first slave \n"
  ID=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/slaves | jq '.[1].id' | sed 's/"//g' )
  PORT=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/slaves | jq '.[1].port')
  echo "$ID:$PORT"
  echo "Reseting first slave \n"
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID/actions/reset-slave-all
  sleep 2
  echo "Stopping first server slave replication \n"
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID/actions/stop-slave
  sleep 20
  echo "Stopping first mariadb server \n"
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID/actions/stop
  echo "Starting first mariadb server \n"
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID/actions/start
  loop=true
  while $loop; do
    sleep 1
    IO_THREADS=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/slaves | jq '.[1].replications[0].slaveIoRunning.String')
    SQL_THREADS=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/slaves | jq '.[1].replications[0].slaveSqlRunning.String')
    echo $IO_THREADS
    echo $SQL_THREADS
    if [ "$IO_THREADS" == "\"No\"" ] || [ "$SQL_THREADS" == "\"No\"" ]; then
      loop=false
    fi
  done

  echo "Getting and saving slaves initial statuses \n"
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/slaves | jq '.[1].replications'>replications.save

  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID/actions/reset-slave-all
  sleep 2
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID/actions/stop
  echo "Waiting for database server $ID to status failed \n"
  loop=true
  while $loop; do
    ID_CHECK=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/servers | jq -c '.[] | select(.state == "Failed").id' | sed 's/"//g')
    if [ "$ID_CHECK" == "$ID" ]; then
      loop=false
    fi
  done
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID/actions/start
fi

if [ $NB_SLAVES -eq 1 ]; then
  echo "picking last slave and founding standalone \n"
  ID=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/servers | jq -c '.[] | select( .state == "StandAlone" ).id' | sed 's/"//g')
  echo "found standalone server $ID \n"
  echo "reseting master position on standalone \n"
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID/actions/reset-master
  echo "setup replication manager for reseeding \n"
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/settings/actions/switch/autoseed
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/settings/actions/switch/autorejoin-logical-backup
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/settings/actions/switch/autorejoin-force-restore
  sleep 2
  echo "Stopping database server $ID \n"
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID/actions/stop
  echo "Waiting for database server $ID to status failed \n"
  loop=true
  while $loop; do
    ID_CHECK=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/servers | jq -c '.[] | select(.state == "Failed").id' | sed 's/"//g')
    if [ "$ID_CHECK" == "$ID" ]; then
      loop=false
    fi
  done
  echo "Start database server $ID \n"
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID/actions/start
  sleep 20
  echo "Reseting replciation manager settings for reseed = false \n"
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/settings/actions/switch/autoseed
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/settings/actions/switch/autorejoin-logical-backup
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/settings/actions/switch/autorejoin-force-restore

###### Now set last slave as standalone

# Get the last available slave
ID_SLAVE=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/servers | jq -c '.[] | select(.state == "Slave").id' | sed 's/"//g')
  echo "last slave found for staging $ID_SLAVE \n"
  echo "Stopping replication on last slave \n"
  get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID_SLAVE/actions/stop-slave
  loop=true
  while $loop; do
    echo "Waiting replication to stop \n"
    sleep 5
    IO_THREADS=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/slaves | jq --arg id "$ID_SLAVE" '.[] | select(.id == $id).replications[0].slaveIoRunning.String')
    SQL_THREADS=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/slaves | jq --arg id "$ID_SLAVE" '.[] | select(.id == $id).replications[0].slaveSqlRunning.String')
#    IO_THREADS=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/slaves | jq '.[] | select( .id == $ID_SLAVE ).replications[0].slaveIoRunning.String')
#    SQL_THREADS=$(get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/slaves | jq '.[] | select( .id == $ID_SLAVE ).replications[0].slaveSqlRunning.String')
    echo $IO_THREADS
    echo $SQL_THREADS
    if [ "$IO_THREADS" == "\"No\"" ] || [ "$SQL_THREADS" == "\"No\"" ]; then
      loop=false
    fi
    done
    echo "Saving replication info in replication.save \n"
    get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/slaves | jq --arg id "$ID_SLAVE" '.[] | select(.id == $id).replications' > replications.save
#    get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/topology/slaves | jq '.[] | select( .id == $ID_SLAVE ).replications'>replications.save
    echo "Reset all replication information \n"
    get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID_SLAVE/actions/reset-slave-all
    sleep 2
    get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID_SLAVE/actions/stop
    sleep 2
    get $REPLICATION_MANAGER_URL/api/clusters/$REPLICATION_MANAGER_CLUSTER_NAME/servers/$ID_SLAVE/actions/start
fi
  • Adapt the script to not stop start the database nodes
  • Adapt the script to use env variables deploy in the bash instead of hardcoded variables

Today we can customize status change script to trigger some dedicated proxy config reload bu need native proxy integration

4.4.12.11 To be removed after integration

database_state_change.sh

#!/bin/bash
# This script is given as sample and will be overwrite on upgrade
# db-servers-state-change-script
echo "Database state change script args"
echo "Script:$0, Cluster:$1, Host:$2, Port:$3, State:$4, OldState:$5"
if [ "$4" = "StandAlone" ]; then
        scp /data/repman/script/ha.cfg.$2 root@staging-proxy:/etc/haproxy/haproxy.cfg
        ssh root@staging-proxy "systemctl reload haproxy"
fi

4.4.12.12 Todo

  • New cluster variables haproxy more variables haproxy-staging-port, haproxy-staging-bind, haproxy-staging-backend
  • New cluster variable proxysql-write-track-state="master|standalone" , proxysql-read-track-state="slave|standalone"