3,200 瀏覽數

CentOS 7 設定 MariaDB Galera Cluster Server

作業環境 CentOS 7.2 ,MariaDB & Galera 5.5 ,安裝方式請參考 http://benjr.tw/95350

MariaDB Galera Cluster 架構圖

MariaDB_Cluster
每一個 Node 都需要以下的設定.

  1. Node1 : cn167 , IP – 172.16.15.167
  2. Node2 : cn168 , IP – 172.16.15.168
  3. Node3 (暫時加入的方式) : cn169 , IP – 172.16.15.169

hosts & hostname

每一個 Node 都需要知道彼此相對應的 Hostname.

[root@cn167 ~]# vi /etc/hostname
cn167
[root@cn167 ~]# vi /etc/hosts
172.16.15.167 cn167
172.16.15.168 cn168

Firewall & SELinux

因為單純試 MariaDB Galera Cluster 的功能,所以為了避免影響測試,我將 Firewall 以及 SELinux 關閉 (安裝完 CentOS 預設都是開啟的).
停止 Firewall (iptables) 功能.

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld

selinux 只能調整開機後不要啟動.所以設定完後必須重新開關機.

[root@localhost ~]# vi /etc/sysconfig/selinux 
SELINUX=disabled
SELINUXTYPE=targeted
[root@localhost ~]# reboot

SELINUX 預設為 enforcing 需要修改成為 disabled .

[root@localhost ~]# sestatus
SELinux status:          disabled

設定 MySql

安裝完之後第一步就是要設定 MySQL ,我們可以透過 #mysql , #mysqladmin 指令或是透過 #mysql_secure_installation 來設定,透過指令可以參考這一篇的說明 http://benjr.tw/12461

[root@cn167 ~]# mysql_secure_installation 
/usr/bin/mysql_secure_installation: line 379: find_mysql_client:命令找不到

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current password for the root user.  If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them.  This is intended only for testing, and to make the installation go a bit smoother.  You should remove them before moving into a production environment.

Remove anonymous users? [Y/n] 
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can access.  This is also intended only for testing, and should be removed before moving into a production environment.

Remove test database and access to it? [Y/n] 
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n] 
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB installation should now be secure.

Thanks for using MariaDB!

除了設定密碼外,我都用預設值,不過我希望 root 能透過遠端連結到 database 所以把 Disallow root login remotely? [Y/n] n , 但 root 權限還是要設定過從能從遠端登入.

[root@cn167 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.51-MariaDB-wsrep MariaDB Server, 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)]> grant all privileges on *.* to root@'%' identified by '111111' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit;
Bye

主要的指令是下面這一行.

 MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by '111111' with grant option;

各代表的意思

  • grant all privileges
    授權的權限 (ALL: SELECT, INSERT, DELETE …. etc)
  • *.*
    指定可以存取哪些 Db_name/Table (* 代表全部)
  • root@’%’
    username@localhost , username – 新增的使用者, localhost – 限制可從哪裡來存取, 可用 ‘%’ (代表全部)
  • identified by ‘111111’
    password: 使用者密碼

試試看 root 可以透過遠端登入到 database.

[root@cn167 ~]# mysql -u root -p -h 172.16.15.167
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 5.5.51-MariaDB-wsrep MariaDB Server, 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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

Node1-cn167

接下來要修改 Cluster 的設定值,需先把 mysql 服務停止.

[root@cn167 ~]# service mysql stop
Shutting down MySQL. SUCCESS! 

Cluster 設定檔位於 /etc/my.cnf.d/server.cnf

[root@cn167 ~]# vi /etc/my.cnf.d/server.cnf
[galera]
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name='cluster'
wsrep_cluster_address='gcomm://'
binlog_format=row
wsrep_node_name='cn167'
wsrep_sst_auth=root:111111
wsrep_sst_method=rsync
wsrep_node_address=172.16.15.167
  1. wsrep_provider
    Path to the Galera library.
  2. wsrep_cluster_name
    The name of the cluster. Nodes cannot connect to clusters with a different name, so needs to be identical on all nodes in the same cluster.
  3. wsrep_cluster_address
    Galera Cluster Address,gcomm – This is the option to use for a working implementation.
  4. binlog_format
    Binary Log Formats, There are three formats for binary logging – statement-based, row-based and mixed
    Row-based – Records events affecting individual table rows. Enable with –binlog-format=ROW.
  5. wsrep_node_name
    Name of this node.Default Value: The server’s hostname.
  6. wsrep_sst_auth
    Username and password of the user to use for replication. Unused if wsrep_sst_method is set to rsync, while for other methods it should be in the format :. T
  7. wsrep_sst_method=rsync
    Method used for taking the state snapshot transfer (sst).
  8. wsrep_node_address
    Specifies the node’s network address, in the format ip address[:port]. As of MariaDB 10.1.8, supports IPv6.

其他參考設定值

  1. wsrep_on
    ON – Enable wsrep replication (starting 10.1.1)
  2. default_storage_engine
    Default Value: InnoDB (>= MariaDB 5.5)
  3. innodb_autoinc_lock_mode
    2 – interleaved” lock mode

更多關於 Galera Cluster System Variables 參數,請參考官方網站的說明:
https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/

因為第一個 Node 需要用 bootstrap 參數或是 start –wsrep-new-cluster 開啟 mysql.

[root@cn167 ~]# /etc/init.d/mysql bootstrap
Bootstrapping the cluster.. Starting MySQL.... SUCCESS!

在 Mariadb 10.1 之後就不能用 bootstrap 參數需要用 start –wsrep-new-cluster 開啟 mysql

[root@cn167 ~]# /etc/init.d/mysql bootstrap
 ERROR! Please use galera_new_cluster to start the mariadb service with --wsrep-new-cluster

[root@cn167 ~]# /etc/init.d/mysql start --wsrep-new-cluster
Starting MySQL SUCCESS! 

透過下面指令可以確認設定是否正確.

[root@cn167 ~]# mysql -u root -p -e "show status like 'wsrep%'"
.....
wsrep_cluster_size           | 1
wsrep_cluster_status         | Primary
wsrep_connected              | ON

看一下 wsrep_cluster_size , wsrep_cluster_status , wsrep_connected 狀態是否正常.發生錯誤時可以檢視一下 cat /var/lib/mysql/cn167.err (cn167 – hostname) 或是看一下目前 TCP port 的狀態.

[root@cn167 ~]# netstat -anlp | grep -e 4567 -e 3306 -e 4568 -e 4444
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      2457/mysqld         
tcp        0      0 0.0.0.0:4567            0.0.0.0:*               LISTEN      2457/mysqld              
  • 3306 – MySQL port
  • 4567 – Galera Cluster
  • 4568 – IST (Incremental State Transfers) port
  • 4444 – SST (state snapshot transfer) port

如果啟動上有問題請參考 http://benjr.tw/95413

Node2-cn168

修改一下 cn168 的 hostname.

[root@cn168 ~]# vi /etc/hostname
cn168

只需要修改一下 cn167 的設定檔位於即可以使用在 cn168 .

[root@cn168 ~]# /etc/init.d/mysql stop
[root@cn168 ~]# vi /etc/my.cnf.d/server.cnf
[galera]
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name='cluster'
wsrep_cluster_address='gcomm://172.16.15.167'
binlog_format=row
wsrep_node_name='cn168'
wsrep_sst_auth=root:111111
wsrep_sst_method=rsync
wsrep_node_address=172.16.15.168

啟動 mysql.

[root@cn168 ~]# /etc/init.d/mysql start
Starting MySQL....SST in progress, setting sleep higher. SUCCESS!

確定一下 WSREP 的狀態, wsrep_cluster_size 應該變成 2 而 wsrep_incoming_addresses 應該會列出 cn167 與 cn168 兩個 Node 的 IP (172.16.15.167:3306,172.16.15.168:3306)

[root@cn168 ~]# mysql -u root -p -e "show status like 'wsrep%'"
.....
wsrep_incoming_addresses     | 172.16.15.167:3306,172.16.15.168:3306
wsrep_cluster_size           | 2
wsrep_cluster_status         | Primary
wsrep_connected              | ON

看一下目前 TCP port 的狀態.

[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      2457/mysqld         
tcp        0      0 0.0.0.0:4567            0.0.0.0:*               LISTEN      2457/mysqld         
tcp        0      0 172.16.15.168:36143     172.16.15.167:4567      ESTABLISHED 2457/mysqld    

Node3-cn169

其實不一定要去修改 server.cnf 檔案才可以加入到 Cluster ,可以先將 gcomm:// 留空白,再手動加入到 Cluster .

[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.167:4567      ESTABLISHED 2572/mysqld         
tcp        0      0 172.16.15.169:42232     172.16.15.168: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                                                        |
+------------------------------+----------------------------------------------------------+

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

測試看看 Cluster

從 cn168 建立新增 test 資料庫,從 cn167 也可以看到新增加的 test 資料庫,就代表我們的 Cluster 已經設定成功了.

  1. CN168
    [root@cn168 ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 5
    Server version: 5.5.51-MariaDB-wsrep MariaDB Server, 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)]> create database test;
    ERROR 1007 (HY000): Can't create database 'test'; database exists
    MariaDB [(none)]> drop database test;
    Query OK, 0 rows affected (0.01 sec)
    
    MariaDB [(none)]> create database test;
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.00 sec)
    
  2. CN167
    [root@cn167 ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 5
    Server version: 5.5.51-MariaDB-wsrep MariaDB Server, 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)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.00 sec)
    

    這個 Cluster 還沒結束,雖然使用者不管連線到哪一台資料庫的資料都會同步,但對於使用者太不方便,他需要自行選擇不同台的資料庫,我們欠缺一台 proxy (HAProxy – http://benjr.tw/95536 )讓使用者有統一窗口可以進行連線.

6 Replies to “CentOS 7 設定 MariaDB Galera Cluster Server”

  1. 自動參照通知: Sysbech – OLTP 測試結果 (MariaDB Galera Cluster + HAProxy) – Benjr.tw

  2. 自動參照通知: MariaDB 10.1 Galera Cluster (Ubuntu) – Benjr.tw

  3. 自動參照通知: MariaDB Galera Cluster + HAProxy – Benjr.tw

  4. 自動參照通知: Ubuntu 16.04 編譯 MariaDB – Benjr.tw

  5. 自動參照通知: 編譯 mariaDB + Galera Cluster – Benjr.tw

  6. 自動參照通知: 安裝 MariaDB Galera Server – Benjr.tw

發表迴響