Skip to main content

MySQL: データベースを mysqldump からのリストアでコピーする

プライベートサブネットやフィルタリングにより直接アクセスできない MySQL サーバー間で、踏み台への SSH トンネルを経由してデータベースをコピーする手順の例です。(直接アクセスできる場合も、トンネル作成手順を除けば同じように使えます。)

概要

条件​

  • MySQL へアクセスできる踏み台 (Bastion) ホストへ SSH アクセスできる
  • Bastion ホストには MySQL クライアントや Docker がない
  • ローカル PC では Docker が動作 (MySQL クライアントは不要)
  • MySQL のバージョンは同一またはダンプに互換性があること

注意事項​

  • コピー開始後に発生した変更は反映されません。
  • データは一旦手元まで受信して折り返しています。そのぶん通信が発生するので注意。

手順​

各手順は、それぞれ別のターミナルで実行します。

1. コピー元 DB クラスターへの SSH トンネルを作成​

コピー元 DB クラスターへの SSH トンネルをローカル PC の 10001 番ポートに割り当てます。Docker コンテナから通信するために、ループバックでなくローカル PC の IP アドレスも指定します。

#!/bin/bash

MY_HOST=(ローカル PC の IP アドレス)
BASTION_HOST=(Bastion の IP アドレス)
BASTION_SSH_PORT=(Bastion の SSH ポート番号)
BASTION_USERNAME=(Bastion の SSH ユーザー名)
SOURCE_DB_HOST=(コピー元 DB クラスターのエンドポイント)
SOURCE_DB_PORT=(コピー元 DB クラスターのポート番号)

ssh -v -N -L "${MY_HOST}:10001:${SOURCE_DB_HOST}:${SOURCE_DB_PORT}" \
    -p "${BASTION_SSH_PORT}" "${BASTION_USERNAME}@${BASTION_HOST}"

2. コピー先 DB クラスターへの SSH トンネルを作成​

コピー先 DB クラスターへの SSH トンネルをローカル PC の 10002 番ポートに割り当てます。Docker コンテナから通信するために、ループバックでなくローカル PC の IP アドレスも指定します。

#!/bin/bash

MY_HOST=(ローカル PC の IP アドレス)
BASTION_HOST=(Bastion の IP アドレス)
BASTION_SSH_PORT=(Bastion の SSH ポート番号)
BASTION_USERNAME=(Bastion の SSH ユーザー名)
TARGET_DB_HOST=(コピー先 DB クラスターのエンドポイント)
TARGET_DB_PORT=(コピー先 DB クラスターのポート番号)

ssh -v -N -L "${MY_HOST}:10002:${TARGET_DB_HOST}:${TARGET_DB_PORT}" \
    -p "${BASTION_SSH_PORT}" "${BASTION_USERNAME}@${BASTION_HOST}"

3. データベースのコピーを実行​

それぞれトンネルを経由して通信しつつデータベースをコピーします。

#!/bin/bash

MY_HOST=(ローカル PC の IP アドレス)
SOURCE_DB_HOST=${MY_HOST}
SOURCE_DB_PORT=10001
SOURCE_DB_USERNAME=(コピー元 DB のユーザー名)
SOURCE_DB_PASSWORD=(コピー元 DB のパスワード)
TARGET_DB_HOST=${MY_HOST}
TARGET_DB_PORT=10002
TARGET_DB_USERNAME=(コピー先 DB のユーザー名)
TARGET_DB_PASSWORD=(コピー先 DB のパスワード)

DATABASES=(コピーするデータベース名(複数指定はスペースで区切る))

MYSQL_IMAGE=mysql:8
#MYSQL_IMAGE=amd64/mysql:5.7.40  # for MySQL 5.7
#MYSQL_IMAGE=mariadb:10.1  # MySQL 5.6 compatible
#MYSQL_IMAGE=mariadb:10.3  # MySQL 5.7 compatible

for dbname in ${DATABASES}
do
    docker run -it --rm -p "${SOURCE_DB_PORT}" -p "${TARGET_DB_PORT}" "${MYSQL_IMAGE}" bash -c "
        # Create database
        echo \"--> Create database: ${dbname}\"
        #mysql -h \"${TARGET_DB_HOST}\" -P \"${TARGET_DB_PORT}\" -u \"${TARGET_DB_USERNAME}\" -p\"${TARGET_DB_PASSWORD}\" \
        #    -e \"DROP DATABASE IF EXISTS \\\`${dbname}\\\`;\"
        mysql -h \"${TARGET_DB_HOST}\" -P \"${TARGET_DB_PORT}\" -u \"${TARGET_DB_USERNAME}\" -p\"${TARGET_DB_PASSWORD}\" \
            -e \"CREATE DATABASE \\\`${dbname}\\\`;\"

        # Copy database
        echo \"--> Copy database: ${dbname}\"
        mysqldump -h \"${SOURCE_DB_HOST}\" -P \"${SOURCE_DB_PORT}\" -u \"${SOURCE_DB_USERNAME}\" -p\"${SOURCE_DB_PASSWORD}\" \
            --single-transaction --compress --order-by-primary --set-gtid-purged=OFF \"${dbname}\" \
        | mysql -h \"${TARGET_DB_HOST}\" -P \"${TARGET_DB_PORT}\" -u \"${TARGET_DB_USERNAME}\" -p\"${TARGET_DB_PASSWORD}\" \
            --compress --database \"${dbname}\"
    "
done
  • MySQL クライアントを Docker コンテナ上で動作
    • コンテナからホストの SSH トンネルポートへ通信をおこなう
  • mysqldump に --single-transaction オプションを指定することで、テーブルロックはおこなわない
    • mysql:5.7.41 では Couldn't execute 'FLUSH TABLES WITH READ LOCK' というエラーになってしまうため、5.7.40 を使用
  • MySQL サーバーに直接アクセスできる場合は、トンネルを作成せず TARGET_DB_HOST/TARGET_DB_PORT に直接データベースのエンドポイントを指定すれば良い

ダンプを一旦ファイル保存してからコピーする場合は、以下のようにします。この場合、pv (Pipe Viewer) で進捗を表示することもできます。

# Dump from source
for dbname in ${DATABASES}
do
    dumpfile="/work/${dbname}.db"
    docker run -it --rm -v "$(pwd):/work" -p "${SOURCE_DB_PORT}" "${MYSQL_IMAGE}" bash -eu -c "
        # Dump database
        echo \"--> Dump database: ${dbname}\"
        mysqldump -h \"${SOURCE_DB_HOST}\" -P \"${SOURCE_DB_PORT}\" -u \"${SOURCE_DB_USERNAME}\" -p\"${SOURCE_DB_PASSWORD}\" \
            --single-transaction --order-by-primary --set-gtid-purged=OFF \
            --compression-algorithms=zlib \"${dbname}\" > \"${dumpfile}\"
    "
done

# Copy to target
for dbname in ${DATABASES}
do
    dumpfile="/work/${dbname}.db"
    docker run -it --rm -v "$(pwd):/work" -p "${TARGET_DB_PORT}" "${MYSQL_IMAGE}" bash -eu -c "
        # install pv(Pipe Viewer)
        #yum install -y pv  # for amd64/mysql:5.7.40
        #apt-get update && apt-get install -y pv  # for mariadb
        microdnf install -y epel-release && microdnf install -y pv  # for mysql:8 (oraclelinux)

        # Create database
        echo \"--> Create database: ${dbname}\"
        #mysql -h \"${TARGET_DB_HOST}\" -P \"${TARGET_DB_PORT}\" -u \"${TARGET_DB_USERNAME}\" -p\"${TARGET_DB_PASSWORD}\" \
        #    -e \"DROP DATABASE IF EXISTS \\\`${dbname}\\\`;\"
        mysql -h \"${TARGET_DB_HOST}\" -P \"${TARGET_DB_PORT}\" -u \"${TARGET_DB_USERNAME}\" -p\"${TARGET_DB_PASSWORD}\" \
            -e \"CREATE DATABASE \\\`${dbname}\\\`;\"

        # Restore database
        echo \"--> Restore database: ${dbname}\"
        pv \"${dumpfile}\" \
        | mysql -h \"${TARGET_DB_HOST}\" -P \"${TARGET_DB_PORT}\" -u \"${TARGET_DB_USERNAME}\" -p\"${TARGET_DB_PASSWORD}\" \
            --compression-algorithms=zlib --database \"${dbname}\"
    "
done
Last updated on