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=amd64/mysql:5.7.40
#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 -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 \
            --compress \"${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 -c "
        yum install -y pv
        #apt-get update && apt-get install -y pv  # for mariadb

        # 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}\" \
            --compress --database \"${dbname}\"
    "
done