如何安裝和配置MariaDB Galera CentOS-7叢集

標籤：mysql   MariaDB Galera 資料庫叢集   

192.168.3.130 clustera galera-db01
192.168.3.131 clusterb galera-db02
192.168.3.129 clusterc galera-db03

以下1-3步三台都需要執行
1：卸載mysql（因為mysql與mariaDB與mysql不相容）
yum erase mysql-server mysql mysql-devel mysql-libs -y
rm -rf /var/lib/mysql

2：建立MariaDB Galera存放庫，mariadb 官方Yum 源配置：
vim /etc/yum.repos.d/mariadb.repo
vim 沒有的話安裝 yum install -y vim*

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

3：setenforce 0 關閉selinux
安裝防火牆並啟動設定允許連接埠
yum install firewalld firewall-config
systemctl start firewalld
firewall-cmd --add-rich-rule ‘rule family=ipv4 source address=192.168.3.129 port port=3306 protocol=tcp accept‘ --permanent
firewall-cmd --add-rich-rule ‘rule family=ipv4 source address=192.168.3.130 port port=3306 protocol=tcp accept‘ --permanent
firewall-cmd --add-rich-rule ‘rule family=ipv4 source address=192.168.3.131 port port=3306 protocol=tcp accept‘ --permanent
firewall-cmd --add-rich-rule ‘rule family=ipv4 source address=192.168.3.129 port port=4567 protocol=tcp accept‘ --permanent
firewall-cmd --add-rich-rule ‘rule family=ipv4 source address=192.168.3.130 port port=4567 protocol=tcp accept‘ --permanent
firewall-cmd --add-rich-rule ‘rule family=ipv4 source address=192.168.3.131 port port=4567 protocol=tcp accept‘ --permanent

以下4-6步在叢集mariadb節點伺服器上面操作
4：安裝MariaDB Galera伺服器和用戶端，rsync和xinetd
yum install MariaDB-Galera-server MariaDB-client rsync galera -y

如果系統中存在mysql會出如下錯誤

如果要自訂MariaDB的檔案目錄就如下操作（以自訂/data/目錄為例）
rm -rf /var/lib/mysql
mkdir -p /data/{mariadb,tmp,logs} && chown -R mysql:mysql /data/{mariadb,tmp,logs} 建目錄與許可權
ln -s /data/mariadb /var/lib/mysql
編輯/etc/my.cnf 檔案及設定最佳化參數，可以參照centos6配置mariadb叢集
mysql_install_db --defaults-file=/etc/my.cnf --user=mysql --datadir=/data/mariadb 初始化

5：啟動mariaDB並添加開機啟動和加固
service mysql start 啟動mariaDB
chkconfig --add mysql && chkconfig mysql on
/usr/bin/mysql_secure_installation 加固Mariadb 安全：

[[email protected] ~]# /usr/bin/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] n 這裡設定root密碼，正式環境設定選擇y
... skipping.

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] y
... 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] y

• 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] y
... 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!

首先是設定密碼，會提示先輸入密碼
Enter current password for root (enter for none):<–初次運行直接斷行符號
設定密碼
Set root password? [Y/n] <– 是否設定root使用者密碼，輸入y並斷行符號或直接斷行符號
New password: <– 設定root使用者的密碼
Re-enter new password: <– 再輸入一次你設定的密碼
其他配置
Remove anonymous users? [Y/n] <– 是否刪除匿名使用者，斷行符號
Disallow root login remotely? [Y/n] <–是否禁止root遠程登入,斷行符號,
Remove test database and access to it? [Y/n] <– 是否刪除test資料庫，斷行符號
Reload privilege tables now? [Y/n] <– 是否重新載入許可權表，斷行符號

6:建立使用者並授權
mysql -uroot 登入資料庫，正式設定密碼使用 mysql -uroot -p密碼
建立使用者並授權一般選用第一條命令即可，第二條命令後面三個參數是授權建立的使用者擁有建立他本身的許可權,實驗時候執行的第三條命令

GRANT ALL PRIVILEGES ON . TO ‘cluster-user‘@‘%‘ IDENTIFIED BY ‘clusterpass‘ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON . TO ‘cluster-user‘@‘localhost‘ IDENTIFIED BY ‘clusterpass‘ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON . TO ‘cluster-user‘@‘192.168.3.130‘ IDENTIFIED BY ‘clusterpass‘ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON . TO ‘cluster-user‘@‘192.168.3.131‘ IDENTIFIED BY ‘clusterpass‘ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON . TO ‘cluster-user‘@‘192.168.3.129‘ IDENTIFIED BY ‘clusterpass‘ WITH GRANT OPTION;

flush privileges;
exit
service mysql stop

7:節點配置 在mariadb1上面配置
vim /etc/my.cnf.d/server.cnf [mariadb-10.0] 下添加

query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.3.130,192.168.3.131,192.168.3.129"
wsrep_cluster_name="cluster1"
wsrep_node_address="192.168.3.130"
wsrep_node_name="galera-db01"
wsrep_sst_method=rsync
wsrep_sst_auth=cluster-user:clusterpass

query_cache_size 查詢快取，0關閉
binlog_format=ROW 複製模式
wsrep_cluster_address 所有節點的IP
wsrep_cluster_name 叢集名字
wsrep_node_address 當前伺服器IP，也就是每個節點本身的IP
wsrep_node_name 當前伺服器名字，也就是每個節點本身的名字
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
32位是/usr/lib/galera/libgalera_smm.so
64位是/usr/lib64/galera/libgalera_smm.so
不填寫正確啟動以後始終發現不要了叢集

8:在伺服器mariadb1上，我們使用以下命令引導叢集：
/etc/init.d/mysql bootstrap

mysql -u root -e "show status like ‘wsrep%‘" 查看狀態

8:節點配置 在mariadb2上面配置
vim /etc/my.cnf.d/server.cnf [mariadb-10.0] 下添加

query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.3.130,192.168.3.131,192.168.3.129"
wsrep_cluster_name="cluster1"
wsrep_node_address="192.168.3.131"
wsrep_node_name="galera-db02"
wsrep_sst_method=rsync
wsrep_sst_auth=cluster-user:clusterpass

/etc/init.d/mysql start

mysql -u root -e "show status like ‘wsrep%‘" 查看狀態

9:節點配置 在mariadb3上面配置
vim /etc/my.cnf.d/server.cnf [mariadb-10.0] 下添加

query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.3.130,192.168.3.131,192.168.3.129"
wsrep_cluster_name="cluster1"
wsrep_node_address="192.168.3.131"
wsrep_node_name="galera-db03"
wsrep_sst_method=rsync
wsrep_sst_auth=cluster-user:clusterpass

/etc/init.d/mysql start

mysql -u root -e "show status like ‘wsrep%‘" 查看狀態

驗證叢集
1.任意一台，CREATE DATABASES clustertest 建立庫
在另外兩台台可以查看到也建立了clustertest庫
2.在任意一台刪除clustertest庫，另外兩台也可以查看到已經刪除
3.dump掉MariaDB1的網卡 ifdown 網卡名，再嘗試建立test表，在表中寫資料，create table test (id int(4), name char(40)); 再啟動MariaDB1的網卡，ifup 網卡名，再到MAriaDB1查看寫的資料 select * from test;

10.使用haporxy代理的話，在Proxy 伺服器上操作（以下命令在叢集只有兩台作為仲裁使用）
yum erase mysql-server mysql mysql-devel mysql-libs -y
rm -rf /var/lib/mysql
yum install -y galera
which garbd
garbd -a gcomm://192.168.1.130:4567 -g cluster1 -l /tmp/1.out -d
mariaDB1上面執行
mysql -u root -e "show status like ‘wsrep%‘" 再查看狀態
wsrep_cluster_sixe 數值已更改

11.安裝配置haproxy
yum install -y haproxy

/etc/haproxy/haproxy.cfg
vim !$
global
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon
stats socket /var/lib/haproxy/stats
defaults
mode tcp
#mode http
option httplog
log global
option dontlognull
option redispatch
option tcpka
retries 3
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout check 10s
timeout http-keep-alive 10s
maxconn 10000

frontend main *:3306
bind 192.168.3.135:3306
default_backend app

backend app         balance roundrobin         server app1 192.168.3.130:3306  maxconn 151 check         server  app2 192.168.3.131:3306  maxconn 151 check         server  app3  192.168.3.129:3306  maxconn 151 check

haproxy加入開機啟動並啟動
chkconfig haproxy on
service haproxy start

驗證代理
mysql -u cluster-use -p clusterpass -h 192.168.3.135 -p 3306 -e "select @@hostname;"

如果有需要，可以配置MariaDB的字元集
vi /etc/my.cnf
在[mysqld]標籤下添加
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
vi /etc/my.cnf.d/client.cnf
在[client]中添加
default-character-set=utf8

vi /etc/my.cnf.d/mysql-clients.cnf
在[mysql]中添加
default-character-set=utf8
全部配置完成，重啟mariadb
systemctl restart mariadb
之後進入MariaDB查看字元集
mysql> show variables like "%character%";show variables like "%collation%";

如何安裝和配置MariaDB Galera CentOS-7叢集