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

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

![概要](https://hirose-public.s3-ap-northeast-1.amazonaws.com/qiita/2023/01/13/5c297d1a-32b3-4775-8d3f-aeb9bfd9d670.png)

## 条件

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

## 注意事項

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

## 手順

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

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

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

```bash
#!/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 アドレスも指定します。

```bash
#!/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. データベースのコピーを実行

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

```bash
#!/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)](https://catonmat.net/unix-utilities-pipe-viewer) で進捗を表示することもできます。

```bash
# 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
```