[Mysql] 建立叢集式資料庫2/4 -- DB2、DB3設定

URL Link //n.sfs.tw/11038

2017-05-07 05:51:34 By 張○○

建立Mysql的叢集式資料庫可以讓Mysql的可用性更大,提昇服務的能量和質量。在以下的設定中,防火牆和selinux都是enabled,細節比較多,此文分幾個部分:

[Mysql] 建立叢集式資料庫1/4 -- 安裝及設定DB1

[Mysql] 建立叢集式資料庫2/4 -- DB2、DB3設定及測試

[Mysql] 建立叢集式資料庫3/4 -- DB1設置及DB PROXY

[Mysql] 建立叢集式資料庫4/4--觀察及測試

前面已建立DB1並打開防火牆、停掉SELINUX,並作初次的啟動,接下來先放著他,啟動DB2和DB3....資料庫。

七、設定DB2和DB3

DB2, DB3, ...請依照前面的步驟一、二、三、四設定,步驟三要修改的只有幾項:

[galera]
wsrep_cluster_address='gcomm://192.168.1.251,192.168.1.252,192.168.1.253'  <==把所有結點都填上,包括自己,用逗號區隔
wsrep_node_address='192.168.1.252'  <==改成該機器的IP
wsrep_cluster_name='mycluster'
wsrep_node_name='db252'   <==改成該結點名稱,不得重覆

其餘保持不變

八、設定 selinux

接下來要處理棘手的selinux,和DB1關掉selinux不一樣,DB2,DB3要真正來面對。

如果你想要忽略selinux的權限,放大絕就是把它關掉,或是你想選擇下面三者中任一個來處理都行,我試過都有效。

法一、關掉selinux

# vi /etc/sysconfig/selinux

SELINUX=permissive

重新啟動生效,這樣就能直接跳步驟六

法二、將 mysql用到的程序設成 permissive[4]

這招也行,其實我滿建議這招的,因為影響只有mysql用到的埠

安裝 semanage,請參考 [Centos7] 安裝 semanage (selinux工具程式)@新精讚

指定 port 給mysql_port_t 的角色
# semanage port -a -t mysqld_port_t -p tcp 4567

# semanage port -a -t mysqld_port_t -p tcp 4568

# semanage port -a -t mysqld_port_t -p tcp 4444

# semanage port -a -t mysqld_port_t -p udp 4567

設定permissive模式

# semanage permissive -a mysqld_t

法三、直接使用增強模式(enforcing mode) [4]

這招有難度,先動態設定selinux為 permissive mode,這設定前預設不可為disabled,換句話說,原本是enforcing或prermissive都可以。

# setenforce Permissive

查看selinux state
# sestatus
SELinux status:                 enabled
SELinuxfs mount:                /sys/fs/selinux
SELinux root directory:         /etc/selinux
Loaded policy name:             targeted

Current mode:                   permissive
Mode from config file:          enforcing
Policy MLS status:              enabled
Policy deny_unknown status:     allowed

重啟 mysql
# systemctl restart mysqld

DB2或DB3啟動的時候,DB1會跳出一堆訊息,並啟動成功,如果這時卡了很久沒回應,代表你的設定有問題或是防火牆?SELINUX都得檢查一下

# fgrep "mysqld" /var/log/audit/audit.log | audit2allow -m MySQL_galera -o galera.te

# checkmodule -M -m galera.te -o galera.mod
checkmodule:  loading policy configuration from galera.te
checkmodule:  Module name MySQL_galera is different than the output base filename galera

這裡的有錯誤要修改
# vi galera.te

把第2行

module MYSQL_galera 1.0;

換成

module galera 1.0;

存檔重新執行

# checkmodule -M -m galera.te -o galera.mod
checkmodule:  loading policy configuration from galera.te
checkmodule:  policy configuration loaded
checkmodule:  writing binary representation (version 17) to galera.mod

# semodule_package -m galera.mod -o galera.pp
# semodule -i galera.pp
< 這個要等大概20秒,這是正常的 >

# setenforce 1

再查看selinux state
# sestatus
SELinux status:                 enabled
SELinuxfs mount:                /sys/fs/selinux
SELinux root directory:         /etc/selinux
Loaded policy name:             targeted

Current mode:                   enforcing
Mode from config file:          enforcing
Policy MLS status:              enabled
Policy deny_unknown status:     allowed

重啟mysql

# systemctl restart mysql

如果沒有設定正確,重啟會出現錯誤:
Starting mysql (via systemctl):  Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.                                                           [失敗]

DB3或其他的DBn都照此操作

九、檢查啟動

任一個DB都可以,目前root 還沒有設定密碼,檢查運作的狀況

# mysql -u root

MariaDB [(none)]> show status like 'wsrep%';
+------------------------------+----------------------------------------------------------+
| Variable_name                | Value                                                    |
+------------------------------+----------------------------------------------------------+
| wsrep_apply_oooe             | 0.000000                                                 |
| wsrep_apply_oool             | 0.000000                                                 |
| wsrep_apply_window           | 0.000000                                                 |
| wsrep_causal_reads           | 0                                                        |
| wsrep_cert_deps_distance     | 0.000000                                                 |
| wsrep_cert_index_size        | 0                                                        |
| wsrep_cert_interval          | 0.000000                                                 |
| wsrep_cluster_conf_id        | 13                                                       |
| wsrep_cluster_size           | 3               <== 3 個結點代表三台主機                   |
| wsrep_cluster_state_uuid     | 96ecd3ef-332a-11e7-ab2a-ebf4cffc377d                     |
| wsrep_cluster_status         | Primary                                                  |
| wsrep_commit_oooe            | 0.000000                                                 |
| wsrep_commit_oool            | 0.000000                                                 |
| wsrep_commit_window          | 0.000000                                                 |
| wsrep_connected              | ON              <== 已連線                                |
| 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_flow_control_paused    | 0.000000                                                 |
| wsrep_flow_control_paused_ns | 0                                                        |
| wsrep_flow_control_recv      | 0                                                        |
| wsrep_flow_control_sent      | 0                                                        |
| wsrep_gcomm_uuid             | 4b6cdbed-332f-11e7-a4f6-328da6d17051                     |
| wsrep_incoming_addresses     | 192.168.1.251:3306,192.168.1.253:3306,192.168.1.252:3306 |
| wsrep_last_committed         | 0                                                        |
| wsrep_local_bf_aborts        | 0                                                        |
| wsrep_local_cached_downto    | 18446744073709551615                                     |
| wsrep_local_cert_failures    | 0                                                        |
| wsrep_local_commits          | 0                                                        |
| wsrep_local_index            | 2                                                        |
| wsrep_local_recv_queue       | 0                                                        |
| wsrep_local_recv_queue_avg   | 0.000000                                                 |
| wsrep_local_recv_queue_max   | 1                                                        |
| wsrep_local_recv_queue_min   | 0                                                        |
| wsrep_local_replays          | 0                                                        |
| wsrep_local_send_queue       | 0                                                        |
| wsrep_local_send_queue_avg   | 0.000000                                                 |
| wsrep_local_send_queue_max   | 1                                                        |
| wsrep_local_send_queue_min   | 0                                                        |
| wsrep_local_state            | 4                                                        |
| wsrep_local_state_comment    | Synced                                                   |
| wsrep_local_state_uuid       | 96ecd3ef-332a-11e7-ab2a-ebf4cffc377d                     |
| wsrep_protocol_version       | 7                                                        |
| wsrep_provider_name          | Galera                                                   |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>                        |
| wsrep_provider_version       | 25.3.20(r3703)                                           |
| wsrep_ready                  | ON                 <== 已啟動                             |
| wsrep_received               | 3                                                        |
| wsrep_received_bytes         | 486                                                      |
| wsrep_repl_data_bytes        | 0                                                        |
| wsrep_repl_keys              | 0                                                        |
| wsrep_repl_keys_bytes        | 0                                                        |
| wsrep_repl_other_bytes       | 0                                                        |
| wsrep_replicated             | 0                                                        |
| wsrep_replicated_bytes       | 0                                                        |
| wsrep_thread_count           | 2                                                        |
+------------------------------+----------------------------------------------------------+

因為我有三個DB,因此會有三個節點。

設定到這裡大致上沒問題,接下來是要處理DB1,記得嗎?DB1還是處於初始啟動的狀態,而且SELINUX也是關掉的。