mysql-mhaを入れてみた！ その２

mysql-mhaを入れてみた！ 

ということでー、前回は何も考えずに1サーバの構成で構築をしましたが、
上長構成の意味が無いので、サーバを二台用意してセットアップ。
監視ノードを別のサーバにして上手くいくかどうか。

むしろこっちが本題。

サーバ構成

監視サーバ :MySlaveHost
mysqlサーバ:MyMasterHost

以下作業内容

こんなconfを準備

[root@MySlaveHost ~]# more /etc/app1.cnf
[server default]
# mysql user and password
user=repl
password=repl
ssh_user=root
# working directory on the manager
manager_workdir=/var/log/masterha/app1
# working directory on MySQL servers
remote_workdir=/var/log/masterha/app1
# master binary-log directory
master_binlog_dir=/var/lib/mysql_s1/data
[server1]
port=3306
hostname=[MyMasterHost's-ip]
[server2]
port=5002
hostname=[MyMasterHost's-ip]
server3]
port=5003
hostname=[MyMasterHost's-ip]

■IPアドレスだけ書き直して、監視サーバに設置

[root@MySlaveHost ~]# masterha_check_repl --conf=/etc/app1.cnf
Tue Jun 26 16:39:14 2012 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Jun 26 16:39:14 2012 - [info] Reading application default configurations from /etc/app1.cnf..
Tue Jun 26 16:39:14 2012 - [info] Reading server configurations from /etc/app1.cnf..
Tue Jun 26 16:39:14 2012 - [info] MHA::MasterMonitor version 0.53.
Tue Jun 26 16:39:14 2012 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln622] Master 127.0.0.1:3306 from which slave [MyMasterHost's-ip]([MyMasterHost's-ip]:5003) replicates is not defined in the configuration file!
Tue Jun 26 16:39:14 2012 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln383] Error happend on checking configurations.  at /usr/local/share/perl5/MHA/MasterMonitor.pm line 298
Tue Jun 26 16:39:14 2012 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln478] Error happened on monitoring servers.
Tue Jun 26 16:39:14 2012 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!

■ん・・・？MasterのIPが127.0.0.1になっておる。なぜ？

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

ということで、ちょっと構成を変えて検証をば。

監視サーバ :MySlaveHost
Master     :MyMasterHost(5001)
Slave      :MySlaveHost(3306)

[root@MySlaveHost ~]# more /etc/app1.cnf
[server default]
# mysql user and password
user=repl
password=repl
ssh_user=root
# working directory on the manager
manager_workdir=/var/log/masterha/app1
# working directory on MySQL servers
remote_workdir=/var/log/masterha/app1
# master binary-log directory
master_binlog_dir=/var/lib/mysql_s1/data
[server1]
hostname=MyMasterHost
port=5001
[server2]
hostname=MySlaveHost

■こんな感じにapp1.cnfを変更
------------------------------------------------------------------------------------------------------------------------

[root@MySlaveHost ~]# masterha_check_repl --conf=/etc/app1.cnf
Thu Jun 28 11:18:30 2012 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 28 11:18:30 2012 - [info] Reading application default configurations from /etc/app1.cnf..
Thu Jun 28 11:18:30 2012 - [info] Reading server configurations from /etc/app1.cnf..
Thu Jun 28 11:18:30 2012 - [info] MHA::MasterMonitor version 0.52.
Thu Jun 28 11:18:30 2012 - [info] Dead Servers:
Thu Jun 28 11:18:30 2012 - [info] Alive Servers:
Thu Jun 28 11:18:30 2012 - [info]   MyMasterHost(49.212.145.136:5001)
Thu Jun 28 11:18:30 2012 - [info]   MySlaveHost(49.212.133.18:3306)
Thu Jun 28 11:18:30 2012 - [info] Alive Slaves:
Thu Jun 28 11:18:30 2012 - [info]   MySlaveHost(49.212.133.18:3306)  Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Thu Jun 28 11:18:30 2012 - [info]     Replicating from MyMasterHost(49.212.145.136:5001)
Thu Jun 28 11:18:30 2012 - [info] Current Alive Master: MyMasterHost(49.212.145.136:5001)
Thu Jun 28 11:18:30 2012 - [info] Checking slave configurations..
Thu Jun 28 11:18:30 2012 - [warning]  read_only=1 is not set on slave MySlaveHost(49.212.133.18:3306).
Thu Jun 28 11:18:30 2012 - [warning]  relay_log_purge=0 is not set on slave MySlaveHost(49.212.133.18:3306).
Thu Jun 28 11:18:30 2012 - [info] Checking replication filtering settings..
Thu Jun 28 11:18:30 2012 - [info]  binlog_do_db= , binlog_ignore_db=
Thu Jun 28 11:18:30 2012 - [info]  Replication filtering check ok.
Thu Jun 28 11:18:30 2012 - [info] Starting SSH connection tests..
Thu Jun 28 11:18:31 2012 - [info] All SSH connection tests passed successfully.
Thu Jun 28 11:18:31 2012 - [info] Checking MHA Node version..
Thu Jun 28 11:18:31 2012 - [info]  Version check ok.
Thu Jun 28 11:18:31 2012 - [info] Checking SSH publickey authentication and checking recovery script configurations on the current master..
Thu Jun 28 11:18:32 2012 - [info]   Executing command: save_binary_logs --command=test --start_file=mysql-bin.000024 --start_pos=4 --binlog_dir=/var/lib/mysql_s1/data --output_file=/var/log/masterha/app1/save_binary_logs_test --manager_version=0.52
Thu Jun 28 11:18:32 2012 - [info]   Connecting to root@MyMasterHost(MyMasterHost)..
  Creating /var/log/masterha/app1 if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /var/lib/mysql_s1/data, up to mysql-bin.000024
Thu Jun 28 11:18:32 2012 - [info] Master setting check done.
Thu Jun 28 11:18:32 2012 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu Jun 28 11:18:32 2012 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user=repl --slave_host=MySlaveHost --slave_ip=49.212.133.18 --slave_port=3306 --workdir=/var/log/masterha/app1 --target_version=5.5.25-log --manager_version=0.52 --relay_log_info=/var/lib/mysql/data/relay-log.info  --slave_pass=xxx
Thu Jun 28 11:18:32 2012 - [info]   Connecting to root@49.212.133.18(MySlaveHost)..
  Checking slave recovery environment settings..
    Opening /var/lib/mysql/data/relay-log.info ... ok.
    Relay log found at /var/lib/mysql/data, up to mysqld-relay-bin.000002
    Temporary relay log file is /var/lib/mysql/data/mysqld-relay-bin.000002
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Thu Jun 28 11:18:33 2012 - [info] Slaves settings check done.
Thu Jun 28 11:18:33 2012 - [info]
MyMasterHost (current master)
 +--MySlaveHost
Thu Jun 28 11:18:33 2012 - [info] Checking replication health on MySlaveHost..
Thu Jun 28 11:18:33 2012 - [info]  ok.
Thu Jun 28 11:18:33 2012 - [warning] master_ip_failover_script is not defined.
Thu Jun 28 11:18:33 2012 - [warning] shutdown_script is not defined.
Thu Jun 28 11:18:33 2012 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.

■えっ。。。なんかうまくいってるんですけど・・・
  Master-Slaveが同一hostで 3306port 以外の場合はだめってこと？
  試しに、以下の構成で確認してみたらだめだった。
  Master:MyMasterHost(5001)
  Slave1:MySlaveHost(3306)
  Slave2:MyMasterHost(5002)
------------------------------------------------------------------------------------------------------------------------

■一応確認
  Slave1のportを変えてみる。
  以下の構成でも特に問題無く大丈夫だった。なるほどなー。
  Master:MyMasterHost(5001)
  Slave1:MySlaveHost(5002)

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

という事で纏め

だいじょうぶな構成

1,全部一個ですませる
  監視ノード→ServerA
  DBmaster  →ServerA
  DBslave   →ServerA

2,監視とDBmasterを分ける
  監視ノード→ServerB
  DBmaster  →ServerA
  DBslave   →ServerB
  ※MasterとSlaveを別hostにする必要がある

だめな構成

1,監視とDBを分ける
  監視ノード→ServerA
  DBmaster  →ServerB
  DBslave   →ServerB
  ※どうやってもMasterのIPアドレスが127.0.0.1になる不具合

上記の挙動は正常なの？バグなの？

おわり

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

おまけ的備忘録

毎回わすれる・・・
- ユーザー追加(rootとおなじ権限)
  GRANT ALL PRIVILEGES ON *.* TO repl@MySlaveHost IDENTIFIED BY 'repl' WITH GRANT OPTION;

- ユーザー削除
  DELETE FROM mysql.user WHERE user='repl' AND host='localhost';

- slave の準備
  CHANGE MASTER TO MASTER_HOST = 'MySlaveHost',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'repl',
  MASTER_PORT = 5002,
  MASTER_LOG_FILE = 'mysql-bin.000004',
  MASTER_LOG_POS = 107;