MariaDBのレプリケーション構成をk3dでクイック構築

はじめに

ここでは、
Kubernetes上でStatefulなデータベースがどのように動作するかを確認するための環境を
クイックに構築します。

---

背景・課題

ローカル環境でデータベースの冗長構成や障害挙動を検証しようとすると、
仮想マシンを複数台用意する必要があり、環境準備のコストが高くなりがちです。

また Docker Compose でも複数コンテナは起動できますが、
Compose は単一ホスト上のプロセス管理ツールであり、
Kubernetes のような「クラスタスケジューリング」は行われません。

そのため、複数仮想マシンとDocker Composeでは次のような挙動は再現が困難です。

• Pod再配置時のDB接続挙動（DNS名の再解決）
• 永続ボリューム再接続時のデータ整合性
• ノード障害を想定した再起動・再スケジューリング
• StatefulSet上のデータベース運用確認

---

2. 目的

冒頭で記載した背景から、本番を想定した検証を行うには、以下が必要になります。

• DNS名でのDB接続
• Pod再生成時のデータ維持
• StatefulSet
• Headless Service

これらを一番簡単に試せるのが k3d です。

本記事では、軽量Kubernetesである k3d を利用し、ローカルPC上にKubernetesクラスタを作成したうえで、
MariaDBのPrimary / Replicaレプリケーション構成を構築します。

• Kubernetes StatefulSet 上での MariaDB レプリケーション構築
• Primary/Replica 同期確認
• Pod再作成時のデータ維持
• Replica への読み取りアクセス

---

3. やったこと（構成と構築）

3.1 システム構成図

前提として、以下の注意点があります。

1. 自動フェイルオーバー機能は実装しない方式になります。
   • （自動フェイルオーバーを実装する場合は、Orchestratorが必要になります。）
   • 自動フェイルオーバー機能はなく、マスターデータベースからスレーブデータベースへの同期を行うのみとします。
2. k3dの設定になりますが、k3dボリュームマウントで、StatefuleSetで作成されたPodから固有のPersistentVolumeが設定できません。
   • k3dのドキュメントを確認してもそういった記載はないのですが、どうもDockerの仕様とk3dとStatefulSetとがかち合っている感じで調査中ですが、
   • 今回はローカルボリュームとして構築します。

また、コンテナイメージは軽量化のため、AlpineLinuxを使用します。

imagemysql/Dockerfileの内容

FROM alpine:3.23

# MySQLと必要最小限パッケージ
RUN apk add --no-cache mysql mysql-client bash

COPY ./entrypoint.sh /entrypoint.sh
RUN chmod +x /entrypoint.sh

COPY ./conf/my.cnf /etc/my.cnf
COPY ./conf/my.cnf /etc/my.cnf.d/mariadb-server.cnf

WORKDIR /

EXPOSE 3306
ENTRYPOINT ["/entrypoint.sh"]
CMD ["mysqld", "--user=mysql", "--datadir=/var/lib/mysql"]

imagemysql/entrypoint.shの内容

#!/bin/sh
set -e

echo "[entrypoint] starting mysql entrypoint"

# =========================
# Pod index 取得
# =========================
ORDINAL=$(echo "${HOSTNAME}" | awk -F'-' '{print $NF}')
SERVER_ID=$((ORDINAL + 1))
OFFSET=$((ORDINAL + 1))

if [ "${ORDINAL}" = "0" ]; then
  READONLY=0
else
  READONLY=1
fi

echo "[entrypoint] ordinal=${ORDINAL} server-id=${SERVER_ID} read-only=${READONLY}"

# =========================
# node.cnf 生成（書き込み可能領域）
# =========================
cat > /etc/mysql/conf.d/node.cnf <<EOF
[mysqld]
server-id=${SERVER_ID}
auto-increment-offset=${OFFSET}
read-only=${READONLY}
EOF

# Master/Slave判定（Master:ORDINAL=0）
if [ "${ORDINAL}" = "0" ]; then
  echo "log-bin=mysql-bin" >> /etc/mysql/conf.d/node.cnf
  echo "binlog-format=ROW" >> /etc/mysql/conf.d/node.cnf
else
  echo "relay-log=relay-bin" >> /etc/mysql/conf.d/node.cnf
fi

# =========================
# 初期化（初回のみ）
# =========================
if [ ! -d "/var/lib/mysql/mysql" ]; then
  echo "[entrypoint] initializing mysql datadir"
  mysql_install_db --user=mysql --datadir=/var/lib/mysql
fi

# =========================
# 権限調整
# =========================
chown -R mysql:mysql /var/lib/mysql
mkdir -p /run/mysqld
chown -R mysql:mysql /run/mysqld

# =========================
# 起動（これだけ）
# =========================
echo "[entrypoint] starting mysqld"
exec mysqld --user=mysql

imagemysql/conf/my.cnfの内容

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[server]

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
max_connections = 10
innodb_buffer_pool_size = 34M
innodb_thread_concurrency = 8
innodb_io_capacity = 100
innodb_io_capacity_max = 1000
innodb_read_io_threads = 2
innodb_write_io_threads = 2
bind-address = 0.0.0.0
max_allowed_packet = 256M

# Replication settings (will be added dynamically by entrypoint.sh)
# server-id will be set based on pod name
# log-bin=mysql-bin
# binlog-format=ROW
# relay-log=relay-bin

[galera]

[embedded]

[mariadb]

[mariadb-10.5]

install_statefulset_mysql.sh

install_statefulset_mysql.shの内容

#!/bin/bash

# スクリプトが失敗した場合、即座に終了するように設定
# set -e

# 現在のディレクトリを変数に設定
hostexexecution_path=$(pwd)

# 管理者権限に昇格
sudo su - <<'EOF'
  mkdir -pvm 777 /tmp/datamnt/data
  ln -sf /tmp/datamnt /datamnt
  mkdir -pvm 777 /home/kubeuser/podmultinode
EOF

# configmysqlディレクトリは不要になったため、コピー削除
sudo rsync -avz imagemysql /home/kubeuser/podmultinode/imagemysql
sudo rsync -avz podmysql.yml /home/kubeuser/podmultinode/podmysql.yml

# 必要なパッケージのインストール
echo "hogehoge"
sudo apt-get update
sudo apt-get install -y cron cpulimit rsync

# cronサービスの起動
sudo service cron start

registry_directory_path="registry"

# chmod -v 755 ${hostexexecution_path}/*.sh
tags_select="v5.8.3"
image_k3s="rancher/k3s:v1.34.3-k3s1"
echo "k3s image: ${image_k3s} is selected. and k3d version: ${tags_select} is selected."

# install k3d
curl -s https://raw.githubusercontent.com/k3d-io/k3d/main/install.sh | TAG=${tags_select} bash

k3d node list
chmod 755 ${hostexexecution_path}/*.sh
mkdir -pv ~/.k3d_t_kyn
mkdir -pv ~/.k3d_t_kyn/k3s_etc

# レジストリ設定ファイルのコピー
cp -pv ${registry_directory_path}/.k3d_t_kyn/t_kyn_config.tmplate ~/.k3d_t_kyn/t_kyn_config.tmplate 2>/dev/null || true
cp -pv ${registry_directory_path}/.k3d_t_kyn/k3s_etc/registries.yaml ~/.k3d_t_kyn/k3s_etc/registries.yaml
cp -pv ${registry_directory_path}/registry-config.json ~/ 2>/dev/null || true
cat ~/registry-config.json 2>/dev/null || true

# Dockerレジストリの作成
docker volume create local_registry
docker volume ls | grep local_registry

# レジストリコンテナの起動（既に起動している場合はスキップ）
if ! docker ps | grep -q tkynregistry.local; then
    docker container run -d --name tkynregistry.local \
        -v local_registry:/var/lib/registry \
        --restart always \
        -p 5000:5000 \
        registry:2
fi

# container_network_list and k3d cluster list confirm
docker network ls
kubectl config get-contexts 2>/dev/null || true

# k3dボリュームディレクトリの作成
mkdir -pvm 777 /tmp/k3d-vols/server-0 /tmp/k3d-vols/agent /tmp/k3d-vols/registry /datamnt/var /datamnt/data

# k3dクラスタの作成（既に存在する場合はスキップ）
if ! k3d cluster list | grep -q "k3s-default"; then
    k3d cluster create \
        --agents 2 \
        --port "8080:30000@loadbalancer" \
        --wait \
        --image ${image_k3s} \
        --registry-config ~/.k3d_t_kyn/k3s_etc/registries.yaml \
        --volume /datamnt/data:/datamnt/data \
        --volume /tmp/k3d-vols/server-0:/var/lib/rancher/k3s/storage
fi

# レジストリをk3dネットワークに接続
docker network connect k3d-k3s-default tkynregistry.local 2>/dev/null || true

# コンテナレジストリへの起動確認
curl -kv http://tkynregistry.local:5000/v2/_catalog

# ホストへの登録をします
entry_count=$(cat /etc/hosts | grep tkynregistry.local | grep 127.0.0.1 | wc -l)
if [ "$entry_count" -ge 1 ]; then
    echo "tkynregistry.local already exists in /etc/hosts. Skipping..."
else
    echo '127.0.0.1       tkynregistry.local' | sudo tee -a /etc/hosts
    echo "Added tkynregistry.local to /etc/hosts."
    cat /etc/hosts | grep tkynregistry.local
fi

curl -kv http://tkynregistry.local:5000/v2/_catalog

# Dockerイメージのビルドとプッシュ
cd imagemysql
docker build -t imagemysql:v1.0.0 .
docker tag imagemysql:v1.0.0 tkynregistry.local:5000/imagemysql:v1.0.0
docker push tkynregistry.local:5000/imagemysql:v1.0.0
cd ..
pwd

# generation of database mysql
mkdir -pvm 777 /datamnt/data/mysqlpv{1,2,3}

# deploy pod mysql
_mysqlYamlFilename="podmysql.yml"

echo "Applying MySQL StatefulSet..."
kubectl apply -f ${_mysqlYamlFilename}

echo "Waiting for pods to be ready..."
kubectl wait --for=condition=ready pod -l app=mysql --timeout=300s

echo "Deployment complete!"
kubectl get all
kubectl get pvc

実行結果

k3d_taist_statefulset_test $ ./install_statefulset_mysql.sh
mkdir: created directory '/tmp/datamnt'
mkdir: created directory '/tmp/datamnt/data'
mkdir: created directory '/home/kubeuser'
mkdir: created directory '/home/kubeuser/podmultinode'
sending incremental file list
created directory /home/kubeuser/podmultinode/imagemysql
imagemysql/
imagemysql/Dockerfile
imagemysql/entrypoint.sh
imagemysql/entrypoint.sh_tmp20260206
imagemysql/conf/
imagemysql/conf/my.cnf

sent 3,127 bytes  received 169 bytes  6,592.00 bytes/sec
total size is 6,359  speedup is 1.93
sending incremental file list
podmysql.yml

sent 2,024 bytes  received 35 bytes  4,118.00 bytes/sec
total size is 5,915  speedup is 2.87
hogehoge
0% [Working]            Get:1 https://repo.anaconda.com/pkgs/misc/debrepo/conda stable InRelease [3961 B]
！！！！！長いんで略します！！！！！
Applying MySQL StatefulSet...
service/mysql-headless created
configmap/mysql-config created
statefulset.apps/mysql created
Waiting for pods to be ready...
pod/mysql-0 condition met
Deployment complete!
NAME          READY   STATUS    RESTARTS   AGE
pod/mysql-0   1/1     Running   0          36s
pod/mysql-1   0/1     Pending   0          0s

NAME                     TYPE        CLUSTER-IP   EXTERNAL-IP   PORT(S)    AGE
service/kubernetes       ClusterIP   10.43.0.1    <none>        443/TCP    99s
service/mysql-headless   ClusterIP   None         <none>        3306/TCP   36s

NAME                     READY   AGE
statefulset.apps/mysql   1/3     36s
NAME                 STATUS    VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS   VOLUMEATTRIBUTESCLASS   AGE
mysql-data-mysql-0   Bound     pvc-f4279a73-8e44-4b18-a5ef-e18210734276   1Gi        RWO            local-path     <unset>                 36s
mysql-data-mysql-1   Pending                                                                        local-path     <unset>                 0s
k3d_taist_statefulset_test $ echo $?
0
k3d_taist_statefulset_test $ kubectl wait --for=condition=ready pod -l app=mysql --timeout=300sk3d_taist_statefulset_test $ kubectl wait --for=condition=ready pod -l app=mysql --timeout=300s
pod/mysql-0 condition met
pod/mysql-1 condition met
pod/mysql-2 condition met
k3d_taist_statefulset_test $ kubectl get podskubectl get pods
NAME      READY   STATUS    RESTARTS   AGE
mysql-0   1/1     Running   0          98s
mysql-1   1/1     Running   0          62s
mysql-2   1/1     Running   0          31s
k3d_taist_statefulset_test $ 

3.2 設定ファイル

mariadbのymlの設定

マニフェストファイルは、以下の通りになっております。

podmysql.yml

---
# =========================
# Headless Service for StatefulSet
# =========================
apiVersion: v1
kind: Service
metadata:
  name: mysql-headless
spec:
  clusterIP: None
  selector:
    app: mysql
  ports:
    - name: mysql
      port: 3306
      targetPort: 3306

---
# =========================
# ConfigMap (my.cnf + server-id 雛形)
# =========================
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-config
data:
  my.cnf: |
    [client]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock

    [mysqld]
    user = mysql
    pid-file = /var/run/mysqld/mysqld.pid
    socket = /var/run/mysqld/mysqld.sock
    port = 3306
    bind-address = 0.0.0.0

    max_connections = 10
    max_allowed_packet = 256M

    innodb_buffer_pool_size = 34M
    innodb_thread_concurrency = 8
    innodb_io_capacity = 100
    innodb_io_capacity_max = 1000
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2

    !includedir /etc/mysql/conf.d

  server-id.cnf: |
    [mysqld]
    # generated by initContainer

  log-bin.cnf: |
    [mysqld]
    # generated by initContainer
---
# =========================
# StatefulSet
# =========================
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  serviceName: mysql-headless
  replicas: 3
  selector:
    matchLabels:
      app: mysql

  template:
    metadata:
      labels:
        app: mysql
    spec:
      securityContext:
        fsGroup: 999

      # -------------------------
      # initContainer
      # -------------------------
      initContainers:
      - name: init-server-id
        image: busybox:1.36
        command:
          - sh
          - -c
          - |
            ORDINAL=${POD_NAME##*-}
            SERVER_ID=$((ORDINAL + 1))

            cat > /conf/server-id.cnf <<EOF
            [mysqld]
            server-id=${SERVER_ID}
            EOF

            if [ "$ORDINAL" -eq 0 ]; then
            # Master: binlog 有効
            cat > /conf/log-bin.cnf <<EOF
            [mysqld]
            log_bin = mysql-bin
            binlog_format = ROW
            sync_binlog = 1
            EOF
            else
            # Slave: binlog 無効
            cat > /conf/log-bin.cnf <<EOF
            [mysqld]
            # log_bin OFF for slave
            EOF
            fi

            # # slave setting for replicas
            # if [ "$ORDINAL" -ne 0 ]; then
            #   echo "Initializing replica"

            # # not mysql-0.mysql-headless --> localhost
            #   mysql -h localhost \
            #     -uroot -p${MYSQL_ROOT_PASSWORD} <<EOF
            #   STOP SLAVE;
            #   CHANGE MASTER TO
            #     MASTER_HOST='mysql-0.mysql-headless',
            #     MASTER_USER='repl',
            #     MASTER_PASSWORD='replpass',
            #     MASTER_AUTO_POSITION=1;
            #   START SLAVE;
            #   EOF
            # fi

            chown -R 999:999 /conf
        env:
          - name: POD_NAME
            valueFrom:
              fieldRef:
                fieldPath: metadata.name
        volumeMounts:
          - name: mysql-node-conf
            mountPath: /conf

      # -------------------------
      # main container
      # -------------------------
      containers:
      - name: mysql
        image: tkynregistry.local:5000/imagemysql:v1.0.0
        imagePullPolicy: IfNotPresent

        ports:
          - containerPort: 3306

        env:
          - name: MYSQL_ROOT_PASSWORD
            value: "examplepassword"

        resources:
          limits:
            cpu: "0.4"
            memory: "512Mi"
          requests:
            cpu: "0.2"
            memory: "256Mi"

        volumeMounts:
          - name: mysql-data
            mountPath: /var/lib/mysql

          - name: mysql-config
            mountPath: /etc/mysql/my.cnf
            subPath: my.cnf
            readOnly: true

          - name: mysql-node-conf
            mountPath: /etc/mysql/conf.d

          - name: mysql-run
            mountPath: /run/mysqld

        livenessProbe:
          exec:
            command: ["mysqladmin", "ping", "-h", "127.0.0.1"]
          initialDelaySeconds: 30
          periodSeconds: 10

        readinessProbe:
          exec:
            command: ["mysqladmin", "ping", "-h", "127.0.0.1"]
          initialDelaySeconds: 10
          periodSeconds: 5

        # # postStart: set replica
        # lifecycle:
        #   postStart:
        #     exec:
        #       command:
        #         - sh
        #         - -c
        #         - |
        #           (
        #             ORDINAL=${HOSTNAME##*-}

        #             echo "[postStart] waiting mysql..."
        #             for i in $(seq 1 60); do
        #               mysqladmin ping -uroot -p${MYSQL_ROOT_PASSWORD} --silent && break
        #               sleep 2
        #             done

        #             if [ "$ORDINAL" != "0" ]; then
        #               mysql -uroot -p${MYSQL_ROOT_PASSWORD} <<EOF
        #               STOP SLAVE;
        #               CHANGE MASTER TO
        #                 MASTER_HOST='mysql-0.mysql-headless',
        #                 MASTER_USER='repl',
        #                 MASTER_PASSWORD='replpass',
        #                 MASTER_AUTO_POSITION=1;
        #               START SLAVE;
        #               EOF
        #             fi
        #           ) >/proc/1/fd/1 2>&1 &

      volumes:
        - name: mysql-config
          configMap:
            name: mysql-config

        - name: mysql-node-conf
          emptyDir: {}

        - name: mysql-run
          emptyDir: {}

  # -------------------------
  # PVC (dynamic provisioning)
  # -------------------------
  volumeClaimTemplates:
  - metadata:
      name: mysql-data
    spec:
      accessModes:
        - ReadWriteOnce
      storageClassName: local-path
      resources:
        requests:
          storage: 1Gi

レプリケーション設定

初回セットアップ用のスクリプトになります。
マスタデータベースにあるbin_logを参照し、
スレーブ側のデータベースにbin_logのindexを更新することにより、
スレーブ設定を自動反映します。
このまま実行することで反映できます。

initial_setup_of_replication_on_pods.sh

#!/bin/bash

################################################################################
# MySQL Replication Initial Setup Script
################################################################################
# Purpose: 
#   - Setup master-slave replication AFTER initial bulk data loading
#   - This script should be run AFTER loading 1000 tables x 100000 records
#
# Workflow:
#   1. Verify all pods are ready
#   2. Create replication user on master (mysql-0)
#   3. Get master binlog file and position
#   4. Configure slave nodes (mysql-1, mysql-2) to replicate from master
#   5. Start replication
#   6. Verify replication status
################################################################################

set -e

# Configuration
DB_USER="root"
DB_PASS="examplepassword"
REPL_USER="repl"
REPL_PASS="replpass"
MASTER_POD="mysql-0"
SLAVE_PODS=("mysql-1" "mysql-2")
NAMESPACE="default"

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

################################################################################
# Helper Functions
################################################################################

print_header() {
    echo ""
    echo "========================================="
    echo -e "${BLUE}$1${NC}"
    echo "========================================="
}

print_success() {
    echo -e "${GREEN}✅ $1${NC}"
}

print_warning() {
    echo -e "${YELLOW}⚠️  $1${NC}"
}

print_error() {
    echo -e "${RED}❌ $1${NC}"
}

print_info() {
    echo -e "${BLUE}ℹ️  $1${NC}"
}

# Execute MySQL command on a pod
exec_mysql() {
    local pod=$1
    local query=$2
    kubectl exec -it ${pod} -n ${NAMESPACE} -- mysql -u ${DB_USER} -p${DB_PASS} -e "${query}" 2>/dev/null
}

# Execute MySQL command and suppress warnings
exec_mysql_silent() {
    local pod=$1
    local query=$2
    kubectl exec ${pod} -n ${NAMESPACE} -- mysql -u ${DB_USER} -p${DB_PASS} -e "${query}" 2>/dev/null | grep -v "Deprecated" | grep -v "Warning"
}

################################################################################
# Step 1: Verify Pod Status
################################################################################

print_header "Step 1: Verifying Pod Status"

for pod in ${MASTER_POD} ${SLAVE_PODS[@]}; do
    print_info "Checking ${pod}..."
    
    # Check if pod is running
    if ! kubectl get pod ${pod} -n ${NAMESPACE} &>/dev/null; then
        print_error "Pod ${pod} does not exist"
        exit 1
    fi
    
    # Check if pod is ready
    if ! kubectl wait --for=condition=ready pod/${pod} -n ${NAMESPACE} --timeout=10s &>/dev/null; then
        print_error "Pod ${pod} is not ready"
        exit 1
    fi
    
    # Check MySQL connection
    if ! exec_mysql ${pod} "SELECT 1;" &>/dev/null; then
        print_error "Cannot connect to MySQL on ${pod}"
        exit 1
    fi
    
    print_success "${pod} is ready and MySQL is accessible"
done

################################################################################
# Step 2: Check if Binary Logging/Relay Logging is Enabled
################################################################################

print_header "Step 2: Verifying Logging Settings"

# Master: log_binチェック
print_info "Checking binary logging on ${MASTER_POD}..."
log_bin=$(exec_mysql_silent ${MASTER_POD} "SHOW VARIABLES LIKE 'log_bin';" | grep log_bin | awk '{print $2}')
if [ "$log_bin" = "ON" ]; then
    print_success "${MASTER_POD}: Binary logging is ENABLED"
else
    print_error "${MASTER_POD}: Binary logging is DISABLED"
    print_error "Please rebuild the Docker image with binary logging enabled (Master側)"
    exit 1
fi

# Slave: relay-logチェック（log_binは不要）
for slave in ${SLAVE_PODS[@]}; do
    print_info "Checking relay logging on ${slave}..."
    relay_log=$(exec_mysql_silent ${slave} "SHOW VARIABLES LIKE 'relay_log';" | grep relay_log | awk '{print $2}')
    if [ -n "$relay_log" ]; then
        print_success "${slave}: Relay logging is ENABLED (relay_log: $relay_log)"
    else
        print_warning "${slave}: Relay logging is NOT explicitly set (relay_log: $relay_log)"
        # relay-logは自動生成される場合もあるため、エラーにはしない
    fi
done

################################################################################
# Step 3: Stop Any Existing Replication on Slaves
################################################################################

print_header "Step 3: Stopping Existing Replication (if any)"

for slave in ${SLAVE_PODS[@]}; do
    print_info "Stopping replication on ${slave}..."
    exec_mysql ${slave} "STOP SLAVE;" 2>/dev/null || true
    print_success "${slave}: Replication stopped"
done

sleep 2

################################################################################
# Step 4: Create Replication User on Master
################################################################################

print_header "Step 4: Creating Replication User on Master"

print_info "Creating replication user '${REPL_USER}' on ${MASTER_POD}..."

# Drop user if exists and create new one
exec_mysql ${MASTER_POD} "DROP USER IF EXISTS '${REPL_USER}'@'%';"
exec_mysql ${MASTER_POD} "CREATE USER '${REPL_USER}'@'%' IDENTIFIED BY '${REPL_PASS}';"
exec_mysql ${MASTER_POD} "GRANT REPLICATION SLAVE ON *.* TO '${REPL_USER}'@'%';"
exec_mysql ${MASTER_POD} "FLUSH PRIVILEGES;"

print_success "Replication user created successfully"

sleep 2

################################################################################
# Step 5: Get Master Status
################################################################################

print_header "Step 5: Getting Master Binlog Position"

print_info "Fetching master status from ${MASTER_POD}..."

# Get master status
MASTER_STATUS=$(kubectl exec ${MASTER_POD} -n ${NAMESPACE} -- mysql -u ${DB_USER} -p${DB_PASS} -e "SHOW MASTER STATUS\G" 2>/dev/null | grep -v "Deprecated" | grep -v "Warning")

MASTER_LOG_FILE=$(echo "$MASTER_STATUS" | grep "File:" | awk '{print $2}')
MASTER_LOG_POS=$(echo "$MASTER_STATUS" | grep "Position:" | awk '{print $2}')

if [ -z "$MASTER_LOG_FILE" ] || [ -z "$MASTER_LOG_POS" ]; then
    print_error "Failed to get master binlog file or position"
    echo "$MASTER_STATUS"
    exit 1
fi

print_success "Master binlog file: ${MASTER_LOG_FILE}"
print_success "Master binlog position: ${MASTER_LOG_POS}"

################################################################################
# Step 6: Configure Slaves
################################################################################

print_header "Step 6: Configuring Slave Nodes"

for slave in ${SLAVE_PODS[@]}; do
    print_info "Configuring ${slave}..."
    
    # Reset slave configuration
    exec_mysql ${slave} "RESET SLAVE ALL;" 2>/dev/null || true
    
    # Configure master connection
    exec_mysql ${slave} "CHANGE MASTER TO 
        MASTER_HOST='${MASTER_POD}.mysql-headless',
        MASTER_USER='${REPL_USER}',
        MASTER_PASSWORD='${REPL_PASS}',
        MASTER_LOG_FILE='${MASTER_LOG_FILE}',
        MASTER_LOG_POS=${MASTER_LOG_POS};"
    
    print_success "${slave}: Master connection configured"
done

sleep 2

################################################################################
# Step 7: Start Replication
################################################################################

print_header "Step 7: Starting Replication"

for slave in ${SLAVE_PODS[@]}; do
    print_info "Starting replication on ${slave}..."
    exec_mysql ${slave} "START SLAVE;"
    print_success "${slave}: Replication started"
done

sleep 3

################################################################################
# Step 8: Verify Replication Status
################################################################################

print_header "Step 8: Verifying Replication Status"

ALL_SLAVES_OK=true

for slave in ${SLAVE_PODS[@]}; do
    print_info "Checking replication status on ${slave}..."
    
    SLAVE_STATUS=$(kubectl exec ${slave} -n ${NAMESPACE} -- mysql -u ${DB_USER} -p${DB_PASS} -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -v "Deprecated" | grep -v "Warning")
    
    SLAVE_IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
    SLAVE_SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
    SECONDS_BEHIND=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')
    LAST_ERROR=$(echo "$SLAVE_STATUS" | grep "Last_Error:" | cut -d: -f2- | xargs)
    
    echo ""
    echo "  Pod: ${slave}"
    echo "  ├─ Slave_IO_Running:  ${SLAVE_IO_RUNNING}"
    echo "  ├─ Slave_SQL_Running: ${SLAVE_SQL_RUNNING}"
    echo "  ├─ Seconds_Behind_Master: ${SECONDS_BEHIND}"
    
    if [ -n "$LAST_ERROR" ]; then
        echo "  └─ Last_Error: ${LAST_ERROR}"
    fi
    
    if [ "$SLAVE_IO_RUNNING" = "Yes" ] && [ "$SLAVE_SQL_RUNNING" = "Yes" ]; then
        print_success "${slave}: Replication is working correctly"
    else
        print_error "${slave}: Replication is NOT working correctly"
        ALL_SLAVES_OK=false
        
        if [ -n "$LAST_ERROR" ]; then
            print_error "Error: ${LAST_ERROR}"
        fi
    fi
done

################################################################################
# Step 9: Final Summary
################################################################################

print_header "Step 9: Replication Setup Summary"

echo ""
echo "Master Node:"
echo "  └─ ${MASTER_POD}"
echo ""
echo "Slave Nodes:"
for slave in ${SLAVE_PODS[@]}; do
    echo "  ├─ ${slave}"
done
echo ""
echo "Replication User:"
echo "  ├─ Username: ${REPL_USER}"
echo "  └─ Password: ${REPL_PASS}"
echo ""
echo "Master Binlog:"
echo "  ├─ File: ${MASTER_LOG_FILE}"
echo "  └─ Position: ${MASTER_LOG_POS}"
echo ""

if [ "$ALL_SLAVES_OK" = true ]; then
    print_header "✅ Replication Setup Completed Successfully!"
    echo ""
    print_info "Next steps:"
    echo "  1. Verify data synchronization with: ./check_replication.sh"
    echo "  2. Test replication with: ./test_replication.sh"
    echo "  3. Monitor replication lag regularly"
    echo ""
    exit 0
else
    print_header "❌ Replication Setup Failed"
    echo ""
    print_error "Some slaves are not replicating correctly"
    print_info "Run the following command to check details:"
    echo "  kubectl exec -it mysql-1 -- mysql -u root -pexamplepassword -e 'SHOW SLAVE STATUS\G'"
    echo ""
    exit 1
fi

動作確認

テストデータ用にテーブルとデータを挿入

以下の通りテストデータを作成し、マスターのほうのみ追加します。

k8s_generate_test_data.sh

#!/bin/bash

# Kubernetes Pod内でテストデータを生成するスクリプト

POD_NAME=${1:-"mysql-0"}
NAMESPACE=${2:-"default"}
DB_USER="root"
DB_PASS="examplepassword"
DB_NAME="t_kyn_usrdb"

echo "==================================="
echo "K8s Pod内でテストデータ生成"
echo "==================================="
echo "Pod: $POD_NAME"
echo "Namespace: $NAMESPACE"
echo "==================================="

# SQLファイルをPodにコピー
echo "SQLファイルをPodにコピー中..."
kubectl cp create_test_data.sql ${NAMESPACE}/${POD_NAME}:/tmp/create_test_data.sql

# データベース作成
echo "データベース作成中..."
kubectl exec -it ${POD_NAME} -n ${NAMESPACE} -- mysql -u ${DB_USER} -p${DB_PASS} -e "CREATE DATABASE IF NOT EXISTS ${DB_NAME} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# SQLファイル実行
echo "テストデータ生成中..."
kubectl exec -it ${POD_NAME} -n ${NAMESPACE} -- mysql -u ${DB_USER} -p${DB_PASS} ${DB_NAME} -e "source /tmp/create_test_data.sql"

# 結果確認
echo "==================================="
echo "✅ 完了しました"
echo "==================================="
kubectl exec -it ${POD_NAME} -n ${NAMESPACE} -- mysql -u ${DB_USER} -p${DB_PASS} ${DB_NAME} -e "SELECT COUNT(*) as total_records FROM test_table;"

確認用スクリプト

データベースがマスタ、スレーブで同じ行数出ているか確認します。

check_replication.sh

#!/bin/bash

DB_USER="root"
DB_PASS="examplepassword"
DB_NAME="t_kyn_usrdb"

echo "========================================="
echo "MySQL Replication Status Check"
echo "========================================="

# マスターのステータス確認
echo ""
echo "=== MASTER (mysql-0) Status ==="
kubectl exec -it mysql-0 -- mysql -u ${DB_USER} -p${DB_PASS} -e "SHOW MASTER STATUS\G"

# スレーブのステータス確認
echo ""
echo "=== SLAVE (mysql-1) Status ==="
kubectl exec -it mysql-1 -- mysql -u ${DB_USER} -p${DB_PASS} -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Error"

echo ""
echo "=== SLAVE (mysql-2) Status ==="
kubectl exec -it mysql-2 -- mysql -u ${DB_USER} -p${DB_PASS} -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Error"

# 各Podのデータベース一覧確認
echo ""
echo "========================================="
echo "Database List on Each Pod"
echo "========================================="

for pod in mysql-0 mysql-1 mysql-2; do
    echo ""
    echo "=== ${pod} ==="
    kubectl exec -it ${pod} -- mysql -u ${DB_USER} -p${DB_PASS} -e "SHOW DATABASES;" 2>/dev/null | grep -v "information_schema\|performance_schema\|mysql\|Database"
done

# テストデータの同期確認（test_tableが存在する場合）
echo ""
echo "========================================="
echo "Test Table Record Count"
echo "========================================="

for pod in mysql-0 mysql-1 mysql-2; do
    echo ""
    echo "=== ${pod} ==="
    kubectl exec -it ${pod} -- mysql -u ${DB_USER} -p${DB_PASS} ${DB_NAME} -e "SELECT COUNT(*) as record_count FROM test_table;" 2>/dev/null || echo "Table does not exist yet"
done

---

4. 結論・まとめ

k3d + StatefulSet を利用することで、MariaDB レプリケーション検証が可能でした。

特に以下が検証できる点が有用です。

• Pod再生成時のデータ永続性
• DNS名によるDB接続
• レプリケーション追従挙動

Docker Composeでは難しい「Statefulな挙動確認」が可能になります。

---

今後の展望:

• MaxScale導入による自動フェイルオーバー
• Read/Write 分離
• Orchestratorによる自動昇格

---

参考リンク

• https://k3d.io
• https://mariadb.com/kb/en/replication-overview/