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

URL Link //n.sfs.tw/11036

2017-05-07 03:38:59 By 張○○

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

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

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

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

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

OS

CentOS Linux release 7.2.1511 (Core)

一、安裝Mariadb10

請先移除你目前的mysql/mariadb。

建立 yum reposity

# vim /etc/yum.repos.d/MariaDB.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

安裝

# yum install MariaDB-server MariaDB-client

二、啟動 rsync

使用 rsync 作為同步方式，得啟動rsync

# systemctl enable rsyncd
# systemctl start rsyncd

三、設置DB1(192.168.1.251)

由於 galera cluster是multi-master的架構，所以每一台都是master，有別於其他的master-slave架構，因此方便快速佈建。

# vim /etc/my.cnf.d/server.cnf

# 單機模式用
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
max_connections=2000
innodb_buffer_pool_size=1G

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://'
wsrep_node_address='192.168.1.251'
wsrep_cluster_name='mycluster'
wsrep_node_name='db251'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=myISAM
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

四、設定防火牆

需要用到的埠 3306, 4444, 4567, 4568，限定讓 192.168.1.0/24的網段存取，請依需要修改

# firewall-cmd --zone=public --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port port="3306" protocol="tcp" accept' --permanent

# firewall-cmd --zone=public --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port port="4444" protocol="tcp" accept' --permanent

# firewall-cmd --zone=public --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port port="4567-4568" protocol="tcp" accept' --permanent

# firewall-cmd --zone=public --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port port="4567" protocol="udp" accept' --permanent

重啟 firewalld

# firewall-cmd --reload

查看

# firewall-cmd --zone=public --list-all
public (default, active)
  interfaces: ens32
  sources:
  services: dhcpv6-client ssh
  ports:
  masquerade: no
  forward-ports:
  icmp-blocks:
  rich rules:
        rule family="ipv4" source address="192.168.1.0/24" port port="4444" protocol="tcp" accept
        rule family="ipv4" source address="192.168.1.0/24" port port="3306" protocol="tcp" accept
        rule family="ipv4" source address="192.168.1.0/24" port port="4567-4568" protocol="tcp" accept
        rule family="ipv4" source address="192.168.1.0/24" port port="4567" protocol="udp" accept


有關防火牆操作可參看 [Centos7] 新的防火牆firewalld @新精讚

五、關掉selinux

對於DB1，先把他的selinux關掉，等到其他的 db2, db3 ....設完再打開

# setenforce 0

六、初次啟動 DB1

得用 mysql的身分啟動才行

# sudo -u mysql mysqld --wsrep-new-cluster &

... 前面略...
2017-05-07  5:43:26 139712514410752 [Note] mysqld: ready for connections.
Version: '10.1.23-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

查看 DB1的狀態

# 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        | 1                                             |
| wsrep_cluster_size           | 1                    一個節點                  |
| wsrep_cluster_state_uuid     | cdbfb004-300f-11e7-b7a6-9a9083007cb8          |
| 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       | 3.788e-06/7.1326e-06/1.3821e-05/3.68898e-06/5 |
| 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             | 433aa2bd-32af-11e7-9336-6782b9677729          |
| wsrep_incoming_addresses     | 192.168.1.250: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            | 0                                             |
| wsrep_local_recv_queue       | 0                                             |
| wsrep_local_recv_queue_avg   | 0.500000                                      |
| wsrep_local_recv_queue_max   | 2                                             |
| 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       | cdbfb004-300f-11e7-b7a6-9a9083007cb8          |
| 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               | 2                                             |
| wsrep_received_bytes         | 142                                           |
| 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                                             |
+------------------------------+-----------------------------------------------+

OK，先放面不管，來設定其他的DB2、DB3...

參考資料

[1] Galera官網 http://galeracluster.com/products/

[2] http://blog.sina.com.cn/s/blog_704836f40101lixp.html

[3] https://blog.wu-boy.com/2013/03/galera-cluster-for-mysql-multi-master-replication/

[4] http://galeracluster.com/documentation-webpages/selinux.html