2014年11月5日水曜日

MySQL Fabricつらい(HA/登録編)

昨日インストールしたMySQL Fabric にファーム(MySQL Fabric管理下のmysqld群をこう呼ぶぽい)を追加していく。

まずはグループを作成。これが「同じデータセットを持っているマスターとスレーブの組」扱いになるはず。


$ mysqlfabric group create my_first_fabric --description="MySQL Fabric is too hard to me"
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
8fb1a6df-0be9-4395-8240-8082c50a88e6        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.41515e+09 Triggered by <mysql.fabric.events.Event object at 0x24e2cd0>.
    4       2   1.41515e+09                             Executing action (_create_group).
    5       2   1.41515e+09                              Executed action (_create_group).


$ mysqlfabric group lookup_groups
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

       group_id                    description failure_detector master_uuid
--------------- ------------------------------ ---------------- -----------
my_first_fabric MySQL Fabric is too hard to me                0        None

最初はdescriptionを"MySQL Fabricつらい"にしたんだけど、非ASCII文字だとlookup_groupsした時に表示されなかった(´・ω・`)
そしてグループにサーバーを追加する。


$ mysqlfabric group add my_first_fabric 127.0.0.1:20886
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

ServerError: Error accessing server (127.0.0.1:20886): 1045 (28000): Access denied for user 'fabric'@'localhost' (using password: NO).

ありゃ、こっちにもfabricユーザー必要なの。


master> GRANT ALL ON fabric.* TO fabric@localhost IDENTIFIED BY 'fabric_password';

$ mysqlfabric group add my_first_fabric 127.0.0.1:20886
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

ServerError: Error accessing server (127.0.0.1:20886): 1045 (28000): Access denied for user 'fabric'@'localhost' (using password: NO).

まだ転ける。ってか、using password: NOになってるじゃん。


$ vim /etc/mysql/fabric.cfg
..
[servers]
password = 
user = fabric
unreachable_timeout = 5
..

ここかな。password = fabric_passwordとして


$ mysqlfabric group add my_first_fabric 127.0.0.1:20886
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

ServerError: Error accessing server (127.0.0.1:20886): 1045 (28000): Access denied for user 'fabric'@'localhost' (using password: NO).

( ゚д゚) ポルァ

マニュアル読むと、[servers]セクションで良さそうなもんなのに。。
http://dev.mysql.com/doc/mysql-utilities/1.5/en/fabric-cfgref-servers.html


==試行錯誤すること2時間あまり==


記載する先は[servers]セクションで合ってるけど、コンフィグ書き換えたあとに再起動が必要だった_| ̄|○ mysqlfabric groupで起動したmysqlfabricコマンドは飽くまでmysqlfabric manage startしたFabricサーバーのRPCを叩くだけで、実際にファームのMySQLにコマンドを投げたりしているのはmysqlfabric manage start側だった、という仕組みぽい。

これで今度こそ上手くいくのだー(フラグ)


$ mysqlfabric group add my_first_fabric 127.0.0.1:20886
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

ServerError: User (fabric) does not have appropriate privileges (ALL PRIVILEGES) on server (127.0.0.1:20886, 47cf54df-63fc-11e4-942e-fa163e020fd0).

あ、グローバルALLが必要ですか。。


master> GRANT ALL ON *.* TO fabric@localhost;

$ mysqlfabric group add my_first_fabric 127.0.0.1:20886
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
795e8cbc-5878-409a-9c7b-35c353081f8a        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                               Executing action (_add_server).
    5       2   1.41516e+09                                Executed action (_add_server).

$ mysqlfabric group lookup_servers my_first_fabric
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status      mode weight
------------------------------------ --------------- --------- --------- ------
47cf54df-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20886 SECONDARY READ_ONLY    1.0

やっとキタ━━━━(゚∀゚)━━━━!!
この調子でスレーブも登録。


$ mysqlfabric group add my_first_fabric 127.0.0.1:20887
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
59db0454-22cf-4a79-bd6a-9d576003be4a        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                               Executing action (_add_server).
    5       2   1.41516e+09                                Executed action (_add_server).


$ mysqlfabric group lookup_servers my_first_fabric
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status      mode weight
------------------------------------ --------------- --------- --------- ------
47cf54df-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20886 SECONDARY READ_ONLY    1.0
4b7036a9-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20887 SECONDARY READ_ONLY    1.0

チュートリアルでは「正しくレプリケーションが構成されていればpromoteしなくてもいいよ」とか言ってたけど、両方ともstatus: SECONDARYになっちゃってるしmode: READ_ONLYと認識されているのでpromoteしておく。


$ mysqlfabric group promote my_first_fabric
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
1879f58e-f3a6-47b3-b70a-83d7756ea275        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                      Executing action (_define_ha_operation).
    5       2   1.41516e+09                       Executed action (_define_ha_operation).
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                      Executing action (_find_candidate_fail).
    5       2   1.41516e+09                       Executed action (_find_candidate_fail).
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                     Executing action (_check_candidate_fail).
    5       2   1.41516e+09                      Executed action (_check_candidate_fail).
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                          Executing action (_wait_slave_fail).
    5       2   1.41516e+09                           Executed action (_wait_slave_fail).
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                      Executing action (_change_to_candidate).
    5       2   1.41516e+09                       Executed action (_change_to_candidate).

$ mysqlfabric group lookup_servers my_first_fabric
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
47cf54df-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20886 SECONDARY  READ_ONLY    1.0
4b7036a9-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20887   PRIMARY READ_WRITE    1.0

Σ(゚д゚lll) 引数なしで行ったらスレーブをPRIMARYって認識した!!
どうしてそうなった。。server_uuidが若いからか。SHOW SLAVE STATUSとか見ないのかそうなのか。

promoteしなおしてみる。


$ mysqlfabric group promote my_first_fabric
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

GroupError: There is no valid candidate that can be automatically chosen in group (my_first_fabric). Please, choose one manually.

あ、あー、FabricがPRIMARYだと思っているスレーブ(127.0.0.1:20087)にはスレーブが存在しないからダメと。手動で選択しろと。


$ mysqlfabric group promote my_first_fabric --slave_id=47cf54df-63fc-11e4-942e-fa163e020fd0
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

DatabaseError: Command (SHOW SLAVE STATUS, ()) failed accessing (127.0.0.1:20886). 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation.

( ゚д゚) ファッ!? と思ったら、さっきと同じで、mysqlfabric manage startの時点でコネクションが張りっぱなしになっててchange_db()が走るタイミングがないので、権限がリロードされてないぽい。


$ mysqlfabric manage stop
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

Success (empty result set)

$ mysqlfabric manage start --daemonize

$ mysqlfabric group promote my_first_fabric --slave_id=47cf54df-63fc-11e4-942e-fa163e020
fd0
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

ServerError: Server (47cf54df-63fc-11e4-942e-fa163e020fd0) is not a valid candidate slave due to the following reason: ({'sql_error': False, 'io_error': False, 'io_not_running': False, 'sql_not_running': False, 'is_not_configured': True, 'is_not_running': False}).

うむ。
SECONDARYだと思ってるけどマスターだもんね。SHOW SLAVE STATUSの結果からっぽだよね。うん。


$ mysqlfabric group demote my_first_fabric
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
beb3d5e5-6753-40fc-b5ef-1297e48e49b9        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                       Executing action (_block_write_demote).
    5       2   1.41516e+09                        Executed action (_block_write_demote).
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                       Executing action (_wait_slaves_demote).
    5       2   1.41516e+09                        Executed action (_wait_slaves_demote).

$ mysqlfabric group lookup_servers my_first_fabric
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status      mode weight
------------------------------------ --------------- --------- --------- ------
47cf54df-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20886 SECONDARY READ_ONLY    1.0
4b7036a9-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20887 SECONDARY READ_ONLY    1.0

$ mysqlfabric group promote my_first_fabric --slave_id=47cf54df-63fc-11e4-942e-fa163e020fd0
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
06b7ea19-1202-4b03-962d-1f36b4d50768        1       1      1

state success          when                                                   description
----- ------- ------------- -------------------------------------------------------------
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                      Executing action (_define_ha_operation).
    5       2   1.41516e+09                       Executed action (_define_ha_operation).
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                     Executing action (_check_candidate_fail).
    5       2   1.41516e+09                      Executed action (_check_candidate_fail).
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                          Executing action (_wait_slave_fail).
    5       2   1.41516e+09                           Executed action (_wait_slave_fail).
    3       2   1.41516e+09 Triggered by .
    4       2   1.41516e+09                      Executing action (_change_to_candidate).
    5       2   1.41516e+09                       Executed action (_change_to_candidate).

$ mysqlfabric group lookup_servers my_first_fabric
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
47cf54df-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20886   PRIMARY READ_WRITE    1.0
4b7036a9-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20887 SECONDARY  READ_ONLY    1.0

demoteで一度SECONDARYに戻してやってから、正しい方を指定してpromote。
この状態でMasterを落としてやると(MySQL::Sandbox使ってます)


$ master/stop

$ mysqlfabric group lookup_servers my_first_fabric
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
47cf54df-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20886   PRIMARY READ_WRITE    1.0
4b7036a9-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20887 SECONDARY  READ_ONLY    1.0

$ mysqlfabric group health my_first_fabric
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid is_alive    status is_not_running is_not_configured io_not_running sql_not_running                                                                           io_error sql_error
------------------------------------ -------- --------- -------------- ----------------- -------------- --------------- ---------------------------------------------------------------------------------- ---------
47cf54df-63fc-11e4-942e-fa163e020fd0        0    FAULTY              0                 0              0               0                                                                              False     False
4b7036a9-63fc-11e4-942e-fa163e020fd0        1 SECONDARY              0                 0              1               0 error reconnecting to master 'fabric@127.0.0.1:20886' - retry-time: 60  retries: 2     False

issue
-----

$ mysqlfabric group lookup_servers my_first_fabric
Password for admin:
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
47cf54df-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20886   PRIMARY READ_WRITE    1.0
4b7036a9-63fc-11e4-942e-fa163e020fd0 127.0.0.1:20887 SECONDARY  READ_ONLY    1.0

(少なくとも)すぐには切り替わらなかった(´・ω・`)


$ vim /etc/mysql/fabric.cnf
..
[failure_tracking]
notification_interval = 60
notification_clients = 50
detection_timeout = 1
detection_interval = 6
notifications = 300
detections = 3
failover_interval = 0
prune_time = 3600
..

この辺の設定なんだろうけど、そのあたりはまたこんど。。

0 件のコメント :

コメントを投稿