akishin999の日記

調べた事などを書いて行きます。

MySQL のトリガからシェルスクリプトを実行する

MySQL

久々の更新。
MySQL の特定のテーブルのデータ変更を検知して実行したい処理があったので、トリガを使ってなんとか出来ないか調べてみました。

system 関数を使う

最初 system 関数(\!) を使ってできそうな以下の記事を見つけました。

MySQL :: Re: Can triggers call SYSTEM?
http://forums.mysql.com/read.php?99,170973,236208#msg-236208

が、mysql client 上からは上手く動くものの、アプリからの DB 変更に反応してくれず・・・。
ちょっと調べてみると以下のような記事がありました。

Using the following DOES NOT work!

\! /bin/ls >> /log/yourlog.txt

The !\ (bang or exclamation point + backslash) is a mysql console feature for running commands in the console.
These are ignored on the actual server. Do not be fooled.
Since you are testing in the console, it will appear to work once, but the trigger will not cause your code to fire.

http://patternbuffer.wordpress.com/2012/09/14/triggering-shell-script-from-mysql/

どうもトリガ内の system 関数は端末上からしか動かないようです。
どうしたものか・・・。

lib_mysqludf_sys

更に調べていると、以下の記事で UDF を作れば出来るという情報を発見。

MySQL で UDF を定義しよう - にょきにょきブログ
http://aoking.hatenablog.jp/entry/20120824/1345778096

ただ、UDF を自分で作るのはちょっとめんどい・・・。
と思って誰か作っている人がいないか探してみたらやはりありました。

mysqludf/lib_mysqludf_sys
https://github.com/mysqludf/lib_mysqludf_sys

というわけで早速導入してみます。
試した環境は以下です。

ちなみに本来 sudo がお作法ですが、 /usr/local/src/ にソース置く場合に面倒なので以下 root で作業しています。
(こういう場合本当はどうするのがいいんでしょうね？)

まずは GitHub から clone。

# cd /usr/local/src/
# git clone https://github.com/mysqludf/lib_mysqludf_sys.git
# cd lib_mysqludf_sys/

GitHub の issue に挙げられていますが、 64 bit 環境の場合、このままではコンパイルできないので Makefile を編集します。

# vim Makefile

gcc のコマンドラインについては以下の issue のコメントで紹介されているものに変更します。

https://github.com/mysqludf/lib_mysqludf_sys/issues/4#issuecomment-48470107

また、手元の MySQL では plugin ディレクトリが「/usr/lib/mysql/plugin」だったので LIBDIR の値も修正しています。

LIBDIR=/usr/lib/mysql/plugin

install:
        gcc -DMYSQL_DYNAMIC_PLUGIN -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o $(LIBDIR)/lib_mysqludf_sys.so

修正したらインストール。

# ./install.sh

実行すると mysql の root ユーザのパスワードを聞かれるので入力するとプラグインがロードされて UDF が登録されます。
登録された関数を確認してみます。

# mysql -uroot -p -e "select * from mysql.func;"
Enter password: 
+-----------------------+-----+---------------------+----------+
| name                  | ret | dl                  | type     |
+-----------------------+-----+---------------------+----------+
| lib_mysqludf_sys_info |   0 | lib_mysqludf_sys.so | function |
| sys_get               |   0 | lib_mysqludf_sys.so | function |
| sys_set               |   2 | lib_mysqludf_sys.so | function |
| sys_exec              |   2 | lib_mysqludf_sys.so | function |
| sys_eval              |   0 | lib_mysqludf_sys.so | function |
+-----------------------+-----+---------------------+----------+

無事追加されました。

ただ、同じく issue コメントで指摘されていましたが、このままでは apparmor が邪魔をして上手く動作しません。
ローカル環境という事もあり、サクッと無効にしてしまいます。

# ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
# apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

これでインストールは完了したはずなので、試しに使ってみます。

# mysql -uroot -p -e "select sys_eval('id');"
Enter password: 
+--------------------------------------------------+
| sys_eval('id')                                   |
+--------------------------------------------------+
| uid=117(mysql) gid=127(mysql) groups=127(mysql)
|
+--------------------------------------------------+

どうやらちゃんと動いているようです。

トリガから使ってみる

これでやっと本来やりたかったトリガからのスクリプト起動を試してみます。

実際にスクリプトを動かす場合、環境変数などを適切に設定する必要がある場合も多いので、ここでは Ruby スクリプトを実行するシェルスクリプトを作成し、そのシェルスクリプトを実行するトリガを作成してみました。

Ruby スクリプトとシェルスクリプトは以下のような単純なものです。

  • example.sh
#!/bin/sh
ruby /home/akishin/src/ruby/example.rb $@
  • example.rb
# -*- coding: utf-8 -*-
File.open('/tmp/ruby.log', 'a') { |f| f.puts ARGV.join(',') }

MySQL 上に example.sh を呼び出すトリガを作成します。
CONCAT で実行するコマンド文字列を作成し、lib_mysqludf_sys で追加された sys_eval 関数で実行しています。

DELIMITER //
DROP TRIGGER IF EXISTS logging_notes;
CREATE TRIGGER logging_notes AFTER INSERT ON notes
FOR EACH ROW
BEGIN
  DECLARE cmd CHAR(255);
  DECLARE result CHAR(255);
  SET cmd = CONCAT('/home/akishin/src/sh/example.sh ', NEW.id, ' ', NEW.title);
  SET result = sys_eval(cmd);
END //
DELIMITER ;

これで画面から適当に登録を行うと /tmp/ruby.log 内に id カラムの値と title カラムの値が記録されるようになりました！

実際今回自分の要件ではカラムの値までは必要なかったんですが、sys_eval であればカラム値もスクリプトに渡せるので活用範囲は広がりそうです。

参考

sql - Invoking a PHP script from a mysql trigger - Stack Overflow
http://stackoverflow.com/questions/1467369/invoking-a-php-script-from-a-mysql-trigger