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

[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

$ 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

#!/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."

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');

$ 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

#!/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"

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
}

# 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
}

#!/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