MySQLでクロス集計っぽいこと

以前Accessを使っていた時に、TRANSFORM .. PIVOT ..を使ったクロス集計クエリが意外と便利で
（TRANSFORM .. PIVOT ..が便利というより、クロス集計ウィザードが便利だった）
MySQL単体でもアレやりたいなぁとか思ったり思わなかったり。

実際のところ、MySQLにTRANSFORM .. PIVOT .. に該当する構文はMySQLには無いので
かなり泥臭い感じのクエリにはなるけれども、
下手をしなければテーブルスキャン1回分 + テンポラリテーブル + ファイルソートのコストで処理できたりするので、
手でログテーブルの中身をざっと洗ってざっとした傾向を見るのくらいには意外と使える。

テストデータはこんな感じ。



mysql> SELECT * FROM t1 LIMIT 3;
+-----+-----------+---------------------+
| seq | user_id   | dateval             |
+-----+-----------+---------------------+
|   1 | user_0009 | 2012-12-29 12:26:43 |
|   2 | user_0008 | 2012-12-29 01:12:07 |
|   3 | user_0007 | 2012-12-29 03:16:24 |
+-----+-----------+---------------------+
3 rows in set (0.00 sec)

アクセスログからカラムを絞って取り込んだ様なイメージ。

これを、user_idを縦軸に、datevalのxx時台を横軸にして引っこ抜く。

mysql> SELECT

    ->  user_id,

    ->  SUM(HOUR(dateval) = 0) AS '0',

    ->  SUM(HOUR(dateval) = 1) AS '1',

    ->  SUM(HOUR(dateval) = 2) AS '2',

    ->  SUM(HOUR(dateval) = 3) AS '3',

    ->  SUM(HOUR(dateval) = 4) AS '4',

    ->  SUM(HOUR(dateval) = 5) AS '5',

    ->  SUM(HOUR(dateval) = 6) AS '6',

    ->  SUM(HOUR(dateval) = 7) AS '7',

    ->  SUM(HOUR(dateval) = 8) AS '8',

    ->  SUM(HOUR(dateval) = 9) AS '9',

    ->  SUM(HOUR(dateval) = 10) AS '10',

    ->  SUM(HOUR(dateval) = 11) AS '11',

    ->  SUM(HOUR(dateval) = 12) AS '12',

    ->  SUM(HOUR(dateval) = 13) AS '13',

    ->  SUM(HOUR(dateval) = 14) AS '14',

    ->  SUM(HOUR(dateval) = 15) AS '15',

    ->  SUM(HOUR(dateval) = 16) AS '16',

    ->  SUM(HOUR(dateval) = 17) AS '17',

    ->  SUM(HOUR(dateval) = 18) AS '18',

    ->  SUM(HOUR(dateval) = 19) AS '19',

    ->  SUM(HOUR(dateval) = 20) AS '20',

    ->  SUM(HOUR(dateval) = 21) AS '21',

    ->  SUM(HOUR(dateval) = 22) AS '22',

    ->  SUM(HOUR(dateval) = 23) AS '23'

    -> FROM t1

    -> GROUP BY user_id WITH ROLLUP;

+-----------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+

| user_id   | 0    | 1    | 2    | 3    | 4    | 5    | 6    | 7    | 8    | 9    | 10   | 11   | 12   | 13   | 14   | 15   | 16   | 17   | 18   | 19   | 20   | 21   | 22   | 23   |

+-----------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+

| user_0001 |   79 |   88 |   86 |   88 |   70 |   71 |   84 |   91 |   70 |   77 |   79 |   91 |   85 |   90 |   89 |   93 |   84 |   67 |   90 |   82 |   88 |   88 |   83 |   80 |

| user_0002 |   88 |   71 |   85 |   74 |   75 |   85 |   82 |   93 |   85 |   71 |   91 |  106 |   80 |  103 |   70 |   95 |   89 |   90 |   86 |   72 |  103 |   80 |   72 |   79 |

| user_0003 |   84 |   79 |   81 |   85 |   90 |   87 |   84 |  111 |   80 |   88 |   80 |   86 |   84 |   85 |   70 |   81 |   85 |   92 |   75 |   75 |   85 |   89 |   74 |   82 |

| user_0004 |   82 |   73 |   83 |   89 |   75 |   79 |   74 |   93 |   76 |   90 |   89 |   86 |   70 |   88 |   93 |   87 |   68 |   73 |   91 |   90 |   94 |   86 |   82 |   93 |

| user_0005 |   75 |   83 |   82 |   96 |   96 |   80 |   85 |   78 |  101 |   79 |   82 |  102 |   80 |   99 |   67 |   92 |   79 |   78 |   82 |   76 |   88 |   95 |   88 |   86 |

| user_0006 |   79 |   82 |   81 |   81 |   93 |   95 |   76 |   87 |   86 |   73 |   91 |   74 |   87 |   85 |   87 |   94 |   96 |   91 |   71 |   73 |   92 |   89 |   85 |   81 |

| user_0007 |   87 |   73 |   79 |   87 |   97 |   75 |   98 |   85 |   82 |   94 |   82 |   76 |   80 |   93 |   81 |   81 |  100 |   74 |   88 |   79 |   58 |   94 |   73 |   80 |

| user_0008 |   86 |   81 |   82 |   90 |   83 |   73 |   67 |   92 |   81 |   90 |   75 |   85 |   79 |   81 |   80 |   90 |   63 |   84 |   70 |   82 |   75 |   76 |   89 |   75 |

| user_0009 |  109 |   75 |   66 |   82 |   83 |   98 |   96 |   80 |   52 |   93 |   72 |   78 |   85 |   86 |   66 |   70 |   82 |  102 |  101 |   89 |   77 |   79 |   67 |   85 |

| user_0010 |   97 |   87 |   71 |   78 |   75 |   78 |   87 |   98 |   81 |   82 |   85 |   77 |   79 |   66 |   69 |   73 |   91 |   71 |   98 |   93 |   88 |   77 |   94 |   95 |

| NULL      |  866 |  792 |  796 |  850 |  837 |  821 |  833 |  908 |  794 |  837 |  826 |  861 |  809 |  876 |  772 |  856 |  837 |  822 |  852 |  811 |  848 |  853 |  807 |  836 |

+-----------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+

11 rows in set (0.87 sec)

……泥臭い。。

変なサブクエリでなく、SELECT 真偽文 ..で0か1を返させてSUMするのがポイント（たぶん）

ホントは横軸を日付にして、7日以上連続アクセスしているユーザーの数を数える、とか考えていて、

`この結果セットを作るところまでがSQLで、連続アクセスになるかどうかの判定はPerlでラッピングして判定しよう'とか思っている。

計らずしも、年越しエントリになってしまった。。

今年もお世話になりました、来年もよろしくお願いします。

cactiが動かないので遠回りに色々トレースしてみた

俺の仕事用VMはこんな感じでメジャーバージョン違いのMySQLが複数入っている

（しかも、昨日から同時起動している）関係で、ポートやソケットファイルがデフォルトと違う状態で4つある。

色々あってそこにcactiを突っ込みたくなって、cactiのデータ格納用MySQLに5.5.29を任命。

portは64055, socketは/usr/mysql/5.5.29/data/mysql.sock。

さて、cacti/include/config.phpを書き換えてっと。

多分接続先をlocalhostにすると、TCP portを使わずにソケット使おうとして、

/var/lib/mysql/mysql.sockを見に行って転けるだろうから、

（ソケットを指定できそうなパラメータはconfig.phpには無かったので)

$host = '127.0.0.1';

$port = '64055';

にしておく。

…が、敢え無く失敗。

FATAL: Cannot connect to MySQL server on '127.0.0.1'. Please make sure you have specified a valid MySQL database name in 'include/config.php'

ヽ(｀Д´)ﾉ 肝心の`どうして接続出来なかったのか'が書いてNEEE。

ポートもDB名もユーザー名もパスワードも、クライアントからは行けるのになぁ。。

と思ってtcpdumpを取る。

# tcpdump -i any port 64055

tcpdump: verbose output suppressed, use -v or -vv for full protocol decode

listening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes

^C

0 packets captured

0 packets received by filter

0 packets dropped by kernel

…0 packets caputured。。

64055叩いてないねこりゃ。3306になってるかな？

行ってみようstrace。

# strace php index.php 2>&1 | egrep -v "map"

execve("/usr/bin/php", ["php", "index.php"], [/* 23 vars */]) = 0

..

socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 4

fcntl(4, F_SETFL, O_RDONLY)             = 0

fcntl(4, F_GETFL)                       = 0x2 (flags O_RDWR)

fcntl(4, F_GETFL)                       = 0x2 (flags O_RDWR)

fcntl(4, F_SETFL, O_RDWR|O_NONBLOCK)    = 0

connect(4, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("127.0.0.1")}, 16) = -1 EINPROGRESS (Operation now in progress)

fcntl(4, F_SETFL, O_RDWR)               = 0

poll([{fd=4, events=POLLIN|POLLPRI}], 1, 60000) = 1 ([{fd=4, revents=POLLIN|POLLERR|POLLHUP}])

setsockopt(4, SOL_SOCKET, SO_RCVTIMEO, "\2003\341\1\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0

setsockopt(4, SOL_SOCKET, SO_SNDTIMEO, "\2003\341\1\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0

setsockopt(4, SOL_IP, IP_TOS, [8], 4)   = 0

setsockopt(4, SOL_TCP, TCP_NODELAY, [1], 4) = 0

setsockopt(4, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0

poll([{fd=4, events=POLLIN}], 1, 60000) = 1 ([{fd=4, revents=POLLIN|POLLERR|POLLHUP}])

read(4, 0x189ede0, 16384)               = -1 ECONNREFUSED (Connection refused)

shutdown(4, 2 /* send and receive */)   = -1 ENOTCONN (Transport endpoint is not connected)

close(4)                                = 0

..

はい、大当たり。。

この出力のちょっと前あたりに

..

open("/var/www/cacti/lib/adodb/adodb-iterator.inc.php", O_RDONLY) = 4

..

open("/var/www/cacti/lib/database.php", O_RDONLY) = 4

..

open("/var/www/cacti/lib/adodb/drivers/adodb-mysql.inc.php", O_RDONLY) = 4

..

と並んでいるのを見つけるので、この辺りを探る。

lib/database.phpの中で

$cnn_id = ADONewConnection($dsn);

で接続しているところまでは雰囲気で判ったんだけど、その後がどうも判らない。。(PHP読めない)

$dsnはちゃんとport=64055を渡してるんだけどな。。書き方が違うのかしら。よく判らん。

結局こちらさまを参考にしてsocketファイルでつなぐ様に変えたら5秒で動かせたんだけど、

/etc/php.iniとかが怪しいのかなぁ…？

5秒で済むことに30分かけて結局謎が解けなくても、楽しかったから良いのです。

バイナリだったらこのままgdbでステップ実行してたと思う。

There's more than one way to do itなのですよ（いや、解決してないけど）

良いお年を。

mysqld_multiを使って強引に4つのメジャーバージョンを使い分ける

ちょこちょこ試したりする為に1つのVMにいくつもMySQLを入れてたりするけれど、
いちいちmy.cnfを書き分けるのが嫌になったのでついにmysqld_multiを使うことに。

mysqld_multiはたぶん1つのバイナリで複数のインスタンスを立ち上げることをメイン用途として想定している気がするけれど、意外と何とかなったのでメモ。


# ls -l /usr/mysql

合計 16
drwxr-xr-x 10 mysql mysql 4096 12月 27 16:23 2012 5.0.96
drwxr-xr-x 10 mysql mysql 4096 12月 27 11:26 2012 5.1.66
drwxr-xr-x 13 mysql mysql 4096 12月 27 11:44 2012 5.5.29
drwxr-xr-x 13 mysql mysql 4096 12月 27 16:48 2012 5.6.9

ソースコンパイルの時に、./configure --prefix=/usr/mysql/x.x.xxだったり
cmake -DCMAKE_INSTALL_PREFIX=/usr/mysql/x.x.xxだったりでまるっと突っ込んでいる。


強引にいったmy.cnfはこちら。



[mysqld_multi]
user       = mysqld_multi
password   = mysqld_multi
log        = /var/log/mysqld_multi.log

[mysqld]
user = mysql

log-error = error.log
log-warnings = 1
log-bin = bin
relay-log = relay
loose-binlog-format = mixed
loose-skip-general-log
loose-general-log-file = general.log
loose-slow-query-log
loose-slow-query-log-file = slow.log
long-query-time = 1
loose-log-output = FILE

character-set-server = utf8

innodb-buffer-pool-size = 1G
innodb-log-file-size = 128M
innodb-file-per-table = 1

[client]

[mysql]
no-auto-rehash

[mysqldump]
single-transaction
triggers
routines
events

[mysqld50]
basedir    = /usr/mysql/5.0.96
mysqld     = /usr/mysql/5.0.96/bin/mysqld_safe
socket     = /usr/mysql/5.0.96/data/mysql.sock
port       = 64050
pid-file   = /usr/mysql/5.0.96/data/mysql.pid
datadir    = /usr/mysql/5.0.96/data
log-slow-queries = slow.log
skip-log = general.log

[mysqld51]
basedir    = /usr/mysql/5.1.66
mysqld     = /usr/mysql/5.1.66/bin/mysqld_safe
socket     = /usr/mysql/5.1.66/data/mysql.sock
port       = 64051
pid-file   = /usr/mysql/5.1.66/data/mysql.pid
datadir    = /usr/mysql/5.1.66/data

[mysqld55]
basedir    = /usr/mysql/5.5.29
mysqld     = /usr/mysql/5.5.29/bin/mysqld_safe
socket     = /usr/mysql/5.5.29/data/mysql.sock
port       = 64055
pid-file   = /usr/mysql/5.5.29/data/mysql.pid
datadir    = /usr/mysql/5.5.29/data

[mysqld56]
basedir    = /usr/mysql/5.6.9
mysqld     = /usr/mysql/5.6.9/bin/mysqld_safe
socket     = /usr/mysql/5.6.9/data/mysql.sock
port       = 64056
pid-file   = /usr/mysql/5.6.9/data/mysql.pid
datadir    = /usr/mysql/5.6.9/data


5.0～5.6まで共通で使う部分を[mysqld]セクション、
バージョンごと個別の部分を[mysqldxx]セクションに書き込む。

[mysqldxx]セクションは[mysqld-x.x]セクション
(x.x以上のバージョンだったらこのセクション読む、というやつ)と違ってただの名前付けなので、
mysqld_multiから起動した時にだけ個々のセクションを読みに行く。
ので、動作確認でbin/mysqld_safeから上げた時に「あれれ？」となったり。。

この状態で、

# /usr/mysql/5.5.29/bin/mysqld_multi start 56

とかやると、5.6のインスタンスが起動できたり、
(mysqld_multiはただのラッパーなので、取り合えずGAバージョンのmysqld_multiを使っている)

# /usr/mysql/5.5.29/bin/mysqld_multi stop 51-56

とかやると5.1, 5.5, 5.6のインスタンスがいっぺんに止められたりする。


ちなみに.bashrcもひどいことになった。


alias mysqld_multi=/usr/mysql/5.5.29/bin/mysqld_multi
alias mysql50="/usr/mysql/5.0.96/bin/mysql -S /usr/mysql/5.0.96/data/mysql.sock -P 64050"
alias mysql51="/usr/mysql/5.1.66/bin/mysql -S /usr/mysql/5.1.66/data/mysql.sock -P 64051"
alias mysql55="/usr/mysql/5.5.29/bin/mysql -S /usr/mysql/5.5.29/data/mysql.sock -P 64055"
alias mysql56="/usr/mysql/5.6.9/bin/mysql -S /usr/mysql/5.6.9/data/mysql.sock -P 64056"
alias mysqladmin50="/usr/mysql/5.0.96/bin/mysqladmin -S /usr/mysql/5.0.96/data/mysql.sock -P 64050"
alias mysqladmin51="/usr/mysql/5.1.66/bin/mysqladmin -S /usr/mysql/5.1.66/data/mysql.sock -P 64051"
alias mysqladmin55="/usr/mysql/5.5.29/bin/mysqladmin -S /usr/mysql/5.5.29/data/mysql.sock -P 64055"
alias mysqladmin56="/usr/mysql/5.6.9/bin/mysqladmin -S /usr/mysql/5.6.9/data/mysql.sock -P 64056"



localhost向けにはsocket、-hでTCP/IP接続を使う場合と両方に備えて-Sと-Pが両方してあったり。
（つまり、この1台以外にもこんな環境が）

年末の大掃除の代わりに、ってことで。。（掃除しろ。。）

MySQL5.6.8(Red Hat系リポジトリ)の起動が失敗する

wingリポジトリさんのメーリングリストでご指名いただいたので調べた時のメモ。


MySQL5.6.8のrpmをビルドしてインストールすると、/etc/init.d/mysqld startでコケるらしい。
MySQL5.5.xを入れてあって、アンインストールしてからだとコケないらしい。
MySQL5.5.xを入れてあってアンインストールした後、/var/lib/mysqlを消してからだとコケるらしい。

メールに貼ってあったエラーログには、

ERROR: 1005  Can't create table 'db' (errno: 13)

errno: 13ってPermission Deniedだなぁ。。

MySQL5.5.xの作った/var/lib/mysqlを残しておけばコケないらしいので、

やっぱり/var/lib/mysqlを作る時とかのパーミッションがおかしいんだろうなぁ。

それでmysql_install_dbがコケてるのかしら。

でも/etc/init.d/mysqld startじゃなくて直接/usr/bin/mysql_install_dbを叩くと通るなぁ。

5.6系のmysql_install_dbはPerlスクリプトなので、シバンを#!/usr/bin/perl -dに書き換えて、

$DB::single=1をちょこちょこ追加して様子を見る。

Perl版mysql_install_dbがおかしいってどこかで見た気がするなぁとか思いながら。

（記憶にあったのはriywoさんのBlogだった。

 mysql_install_db perl version has a bug? http://weblog.riywo.com/post/35520875097）

うん、確かに/var/lib/mysqlをmysql:mysqlにした後、

/var/lib/mysql/mysqlをroot:rootで作ってその後chownされてないな。

Bugsにも上がってる。次回のビルドでは直るって。

Bug #67672 new perl mysql_install_db fails when run as root with --user option

取り敢えずBugsに上がっていた修正を自分のmysql_install_dbに入れて、

diff -c取ってＷＩＮＧ☆さんに丸投げしてみた。

こういう連携で何かが上手く動くのって、とても楽しかったりするよね。

ところで、WINGリポジトリのMySQLはepelのspecファイルとかを流用して作ってると聞いていたけど、

epel系のrpmってインストール時にmysql_install_dbじゃなくて、/etc/init.d/mysqldが叩くのね。

ちょっと新鮮でした。

せがれ vs とうさん in 2012

子育てエンジニア advent calendar 2012 参加エントリです。

せがれは今年5月で2歳になりました。
2012年のせがれとの攻防を以下に。

俺が子供の時もそうだった様に、ボタン（押す方）が大好き。
　⇒とうさん貧弱なデスクトップでMySQLコンパイル中。
　　⇒せがれ電源ボタン長押し。
　　　⇒Σ(ﾟдﾟlll) ｷﾞｬｰ
　⇒電源ボタンを押せない様に手でガードしながら、とうさん再度MySQLコンパイル。
　　⇒せがれ電源タップスイッチのスイッチ押下。
　　　⇒Σ(ﾟдﾟlll) ｷﾞｬｰ
家で勉強してられなーいorz

勉強用に安いノートPC買いました。これで死角は無い。
　⇒とうさん貧弱なノートPCでtpcc-mysqlロード中。コーヒーでも淹れるかな。
　　⇒せがれキーボード乱打。偶然Ctrl+Cが入ったっぽい。
　　　⇒Σ(ﾟдﾟlll) ｷﾞｬｰ
　⇒とうさん腹ばいになってログ解析中。
　　⇒迷わず背中にまたがる。いつもみたいにお馬さんパカパカしろよ、しないのかよ、うわーん！
　　　⇒Σ(ﾟдﾟlll) ｷﾞｬｰ背中を尻でどすどすしないで
結局デスクトップ用の机のモニターをどかしてやってるというorz

そろそろ買い換えたい初代XPERIA。でもまだまだ現役。
せがれはとうさんのすまほんでYoutube見るのがお気に入り。
　⇒とうさんメールなう。
　　⇒せがれ「なにみる？ なにみる？」
　　　⇒Σ(ﾟдﾟlll) たまにはYoutube意外のこともしてるんだよ！？
　⇒とうさんお風呂上り。
　　⇒せがれ「とうさんケイタイ、はいっ」 渡してくれる。
　　　⇒とうさん「あー、ありがとありがと。でもまだドライヤーしてないから持ってて」
　　　　⇒せがれ「うん。なにみる？」
　　　　　⇒Σ(ﾟдﾟlll) すまほんは湯上りの湿った指だと操作できないんだYO！？
最近はアプリを立ち上げてやれば自分で動画を選んで見るようになった。後世畏るべし。。

とうさん寝るときメガネ外す（あたりまえ）
いつもメガネを置くのはカーテンの後ろ（高さ1mくらい、窓の枠）
　⇒朝、せがれが先に起きている。床にはつるがねじ曲がって折れたメガネ。
　　⇒Σ(ﾟдﾟlll) ｷﾞｬｰいつの間に手が届くように
眼鏡屋さんが引くくらいつるがべきっといってたけど、なんとか直してくれました。
とうさん寝るときメガネ外す（リピート）
　⇒朝、せがれが先に（ｒｙ
　　⇒Σ(ﾟдﾟlll) ｷﾞｬｰ
2回目持っていったときは流石に色々心配されたり怒られたり可哀想な目で見られたり。。orz
取り敢えずメガネドラッグさん愛してる。

とうさん(平成)仮面ライダーが好き。
　⇒1歳の頃からせがれにベルトを巻かせて変身ポーズを練習させた。
　　⇒せがれ「へんしんっ！ あいだー、きっく！(カブト風)」
　　　⇒Σ(ﾟдﾟlll) どめすてぃっくばいおれーんす！
　　⇒せがれ「へんしんっ！(右手を自分のおへそにつける) ぷぃーじゅ！ ひー！ ひー！」 でんぐりがえし。
　　　⇒Σ(ﾟдﾟlll) ねえそれキックストライクなの！？ ﾁｮｰｲｲﾈｻｲｺｰ！？
　　　　⇒地味に色々巻き込まれてとうさんのご飯とかご飯とかご飯とかorz
仮面ライダーウィザード(2012/09～)のベルトはまだ買ってません。



これみんな、去年はできなかった、今年になってできる様になった攻撃。
来年はどんな攻撃を仕掛けてくるのか。
とうさんとせがれの戦いは来年も再来年も続く。

かかってきたまえ。
ただしメガネへの打撃と武器の使用は勘弁な。。orz

bashとPerlの真偽値的な何か

PerlスクリプトでDBI使わずにMySQL接続確認したい…と思ってたら、ハマった。



system("mysql --user=tpcc --password=xxxx") or die("MySQL connection failure.\nexit.\n");

……接続できなくてもdieしてくれないよ！？ Σ(ﾟдﾟlll)

というかむしろ、接続出来た時にdieしてるよ！？ Σ(ﾟдﾟlll)

↓はちゃんと動くのになぁ。

$ mysql --user=tpcc --password=xxxx || echo die

ERROR 1045 (28000): Access denied for user 'tpcc'@'localhost' (using password: YES)

die

と、思ったら。

system関数の戻り値は実行したプロセスの終了コードなので、接続成功すると正常終了で0なのですね。

ところでPerlの0は偽ですね。

つまり、mysqlコマンドが接続できた時が偽で、接続に失敗した時が真ですね？

...orz

気持ち悪いけど、

system("mysql --user=tpcc --password=xxxx") and die("MySQL connection failure.\nexit.\n");

と書いて事無きを得る。。

$HOME/.my.cnfで無理矢理遊ぶ

意外に知られていない$HOME/.my.cnfを使って遊んでみよう企画。

$HOME/.my.cnfはMySQL関連のプログラムを起動した時に最後に読み込まれるオプションファイルで、

オプションの優先順位が`後勝ち'なので直接渡したオプション以外は

ここに書いておいたものが/etc/my.cnfなんかと重複していても優先される。

かつ、環境変数$HOMEに依存するので、特定のUNIXアカウントに対してのみ悪戯設定出来る。

ではれっつごー。

$ cat .my.cnf

[mysql]

execute = "SHOW PROCESSLIST"

$ mysql

+----+------+-----------+------+---------+------+-------+------------------+

| Id | User | Host      | db   | Command | Time | State | Info             |

+----+------+-----------+------+---------+------+-------+------------------+

|  7 | root | localhost | NULL | Query   |    0 | NULL  | SHOW PROCESSLIST |

+----+------+-----------+------+---------+------+-------+------------------+

おおおお。

--executeオプションも利くんだねこれ。

ところで、--executeってコマンドラインオプションで打ち消せない気がする(少なくとも--skip-executeではダメだった)ので、

これを誰かさんの$HOME/.my.cnfに書いておけば楽しい誰も幸せにならないこと請け合い。。

副産物的に思い付いたんだけれど、

5.6の$HOME/.mylogin.cnf(別に平文で書くのが嫌じゃなければ$HOME/.my.cnfでも良い)と

デフォルトシェルをmysqlクライアントにすると一発でログイン出来る上に。

$ mysql_config_editor print --all

[client]

user = root

password = *****

$ grep myadmin /etc/passwd

myadmin:x:502:503::/home/myadmin:/usr/bin/mysql

$ su - myadmin

Password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.5.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

# tail /var/log/secure

..

Nov 28 10:06:02 cent01 su: pam_unix(su-l:session): session opened for user myadmin by root(uid=0)

Nov 28 10:07:09 cent01 su: pam_unix(su-l:session): session closed for user myadmin

suの履歴はsyslogに残るから、この経路以外でのmysqlのrootへのアクセスを塞ぐ

（.mylogin.cnf頼りrootのパスワードを配布しない)様にすれば、

MySQLにrootでアクセスした時のUNIXアカウントとアクセスしていた時間が綺麗にログに取れる。

んー。。これちょっと実装というか設定したいなぁ。。

Perlのprintfの謎4096バイト問題

/proc/net/devを定期的に読んで、テキトーに整形して
テキストファイルに吐き出すスクリプトを書いていた時に気付いた。

3秒に1回/proc/net/devを読んでprintfで書かせる様にしている割に、
なぜかtail -fで出力先ファイルを開いても何も表示されない。

かと思うと、しばらくしてからがばっと出力、またしばらく沈黙が続く。

コードはこんなの。


while() {

if (! -e $sPidFile) { exit 0; }

### open /proc/net/dev.
sysopen(IN, "/proc/net/dev", O_RDONLY);
printf(OUT "%s", time());

while ($sBuff = <IN>) {
if ($sBuff =~ /(.+):(.+)/) {
chomp($sBuff);
$sBuff =~ s/.+:\s+//;
printf(OUT "\t%s", $sBuff);
} else {
next;
}
}

printf(OUT "\n");
sleep($sInterval);
}

美しくないけど泣かない。

strace -t -e write,readで見てみたらこんなになってた。

14:59:12 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:12 read(5, "", 4096)              = 0

14:59:15 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:15 read(5, "", 4096)              = 0

14:59:18 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:18 read(5, "", 4096)              = 0

14:59:21 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:21 read(5, "", 4096)              = 0

14:59:24 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:24 read(5, "", 4096)              = 0

14:59:27 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:27 read(5, "", 4096)              = 0

14:59:30 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:30 read(5, "", 4096)              = 0

14:59:33 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:33 read(5, "", 4096)              = 0

14:59:36 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:36 write(4, "909543\t0       0    0    0    0 "..., 4096) = 4096

14:59:36 read(5, "", 4096)              = 0

14:59:39 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:39 read(5, "", 4096)              = 0

14:59:42 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:42 read(5, "", 4096)              = 0

14:59:45 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:45 read(5, "", 4096)              = 0

14:59:48 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:48 read(5, "", 4096)              = 0

14:59:51 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:51 read(5, "", 4096)              = 0

14:59:54 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:54 read(5, "", 4096)              = 0

14:59:57 read(5, "Inter-|   Receive               "..., 4096) = 569

14:59:57 read(5, "", 4096)              = 0

15:00:00 read(5, "Inter-|   Receive               "..., 4096) = 569

15:00:00 read(5, "", 4096)              = 0

15:00:03 read(5, "Inter-|   Receive               "..., 4096) = 569

15:00:03 read(5, "", 4096)              = 0

15:00:06 read(5, "Inter-|   Receive               "..., 4096) = 569

15:00:06 read(5, "", 4096)              = 0

15:00:09 read(5, "Inter-|   Receive               "..., 4096) = 569

15:00:09 read(5, "", 4096)              = 0

15:00:12 read(5, "Inter-|   Receive               "..., 4096) = 569

15:00:12 write(4, "   0    0    0     0       0    "..., 4096) = 4096

読んだものを加工してから書かせてるから、毎回569バイトずつ溜まってる訳じゃないはずなんだけど、

なんか4096バイトまで溜め込んでwrite()している雰囲気。

なんだろう。。

噂どおりのslave_parallel_workers

やっとparallelのRとLを間違えなくなった。。

http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_slave-parallel-workers

個人的にかなり期待していた5.6の新機能ではあるけれど、実際試してみた感想をメモ。


1) 前提条件として、パラレルで動けるのはデータベース単位。
　⇒全部同じデータベースに突っ込んである環境だと効果なし。
　⇒USEステートメントで選んだデフォルトデータベースでなく、
　　ちゃんと実効データベース(--replicate-wild-*-tableみたいな感じ)で判定してくれるっぽい。

2) データベース間をまたぐステートメント(INSERT INTO d1.t1 SELECT * FROM d2.t1とか)は
　2つのデータベースのparallel workを阻害する。
　⇒d1に対する反映とd2に対する反映はそのステートメントが終わるまで待たされる。
　　d3に対する反映は追い越してparallel workできる。

3) パラレルじゃないレプリケーションならリトライできるエラーでも、
　SQL_THREADが止まった上にその更新は適用されず失われる。
　⇒スレーブ側でSELECT .. FOR UPDATEでロックしてlock wait timeout exceededにしたら、
　　SQL_THREADは止まったけどSTART SLAVEしてもその更新は適用されないままさっくり続きのRelay Logを待つ。
　　⇒START SLAVEするたびにWarningで`こういう動作になるよ！'って教えてくれるけど。


うーん。。。
2)は良いとして、1)が問題にならないくらいデータベースがきちんと分かれてないとなぁ。。
レプリケーションの並列度を上げる為に水平分割とか1テーブル1データベース構成とか。。

あと、3)が地味に痛い。
クエリの中身とかはエラーログに吐いてくれるから、START SLAVEする前にそのクエリ手で叩けば良いのかな？

GTIDと並んでいきなり試すには鬼門な感じがする。。


【2012/02/06 10:18】
ちなみにこんなWarning。

slave_transaction_retries is not supported in multi-threaded slave mode. In the event of a transient failure, the slave will not retry the transaction and will stop.

mysqldumpの--flush-logsオプション

mysqldumpの--flush-logsオプション、mysqldumpを取ると同時にFLUSH LOGSしてくれるので、
バイナリログの管理が便利になったりするあのオプション。

ドキュメントには、
`--master-dataまたは--lock-all-tablesを一緒につけないと、複数データベースをダンプする時に
　各データベースのダンプごとにFLUSH LOGSするよ！'
とか書いてあるんだけど、一向に手元で再現しなかったのでソースを読んでみた。

結論、5.5.21以降(5.6も)では、--flush-logs --single-transactionならFLUSH LOGSは1回だけしか実行されない。
5.5.20以前(5.1も)では、--flush-logs --single-transactionではデータベースごとにFLUSH LOGSされる。



5.5.21以降のclients/mysqldump.cのmain関数の中で、

-------------------------------------------------------



  /*
    Flush logs before starting transaction since
    this causes implicit commit starting mysql-5.5.
  */
  if (opt_lock_all_tables || opt_master_data ||
      (opt_single_transaction && flush_logs) ||
      opt_delete_master_logs)
  {
    if (flush_logs || opt_delete_master_logs)
    {
      if (mysql_refresh(mysql, REFRESH_LOG))
        goto err;
      verbose_msg("-- main : logs flushed successfully!\n");
    }

    /* Not anymore! That would not be sensible. */
    flush_logs= 0;
  }

-------------------------------------------------------






となっているのを発見した。。

5.5.20の同じ部分は、

-------------------------------------------------------

  if (opt_delete_master_logs)
  {
    if (mysql_refresh(mysql, REFRESH_LOG) ||
        get_bin_log_name(mysql, bin_log_name, sizeof(bin_log_name)))
      goto err;
    flush_logs= 0;
  }
  if (opt_lock_all_tables || opt_master_data)
  {
    if (flush_logs && mysql_refresh(mysql, REFRESH_LOG))
      goto err;
    flush_logs= 0; /* not anymore; that would not be sensible */
  }
-------------------------------------------------------

と、ドキュメントどおりの動作。

ドキュメントのバグとして人生初Bugs投稿しました。。ドキがムネムネ。


ネタをくれた@studio3104さん、ありがとうございました(*-人-) 楽しかったです

my.cnfのパラメータ優先順位

複数のmy.cnfがデフォルトで読み込まれるパスに置いてあったり、
中で!includeしたり、直接オプションを渡したりしたとき。

同じパラメータが指定されている場合は、常に後勝ち（後から設定された方で上書き）です。


Linux系のMySQLでは、
/etc/my.cnf -> /etc/mysql/my.cnf -> SYSCONFDIR/my.cnf -> $MYSQL_HOME/my.cnf -> defaults-extra-fileで指定されたファイル -> $HOME/.my.cnf -> 直接渡したオプション
の順番で読み込まれる。

優先順位で考えると、後勝ちなので後ろが一番強い。

SYSCONFDIRはcmakeのオプションで指定するものの、デフォルトは/usr/local/mysql/etcになっていた（5.6.8-rcのsource）
$MYSQL_HOMEは、mysqld_safeがごにょごにょして/usrに置き換えられる場合が多い（CentOS）


さて、実験。

# cat /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 200M

# cat /usr/my.cnf   #MYSQL_HOME/my.cnf

[mysqld]
innodb_buffer_pool_size = 1G

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
1 row in set (0.00 sec)


　⇒後勝ち。


# cat /etc/my.cnf
!include /etc/my2.cnf
[mysqld]
innodb_buffer_pool_size = 200M

# cat /etc/my2.cnf
[mysqld]
innodb_buffer_pool_size = 250M

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 209715200 |
+-------------------------+-----------+
1 row in set (0.00 sec)

　⇒includeして250Mの値を1行目で得た後、3行目で200Mで上書き。

# cat /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 200M

!include /etc/my2.cnf

# cat /etc/my2.cnf
[mysqld]
innodb_buffer_pool_size = 250M

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

+-------------------------+-----------+

| Variable_name           | Value     |

+-------------------------+-----------+

| innodb_buffer_pool_size | 262144000 |

+-------------------------+-----------+

1 row in set (0.00 sec)

　⇒200Mの値を3行目でincludeして250Mの値で上書き。

ただし、--defaults-fileを指定すると、そのファイル以外のデフォルトファイルは読まなくなる。

他のデフォルトファイルも読ませたい場合は、--defaults-extra-fileで指定すること。


【2012/11/13 14:08】
本家のリンク貼り忘れた。。

http://dev.mysql.com/doc/refman/5.5/en/option-files.html


【2013/05/30 00:10】
MariaDB 5.5.30のINSTALL_SYSCONFDIRは/etcになってた。
/etc/my.cnf -> /etc/mysql/my.cnf -> /etc/my.cnfの順番に読んだりしないのこれ？（調べてない

MySQL5.6.8ではInnoDBログファイルサイズの変更がお手軽に

今まではinnodb_log_file_sizeを変えると
テーブルスペースとログファイル両方の再作成が必要だったのが、すごくお手軽な感じになっていた！



121109 10:39:01 [Warning] InnoDB: Resizing redo log from 2*8192 to 2*16384 pages, LSN=1626134
121109 10:39:01 [Warning] InnoDB: Starting to delete and rewrite log files.
121109 10:39:01 [Note] InnoDB: Setting log file ./ib_logfile101 size to 256 MB
InnoDB: Progress in MB: 100 200
121109 10:39:02 [Note] InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Progress in MB: 100 200
121109 10:39:02 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
121109 10:39:02 [Warning] InnoDB: New log files created, LSN=1626134


テンポラリファイル作って内容を移してリネームしている様子。
ファイルサイズ大きめにすると、作ってる様子がちゃんと観察できた。
小さいログファイルサイズから大きいログファイルサイズに変更できるのは勿論、
大きいサイズから小さいサイズに変更するのも問題なさそう（ログ上は）

innodb_log_files_in_groupを増やしたり減らしたりするのも問題なく処理してくれる。

使う機会があるかどうかは置いておいて、地味に感動した！

MySQL5.6.8でrpmインストール時に変更になったこと

rpmでMySQL-serverをインストールすると、最後に自動でmysql_install_dbが流れるけれど、

mysql_install_dbに--random-passwordsオプションというのが出来て、

rpmでインストールした時にはそのオプションが有効な状態で流れるとのこと。

これによって、rootの初期パスワードが空っぽじゃなくなる。

【2016/03/14 09:22】
MySQL 5.7では更に変わって、mysqld --initializeになっていて.mysql_secretは作らなくなっています。こちらもどうぞ。 http://yoku0825.blogspot.jp/2015/03/mysql-576-mysqlinstalldbmysqld.html

　⇒/root/.mysql_secretというファイルに書いてあった。

　　カレントディレクトリじゃなくてホームディレクトリに作るっぽい。

　　中身はこんなの。

# cat /root/.mysql_secret

# The random password set for the root user at Fri Nov  9 10:15:47 2012 (local time): EtZjQcMc

# mysql -pEtZjQcMc

mysql> SELECT user,host,password FROM mysql.user;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

　⇒mysql.user.password_expiredの値（これも5.6の新機能）が'Y'になってるので、

　　先にパスワードを変えないと先に進めない。

mysql> SET PASSWORD = '';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user,host,password,password_expired FROM mysql.user;

+------+-----------+-------------------------------------------+------------------+

| user | host      | password                                  | password_expired |

+------+-----------+-------------------------------------------+------------------+

| root | localhost |                                           | N                |

| root | cent24    | *80B1CF9111F7F255A9A5DC45068AFE299FF3CEB3 | Y                |

| root | 127.0.0.1 | *80B1CF9111F7F255A9A5DC45068AFE299FF3CEB3 | Y                |

| root | ::1       | *80B1CF9111F7F255A9A5DC45068AFE299FF3CEB3 | Y                |

+------+-----------+-------------------------------------------+------------------+

4 rows in set (0.00 sec)

確かに無名ユーザーも消えてるし、パスワードはさっきのが設定されてるし、

パスワードはExpireされてる。

5.6がGAになって新しく試した人が、

「rootでログインできません！」

とか路頭に迷う日が来たり？（しないか


【2012/11/09 10:34追記】
mysql_install_dbはついでに、/usr/my.cnf（←が存在すれば/usr/my-new.cnf）を勝手に作ってくれるんだけど、

# cat /usr/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

なんだかなぁ、な内容。。
せめて搭載メモリサイズ拾ってちゃちゃっと値を入れてくれれば良いのに(´･ω･｀)

mysqlbinlogでバイナリログをバックアップするとか

mysqlbinlogは今まで（バックアップ用途で）リモートからバイナリログを読ませようとしても、

・SQLテキスト形式にエンコードした後のものを出力させるだけ。
・その時点の最後のエントリまでしか読み込めない。
・出力先ファイル名は1つしか指定できないので、
　読み込み元のバイナリログが複数でも出力は1つ。

だったのが、5.6のmysqlbinlogでは

・エンコードする前の状態のまま書き出せる。
・tail -fぽく待機して、更新があれば続けて書き出せる。
・ファイル名はマスターのものと同じものにできる。
　スイッチしたらmysqlbinlog側のファイル名も変わる。

と、スレーブに--log-slave-updatesを付けた様な動作が出来る様になった。
バックグラウンドにして常駐させておけば、暇そうなサーバにバイナリログをリアルタイムにバックアップ出来る。
マスターから見るとレプリケーションスレーブが1台増えるのと同じはずなので、
scpやftpで1時間に1回がりがり取るよりもI/O負荷的に優しい（たぶん）
バックアップ先のサーバから見た時に、一気に転送されるのとずっとちまちま転送されるのと
どっちが幸せなのかはちょっと考える余地があるかも。
運用してないので多数派がワカラナイ。。

mysqlbinlogさえ入っていればそれだけで済むので
バックアップ先のサーバにMySQL-clientパッケージだけ入れておけば良いし、
試してみる価値はありそうかなぁと。


使い方。

$ mysqlbinlog --read-from-remote-server --host=cent01 --user=repl --password=repl --stop-never --raw --result-file=cent01- bin.000001 &

--userにはREPLICATION SLAVE権限を持つユーザーを指定。
--stop-neverでtail -fっぽい動作になる。
--rawで、バイナリログをデコードせずにバイナリのまま、かつ、マスターのファイル名を踏襲してコピー。
--result-fileは--rawと一緒に指定した時は、保存先ファイル名のプレフィックスになるので、
cent01にはlog-bin=binと指定してあって大本のバイナリログファイル名がbin.000001～になる場合、
コピー先のバイナリログファイル名はcent01-bin.000001～になる。
最後の引数は、コピーを開始する最初のバイナリログファイル名。これはマスター上の名前で。

上手く使えばちょっとしたバックアップには使える予感。
--raw抜きで--stop-neverだけでもちょこちょこしたものに使えそうかな？

Perl用のBinlog APIとかあったなぁとふと思い出した。。


【2013/03/15 12:25】
資料を作ってふと気付いたんですが、mysqlbinlog --stop-neverしていると、
mysqlbinlogをCtrl+Cで切ってもコネクションが残存してしまう。
Bugs上げてみた。。

http://bugs.mysql.com/bug.php?id=68681

MySQL5.6 .mylogin.cnfで遊んでみる

What's newの頭に書かれているのに誰もやってなさそうなので遊んでみる。

遊んでみるとは言っても、$HOME/.my.cnfにuser=/password=で書くのとやってることは変わらない。
今まで平文で書くのが嫌で特にやってなかったけど、これならやっても良いかなーという風情。

http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html


$ mysql_config_editor set --user=tpcc --password
Enter password: <パスワード手入力>


$ mysql_config_editor print --all
[client]
user = tpcc
password = *****


とまあこの通り記録される。
何も指定しないとclientセクションに記録されるけれど、セクションを指定することも出来る。



$ mysql_config_editor set --login-path=mysqldump --user=root --password
Enter password: <パスワード手入力>

$ mysql_config_editor print --all
[client]
user = tpcc
password = *****
[mysqldump]
user = root
password = *****


追加された。
これで-uや-pなしでmysqldumpの時はrootアカウント、
mysqlクライアントやmysqlcheckとかの時はtpccアカウントでログインできる。

予めDBAがこれ作って読み取りだけして配布しておけば、
ユーザーにパスワードを知らせずにそのアカウントでログインした時だけroot使える、とかできる。
これを作っておけば、馬鹿みたいに長いパスワードを作っても怒られなくて済む。。はず。。

DBIにmy.cnfを読み込ませられる様に.mylogin.cnfも読み込ませられる様になれば、
アプリの側にアカウントパスワード情報を平書きしなくても良くなるはず。。

対応してるインターフェイスの情報が見当たらないなぁ。。

mysqldumpslowを自分好みに改造

MySQL-serverに付属してくるmysqldumpslow、
スロウログをなんとなく集約してくれて標準出力に出すアレですが、
個人的にちょこちょこ好みがあったので改造してみました。

1) SQLテキスト中のASCII文字は全てlowercaseに変換してから処理する。
　⇒オリジナルはSELECT ..とselect ..が違うと集約されないので、集約する様になります。

2) SQLテキスト中のLF文字、連続した空白文字1つの空白文字として扱って集約します。
　⇒オリジナルは複数行に分けて書いたものと単一行のもので別々に集約されるので、
　　それを一括に直します。

……微妙？；


まるごとはこちら。
　https://github.com/yoku0825/my_slowlog/blob/master/mysqldumpslow_ci

patchファイルはこちら。
　https://github.com/yoku0825/my_slowlog/blob/master/mysqldumpslow_ci.patch


Perlはいいな。。自分でいじれるから。

GPLv2ライセンスなので、原著作者とライセンスの表記はそのままにして、
「改変したものであることと改変した人」を明記する。。で良いはず。

mysqlfailoverを試した！ が使えなかったorz

取り敢えずGTIDを使ってレプリケーションを組んだ記事と、
MySQL WorkbenchからMySQL Utilitiesだけを引っこ抜いてインストールした記事。


mysqlfailoverの使い方自体は割と単純で、

$ mysqlfailover --master=root:root@localhost --candidate=root:root@cent22,root:root@cent23 --discover-slaves-login=root:root --log=/tmp/failover.log --rpl-user=repl:repl

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Oct 24 11:35:00 2012

Master Information
------------------
Binary Log File  Position  Binlog_Do_DB  Binlog_Ignore_DB
bin.000005       442

Replication Health Status
+------------+-------+---------+--------+------------+------------------------------------------+
| host       | port  | role    | state  | gtid_mode  | health                                   |
+------------+-------+---------+--------+------------+------------------------------------------+
| localhost  | 3306  | MASTER  | UP     | ON         | OK                                       |
| cent22     | 3306  | SLAVE   | UP     | ON         | OK                                       |
| cent23     | 3306  | SLAVE   | UP     | ON         | Slave has 1 transactions behind master.  |
+------------+-------+---------+--------+------------+------------------------------------------+

joeパスワードなのはご愛嬌。topみたいに画面が更新される様になる。

--masterにはマスターのユーザー名[:パスワード]@ホスト名[:ポート番号]を指定。

mysql.failover_consoleテーブルに書き込みをしたり

mysql.userテーブルから読み込みしたりするので、権限のあるユーザーで。

--candidateにはマスターに昇格できるスレーブの情報を指定。

ユーザー名[:パスワード]@ホスト名[:ポート番号],ユーザー名[:パスワード]@ホスト名[:ポート番号],..

複数指定する時は書いた順番で優先される。

↑のやつだと、

"localhostが転けた時はcent22をマスターに昇格、cent22も転けた時はcent23に昇格"

という意味合い。

--discover-slaves-loginには、

マスターにログインして"SHOW SLAVE HOSTS"を叩けるユーザーを指定する。

--slaves=user@host,user@host,.. で列挙する場合、このオプション要らない。

--rpl-userにはREPLICATION SLAVE権限のユーザーを記載する。

これ書いておかないと謎のユーザー(3306@cent23ｗ)で昇格後の新マスターに

つなぎに行くようになった。

動作的にはmysql.slave_master_infoから読んでる様な気配がするんだけどなぜこうなった。。

で、mysqladmin shutdownで順番に殺してやると、たまにしか上手く行かない(´･ω･｀)

マスターを落とした直後に、"ERROR: No candidate found for failover."と言われて

mysqlfailoverスクリプトが落ちる。

　⇒再現条件はっきりしてない。ログにも

　　"WARNING Candidate cent22:3306 does not meet the requirements."

　　としか書いてくれないから困る。。

また時間が取れたらがんばる。

というか、GTIDベースのレプリケーションにかなりやられたので、

使う予定の人は今から使っておいた方が絶対に良いと思う。。

Chiba.pm #1に行ってきました

去る10/20、Chiba.pm #1に行ってきました。

全員LT！ という触れ込みだったので、恐る恐るへちょい資料を作って、おそるおそる初LT。

いやもういつ椅子が㌧で来るかと（ｒｙ

この類の勉強会って、やっぱりそれなりに勉強会に参加している人たちの輪が出来ていて、

立ち上げ初回とはいえそれなりに既に顔見知りの輪があるんだろうなぁと思っていました。

その感覚は概ね間違っていなかったんですが、一見さんの俺でも色々話せたよ！



みなさんちゃんと（？）YAPCとか行ってるんですねー。

息をするようにCPANモジュールを書いちゃう人とか
Perl普段使ってない人がChiba.pmの為に触った感想とか
IPAの問題をdisってみたりとか。


会場のWAVE船橋校さんもLTしてました。ッターン！

次回も多分ここでしょうとのこと。

#chibapm instagr.am/p/Q_2wkJsF2l/
— uzullaさん (@uzulla) 10月 20, 2012

あ、懇親会、煙草買いに行ってそのまま迷子になってごめんなさい。。orz

mysql.slow_logからデータを抜いてFILE形式にアウトプット

AmazonRDSのスロウログってTABLE形式でしか取れない。
そんな話をChiba.pm#1で聞いてきたので取り敢えずばっこ抜く為だけのPerlを。

https://gist.github.com/3910570


mysql.slow_logにSELECT権限を持ってるアカウントとパスワードだけ指定して叩くだけです。
標準出力に吐きます。

CSVが取れるのであればこっちも使えますが、文字列を色々ごにょごにょしているので
おとなしくDBI使ってるこっちの方が見やすいな。。

mysqlfailoverを試したい（準備。MySQL UtilitiesをLinuxに入れる）

mysqlfailoverその他のMySQL Utilitiesをインストールする。

rpmで入れるとX Window関連の色々なものを要求されて面倒だったので、
ソースから入れることにする。

http://dev.mysql.com/downloads/workbench/5.2.html#downloads


3コマンドでさっくり。

$ tar xzf mysql-workbench-gpl-5.2.44-src.tar.gz
$ cd mysql-workbench-gpl-5.2.44-src/ext/mysql-utilities
$ python setup.py install --prefix=<インストール先>

--prefixを指定しない場合のデフォルトでは/usrなので、
/usr/binの下にごりごりスクリプトが入る。


$ mysqldiskusage
# Source on localhost: ... connected.
# Database totals:
+---------------------+--------------+
| db_name             |       total  |
+---------------------+--------------+
| d1                  |   231541199  |
| innodb_memcache     |   231541199  |
| mysql               |   231541199  |
| performance_schema  |   231541199  |
| test                |   231541199  |
| tpcc                |   231541199  |
+---------------------+--------------+

Total database disk usage = 407,571,482 bytes or 388.00 MB

#...done.


動いた！
じゃあ次こそmysqlfailoverに。。

mysqlfailoverを試したい（準備。GTIDでレプリケーション組むところまで）

MySQL 5.6で使えるGTIDの機能を使うと、MHAみたいなことがMySQL Workbench付属の
mysqlfailoverっていうユーティリティで出来るようになるよ！

ということで、試してみるメモ。


まずはVirtualBoxに5.6.7を3台浮かせる。。
_|￣|○ < 3台は重いなー。。

my.cnfに↓を設定。


log-bin=bin
log-slave-update
gtid-mode = ON
disable-gtid-unsafe-statements

肝心なのはgtid-mode=ONだけだけど、
他のオプションも書いておかないと、
`gtid-mode=ONの時はこれつけないとダメだよ！'って怒られてmysqldが起動しなくなる(´･ω･)


GTIDを有効にしたら、レプリケーションを構成しておく。

mysql> CHANGE MASTER TO MASTER_HOST='cent21',MASTER_USER='repl',MASTER_AUTO_POSITION=1;

MASTER_AUTO_POSITION=1でGTIDを使ったレプリケーションを構築する、らしい。
GTIDを使うので、バイナリログファイル名もポジションも要らなくなる。

スレーブ側で保持している「最後に適用したGTID」の次のGTIDを探して、
それを含むバイナリログとポジションを自動設定してくれる、という様な動きっぽい。


ハマったのは、
121019 10:52:47 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593
と言われたこと。

サーバIDちゃんと別のにしてるのに！ と思ったら、
server_uuidという値をdatadir/auto.cnfに書き込んでいた。
（datadir以下をまるっとマスターからコピーしていたので、これもコピーされたんだ）

これを一度削除して再起動すると、auto.cnfが再作成されてちゃんとユニークになった。


取り敢えずここまで。。


【2012/10/23 12:15】
色々ごにょごにょしていて（やっと）気付いたんだけれど、
disable-gtid-unsafe-statementsを有効にするとトランザクション非対応なテーブルに対する更新を
一切合切拒否る様になる。
つまり、InnoDBだけ更新できて、MyISAMやMemoryはダメ。

http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html

disられてるなぁMyISAM。。(つд｀)

VirtualBoxで共有DISKファイルを作る

クラスタソフトの動作確認をする時に必要になったのでメモ。


まずはvdiファイルを新規作成しておく。

$ VBoxManage createhd --filename <ファイル名> --size <DISKのサイズ MB単位> --variant Fixed

共有DISKにセットアップするにはファイルサイズは固定でないといけないので、
--variant Fixedで予めファイル領域を全て確保しておく。



$ VBoxManage showhdinfo shared.vdi
UUID:                 49a0995c-bcaf-4315-8656-82633abde094
Accessible:           yes
Logical size:         10240 MBytes
Current size on disk: 10240 MBytes
Type:                 normal (base)
Storage format:       VDI
Format variant:       fixed default
Location:             D:\VirtualBox\vdis\shared.vdi


こんな風に作ってみた。
Windows上でCygwinからVBoxManage.exeを呼んでます。


$ VBoxManage modifyhd <ファイル名> --type shareable

modifyhdの--typeオプションでshareableを指定すると共有DISK型になる。
一瞬で終わる。



$ VBoxManage showhdinfo shared.vdi
UUID:                 49a0995c-bcaf-4315-8656-82633abde094
Accessible:           yes
Logical size:         10240 MBytes
Current size on disk: 10240 MBytes
Type:                 shareable
Storage format:       VDI
Format variant:       fixed default
Location:             D:\VirtualBox\vdis\shared.vdi



はいOK。
これで複数のVMのストレージポートに同時に繋げられるvdiファイルの完成。

GFS2なりOCFS2なりでmkfsしてやれば完成かな。

information_schema.GLOBAL_STATUSを収集するスクリプト

作ってみた。

https://gist.github.com/3910575


mysql_status_collector.pl の中身をちょこちょこいじる。
1) $sMySQL .. mysqlクライアントをフルパスで指定
2) $sRrdTool .. rrdtoolをフルパスで指定
3) $sWorkDir .. 色々ファイルを吐き出すディレクトリを指定
4) $sUser .. MySQLに接続できるユーザーを指定、SHOW GLOBAL STATUS叩くだけなので何でもOK。
5) $sPass .. ↑のアカウントのパスワード
6) $sInterval .. information_schema.GLOBAL_STATUSを記録する間隔


設定したら↓で起動。
mysqlクライアントがインストールされていないとダメ。
勝手にバックグラウンドに回る。

shell> perl mysql_status_collector.pl collect

止める時は、$sPidFileのファイルを消す。
PIDファイルが無くなると、次のポーリング間隔になった時にそれを見てexitする仕組み。
killしても構わないけれども。
PIDファイルと言いながら中身はからっぽでPIDは書いてないorz

shell> rm -i /tmp/mywork/mysql_status.pid


ファイルを収集した後は、rrdtoolが入っているマシンの同じディレクトリに持っていって

shell> perl mysql_status_collector.pl total

これで勝手にrrdファイルを作ってデータを突っ込んで、グラフを作る。


ログを収集するマシンにはmysqlクライアントとperlだけ入ってればOK、
集計するマシンにはrrdtoolとperlだけ入ってればOK。

取り敢えずなるべくそれだけで動く何かを目指してみた。