ランサーズ(Lancers)エンジニアブログ > SRE > 開発/Stg環境のための本番DBマスキングと継続的リストアの仕組みを作りました

開発/Stg環境のための本番DBマスキングと継続的リストアの仕組みを作りました

adachin|2021年09月06日
SRE

SREチームの安達(@adachin0817)です。今回はMENTALancers CreativeLancers Agencyマスキングした本番環境のデータをStgや開発環境のMySQLコンテナへ毎週リストアする仕組みを実装しました。実際にここらへんは運用をしていく中で一苦労されている方も多いのではないでしょうか。それではまず背景と、実装するに当たっての活動含めてご紹介できればと思います。

背景

今回はMENTAを例にしています。各サービスの開発環境はDockerを利用しており、本番とStg環境はTerraformで管理しています。カラム追加ではマイグレーションを実行することでサンプルのスキーマファイルを投入して開発をしているのですが、たまに開発環境で動いていたソースがStgや本番で動かないといったことで開発効率が下がることが見受けられます。開発メンバーにとってはより本番環境に近い環境でテストをすることがベストです。また、各AWSのアカウントは本番環境とStg環境を分離しています。もちろん気楽にRDSのクローン機能を利用して即座に同じDBを構築することができないですし、クローンだと本番データが入っているためインシデントが起こる可能性があります。また、運用上再作成することも踏まえると時間もかかるのと工数もかかります。そこでちょっとした工夫と設計を考える必要があり、ランサーズ本家と同様に継続的にリストアできる仕組みを今回実装しました。

※以下スペースマーケットさんと以前LT大会をした際にMENTAのAWS移行について登壇したのでぜひ参考にしてください。

[スペースマーケット×ランサーズ][SRE]第2回目のコラボイベント開催しました!

auto-masking

・環境

  • EC2(ディスク100GB)
  • Local DB(MySQL 8.0.26)
  • RDS Aurora
  • S3

・my.cnf

[mysqld]

disable_log_bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

character_set_server=utf8mb4
collation-server=utf8mb4_bin
default-authentication-plugin = mysql_native_password
default-time-zone='+9:00'

skip-grant-tables
innodb_file_per_table=ON
innodb_buffer_pool_size=4G
innodb_log_file_size=1G
join_buffer_size=256K
read_buffer_size=1M
read_rnd_buffer_size=2M
sort_buffer_size=4M
max_heap_table_size=16M
tmp_table_size=16M
thread_cache_size=100
wait_timeout = 86400
max_allowed_packet = 1G
innodb_buffer_pool_size = 1G
max_allowed_packet = 16M
innodb_strict_mode = 0
long_query_time = 5

sql_mode = 0

[mysql]
default-character-set=utf8mb4

今回本番DBマスキングからStgへリストアするまでauto-maskingと名付けました。サーバー(devops)はコンテナではなく、EC2を選択しました。初期はECS/Fargateで挑戦しようと試みていましたが、現時点のエフェメラルストレージは200GBのみ対応しており、今後データが増える見込みであればストレージの拡張はEFSを利用するしかありません。もちろんEFSはコストが高いのと、エフェメラルストレージの変更はTerraformではサポートされておらず、aws-cliを利用して変更するしかありません。と考慮するとEC2で運用するのがふさわしいと考えました。

また、devopsサーバーのローカルDBはマスキング用のSQLを流すために必要であり、先のことも考えてMySQL 8.0.26を利用しました。各サービスのmy.cnfはAnsibleで共通にしています。マスキング用のSQLは開発メンバーとやり取りさせてもらいながら、個人情報を含むものはUPDATEして全て書き換えるように作り込みをしました。DB Dump先ですが、RDSのRead ReplicaからDumpをすることによりクラスターDBに影響がなくなります。ポイントとしては–skip-lock-tables –single-transactionのオプションを追加してREAD LOCKをさせないようにしましょう。ちなみにMySQL 8.0から8.0以前のバージョンのみ、Dump時に–skip-column-statisticsを指定することでANALYZE TABLE文の自動生成をなくすことでエラーの回避ができます。 これはMySQL 8.0以降ではオプティマイザがヒストグラム統計というものを考慮するようになったからになります。またMySQL 8.0では認証プラグインを必ずmysql_native_passwordに変更し、sql_modeでは特に利用していないので0にしました。

それでは実際のシェルスクリプトのコードと流れを記載しておきます。

・prd-cron

$ crontab -l
#Ansible: cron prd-masking.sh
0 0 * * 6 /var/www/hoge/scripts/auto-masking/prd-masking.sh > /var/log/auto-masking/auto-masking.log 2>&1

・prd-masking.sh

#!/bin/bash

set -eu

$("/home/hoge/.common/secrets.sh")

DB=hoge
MIGRATIONFILE01=masking.sql

# git pull
git -C /var/www/hoge/scripts/auto-masking pull
rm -rf ~/backup/*

# dump RDS prd
echo "dump prd"
mysqldump -v --skip-column-statistics --skip-lock-tables --single-transaction --default-character-set=utf8mb4 --set-gtid-purged=OFF -h "${PROD_HOST}" -u "${PROD_USER}" -p"${PROD_PWD}" "${DB}" | gzip > ~/backup/prd-bk.gz

# local db restore
echo "local drop/create db"
mysql -h localhost -u root -e "drop database hoge;"
mysql -h localhost -u root -e "create database hoge;"

echo "local restore db"
zcat ~/backup/prd-bk.gz | mysql -h localhost -u root "${DB}" -f

# masking local db
echo "masking local db"
mysql -h localhost -u root "${DB}" < /var/www/hoge/masking/${MIGRATIONFILE01}

# dump local masking db
echo "local dump db"
mysqldump --skip-column-statistics --skip-lock-tables --single-transaction --default-character-set=utf8mb4 --routines=0 --triggers=0 --events=0 --set-gtid-purged=OFF -h localhost -u root "${DB}" | gzip > ~/masking_db/mask-bk.gz

# s3 copy s3
echo "copy masking db s3"
aws s3 cp ~/masking_db/mask-bk.gz s3://xxxxxxxxxxxxxxxxxxxx/mysql/masking/

echo "delete dir backup masking_db"
rm -rf ~/backup/*
rm -rf ~/masking_db/*

## slack notify
/var/www/hoge/scripts/auto-masking/post_slack.sh "Prd masking OK. Please wait restore Stg."
  • masking.sql
use hoge;

-- clients
UPDATE clients SET
name = concat('テスト会社', id),
name_kana = concat('テストカイシャ', id),
address = concat('address', id),
tel = '00000000000';

-- users
UPDATE users SET
last_name = concat('山田'),
first_name = concat('太郎', id),
last_name_kana = concat('ヤマダ'),
first_name_kana = concat('タロウ', id),
tel = '00000000000',
email = concat('dev', id, '@example.com');
  • 毎週土曜日深夜0時に本番 devopsサーバーにてRDS(Read)からDump
  • 本番 devopsサーバーのローカルDBにリストア
  • 本番 devopsサーバーのローカルDBに対してマスキングSQLを適用
  • 本番 devopsサーバーのローカルDBをDump
  • StgのS3バケットにDumpファイルをコピー

・stg-cron

$ crontab -l
#Ansible: cron stg-masking-restore.sh
0 5 * * 6 /var/www/hoge/scripts/auto-masking/stg-masking-restore.sh > /var/log/auto-masking/auto-masking.log 2>&1

・stg-masking-restore.sh

#!/bin/bash

set -eu

$("/home/hoge/.common/secrets.sh")
DB=hoge

# git pull
git -C /var/www/hoge/scripts/auto-masking pull
rm -rf ~/masking_db/*

# s3 local copy s3
echo "copy masking db s3 local"
aws s3 cp s3://xxxxxxxxxxxxx/mysql/masking/mask-bk.gz ~/masking_db/

# restore masking stg RDS
echo "stg drop/create db"
mysql -h "${STG_HOST}" -u "${STG_USER}" -p"${STG_PWD}" -e "drop database hoge;"
mysql -h "${STG_HOST}" -u "${STG_USER}" -p"${STG_PWD}" -e "create database hoge;"

echo "stg restore db"
zcat ~/masking_db/mask-bk.gz | mysql -h "${STG_HOST}" -u "${STG_USER}" -p"${STG_PWD}" "${DB}" -f

echo "delete dir masking_db"
rm -rf ~/masking_db/*

## slack notify
/var/www/hoge/scripts/auto-masking/post_slack.sh "Stg restore OK"
  • 毎週土曜日5時にStg devopsサーバーからS3バケットに本番DBマスキングしたDumpファイルをコピー
  • Stg devopsサーバーから本番DBマスキングしたDumpファイルをStg用RDSにリストア

・Slack nofity

auto-renew-mysql-container

・環境

  • AWS CodeBuild
  • CloudWatch Events Rule
  • AWS ECR
  • docker hub
  • GitHub

次は開発環境のMySQLコンテナをリストアして自動化をします。(auto-renew-mysql-container) ここでは開発環境で利用しているMySQLのDockerfileを使いたいため、GitHubのリポジトリを連携したく、AWS CodeBuildとCloudWatch Events Ruleを利用して実装しました。毎週土曜日のAM9:00にマスキングされた本番DBのデータをMySQLコンテナにbuildしてリストアを行い、ECRにPushします。ちなみにCodeBuild上のインスタンスからMySQLコンテナを何度もpullしてしまうと以下Rate Limitsで制限がかかる仕様となりました。必ずdocker loginをしましょう。

https://www.docker.com/increase-rate-limits

また、CodebuildはTerraform化をしておらず、CloudWatch Events RuleのみTerraform化を行いました。以下buildspec.ymlとTerraformのコードを記載しておきます。

  • buildspec.yml
version: 0.2

phases:
  install:
    runtime-versions:
      docker: 18
    commands:
      - yum update -y && yum install -y curl
  pre_build:
    commands:
      - echo Login to AWS ECR...
      - aws ecr get-login-password --region ap-northeast-1 | docker login --username AWS --password-stdin xxxxxxxxxxx.dkr.ecr.ap-northeast-1.amazonaws.com
      - echo Logging in to Docker Hub...
      - echo $DOCKERHUB_PASS | docker login -u $DOCKERHUB_USER --password-stdin
      - cp -rp docker/dev/mysql/ /tmp/
      - cd docker/dev/mysql
      - docker build --no-cache -t mysql-dev:$IMAGE_TAG .
      - docker run -v /tmp:/tmp --name mysql-dev -d mysql-dev:$IMAGE_TAG
  build:
    commands:
      - hostname -i
      - docker ps
      - while true; do docker exec -i mysql-dev mysqladmin -u root ping || (sleep 10; false) && break; done
      - for i in $(find /tmp/mysql/ -name "*.sql"); do docker exec -i mysql-dev mysql -u root < $i ; done
      - docker exec -i mysql-dev mysql -u root -e 'create database hoge'
      - docker exec -i mysql-dev mysql -u root -e 'show databases'
      - aws s3 cp s3://xxxxxxxxxxxxx/mysql/masking/mask-bk.gz /tmp/
      - gunzip /tmp/mask-bk.gz
      - docker exec -i mysql-dev mysql -u root hoge < /tmp/mask-bk
  post_build:
    commands:
      - docker commit mysql-dev $IMAGE_REPO_NAME:$IMAGE_TAG
      - echo Push image to Amazon ECR...
      - docker push $IMAGE_REPO_NAME:$IMAGE_TAG
      - cd ../../../
      - bash ./scripts/auto-masking/post_slack.sh "Dev MySQL container restore OK"
  • codebuild.json
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "codebuild:StartBuild"
            ],
            "Resource": [
                "arn:aws:codebuild:ap-northeast-1:xxxxxxxxxx:project/auto-renew-mysql-container"
            ]
        }
    ]
}
  • terraform/stg/iam.tf
## cloudwatch_events_codebuild_role
resource "aws_iam_role" "cloudwatch_events_codebuild_role" {
  name = "cloudwatch_events_codebuild_role"
  assume_role_policy = file("files/assume_role_policy/ecs-scheduled-tasks.json")
}

resource "aws_iam_policy" "cloudwatch_events_codebuild_policy" {
  name = "cloudwatch_events_codebuild_policy"
  description = "cloudwatch_events_codebuild_policy"
  policy = file("files/assume_role_policy/codebuild.json")
}

resource "aws_iam_role_policy_attachment" "cloudwatch_events_codebuild_attach" {
  role = aws_iam_role.cloudwatch_events_codebuild_role.name
  policy_arn = aws_iam_policy.cloudwatch_events_codebuild_policy.arn
}
  • terraform/stg/cloudwatch_event_target.tf
# auto_renew_mysql_container
resource "aws_cloudwatch_event_rule" "auto_renew_mysql_container" {
  name = "auto_renew_mysql_container"
  description = "auto_renew_mysql_container"
  schedule_expression = "cron(0 0 ? * SAT *)"
  is_enabled = "true"
}

resource "aws_cloudwatch_event_target" "auto_renew_mysql_container" {
  target_id = "auto_renew_mysql_container"
  arn = "arn:aws:codebuild:ap-northeast-1:xxxxxx:project/auto-renew-mysql-container"
  rule = aws_cloudwatch_event_rule.auto_renew_mysql_container.name
  role_arn = aws_iam_role.cloudwatch_events_codebuild_role.arn
}
  • 作成されたロールにAmazonEC2ContainerRegistryPowerUser、S3の権限を付与
  • Slack nofity

※AWS Chatbotではfailのみ通知するように実装しています。

開発環境 MySQLコンテナ更新スクリプト

  • docker-update.sh
#!/bin/bash

## update MySQL container
aws ecr get-login-password --profile ecr | docker login --username AWS --password-stdin xxxxx.dkr.ecr.ap-northeast-1.amazonaws.com
cd ~/www/hoge/docker/dev/
docker-compose pull && docker image prune -f && docker-compose up -d
docker exec -it menta-app php artisan migrate

## update App container
docker stop menta-app-6 && docker rm menta-app
docker rmi menta-app
docker image prune -f && docker-compose up -d

最後に開発環境のMySQLコンテナは毎週各自更新する必要があるので、専用のシェルスクリプトを作成することによって手動での漏れを減らしました。

これにて実装は完了となります。

まとめ

実装内容がとにかくシビアで、疲労困憊でございます。たくさんの時間を使ってテストをしたおかげで継続的にリストアできる仕組みを作れたと感じています。開発メンバーからは非常にありがたいというお言葉もいただけたので、より良い環境を提供できたと思っています。また、現在本番DBの容量はまだまだ少ないので、今後データが増加していく場合、開発環境のMySQLコンテナも重くなってしまうので、データを一部分削る専用のTRUNCATE SQLを作成する必要があります。他にいいやり方あれば教えて下さい!