Pacemaker+Mysqlレプリケーション構成のレプリケーションユーザ権限

PaceMaker+Mysqlの標準機能のレプリケーション構成で、フェイルオーバテストを繰り返していたら、Mysqlが起動できなくなってしまった。
Mysqlログの確認
140724 10:42:10 mysqld_safe Starting mysqld daemon with databases from /data/db/mysqld/hogedb-data
140724 10:42:10 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140724 10:42:10 [Note] Plugin 'FEDERATED' is disabled.
140724 10:42:10 InnoDB: The InnoDB memory heap is disabled
140724 10:42:10 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140724 10:42:10 InnoDB: Compressed tables use zlib 1.2.3
140724 10:42:10 InnoDB: Using Linux native AIO
140724 10:42:10 InnoDB: Initializing buffer pool, size = 2.0G
140724 10:42:10 InnoDB: Completed initialization of buffer pool
140724 10:42:10 InnoDB: highest supported file format is Barracuda.
140724 10:42:10  InnoDB: Waiting for the background threads to start
140724 10:42:11 InnoDB: 5.5.38 started; log sequence number 1600479
140724 10:42:11 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140724 10:42:11 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140724 10:42:11 [Note] Server socket created on IP: '0.0.0.0'.
140724 10:42:11 [Warning] 'proxies_priv' entry '@ root@dbserver-01' ignored in --skip-name-resolve mode.
140724 10:42:11 [Note] Event Scheduler: Loaded 0 events
140724 10:42:11 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.38-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Distributed by The IUS Community Project
140724 10:42:15 [Note] /usr/libexec/mysqld: Normal shutdown

140724 10:42:15 [Note] Event Scheduler: Purging the queue. 0 events
140724 10:42:15  InnoDB: Starting shutdown...
140724 10:42:16  InnoDB: Shutdown completed; log sequence number 1600479
140724 10:42:16 [Note] /usr/libexec/mysqld: Shutdown complete

140724 10:42:16 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
※Mysql起動後に、シャットダウン処理が行われている。

Pacemakerログ
Jul 24 11:22:54 dbserver-03 attrd[24873]:   notice: attrd_trigger_update: Sending flush op to all hosts for: fail-count-res-mysql (<null>)
Jul 24 11:22:54 dbserver-03 attrd[24873]:   notice: attrd_perform_update: Sent delete 29: node=dbserver-03, attr=fail-count-res-mysql, id=<n/a>, set=(null), section=status
Jul 24 11:22:54 dbserver-03 attrd[24873]:   notice: attrd_perform_update: Sent delete 31: node=dbserver-03, attr=fail-count-res-mysql, id=<n/a>, set=(null), section=status
Jul 24 11:22:54 dbserver-03 pacemakerd[26454]:   notice: crm_add_logfile: Additional logging available in /var/log/cluster/corosync.log
Jul 24 11:22:54 dbserver-03 mysql(res-mysql)[26444]: INFO: MySQL is not running
Jul 24 11:22:54 dbserver-03 mysql(res-mysql)[26444]: INFO: MySQL is not running
Jul 24 11:22:56 dbserver-03 mysql(res-mysql)[26444]: INFO: MySQL is not running
Jul 24 11:23:00 dbserver-03 mysql(res-mysql)[26444]: INFO: No MySQL master present - clearing replication state
Jul 24 11:23:00 dbserver-03 mysql(res-mysql)[26444]: INFO: Slave is not running - not waiting to finish
Jul 24 11:23:00 dbserver-03 mysql(res-mysql)[26444]: INFO: Slave not runnig - not waiting to finish
Jul 24 11:23:00 dbserver-03 mysql(res-mysql)[26444]: ERROR: ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
Jul 24 11:23:00 dbserver-03 mysql(res-mysql)[26444]: ERROR: Failed to reset slave
Jul 24 11:23:00 dbserver-03 pacemakerd[27474]:   notice: crm_add_logfile: Additional logging available in /var/log/cluster/corosync.log
Jul 24 11:23:00 dbserver-03 crmd[24875]:   notice: process_lrm_event: LRM operation res-mysql_start_0 (call=48, rc=1, cib-update=18, confirmed=true) unknown error
Jul 24 11:23:01 dbserver-03 attrd[24873]:   notice: attrd_cs_dispatch: Update relayed from dbserver-02
Jul 24 11:23:01 dbserver-03 attrd[24873]:   notice: attrd_trigger_update: Sending flush op to all hosts for: fail-count-res-mysql (INFINITY)
Jul 24 11:23:01 dbserver-03 attrd[24873]:   notice: attrd_perform_update: Sent update 35: fail-count-res-mysql=INFINITY
Jul 24 11:23:01 dbserver-03 attrd[24873]:   notice: attrd_cs_dispatch: Update relayed from dbserver-02
Jul 24 11:23:01 dbserver-03 attrd[24873]:   notice: attrd_trigger_update: Sending flush op to all hosts for: last-failure-res-mysql (1406168580)
Jul 24 11:23:01 dbserver-03 attrd[24873]:   notice: attrd_perform_update: Sent update 37: last-failure-res-mysql=1406168580
Jul 24 11:23:01 dbserver-03 attrd[24873]:   notice: attrd_cs_dispatch: Update relayed from dbserver-02
Jul 24 11:23:01 dbserver-03 attrd[24873]:   notice: attrd_trigger_update: Sending flush op to all hosts for: fail-count-res-mysql (INFINITY)
Jul 24 11:23:01 dbserver-03 attrd[24873]:   notice: attrd_perform_update: Sent update 40: fail-count-res-mysql=INFINITY
Jul 24 11:23:01 dbserver-03 attrd[24873]:   notice: attrd_cs_dispatch: Update relayed from dbserver-02
Jul 24 11:23:01 dbserver-03 attrd[24873]:   notice: attrd_trigger_update: Sending flush op to all hosts for: last-failure-res-mysql (1406168580)
Jul 24 11:23:01 dbserver-03 attrd[24873]:   notice: attrd_perform_update: Sent update 42: last-failure-res-mysql=1406168580
Jul 24 11:23:01 dbserver-03 pacemakerd[27494]:   notice: crm_add_logfile: Additional logging available in /var/log/cluster/corosync.log
Jul 24 11:23:01 dbserver-03 crmd[24875]:   notice: process_lrm_event: LRM operation res-mysql_notify_0 (call=51, rc=0, cib-update=0, confirmed=true) ok
Jul 24 11:23:01 dbserver-03 pacemakerd[27525]:   notice: crm_add_logfile: Additional logging available in /var/log/cluster/corosync.log
Jul 24 11:23:01 dbserver-03 crmd[24875]:   notice: process_lrm_event: LRM operation res-mysql_notify_0 (call=54, rc=0, cib-update=0, confirmed=true) ok
Jul 24 11:23:01 dbserver-03 pacemakerd[27556]:   notice: crm_add_logfile: Additional logging available in /var/log/cluster/corosync.log
Jul 24 11:23:03 dbserver-03 mysql(res-mysql)[27546]: INFO: MySQL is not running
Jul 24 11:23:03 dbserver-03 mysql(res-mysql)[27546]: INFO: MySQL is not running
Jul 24 11:23:03 dbserver-03 mysql(res-mysql)[27546]: INFO: MySQL stopped
Jul 24 11:23:03 dbserver-03 crmd[24875]:   notice: process_lrm_event: LRM operation res-mysql_stop_0 (call=57, rc=0, cib-update=19, confirmed=true) ok
Pacemakerログの中に下記ログから、レプリケーションユーザにRELOAD権限が不足している模様。
Jul 24 11:23:00 dbserver-03 mysql(res-mysql)[26444]: ERROR: ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
Jul 24 11:23:00 dbserver-03 mysql(res-mysql)[26444]: ERROR: Failed to reset slave

Pacemaker+Mysqlレプリケーション構成のレプリケーションユーザ権限
上記ログからPaceMakerでMySQLレプリケーションする場合のレプリケーションユーザにReload権限を与える必要がある。
レプリケーション権限
リモートのレプリケーションユーザの権限REPLICATION SLAVE
ローカルのレプリケーションユーザSUPER,REPLICATION SLAVE,REPLICATION CLIENT,PROCESS,RELOAD
・SQL文例:
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';
GRANT SUPER,REPLICATION SLAVE,REPLICATION CLIENT,PROCESS,RELOAD ON *.* TO 'repl'@'localhost' IDENTIFIED BY 'password';


MySQLおすすめ書籍

[エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド]の書籍横断比較とレビュー

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド
著者:奥野 幹也
出版日:2010-06-12
出版社:技術評論社

[MySQLトラブルシューティング]の書籍横断比較とレビュー

MySQLトラブルシューティング
著者:Sveta Smirnova
出版日:2012-10-13
出版社:オライリージャパン





Google+