作業環境 CentOS 7.2 ,MariaDB & Galera 5.5 ,安裝方式請參考 https://benjr.tw/95350
MariaDB Galera Cluster 架構圖
每一個 Node 都需要以下的設定.
- Node1 : cn167 , IP – 172.16.15.167
- Node2 : cn168 , IP – 172.16.15.168
- 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 來設定,透過指令可以參考這一篇的說明 https://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
- wsrep_provider
Path to the Galera library. - 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. - wsrep_cluster_address
Galera Cluster Address,gcomm – This is the option to use for a working implementation. - 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. - wsrep_node_name
Name of this node.Default Value: The server’s hostname. - 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 - wsrep_sst_method=rsync
Method used for taking the state snapshot transfer (sst). - wsrep_node_address
Specifies the node’s network address, in the format ip address[:port]. As of MariaDB 10.1.8, supports IPv6.
其他參考設定值
- wsrep_on
ON – Enable wsrep replication (starting 10.1.1) - default_storage_engine
Default Value: InnoDB (>= MariaDB 5.5) - 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
如果啟動上有問題請參考 https://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 已經設定成功了.
- 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; 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)
- 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 – https://benjr.tw/95536 )讓使用者有統一窗口可以進行連線.
沒有解決問題,試試搜尋本站其他內容
6 thoughts on “CentOS 7 設定 MariaDB Galera Cluster Server”