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.
http://patternbuffer.wordpress.com/2012/09/14/triggering-shell-script-from-mysql/
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.
どうもトリガ内の 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
Chart.js で凡例を表示する
Chart.js の円グラフで凡例を表示してみました。
ちなみに試したのは Chart.js の Version 1.0.1 beta-2 です。
<!doctype html> <html> <head> <meta charset="utf-8"> <title>Chart.jsで凡例付き円グラフ</title> <script src="Chart.min.js"></script> <style> .center { margin-left: auto; margin-right: auto; text-align: center; } #pieLegend { padding: 10px; overflow: hidden; position: relative; } ul.pie-legend { list-style: none outside none; float: left; margin: 0 0 0 0; padding: 0; position: relative; left: 50%; } ul.pie-legend > li { float: left; margin-right: 5px; padding: 5px; position: relative; left: -50%; } </style> </head> <body> <div id="chartArea" class="center"> <canvas id="pieArea" height="200" width="200"></canvas> <div id="pieLegend"></div> </div> <script> // 円グラフ用データ // この変数名を legendTemplate の中で使っているので注意 var datasets = [ { value: 70, color: "green", lineColor: "green", // 凡例の色 highlight: "seagreen", label: "緑" // 凡例のラベル }, { value: 30, color: "gold", lineColor: "gold", // 凡例の色 highlight: "yellow", label: "黄色" // 凡例のラベル } ]; // オプション var options = { // 凡例表示用の HTML テンプレート legendTemplate : "<ul class=\"<%=name.toLowerCase()%>-legend\"><% for (var i=0; i<datasets.length; i++){%><li><span style=\"background-color:<%=datasets[i].lineColor%>\"> </span><%if(datasets[i].label){%><%=datasets[i].label%><%}%></li><%}%></ul>" }; // 円グラフ描画 var myPie = new Chart(document.getElementById("pieArea").getContext("2d")).Pie(datasets, options); // generateLegend() の出力を HTML に入れる document.getElementById("pieLegend").innerHTML = myPie.generateLegend(); </script> </body> </html>
分かってしまえばなんて事はなかったんですが、最初ちょっと分かりづらかったです。
ChartNew.js だとオプションに legend: true を指定するだけだったりするので、ただ凡例を表示したいだけなら ChartNew.js の方が簡単ですね。
参考
HTML5 × Chart.jsで円グラフを描く | ビジュアルシンキング
http://www.visualthinking.jp/archives/13390
HTML5 × Chart.jsで円グラフを描く(オプション指定方法) | ビジュアルシンキング
http://www.visualthinking.jp/archives/13463
Packer 用のテンプレート集 Box-Cutter を使ってみる
以前試した Packer 用のテンプレート misheska/basebox-packer が Box-Cutter に移動してしまい、若干使い方も変わったようなので試してみました。
packer 自体のインストールは割愛。
Box-Cutter
https://github.com/box-cutter
上記の Organization アカウントの下にディストリビューション毎にプロジェクトが作成されているので、必要なものを clone します。
ここでは CentOS 用のプロジェクトを clone しました。
% git clone https://github.com/box-cutter/centos-vm.git
作成したいバージョンの json ファイルを編集します。
ファイル名で大体どれを弄ればいいかは分かるはず。
% cd centos-vm
% vim centos65.json
disk_size と memsize を変更しました。
"disk_size": 30420, "vmx_data": { "memsize": "1024", "numvcpus": "2", "cpuid.coresPerSocket": "1" } ・ ・ ・ "disk_size": 30420, "vboxmanage": [ ["modifyvm", "{{.Name}}", "--memory", "1024"], ["modifyvm", "{{.Name}}", "--cpus", "2"] ]
日本語環境にしたいので以下のキックスタートファイルを編集します。
% vim http/ks6.cfg
変更したのは以下。
lang ja_JP.UTF-8
keyboard jp106
timezone Asia/Tokyo
OS イメージの ISO ファイルのダウンロード先を変更したい場合は Makefile も変更する必要があります。
% vim Makefile
以下のように使用したいバージョンの ISO の URL が定義されているので、必要に応じて最寄りのミラーに変更するなどします。
・ ・ ・ CENTOS59_X86_64 ?= http://mirror.symnds.com/distributions/CentOS-vault/5.9/isos/x86_64/CentOS-5.9-x86_64-bin-DVD-1of2.iso CENTOS59_I386 ?= http://mirror.symnds.com/distributions/CentOS-vault/5.9/isos/i386/CentOS-5.9-i386-bin-DVD-1of2.iso CENTOS510_X86_64 ?= http://mirror.stanford.edu/yum/pub/centos/5.10/isos/x86_64/CentOS-5.10-x86_64-bin-DVD-1of2.iso CENTOS510_I386 ?= http://mirrors.kernel.org/centos/5.10/isos/i386/CentOS-5.10-i386-bin-DVD-1of2.iso CENTOS64_X86_64 ?= http://mirror.symnds.com/distributions/CentOS-vault/6.4/isos/x86_64/CentOS-6.4-x86_64-bin-DVD1.iso CENTOS64_I386 ?= http://mirror.symnds.com/distributions/CentOS-vault/6.4/isos/i386/CentOS-6.4-i386-bin-DVD1.iso CENTOS65_X86_64 ?= http://mirrors.kernel.org/centos/6.5/isos/x86_64/CentOS-6.5-x86_64-bin-DVD1.iso CENTOS65_I386 ?= http://mirrors.kernel.org/centos/6.5/isos/i386/CentOS-6.5-i386-bin-DVD1.iso ・ ・ ・
これで準備は完了。
Box-Cutter では packer コマンドを直接実行するのではなく、make を使うようになっています。
同梱の Makefile でビルド可能なターゲットは以下で確認できます。
% make list Prepend 'vmware/' or 'virtualbox/' to build only one target platform: make vmware/centos65 Targets: centos65 centos65-desktop centos64 centos64-desktop centos510 centos59 centos65-i386 centos64-i386 centos510-i386 centos59-i386
この中から必要なものを選択してビルドします。
メッセージにあるように、ビルドしたいターゲット名の前に 'vmware/' または 'virtualbox/' を付けることで特定の仮想環境向けの Box のみ作成するように指示することが出来るようです。
packer コマンドで「-only=virtualbox-iso」とか「-only=vmware-iso」指定するのと同じですね。
ここでは virtualbox の box だけ必要だったので以下のようにしました。
% make virtualbox/centos65
途中 VirtualBox が起動してインストールが進んで行きます。
暫く待つと以下に Box が生成されました。
% ls box/virtualbox
centos65-nocm.box
ざっと試したところこんな感じ。
テンプレートの種類も充実しているので、 Packer で Box 作成する際は Box-Cutter で決まりかなー。
Capistrano の db ロール
最近 Capistrano3 を触っていてハマったので忘れないようにメモ。
昨日身に染みて分かったんですが、Capistrano での 「role: :db」って「RDBMS が動作しているサーバ」ではなく、あくまで「rake db:migrate を実行するサーバ」に対して指定するためのロールだったんですね。
Capistrano3 を使っていて、最初以下のような server 指定を書いていました。
server '192.0.2.1', user: 'vagrant', roles: %w{web app} server '192.0.2.2', user: 'vagrant', roles: %w{web app} server '192.0.2.3', user: 'vagrant', roles: :db, no_release: true # DBサーバを指定
「role: :db」は DBサーバ用のロール、と思い込んでいたので、「RDBMS が動作しているサーバ」を指定。
また、DBサーバにはアプリは普通置かないだろ、という事で「no_release: true」も指定しています。
この状態で実行してみると何度やってもエラーが発生してしまいデプロイが正常に行えません。
試しに roles: :db の server 定義をコメントアウトしてみるとデプロイは正常に終了しましたが、当然ですが DB へのマイグレーションは実行されていませんでした。
いろいろ試行錯誤してみてもなかなか動かず困っていたのですが、調べてみると Stack Overflow に以下の投稿を発見しました。
Obviously, the name of role :db is misleading. As you pointed out, Capistrano defines it (with :primary => true) as a host that we execute rake db:migrate on, but database servers are not always running on such hosts. We can alter the schema of remote database server through a Rails app. The correct role name for this kind of host is not :db.
Inferring from the comments on lib/capistrano/configuration/roles.rb, the original meaning of the role :db is a host on which database servers are running. We are expected to login to the :db hosts and do some tasks.
The designers of Capistrano should have defined :migration role or something else for the deploy:migrate task. But the association between the :db role with this task was defined six years ago with 9a6d2fb and has not been changed since then.
Generally speaking, the users of Capistrano can define roles and associate them with tasks freely. The deploy:migrate task is provided just as a recipe for Rails developers. Unfortunately, this recipe includes a misconception about how we do the database migration and is used widely for a long time.
http://stackoverflow.com/questions/9781767/capistrano-db-role-whats-it-for/13637551#13637551
ソースコードを読んでみると、確かに migrate タスクは role: :db のサーバ上で release_path に移動して rake db:migrate を実行するように実装されているようです。
であれば本当の DB サーバの IPアドレスは database.yml にだけ書いてあれば OK、という事になりますね。
というわけで、以下のように修正したところ、今度はあっさり動作しました。
server '192.0.2.1', user: 'vagrant', roles: %w{web app db} server '192.0.2.2', user: 'vagrant', roles: %w{web app}
分かってしまえばそりゃそうだよな、という感じだったんですが、いろいろ検索してみても「DBサーバには role: :db を指定します」というような事を書いてある記事が結構あったりして、なかなか気付く事が出来ませんでした。
エラーメッセージも DB サーバ上で current に移動しようとしてディレクトリが無い!みたいな感じだったので、余計に分かり辛かったですね。
言い訳ですけど。
Stack Overflow の投稿では質問者が capify . とか書いているので、どうも Capistrano2 の頃からこういう挙動だったようです。
ハマった記憶が無かったので、念の為過去に書いた deploy.rb を確かめてみると、ローカルに HAProxy を立てて 127.0.0.1 で DBサーバへアクセスするようにしており、確かに role: :db にアプリを置いていないサーバの IPアドレスを指定しているものはありませんでした。
だから気付かなかったのか・・・。
それにしても検索してもあまり困ってる人がいなかったんですが、この辺りって常識だったりするんでしょうか?
挙動からすると role 名が db というのが混乱の元のような気がしてならないんですが・・・。
同僚に愚痴ったら「でも僕はハマった事ないですね」とはっきり言われてなんかショックでした。
精進したいと思います。
参考
Capistranoでmigration | kaeruspoon - おおいしつかさ
http://www.kaeruspoon.net/articles/350
Capistrano3 でデフォルトの環境を指定する
Capistrano3 からは、task の実行時にステージを指定しないと以下のようなメッセージが出るようになりました。
# cap deploy Stage not set, please call something such as `cap production deploy`, where production is a stage you have defined.
capistrano-ext が不要になり、デフォルトで複数の環境を扱えるようになった為の変更のようです。
これはこれで便利なのですが、処理によってはステージを分ける必要のないものもあり、その場合でもいちいち明示的に指定しなければならないのはちょっと面倒でした。
というわけで何か方法はないか検索してみると、既に試している方がいました。
Capistrano 3: Setting a Default Stage
http://dylanmarkow.com/blog/2014/01/08/capistrano-3-setting-a-default-stage/
以下のようにすることでデフォルトのステージを設定でき、実行時の指定を省略することができるようになるようです。
Capfile を編集します。
# vi Capfile
Capfile の末尾に以下の二行を追記します。
ここでは production 環境をデフォルトにしました。
Rake::Task[:production].invoke invoke :production
これで以降は「cap deploy」のようにステージを省略しても config/deploy/production.rb の内容が読み込まれ実行されるようになりました。
ただ、この設定が入っていると他のステージを指定しても production.rb の内容が読み込まれてしまうようなので、一度設定した場合は注意が必要そうです。
そのうち capistrano-ext のように「set :default_stage, "staging"」のような感じで指定できるようになって欲しいですね。
Migration ファイル生成時にカラムサイズやインデックス生成を指定できる
Rails の generator でマイグレーションファイルを生成する時に、カラムのサイズやインデックス追加を指定する事が出来るんですね。
恥ずかしながら今日まで知りませんでした。
例えば以下のような感じで生成すると
% bundle exec rails g scaffold note title:string{10}:uniq content:text:index state:integer{2} deleted_at:datetime lock_version:integer
以下のようなマイグレーションファイルが作成されます。
class CreateNotes < ActiveRecord::Migration def change create_table :notes do |t| t.string :title, limit: 10 t.text :content t.integer :state, limit: 2 t.datetime :deleted_at t.integer :lock_version t.timestamps end add_index :notes, :title, unique: true add_index :notes, :content end end
型の横のブレース内に指定した数字でカラムのサイズを、型とコロンで区切って index で通常のインデックス、uniq でユニークインデックスの追加が指定できます。
references は知っていたけどこの辺は知らなかったので、今まで生成した後に手で修正してました。
何時からできるようになってたんだろう?
最初から?
MariaDB 10.0.10 と Spider ストレージエンジンを使ってみる
MySQL の Sharding 用ストレージエンジンである Spider を試してみました。
公式のバイナリに同梱されている MariaDB 10.0.10 を使っています。
MariaDB のインストール
まずは MariaDB をインストールするために、yum リポジトリ定義ファイルを作成します。
# vi /etc/yum.repos.d/mariadb.repo
内容は以下。
# MariaDB 10.0 CentOS repository list - created 2014-04-02 07:21 UTC # http://mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.0/centos6-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 enable=0
保存したら以下のように yum コマンドでインストールします。
# yum install -y --enablerepo=mariadb MariaDB-server MariaDB-client
これで MariaDB 本体のインストールは完了。
mysqld_multi の設定
Sharding を試すのに複数台仮想機を用意してもいいんですが、今回は標準で付属している mysqld_multi コマンドを使用して複数インスタンスを用意しました。
まずは /etc/my.cnf.d/server.cnf を編集します。
# vi /etc/my.cnf.d/server.cnf
mysqld セクションに文字コードの設定を追記しておきます。
[mysqld] character-set-server = utf8
次は /etc/my.cnf を編集。
# vi /etc/my.cnf
以下を追記します。
[mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin log = /var/lib/mysql/multi.log [mysqld1] port = 3306 datadir = /var/lib/mysql1 pid-file = /var/lib/mysql1/mysql.pid socket = /var/lib/mysql1/mysql.sock [mysqld2] port = 3307 datadir = /var/lib/mysql2 pid-file = /var/lib/mysql2/mysql.pid socket = /var/lib/mysql2/mysql.sock [mysqld3] port = 3308 datadir = /var/lib/mysql3 pid-file = /var/lib/mysql3/mysql.pid socket = /var/lib/mysql3/mysql.sock
最初は server.cnf と同じ様に別ファイルを my.cnf.d 以下に作成し、そこに mysqld_multi 用の設定を書いてみたのですが、どうも何か間違っているのか mysqld_multi が設定を上手く認識してくれなかったので、しかたなく /etc/my.cnf 内に直接記述しました。
設定ファイルを保存したら各インスタンス用の datadir を作成します。
# mysql_install_db --datadir=/var/lib/mysql1 --user=mysql # mysql_install_db --datadir=/var/lib/mysql2 --user=mysql # mysql_install_db --datadir=/var/lib/mysql3 --user=mysql # chown -R mysql:mysql /var/lib/mysql1 /var/lib/mysql2 /var/lib/mysql3
これで準備はできたのでインスタンスを起動します。
# mysqld_multi start
コンソールには何も出ませんが、プロセス等を確認するとちゃんと三つのインスタンスが起動している事が分かります。
# ps -ef | grep [m]ysqld root 5963 1 0 21:41 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --port=3306 --datadir=/var/lib/mysql1 --pid-file=/var/lib/mysql1/mysql.pid --socket=/var/lib/mysql1/mysql.sock root 5970 1 0 21:41 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --port=3307 --datadir=/var/lib/mysql2 --pid-file=/var/lib/mysql2/mysql.pid --socket=/var/lib/mysql2/mysql.sock root 5983 1 0 21:41 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --port=3308 --datadir=/var/lib/mysql3 --pid-file=/var/lib/mysql3/mysql.pid --socket=/var/lib/mysql3/mysql.sock mysql 6364 5963 0 21:41 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql1 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql1/mariadb.err --pid-file=/var/lib/mysql1/mysql.pid --socket=/var/lib/mysql1/mysql.sock --port=3306 mysql 6366 5970 0 21:41 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql2 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql2/mariadb.err --pid-file=/var/lib/mysql2/mysql.pid --socket=/var/lib/mysql2/mysql.sock --port=3307 mysql 6372 5983 0 21:41 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql3 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql3/mariadb.err --pid-file=/var/lib/mysql3/mysql.pid --socket=/var/lib/mysql3/mysql.sock --port=3308 # netstat -tln | grep 330* tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
これで MariaDB の準備が出来ました。
Spider をインストール
ここでは一つ目のインスタンス(mysqld1) を Spider ノードとして動かしたいと思います。
mysql コマンドで mysqld1 の sock ファイルを指定して接続。
# mysql -uroot --socket=/var/lib/mysql1/mysql.sock
install_spider.sql を実行して Spider ストレージエンジンをインストールします。
install_spider.sql は /usr/share/mysql/ の下にあります。
MariaDB [(none)]> source /usr/share/mysql/install_spider.sql
SHOW ENGINES で追加されたことを確認します。
MariaDB [(none)]> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | SPIDER | YES | Spider storage engine | YES | YES | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
データノード(mysqld2, mysqld3) を SERVER として登録します。
MariaDB [(none)]> CREATE SERVER mysqld2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'spider', PASSWORD 'spider', HOST '127.0.0.1', PORT 3307); MariaDB [(none)]> CREATE SERVER mysqld3 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'spider', PASSWORD 'spider', HOST '127.0.0.1', PORT 3308);
正常に作成された事を確認。
MariaDB [(none)]> SELECT * FROM mysql.servers; +-------------+-----------+----+----------+----------+------+--------+---------+-------+ | Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner | +-------------+-----------+----+----------+----------+------+--------+---------+-------+ | mysqld2 | 127.0.0.1 | | spider | spider | 3307 | | mysql | | | mysqld3 | 127.0.0.1 | | spider | spider | 3308 | | mysql | | +-------------+-----------+----+----------+----------+------+--------+---------+-------+
一旦 mysqld1 を抜けて、Spider 用の DB とユーザを各インスタンスに作成します。
# mysql -uroot --socket=/var/lib/mysql1/mysql.sock -e "CREATE DATABASE example_db; GRANT ALL PRIVILEGES ON *.* TO 'spider'@'localhost' IDENTIFIED BY 'spider'; FLUSH PRIVILEGES;" # mysql -uroot --socket=/var/lib/mysql2/mysql.sock -e "CREATE DATABASE example_db; GRANT ALL PRIVILEGES ON *.* TO 'spider'@'localhost' IDENTIFIED BY 'spider'; FLUSH PRIVILEGES;" # mysql -uroot --socket=/var/lib/mysql3/mysql.sock -e "CREATE DATABASE example_db; GRANT ALL PRIVILEGES ON *.* TO 'spider'@'localhost' IDENTIFIED BY 'spider'; FLUSH PRIVILEGES;"
作成した example_db を指定して再度 mysqld1 に接続。
# mysql -uroot --socket=/var/lib/mysql1/mysql.sock example_db
example_db 上に SPIDER エンジンを指定した以下のようなテスト用のテーブルを作成します。
CREATE TABLE books ( id int AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, price int(11) NOT NULL default 0, created_at DATETIME NOT NULL, updated_at DATETIME, lock_version int(11) NOT NULL default 0, PRIMARY KEY (id) ) ENGINE = SPIDER DEFAULT CHARSET=utf8 PARTITION BY HASH(id) ( PARTITION p1 comment 'server "mysqld2", table "books"', PARTITION p2 comment 'server "mysqld3", table "books"' );
mysqld2, mysqld3 には同じテーブルを InnoDB で作成します。
- mysqld2
# mysql -uroot --socket=/var/lib/mysql2/mysql.sock example_db
CREATE TABLE books ( id int AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, price int(11) NOT NULL default 0, created_at DATETIME NOT NULL, updated_at DATETIME, lock_version int(11) NOT NULL default 0, PRIMARY KEY (id) ) ENGINE = InnoDB DEFAULT CHARSET=utf8;
- mysqld3
# mysql -uroot --socket=/var/lib/mysql3/mysql.sock example_db
CREATE TABLE books ( id int AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, price int(11) NOT NULL default 0, created_at DATETIME NOT NULL, updated_at DATETIME, lock_version int(11) NOT NULL default 0, PRIMARY KEY (id) ) ENGINE = InnoDB DEFAULT CHARSET=utf8;
mysqld1 に接続し、テストデータを投入してみます。
# mysql -uroot --socket=/var/lib/mysql1/mysql.sock example_db
INSERT INTO books(name, price, created_at) VALUES ('3日で分かるJava', 2500, NOW()); INSERT INTO books(name, price, created_at) VALUES ('3日で分かるRuby', 2300, NOW()); INSERT INTO books(name, price, created_at) VALUES ('独習仮想化', 5000, NOW()); INSERT INTO books(name, price, created_at) VALUES ('Java入門', 2000, NOW()); INSERT INTO books(name, price, created_at) VALUES ('入門Ruby', 2800, NOW()); INSERT INTO books(name, price, created_at) VALUES ('Effective Ruby', 4200, NOW()); INSERT INTO books(name, price, created_at) VALUES ('すごいRuby', 5800, NOW()); INSERT INTO books(name, price, created_at) VALUES ('Ruby徹底入門', 3000, NOW()); INSERT INTO books(name, price, created_at) VALUES ('RubyからJavaへ', 1800, NOW()); INSERT INTO books(name, price, created_at) VALUES ('クラウド大全', 6000, NOW());
それぞれのインスタンスでで SELECT してみると以下のような感じでした。
- mysqld1(SPIDER)
MariaDB [example_db]> select * from books order by id; +----+----------------------+-------+---------------------+------------+--------------+ | id | name | price | created_at | updated_at | lock_version | +----+----------------------+-------+---------------------+------------+--------------+ | 1 | 3日で分かるJava | 2500 | 2014-04-18 22:09:30 | NULL | 0 | | 2 | 3日で分かるRuby | 2300 | 2014-04-18 22:09:30 | NULL | 0 | | 3 | 独習仮想化 | 5000 | 2014-04-18 22:09:30 | NULL | 0 | | 4 | Java入門 | 2000 | 2014-04-18 22:09:30 | NULL | 0 | | 5 | 入門Ruby | 2800 | 2014-04-18 22:09:30 | NULL | 0 | | 6 | Effective Ruby | 4200 | 2014-04-18 22:09:30 | NULL | 0 | | 7 | すごいRuby | 5800 | 2014-04-18 22:09:30 | NULL | 0 | | 8 | Ruby徹底入門 | 3000 | 2014-04-18 22:09:30 | NULL | 0 | | 9 | RubyからJavaへ | 1800 | 2014-04-18 22:09:30 | NULL | 0 | | 10 | クラウド大全 | 6000 | 2014-04-18 22:09:31 | NULL | 0 | +----+----------------------+-------+---------------------+------------+--------------+
- mysqld2
MariaDB [example_db]> select * from books order by id; +----+----------------------+-------+---------------------+------------+--------------+ | id | name | price | created_at | updated_at | lock_version | +----+----------------------+-------+---------------------+------------+--------------+ | 2 | 3日で分かるRuby | 2300 | 2014-04-18 22:09:30 | NULL | 0 | | 4 | Java入門 | 2000 | 2014-04-18 22:09:30 | NULL | 0 | | 6 | Effective Ruby | 4200 | 2014-04-18 22:09:30 | NULL | 0 | | 8 | Ruby徹底入門 | 3000 | 2014-04-18 22:09:30 | NULL | 0 | | 10 | クラウド大全 | 6000 | 2014-04-18 22:09:31 | NULL | 0 | +----+----------------------+-------+---------------------+------------+--------------+
- mysqld3
MariaDB [example_db]> select * from books order by id; +----+----------------------+-------+---------------------+------------+--------------+ | id | name | price | created_at | updated_at | lock_version | +----+----------------------+-------+---------------------+------------+--------------+ | 1 | 3日で分かるJava | 2500 | 2014-04-18 22:09:30 | NULL | 0 | | 3 | 独習仮想化 | 5000 | 2014-04-18 22:09:30 | NULL | 0 | | 5 | 入門Ruby | 2800 | 2014-04-18 22:09:30 | NULL | 0 | | 7 | すごいRuby | 5800 | 2014-04-18 22:09:30 | NULL | 0 | | 9 | RubyからJavaへ | 1800 | 2014-04-18 22:09:30 | NULL | 0 | +----+----------------------+-------+---------------------+------------+--------------+
ちゃんとレコードが分散されてますね。
こんな感じで簡単に Sharding する事が出来ました。
ハマったところとか
実は途中、テストデータを INSERT する所で結構ハマりました。
公式ドキュメントの「08_sharding.txt」や、いくつかの参考にさせて頂いたサイトでは、以下の設定を入れていました。
[mysqld1] ・・・ # spider spider_internal_xa=1 spider_semi_trx_isolation=2 spider_support_xa=0
これらの設定は以下のような意味とのこと。
- 08_sharding.txt
spider_internal_xa=1の設定を行うとクライアント側での通常のトランザクションの操作が、内部的にxaトランザクションの操作に置き換えられ、spiderテーブルで複数のDBを利用しても同期更新が実現されます。
spider_semi_trx_isolation=2は、1SQLの間のトランザクション隔離レベルの変更を行うことで、sql実行中に他のクライアントによる更新が行われても一貫性のある結果が得られるようにします。状況に応じてspider_semi_trx_isolation=3の利用を選択しても良いでしょう。なお、spider_semi_trx_isolationはトランザクションをサポートしたストレージエンジンにのみ有効で、トランザクションをサポートしないMyISAMのようなストレージエンジンではspider_semi_table_lockを利用します。
・MySQLは現在2つ以上のxaトランザクションをサポートするストレージエンジンを利用できません。
SpiderとInnoDBを併用する場合は、コンフィグファイルに「spider_support_xa=0」を記載し、Spiderのxaサポートを無効にしてください。
Spiderのxaサポートが無効になっても、「internal_xa」は利用可能です。
http://d.hatena.ne.jp/tetsuyai/20110903/1317288617
説明を読んだ感じ、いかにも入れておいた方が良さそうな設定です。
というわけで、最初は mysqld1 に Spider エンジンをインストール後、これらの設定を追加し、mysqld1 を再起動していました。
# mysqld_multi stop 1 # mysqld_multi start 1
この状態でテストデータを INSERT したところ、以下のようなエラーが発生。
MariaDB [example_db]> INSERT INTO books(name, price, created_at) VALUES ('3日で分かるJava', 2500, NOW()); ERROR 12606 (HY000): This xid member is already exist
インスタンスを再起動したり、最初から作成し直したりしても全く解消しないので、もしやと思い SELECT をしてみると同じようなエラーが発生。
MariaDB [example_db]> SELECT * FROM books; ERROR 12605 (HY000): This xid is already exist
うーむ・・・一体何故だろう?
もしかして mysqld_multi で試しているのがいけなかったりするんだろうか。
いろいろと検索してみたんですが、結局回避策が見つからなかったため、最終的には上でまとめた手順の通り、これらの設定を入れないようにしたところ、上手く動作するようになりました。
しかし、説明によればこれらは XA トランザクション周りの設定との事なので、もしかしてこれらを設定しないとトランザクションが使えなくなってしまったりするのでしょうか。
気になったので確認してみます
mysqld1 に接続し、通常のトランザクションを開始します。
MariaDB [example_db]> begin;
再度同じテストデータを投入。
INSERT INTO books(name, price, created_at) VALUES ('3日で分かるJava', 2500, NOW()); INSERT INTO books(name, price, created_at) VALUES ('3日で分かるRuby', 2300, NOW()); INSERT INTO books(name, price, created_at) VALUES ('独習仮想化', 5000, NOW()); INSERT INTO books(name, price, created_at) VALUES ('Java入門', 2000, NOW()); INSERT INTO books(name, price, created_at) VALUES ('入門Ruby', 2800, NOW()); INSERT INTO books(name, price, created_at) VALUES ('Effective Ruby', 4200, NOW()); INSERT INTO books(name, price, created_at) VALUES ('すごいRuby', 5800, NOW()); INSERT INTO books(name, price, created_at) VALUES ('Ruby徹底入門', 3000, NOW()); INSERT INTO books(name, price, created_at) VALUES ('RubyからJavaへ', 1800, NOW()); INSERT INTO books(name, price, created_at) VALUES ('クラウド大全', 6000, NOW());
SELECT すると当然ですがデータは増えています。
MariaDB [example_db]> select * from books order by id; +----+----------------------+-------+---------------------+------------+--------------+ | id | name | price | created_at | updated_at | lock_version | +----+----------------------+-------+---------------------+------------+--------------+ | 1 | 3日で分かるJava | 2500 | 2014-04-18 22:09:30 | NULL | 0 | | 2 | 3日で分かるRuby | 2300 | 2014-04-18 22:09:30 | NULL | 0 | | 3 | 独習仮想化 | 5000 | 2014-04-18 22:09:30 | NULL | 0 | | 4 | Java入門 | 2000 | 2014-04-18 22:09:30 | NULL | 0 | | 5 | 入門Ruby | 2800 | 2014-04-18 22:09:30 | NULL | 0 | | 6 | Effective Ruby | 4200 | 2014-04-18 22:09:30 | NULL | 0 | | 7 | すごいRuby | 5800 | 2014-04-18 22:09:30 | NULL | 0 | | 8 | Ruby徹底入門 | 3000 | 2014-04-18 22:09:30 | NULL | 0 | | 9 | RubyからJavaへ | 1800 | 2014-04-18 22:09:30 | NULL | 0 | | 10 | クラウド大全 | 6000 | 2014-04-18 22:09:31 | NULL | 0 | | 11 | 3日で分かるJava | 2500 | 2014-04-18 22:36:03 | NULL | 0 | | 12 | 3日で分かるRuby | 2300 | 2014-04-18 22:36:03 | NULL | 0 | | 13 | 独習仮想化 | 5000 | 2014-04-18 22:36:03 | NULL | 0 | | 14 | Java入門 | 2000 | 2014-04-18 22:36:03 | NULL | 0 | | 15 | 入門Ruby | 2800 | 2014-04-18 22:36:03 | NULL | 0 | | 16 | Effective Ruby | 4200 | 2014-04-18 22:36:03 | NULL | 0 | | 17 | すごいRuby | 5800 | 2014-04-18 22:36:03 | NULL | 0 | | 18 | Ruby徹底入門 | 3000 | 2014-04-18 22:36:03 | NULL | 0 | | 19 | RubyからJavaへ | 1800 | 2014-04-18 22:36:03 | NULL | 0 | | 20 | クラウド大全 | 6000 | 2014-04-18 22:36:03 | NULL | 0 | +----+----------------------+-------+---------------------+------------+--------------+
ロールバックを実行。
MariaDB [example_db]> rollback;
SELECT してみます。
MariaDB [example_db]> select * from books order by id; +----+----------------------+-------+---------------------+------------+--------------+ | id | name | price | created_at | updated_at | lock_version | +----+----------------------+-------+---------------------+------------+--------------+ | 1 | 3日で分かるJava | 2500 | 2014-04-18 22:09:30 | NULL | 0 | | 2 | 3日で分かるRuby | 2300 | 2014-04-18 22:09:30 | NULL | 0 | | 3 | 独習仮想化 | 5000 | 2014-04-18 22:09:30 | NULL | 0 | | 4 | Java入門 | 2000 | 2014-04-18 22:09:30 | NULL | 0 | | 5 | 入門Ruby | 2800 | 2014-04-18 22:09:30 | NULL | 0 | | 6 | Effective Ruby | 4200 | 2014-04-18 22:09:30 | NULL | 0 | | 7 | すごいRuby | 5800 | 2014-04-18 22:09:30 | NULL | 0 | | 8 | Ruby徹底入門 | 3000 | 2014-04-18 22:09:30 | NULL | 0 | | 9 | RubyからJavaへ | 1800 | 2014-04-18 22:09:30 | NULL | 0 | | 10 | クラウド大全 | 6000 | 2014-04-18 22:09:31 | NULL | 0 | +----+----------------------+-------+---------------------+------------+--------------+
ちゃんとデータは巻き戻っているようです。
どうやら spider_internal_xa = 1 などの設定を入れなくても、ちゃんと複数DB間でのトランザクションは有効になっているようです。
ということは、ドキュメントには書いてあるけど別にこの辺の設定は不要、って事でいいのかなぁ?
この辺りいまいちまだ良く分かっていないです。
ただ、通常の commit, rollback が使えるのであれば Rails 等のフレームワークからも何も意識せずにトランザクションを使う事は出来そうですね。
まとめ
この他、試した範囲では mysqldump も普通に使えるし、ダンプからのインポートも全く問題ありませんでした。
後はデータノード追加時の Re-sharding をどうやるのかは時間のある時にでも検証しておきたいところ。
Spider は以前から試してみたかったんですが、こうやって公式バイナリに同梱されると導入の敷居が下がって嬉しいです。
個人的に今まで DB は MySQL を使う事が多かったんですが、次に新規に DB サーバ立てる機会があったら MariaDB を使いたいですね。
そのうち VP ストレージエンジンも同梱されるといいなー。
参考
Spider Storage Engine Overview - MariaDB Knowledgebase
https://mariadb.com/kb/en/spider-storage-engine-overview/
漢(オトコ)のコンピュータ道: 快適スケールアウト生活への第一歩。SPIDERストレージエンジンを使ってみよう!
http://nippondanji.blogspot.jp/2010/04/spider.html
MySQL Spiderエンジンを使ってみた。〜データベースシャーディング(sharding)とは〜 - 気ままに書いてみました日記
http://d.hatena.ne.jp/abcb2/20111010/1318224266