3,374 瀏覽數

重啟 MariaDB Galera Cluster

因為是實驗性質所以我常重啟啓動 MariaDB Galera Cluster ,但下次開機後會發現 Cluster 都沒有起來.

# service mysql start --wsrep-new-cluster
# service mysql bootstrap
Starting MySQL................................... ERROR! 
  • gcomm:// 設定為空值
    我們要先找到最後一個關機的 Node ,執行啟動指令時先避開 server.cnf 裡面設定好的 gcomm .

    # service mysql start --wsrep-cluster-address="gcomm://"
    

    但問題是我根本不記得最後關機的是誰啊!!我們可以先直接修改 server.cnf 的 gcomm:// 把它設定為空值.

    # vi /etc/my.cnf.d/server.cnf
    .....
    wsrep_cluster_address='gcomm://'
    

    等正確無誤實在把 gcomm 修改回來,不過可能會有一些同步上的問題.

  • 誰是最大 wsrep_last_committed
    要先找出誰的 wsrep_last_committed 是最大的,就可以把它當作第一個啟動的node (Primary) ,但問題是 Cluster 這時候已經起不來,怎麼有辦法透過 # mysql -u root -p -e “show status like ‘wsrep%'” 來檢查!!

    修改每個 node 的 server.cnf 設定檔,把 gcomm:// 設定加入 ?pc.wait_prim=no

    # vi /etc/my.cnf.d/server.cnf
    .....
    wsrep_cluster_address='gcomm://172.16.15.167:4567?pc.wait_prim=no'
    

    等每個 node 啟動之後,檢查誰的 wsrep_last_committed 是最大的,設定他為 primary node 即可.

    # mysql -u root -p -e "show status like 'wsrep%'" | grep -i last
    wsrep_last_committed         | 7   
    
    # mysql-u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 7
    Server version: 5.5.51-MariaDB-wsrep Source distribution, wsrep_25.14.r9949137
     
    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
     
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
     
    MariaDB [(none)]> SET GLOBAL wsrep_provider_options="pc.bootstrap=true";
    

    最後檢查一下 wsrep_ready 狀態是 ON , wsrep_cluster_size , wsrep_incoming_addresses 的設定是否相同.

    # mysql -u root -p -e "show status like 'wsrep%'"
    Enter password: 
    +------------------------------+----------------------------------------+
    | Variable_name                | Value                                  |
    +------------------------------+----------------------------------------+
    | wsrep_local_state_uuid       | cf44fbea-6e9c-11e6-a4eb-37fc80383cb5   |
    | wsrep_protocol_version       | 7                                      |
    | wsrep_last_committed         | 7                                      |
    | wsrep_replicated             | 0                                      |
    | wsrep_replicated_bytes       | 0                                      |
    | wsrep_repl_keys              | 0                                      |
    | wsrep_repl_keys_bytes        | 0                                      |
    | wsrep_repl_data_bytes        | 0                                      |
    | wsrep_repl_other_bytes       | 0                                      |
    | wsrep_received               | 2                                      |
    | wsrep_received_bytes         | 213                                    |
    | wsrep_local_commits          | 0                                      |
    | wsrep_local_cert_failures    | 0                                      |
    | wsrep_local_replays          | 0                                      |
    | wsrep_local_send_queue       | 0                                      |
    | wsrep_local_send_queue_max   | 1                                      |
    | wsrep_local_send_queue_min   | 0                                      |
    | wsrep_local_send_queue_avg   | 0.000000                               |
    | wsrep_local_recv_queue       | 0                                      |
    | wsrep_local_recv_queue_max   | 1                                      |
    | wsrep_local_recv_queue_min   | 0                                      |
    | wsrep_local_recv_queue_avg   | 0.000000                               |
    | wsrep_local_cached_downto    | 18446744073709551615                   |
    | wsrep_flow_control_paused_ns | 0                                      |
    | wsrep_flow_control_paused    | 0.000000                               |
    | wsrep_flow_control_sent      | 0                                      |
    | wsrep_flow_control_recv      | 0                                      |
    | wsrep_cert_deps_distance     | 0.000000                               |
    | wsrep_apply_oooe             | 0.000000                               |
    | wsrep_apply_oool             | 0.000000                               |
    | wsrep_apply_window           | 0.000000                               |
    | wsrep_commit_oooe            | 0.000000                               |
    | wsrep_commit_oool            | 0.000000                               |
    | wsrep_commit_window          | 0.000000                               |
    | wsrep_local_state            | 4                                      |
    | wsrep_local_state_comment    | Synced                                 |
    | wsrep_cert_index_size        | 0                                      |
    | wsrep_causal_reads           | 0                                      |
    | wsrep_cert_interval          | 0.000000                               |
    | wsrep_incoming_addresses     | 172.16.15.167:3306,=172.16.15.168:3306 |
    | wsrep_desync_count           | 0                                      |
    | wsrep_evs_delayed            |                                        |
    | wsrep_evs_evict_list         |                                        |
    | wsrep_evs_repl_latency       | 0/0/0/0/0                              |
    | wsrep_evs_state              | OPERATIONAL                            |
    | wsrep_gcomm_uuid             | e1efc27a-6f94-11e6-8b05-9fdee394642f   |
    | wsrep_cluster_conf_id        | 4                                      |
    | wsrep_cluster_size           | 2                                      |
    | wsrep_cluster_state_uuid     | cf44fbea-6e9c-11e6-a4eb-37fc80383cb5   |
    | wsrep_cluster_status         | Primary                                |
    | wsrep_connected              | ON                                     |
    | wsrep_local_bf_aborts        | 0                                      |
    | wsrep_local_index            | 1                                      |
    | wsrep_provider_name          | Galera                                 |
    | wsrep_provider_vendor        | Codership Oy <info@codership.com>      |
    | wsrep_provider_version       | 25.3.17(r3619)                         |
    | wsrep_ready                  | ON                                     |
    | wsrep_thread_count           | 2                                      |
    +------------------------------+----------------------------------------+
    
  • 暫時加入 Cluster 的方式
    其實不一定要去修改 server.cnf 檔案才可以加入到 Cluster ,可以先將 gcomm:// 留空白,再手動加入到 Cluster .

    1. Node1 : cn167 , IP – 172.16.15.167
    2. Node2 : cn168 , IP – 172.16.15.168
    3. Node3 (暫時加入的方式) : cn169 , IP – 172.16.15.169
    [root@cn169 ~]# mysql -u root -p -e "SHOW VARIABLES LIKE 'wsrep_cluster_address';"  
    Enter password: 
    +-----------------------+----------+
    | Variable_name         | Value    |
    +-----------------------+----------+
    | wsrep_cluster_address | gcomm:// |
    +-----------------------+----------+
    [root@cn169 ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 7
    Server version: 5.5.51-MariaDB-wsrep Source distribution, wsrep_25.14.r9949137
    
    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> SET GLOBAL wsrep_cluster_address='gcomm://172.16.15.167:4567,172.16.15.168:4567';
    Query OK, 0 rows affected (3.02 sec)
    
    MariaDB [(none)]> SHOW VARIABLES LIKE 'wsrep_cluster_address';
    +-----------------------+-----------------------------------------------+
    | Variable_name         | Value                                         |
    +-----------------------+-----------------------------------------------+
    | wsrep_cluster_address | gcomm://172.16.15.167:4567,172.16.15.168:4567 |
    +-----------------------+-----------------------------------------------+
    1 row in set (0.00 sec)
    
    MariaDB [(none)]> quit;
    Bye
    

    檢查一下看看是否已經成功加入. wsrep_cluster_size 應該為 3 而 wsrep_incoming_addresses 會列出三台 Cluster Node IP ( 172.16.15.167 ,172.16.15.168 ,172.16.15.169 )

    [root@cn169 ~]# netstat -anlp | grep -e 4567 -e 3306 -e 4568 -e 4444
    tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      2572/mysqld         
    tcp        0      0 0.0.0.0:4567            0.0.0.0:*               LISTEN      2572/mysqld         
    tcp        0      0 172.16.15.169:48026     172.16.15.168:4567      ESTABLISHED 2572/mysqld         
    tcp        0      0 172.16.15.169:42232     172.16.15.169:4567      ESTABLISHED 2572/mysqld         
    [root@cn169 ~]# mysql -u root -p -e "show status like 'wsrep%'"
    Enter password: 
    +------------------------------+----------------------------------------------------------+
    | Variable_name                | Value                                                    |
    +------------------------------+----------------------------------------------------------+
    | wsrep_local_state_uuid       | 4ddf508c-7638-11e6-a1d3-fba5ec4dd49e                     |
    | wsrep_protocol_version       | 7                                                        |
    | wsrep_last_committed         | 1                                                        |
    | wsrep_replicated             | 0                                                        |
    | wsrep_replicated_bytes       | 0                                                        |
    | wsrep_repl_keys              | 0                                                        |
    | wsrep_repl_keys_bytes        | 0                                                        |
    | wsrep_repl_data_bytes        | 0                                                        |
    | wsrep_repl_other_bytes       | 0                                                        |
    | wsrep_received               | 5                                                        |
    | wsrep_received_bytes         | 488                                                      |
    | wsrep_local_commits          | 0                                                        |
    | wsrep_local_cert_failures    | 0                                                        |
    | wsrep_local_replays          | 0                                                        |
    | wsrep_local_send_queue       | 0                                                        |
    | wsrep_local_send_queue_max   | 1                                                        |
    | wsrep_local_send_queue_min   | 0                                                        |
    | wsrep_local_send_queue_avg   | 0.000000                                                 |
    | wsrep_local_recv_queue       | 0                                                        |
    | wsrep_local_recv_queue_max   | 2                                                        |
    | wsrep_local_recv_queue_min   | 0                                                        |
    | wsrep_local_recv_queue_avg   | 0.200000                                                 |
    | wsrep_local_cached_downto    | 18446744073709551615                                     |
    | wsrep_flow_control_paused_ns | 0                                                        |
    | wsrep_flow_control_paused    | 0.000000                                                 |
    | wsrep_flow_control_sent      | 0                                                        |
    | wsrep_flow_control_recv      | 0                                                        |
    | wsrep_cert_deps_distance     | 0.000000                                                 |
    | wsrep_apply_oooe             | 0.000000                                                 |
    | wsrep_apply_oool             | 0.000000                                                 |
    | wsrep_apply_window           | 0.000000                                                 |
    | wsrep_commit_oooe            | 0.000000                                                 |
    | wsrep_commit_oool            | 0.000000                                                 |
    | wsrep_commit_window          | 0.000000                                                 |
    | wsrep_local_state            | 4                                                        |
    | wsrep_local_state_comment    | Synced                                                   |
    | wsrep_cert_index_size        | 0                                                        |
    | wsrep_causal_reads           | 0                                                        |
    | wsrep_cert_interval          | 0.000000                                                 |
    | wsrep_incoming_addresses     | 172.16.15.167:3306,172.16.15.168:3306,172.16.15.169:3306 |
    | wsrep_desync_count           | 0                                                        |
    | wsrep_evs_delayed            |                                                          |
    | wsrep_evs_evict_list         |                                                          |
    | wsrep_evs_repl_latency       | 0/0/0/0/0                                                |
    | wsrep_evs_state              | OPERATIONAL                                              |
    | wsrep_gcomm_uuid             | 36f0ddd6-7659-11e6-9251-abb8ac160209                     |
    | wsrep_cluster_conf_id        | 3                                                        |
    | wsrep_cluster_size           | 3                                                        |
    | wsrep_cluster_state_uuid     | 4ddf508c-7638-11e6-a1d3-fba5ec4dd49e                     |
    | wsrep_cluster_status         | Primary                                                  |
    | wsrep_connected              | ON                                                       |
    | wsrep_local_bf_aborts        | 0                                                        |
    | wsrep_local_index            | 2                                                        |
    | wsrep_provider_name          | Galera                                                   |
    | wsrep_provider_vendor        | Codership Oy <info@codership.com>                        |
    | wsrep_provider_version       | 3.17(r0)                                                 |
    | wsrep_ready                  | ON                                                       |
    | wsrep_thread_count           | 2                                                        |
    +------------------------------+----------------------------------------------------------+
    

    這種設定是暫時的,下次重新開機需要同樣的步驟在設定一次.

可能你的狀況會更複雜,請參考下面的結節

  1. https://www.percona.com/blog/2014/09/01/galera-replication-how-to-recover-a-pxc-cluster/
  2. https://mariadb.com/blog/mariadb-galera-cluster-quirk-bootstrapping

One Reply to “重啟 MariaDB Galera Cluster”

  1. 自動參照通知: 設定 MariaDB Galera Cluster Server – Benjr.tw

發表迴響