CentOS 8 設定 MariaDB Galera Cluster Server

Loading

作業環境 CentOS 8 x86_64 (虛擬機)

MariaDB Galera Cluster 架構圖


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

  1. Node1 : cn183 , IP – 192.168.111.183
  2. Node2 : cn201 , IP – 192.168.111.201

以下步驟需在每一台 Cluster Node 設定好.

安裝 MariaDB Galera Cluster

直接透過 dnf 安裝, CentOS 7 安裝方式請參考 – https://benjr.tw/95350

[root@localhost ~]# dnf -y install mariadb-server-galera 
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 16:32:31 ago on Wed Jun 10 00:52:51 2020.
Dependencies resolved.
=================================================================================================================
 Package                         Arch        Version                                        Repository      Size
=================================================================================================================
Installing:
 mariadb-server-galera           x86_64      3:10.3.17-1.module_el8.1.0+257+48736ea6        AppStream       59 k
Installing dependencies:
 galera                          x86_64      25.3.26-1.module_el8.1.0+217+4d875839          AppStream      1.6 M
 mariadb                         x86_64      3:10.3.17-1.module_el8.1.0+257+48736ea6        AppStream      6.1 M
 mariadb-common                  x86_64      3:10.3.17-1.module_el8.1.0+257+48736ea6        AppStream       62 k
 mariadb-connector-c             x86_64      3.0.7-1.el8                                    AppStream      148 k
 mariadb-connector-c-config      noarch      3.0.7-1.el8                                    AppStream       13 k
 mariadb-errmsg                  x86_64      3:10.3.17-1.module_el8.1.0+257+48736ea6        AppStream      232 k
 mariadb-server                  x86_64      3:10.3.17-1.module_el8.1.0+257+48736ea6        AppStream       16 M
 perl-DBD-MySQL                  x86_64      4.046-3.module_el8.1.0+203+e45423dc            AppStream      156 k
Installing weak dependencies:
 mariadb-backup                  x86_64      3:10.3.17-1.module_el8.1.0+257+48736ea6        AppStream      6.0 M
 mariadb-gssapi-server           x86_64      3:10.3.17-1.module_el8.1.0+257+48736ea6        AppStream       49 k
 mariadb-server-utils            x86_64      3:10.3.17-1.module_el8.1.0+257+48736ea6        AppStream      1.6 M
Enabling module streams:
 mariadb                                     10.3                                                               
 perl                                        5.26                                                               
 perl-DBD-MySQL                              4.046                                                              

Transaction Summary
=================================================================================================================
Install  12 Packages

hosts & hostname

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

[root@localhost ~]# vi /etc/hosts
192.168.111.183 cn183
192.168.111.201 cn201

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 來設定,透過指令可以參考這一篇的說明 https://benjr.tw/12461

[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# mysql_secure_installation 

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] 
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@localhost ~]# 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@localhost ~]# mysql -u root -p -h 192.168.111.183
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, 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.001 sec)

MariaDB [(none)]> EXIT;

Node1-cn183

設定 Hostname

[root@localhost ~]# vi /etc/hostname
cn183
[root@localhost ~]# hostname cn183
[root@localhost ~]# exit
logout
Connection to 192.168.111.183 closed.
Ben@Ben10 ~ % ssh root@192.168.111.183
root@192.168.111.183's password: 
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Sat Jun 20 22:16:05 2020 from 192.168.111.1
[root@cn183 ~]# 

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

[root@cn183 ~]# systemctl stop mariadb 
Shutting down MySQL. SUCCESS! 

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

[root@cn183 ~]# vi /etc/my.cnf.d/mariadb-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='cn183'
wsrep_sst_auth=root:111111
wsrep_sst_method=rsync
wsrep_node_address=192.168.111.183
  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/

設定好之後透過 galera_new_cluster 啟動第一個 Galera cluster (當所有 Cluster Node 都關機後,啟動第一個 Node 也是透過這個指令)

[root@cn183 ~]# galera_new_cluster 

啟動 MariaDB .

[root@cn183 ~]# systemctl start mariadb 

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

[root@cn183 ~]# 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 狀態是否正常.發生錯誤時可以檢視一下目前 TCP port 的狀態.

[root@cn183 ~]# 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

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

Node2-cn201

設定 Hostname .

[root@localhost ~]# vi /etc/hostname
cn201
[root@localhost ~]# hostname cn201
[root@localhost ~]# exit
logout
Connection to 192.168.111.201 closed.
Ben@Ben10 ~ % ssh root@192.168.111.201
root@192.168.111.201's password: 
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Sat Jun 20 22:16:05 2020 from 192.168.111.1
[root@cn201 ~]# 

只需要修改一下 cn183 的設定檔即可以使用在 cn201 .

[root@cn201 ~]# systemctl stop mariadb 
[root@cn201 ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[galera]
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name='cluster'
wsrep_cluster_address='gcomm://192.168.111.183'
binlog_format=row
wsrep_node_name='cn201'
wsrep_sst_auth=root:111111
wsrep_sst_method=rsync
wsrep_node_address=192.168.111.201

啟動 mysql.

[root@cn201 ~]# systemctl start mariadb

確定一下 WSREP 的狀態, wsrep_cluster_size 應該變成 2 而 wsrep_incoming_addresses 應該會列出 cn183 與 cn201 兩個 Node 的 IP (192.168.111.201:3306,192.168.111.183:3306 )

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

看一下目前 TCP port 的狀態.

[root@cn201 ~]# netstat -anlp | grep -e 4567 -e 3306 -e 4568 -e 4444
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      2291/mysqld         
tcp        0      0 0.0.0.0:4567            0.0.0.0:*               LISTEN      2291/mysqld         
tcp        0      0 192.168.111.201:49926   192.168.111.183:4567    ESTABLISHED 2291/mysqld  

測試看看 Cluster

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

  1. CN183
    [root@cn183 ~]# 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;
    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. CN201
    [root@cn201 ~]# 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 – https://benjr.tw/95536 )讓使用者有統一窗口可以進行連線.

沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料