Restore

SET PASSWORD FOR 'root'@'%' = 'auth_string';

./restore-single-db.sh <SECRET_NAME_FROM> <SECRET_NAME_TO> <NAMESPACE>

#!/usr/bin/bash

set -e

function ka() {
    kubectl --as "system:admin" "$@"
}

SECRET_NAME_FROM=$1
if [[ -z "$SECRET_NAME_FROM" ]]; then
  read -rp "Enter the secret name to copy the database from: " SECRET_NAME_FROM
fi
if [[ -z "$SECRET_NAME_FROM" ]]; then
  echo "Error: secret name cannot be empty."
  exit 1
fi

SECRET_NAME_TO=$2
if [[ -z "$SECRET_NAME_TO" ]]; then
  read -rp "Enter the secret name to copy the database to: " SECRET_NAME_TO
fi
if [[ -z "$SECRET_NAME_TO" ]]; then
  echo "Error: secret name cannot be empty."
  exit 1
fi

NAMESPACE=$3
if [[ -z "$NAMESPACE" ]]; then
    read -rp "Enter the namespace to perform the action in: " NAMESPACE
fi
if [[ -z "$NAMESPACE" ]]; then
  echo "Error: Namespace cannot be empty."
  exit 1
fi

DB_NAME=$(ka --namespace "$NAMESPACE" get secret "$SECRET_NAME_FROM" -ojson | jq -r '.data.MARIADB_DB' | base64 -d)
if [[ -z "$DB_NAME" ]]; then
  echo "Error: DB_NAME not found."
  exit 1
fi

POD_NAME="mariadb-dump-pod-${RANDOM}"

echo "Creating MariaDB dump pod '$POD_NAME' in namespace '$NAMESPACE'..."

cat <<EOF | ka --namespace "$NAMESPACE"  apply -f -
apiVersion: v1
kind: Pod
metadata:
  creationTimestamp: null
  name: $POD_NAME
spec:
  containers:
  - name: mariadb
    image: mariadb:11.8
    command:
    - tail
    - "-f"
    - /dev/null
    envFrom:
    - secretRef:
        name: $SECRET_NAME_FROM
    env:
    - name: TO_MARIADB_HOST
      valueFrom:
        secretKeyRef:
          name: $SECRET_NAME_TO
          key: MARIADB_HOST
    - name: TO_MARIADB_DB
      valueFrom:
        secretKeyRef:
          name: $SECRET_NAME_TO
          key: MARIADB_DB
    - name: TO_MARIADB_USERNAME
      valueFrom:
        secretKeyRef:
          name: $SECRET_NAME_TO
          key: MARIADB_USERNAME
    - name: TO_MARIADB_PASSWORD
      valueFrom:
        secretKeyRef:
          name: $SECRET_NAME_TO
          key: MARIADB_PASSWORD
  restartPolicy: Never
EOF

echo "Waiting for pod '$POD_NAME' to be ready..."
ka wait --for=condition=ready pod/"$POD_NAME" --namespace="$NAMESPACE" --timeout=300s

if [[ $? -ne 0 ]]; then
  echo "Error: Pod '$POD_NAME' did not become ready in time. Exiting."
  ka delete pod "$POD_NAME" --namespace="$NAMESPACE"
  exit 1
fi

echo "Pod '$POD_NAME' is ready. Running mariadb-dump..."

echo "Running mariadb-dump from pod '$POD_NAME' for database '$DB_NAME'..."

echo "Tables and their stats in old db"
ka exec "$POD_NAME" --namespace="$NAMESPACE" -- \
  sh -c "mariadb --host=\"\$MARIADB_HOST\" --user=\"\$MARIADB_USERNAME\" --password=\"\$MARIADB_PASSWORD\" \"\$MARIADB_DB\" --skip-ssl -t \
  -e \"SELECT CONCAT('SELECT * FROM (', GROUP_CONCAT(CONCAT('SELECT ''', table_name, ''' AS table_name, COUNT(*) AS row_count FROM ', table_name) SEPARATOR ' UNION ALL '), ') AS test ', ' ORDER BY row_count DESC,table_name ') FROM information_schema.tables WHERE table_schema = DATABASE() INTO @sql_statement; PREPARE stmt FROM @sql_statement; EXECUTE stmt; DEALLOCATE PREPARE stmt;\""

ka exec "$POD_NAME" --namespace="$NAMESPACE" -- \
    sh -c "mariadb-dump --host=\"\$MARIADB_HOST\" --user=\"\$MARIADB_USERNAME\" --password=\"\$MARIADB_PASSWORD\" \"\$MARIADB_DB\" --add-drop-table --skip-ssl --skip-lock-tables 2>/dev/null \
       | mariadb --host=\"\$TO_MARIADB_HOST\" --user=\"\$TO_MARIADB_USERNAME\" --password=\"\$TO_MARIADB_PASSWORD\" \"\$TO_MARIADB_DB\" --skip-ssl"

echo "Tables and their stats in new db"
ka exec "$POD_NAME" --namespace="$NAMESPACE" -- \
  sh -c "mariadb --host=\"\$TO_MARIADB_HOST\" --user=\"\$TO_MARIADB_USERNAME\" --password=\"\$TO_MARIADB_PASSWORD\" \"\$TO_MARIADB_DB\" --skip-ssl -t \
  -e \"SELECT CONCAT('SELECT * FROM (', GROUP_CONCAT(CONCAT('SELECT ''', table_name, ''' AS table_name, COUNT(*) AS row_count FROM ', table_name) SEPARATOR ' UNION ALL '), ') AS test ', ' ORDER BY row_count DESC,table_name ') FROM information_schema.tables WHERE table_schema = DATABASE() INTO @sql_statement; PREPARE stmt FROM @sql_statement; EXECUTE stmt; DEALLOCATE PREPARE stmt;\""

if [[ $? -ne 0 ]]; then
  echo "Error: mariadb-dump failed. Check pod logs for details."
  ka logs "$POD_NAME" --namespace="$NAMESPACE"
  echo "Pod '$POD_NAME' retained for debugging. Delete manually when done."
  exit 1
fi

echo "Database '$DB_NAME' successfully migrated"

echo "Deleting pod '$POD_NAME'..."
ka delete pod "$POD_NAME" --namespace="$NAMESPACE" --grace-period=0 --force

if [[ $? -eq 0 ]]; then
  echo "Pod '$POD_NAME' deleted successfully."
else
  echo "Warning: Failed to delete pod '$POD_NAME'. You may need to delete it manually."
fi