Ansible で CREATE TABLE や INSERT を MySQL へ実行する方法

次世代システム研究室の データストア 好きの Y.I. です。

あるプロジェクトにて、MySQL のテーブル作成やレコード登録を Ansible で実施する仕組みを構築したのでご紹介します。

Ansible 公式ページ で MySQL の Module を確認したところ、Create Database や Create User はあるのですが、Create Table や Insert を実行してくれる Module が見つからなかったので、Ansible から shell Module を使って mysql コマンドを実施するシェルスクリプトを実行することで実現しました。

最終的に、Jenkins から Ansible を実行するようにして非エンジニアでも MySQL テーブルやレコード登録を出来るようにしました。

実行する CREATE TABLE(DDL) や INSERT(DML) 文は Excelマクロで出力するようにしています。テーブルの論理設計やデータ値を Excel へ記載すれば sql が作られるので非エンジニアでもDBデータの変更が可能となりました。

ゲームのように運用者（非エンジニア）がDBに密接に関わるようなところで力を発揮すると思います。

Ansible playbook role について

Ansible playbook role は以下の構成になっています。

roles/deploy-db/
├── files
│   ├── deploy_db_ddl.sh
│   └── deploy_db_mstdata.sh
└── tasks
　　└── main.yml

main.yml

実行する task です。

  - name: Re-Create Database/Table
    shell: /bin/sh /tmp/deploy-db/deploy_db_ddl.sh {{ target_db_name }} {{ deploy_db_host }}
    when: deploy_db_pattern == "R" or deploy_db_pattern == "Rebuild"
    register: result
    failed_when: result.rc not in [0]

  - name: MstTable Data
    shell: /bin/sh /tmp/deploy-db/deploy_db_mstdata.sh {{ target_db_name }} {{ deploy_db_host }}
    when: deploy_db_pattern == "M" or deploy_db_pattern == "MasterData"
    register: result
    failed_when: result.rc not in [0]

補足説明

• deploy_db_pattern変数によりDB/TABLE再作成かデータ再登録か実行するシェルスクリプトを切り分けています

deploy_db_ddl.sh

DBやTABLEを作り直した後にマスターデータを登録するシェルスクリプトです。
※開発環境のため DROP Database して作り直しています。本番環境では ALTER TABLE するスクリプトを実行します。

  #!/bin/sh

  base_dir="/tmp/deploy-db"

  # parameter

  db=$1
  host=$2
  user=$3
  pass=$4

  if [ -z "${db}" ]; then db="ddl_verify"; fi
  if [ -z "${host}" ]; then host="dev"; fi
  if [ -z "${user}" ]; then user="hoge"; fi
  if [ -z "${pass}" ]; then pass="fuga"; fi

  # CHECK
  echo "db=${db} / host=${host} / user=${user} / pass=${pass}";

  # DB 再作成
  echo "DROP/CREATE DATABASE";
  mysql -u ${user} -p${pass} -h ${host} ${db} -e "DROP DATABASE IF EXISTS ${db}; CREATE DATABASE ${db}  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;";

  # TABLE 再作成
  echo "DROP/CREATE TABLE";
  mysql --default-character-set=utf8mb4 -u ${user} -p${pass} -h ${host} ${db} < ${base_dir}/create_tables.sql
  test $? != 0 && echo "Failed DROP/CREATE DATABASE" && exit 1;

  # insert master data
  echo "INSERT MASTER RECORD";
  mysql --default-character-set=utf8mb4 -u ${user} -p${pass} -h ${host} ${db} < ${base_dir}/master_insert.sql
  test $? != 0 && echo "Failed INSERT MASTER RECORDS" && exit 1;

  exit 0;

補足説明

• mysql コマンドにて sql ファイルや SQL を実行しています
• test $? != 0 の行にて、 直前の mysql コマンドで実施した SQL が成功したか失敗したか判定しています
• 失敗した場合に、echo コマンドでどこで失敗したか分かるように出力したのち、終了コード1で処理を中止します
• Jenkins は終了コード 0=成功/1=失敗とみなすので正しく終了コードを返却すると、shell script 内の途中でエラーが発生したら Jenkins ジョブを正しく失敗終了とする事ができます

deploy_db_ddl.sh

マスターデータを削除した後に再登録するシェルスクリプトです。

# delete master data
echo "DELETE MASTER RECORD";
for i in `mysql -u ${user} -p${pass} -h ${host} -D ${db} -N -s -e "show tables like 'mst%';"`; do mysql -u ${user} -p${pass} -h ${host} -D ${db} -N -s -e "TRUNCATE TABLE ${i};"; done
test $? != 0 && echo "Failed DELETE MASTER RECORDS" && exit 1;

# insert master data
echo "INSERT MASTER RECORD";
mysql --default-character-set=utf8mb4 -u ${user} -p${pass} -h ${host} ${db} < ${base_dir}/master_insert.sql
test $? != 0 && echo "Failed INSERT MASTER RECORDS" && exit 1;

exit 0;

補足説明

• 対象の初期化するマスターテーブルを mysql の show tables 文で取得して、for ループで TRUNCATE TABLE しています

最後に

今回は MySQL への SQL でしたが、 PostgreSQL や Oracle DB に対しても少しの修正で対応可能です。言ってしまえば、シェルで出来ることは何でも Ansible で実行出来てしまいます。

今まで Ansible は該当の Module を探して使い方を調べて Infrastructure as Code を実現するものとだけ考えていましたが、シェルで出来ることは何でも出来る事が分かったので Ansible への苦手意識がなくなりました。今後は サーバーやDBや各種ミドルウエアの構築だけでなく、Ansible を積極的に利用するつもりです。

次世代システム研究室では、グループ全体のインテグレーションを支援してくれるアーキテクトを募集しています。アプリケーション開発の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ 募集職種一覧 からご応募をお願いします。

皆さんのご応募をお待ちしています。