Interacting with the Game Database

Danger!

Please note that interacting directly with the database can be dangerous. Please be mindful of the risks when applying the approach described here.

INFO

Direct database connection is still an experimental feature. Please use with caution.

The database that your gameserver connects to is not publicly accessible.

An easy way of establishing a direct connection to the database from your local device is by running a temporary pod in your cloud environment (Kubernetes namespace), which has a MySQL client. Additionally, you will need the database credentials, which can be retrieved from the Kubernetes secret of the deployment.

$ export KUBECONFIG=/path/to/file/with/kubeconfig
$ export NAMESPACE=<cloud environment>

Now you can use this shell script to access the database.

#!/bin/bash

trap cleanup INT

function cleanup() {
  if [ "$MODE" == "tunnel" ] && [ ! -z "$NAMESPACE" ]; then
    echo "Stopping mysql-proxy pod..."
    kubectl delete -n $NAMESPACE pod mysql-proxy
  fi

  exit 0
}

if [ -z "$1" ]; then
  echo "Usage: $0 NAMESPACE [client|tunnel] [shard]"
  exit 0
fi
NAMESPACE=$1

MODE="client"
if [ "$2" == "tunnel" ]; then
  MODE="tunnel"
fi

SHARD=0
if [ ! -z "$3" ]; then
  SHARD=$3
fi
echo "Shard is $SHARD"

CONFIGS=$(kubectl get secret -n $NAMESPACE metaplay-config -o json | jq -r '.data["metaplay-infra-options.yaml"]' | base64 -d | yq -o=json)

DB_HOST=$(echo $CONFIGS | jq -r .Database.Shards[$SHARD].ReadWriteHost)
DB_NAME=$(echo $CONFIGS | jq -r .Database.Shards[$SHARD].DatabaseName)
DB_USER=$(echo $CONFIGS | jq -r .Database.Shards[$SHARD].UserId)
DB_PASS=$(echo $CONFIGS | jq -r .Database.Shards[$SHARD].Password)

echo "DB host is ${DB_HOST}"

if [ "$MODE" == "tunnel" ]; then
  echo "Starting mysql-proxy pod..."
  kubectl run \
    --restart=Never \
    --image=alpine/socat \
    -n $NAMESPACE \
    mysql-proxy -- \
    -d -d \
    tcp-listen:3306,fork,reuseaddr \
    tcp-connect:$DB_HOST:3306

  echo "Waiting for mysql-proxy to become ready..."
  kubectl wait -n $NAMESPACE --for=condition=ready pod/mysql-proxy

  echo "Database connection details:"
  echo "  Host: localhost"
  echo "  Port: 3306"
  echo "  Username: $DB_USER"
  echo "  Password: $DB_PASS"
  echo ""

  echo "Binding database to local port 3306..."
  echo "Shut down tunnel with Ctrl-C"
  kubectl port-forward -n $NAMESPACE pod/mysql-proxy 3306:3306

else
  echo "Starting MySQL client..."
  kubectl run \
    -it \
    --rm \
    --image=mysql:8.0.40 \
    --restart=Never \
    -n $NAMESPACE \
    mysql-client -- mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME
fi

The shell script supports two modes:

• Client mode: where you can execute SQL queries interactively. This mode is relatively simple and suitable for ad-hoc queries.
• Tunnel mode: where the shell script opens a tunnel that relays traffic between your local device and the database. This mode is useful for operations such as database dumping.

Client mode

$ ./connect.sh lovely-wombats-build-quickly client

Example output

Starting MySQL client...

If you don't see a command prompt, try pressing enter.

mysql>

Tunnel mode

$ ./connect.sh lovely-wombats-build-quickly tunnel

Example output

Shard is 0
DB host is mariadb.lovely_wombats_build_quickly.svc.cluster.local
Starting mysql-proxy pod...
pod/mysql-proxy created
Waiting for mysql-proxy to become ready...
pod/mysql-proxy condition met
Database connection details:
  Host: localhost
  Port: 3306
  Username: lovely_wombats_build_quickly
  Password: <redacted>

Binding database to local port 3306...
Shut down tunnel with Ctrl-C
Forwarding from 127.0.0.1:3306 -> 3306
Forwarding from [::1]:3306 -> 3306
Handling connection for 3306