Aurora MySQLでリストア権限のあるDBを個別にダンプするスクリプト

はじめに

こんにちは、omkです。

RDSを作成するときに一緒に作成するマスターユーザーですが、実はRDSのマスターユーザーはすべての権限を持っているわけではありません。
すべての権限をもっているのはAWSが管理用に使用している「rdsadmin」ユーザーだけで、これをRDSの利用者が使用することはできません。
よってDBをダンプする際に--all-databasesで一括でダンプしてしますと権限の問題で一部のDBが書き込みできずに全体でリストアがコケることがあります。

今回はmysqldumpコマンドを用いてマスターユーザーでリストアできるDBを個別にダンプしていくスクリプトを作成します。

RDSのマスターユーザーの権限について

まず、マスターユーザーの権限ですが、

mysql> SELECT * FROM mysql.user\G;

                  Host: %
                  User: admin
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: N
          Process_priv: Y
             File_priv: N
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: N
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: N
            ※省略※
     Load_from_S3_priv: Y
   Select_into_S3_priv: Y
    Invoke_lambda_priv: Y
 Invoke_sagemaker_priv: Y
Invoke_comprehend_priv: Y

ところどころ権限がありません。
特にSuper権限が無いことを確認します。

rdsadminはというと、すべての権限を所有しています。

                  Host: localhost
                  User: rdsadmin
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
            ※省略※
     Load_from_S3_priv: Y
   Select_into_S3_priv: Y
    Invoke_lambda_priv: Y
 Invoke_sagemaker_priv: Y
Invoke_comprehend_priv: Y

マスターユーザーはRDS作成時に作成される下記DBにリストア出来ません。

mysql > SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

よってこれらを除くデータベースをそれぞれダンプします。
ダンプ自体は取れるのでDBごとにそれぞれダンプするなら本来は別段除く必要もないのですが、リストアできないのにダンプしても……という場合にそもそもダンプしないようにします。

ダンプスクリプト

個別にDBをダンプして失敗時にSNSでメール通知を行います。
cronで1日1回ダンプして5世代持つ想定です。
ユーザーパスワードが直書きになっているので外部から読み込むかスクリプト自体の権限に注意しましょう。

#!/bin/bash

#============================================
# Set Vars
#============================================

BACKUP_DIR=""
FILENAME=`date +%Y%m%d`_dump
LOGFILE=""

#MySQL
DB_HOST=""
DB_USER=""
DB_PASS=""

# AWS CLI
SNS_TOPIC=""
SNS_SUBJECT=""
SNS_MESSAGE=""

# Get ALL DataBases except filled in 'grep -Ev "^(@@@@)$"'
dbs=$(echo "show databases" | mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" | grep -Ev "^(Database|performance_schema|information_schema|sys|mysql)$")
array=(${dbs// / })

# Set Logfile
echo -e `date +%Y%m%d` > $LOGFILE
echo >> $LOGFILE

#============================================
# Dump
#============================================

# initialize
error_count=0
i=0

# Dump Databases
for e in ${array[@]}; do
  mysqldump -h "$DB_HOST" ${array[$i]} --quick --set-gtid-purged=OFF --single-transaction  -u "$DB_USER" -p"$DB_PASS" > $BACKUP_DIR$FILENAME\_${array[$i]}.sql 2>> $LOGFILE

  # Check Exit status
  if [ $? -ne 0 ]; then
    let error_count++
    echo "[dump_error] ${array[$i]} was failed" >> $LOGFILE
    \rm -f $BACKUP_DIR$FILENAME\_${array[$i]}.sql
  fi
  let i++
done

# Report Errors
if [ $error_count -gt 0 ]; then
  # Alart
  aws sns publish --topic-arn "$SNS_TOPIC" --subject "$SNS_SUBJECT" --message "$(echo -e $SNS_MESSAGE)"
  exit 1
fi

#============================================
#  Rotate DumpFiles
#============================================

# zip
cd $BACKUP_DIR
find ./ -type f -and -daystart -mtime 0 -not -name "*.tar.gz" -exec tar -cvzf {}.tar.gz {} --remove-file \;

# Remove files
find "$BACKUP_DIR" -type f -name "*.tar.gz" -daystart -mtime +4 -exec rm -f {} \;

内容の説明に移ります。
以下の部分で全てのDB名を取得し、grepで権限のないDBを省きます。

dbs=$(echo "show databases" | mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" | grep -Ev "^(Database|performance_schema|information_schema|sys|mysql)$")

取得したDB名一覧がスペース区切りで変数に収納されるのでこれをDBごとに分けて配列に入れます。

array=(${dbs// / })

あとはforで個別にダンプして、ダンプに失敗したらエラーログに失敗したDB名を書き込んで最終的にSNSで通知します。
ダンプファイルは「{年月日}_dump_{DB名}.sql」になります。

終わった後は圧縮して保存し、5日経てば消します。

おわりに

これでリストア権限のあるDBを個別にダンプ出来ました。
リストア時にはリストアしたいDBのダンプデータを利用してリストアするだけです。
このやり方だと後からDBが増えても対応できるので便利ですね。
以上、お付き合いいただきありがとうございました。

参考

https://blog.serverworks.co.jp/tech/2011/10/17/mysql-to-rds-test/
https://qastack.jp/dba/35081/any-option-for-mysqldump-to-ignore-databases-for-backup