Dependencies

• Metaplay CLI - You need to have the Metaplay CLI installed to access the game database.
• A cloud deployment to connect to - You need to have a cloud deployment set up for your project in the developer portal.
• Working familiarity with relational databases - You should be able to understand the structure of the database and how to interact with it.
• Working familiarity with CLI tools - We assume you are comfortable using CLI tools to pipe together commands and redirect output.

Overview

While we generally recommend interacting with your database through the game server, there are times when you may need to connect to the database directly for manual operations.

While reading from the database is safe, you should be mindful that there are no seatbelts when tinkering with the database, and modifying it directly can have unexpected consequences. If an operation somehow breaks the database, it can stop working properly and if it's a database from a production environment it can cause unexpected downtime for live players.

We recommend doing a practice run in a development environment before trying to connect to production, so you can get a feel for the tools without risking your production environment.

If you are unsure of how to proceed or of what is the best way to achieve what you want, feel free to contact us at Metaplay Portal Support

Interacting with the Game Database

Warning!

This is a risky operation, and we don't recommend you proceed without knowing what you are doing. Please check the Overview section before continuing.

The database that your game server connects to is not publicly accessible. To connect to it, we need to deploy a temporary pod in your cloud environment (the Kubernetes namespace), which has a MySQL client to tunnel the connection. For the connection to work, you need to have the database credentials, which can be retrieved from you deployment's Kubernetes secret.

Use the metaplay get kubeconfig command to retrieve the kubeconfig file for your cloud environment and save it to a file.

Next, add the following environment variables:

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

Now, you can use this shell script to access the database:

See script

#!/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: This way 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