2014年1月11日 星期六

[研究] MariaDB Galera Cluster 5.5.34 資料庫安裝(yum) (CentOS 6.5 x64)

[研究] MariaDB Galera Cluster 5.5.34 資料庫安裝(yum) (CentOS 6.5 x64)

2014-01-11
2016-02-17 修訂官方網站和下載網址變更

瑪利亞資料庫 (Maria) 是 MySQL 創辦人離開 Sun 後又開的公司所開發的資料庫系統,很多操作和觀念和原來的 MySQL 相同。

官方網站 : 瑪利亞資料庫 (Maria)
https://www.mariadb.org/

下載網頁
https://downloads.mariadb.org/

安裝說明
https://downloads.mariadb.org/
https://mariadb.com/kb/en/getting-started-with-mariadb-galera-cluster/
http://kb.askmonty.org/v/getting-started
https://mariadb.com/kb/en/installing-mariadb-with-yum/

環境

centos1    192.168.128.101    CentOS 6.5 x64
centos2    192.168.128.101    CentOS 6.5 x64
centos3    192.168.128.101    CentOS 6.5 x64

安裝

centos1, centos2, centos3 上執行

# 立刻關閉 SELinux
/usr/sbin/setenforce 0 

# 設定 reboot 後也關閉 SELinux
sed -i -e "s@SELINUX=enforcing@#SELINUX=enforcing@" /etc/selinux/config
sed -i -e "s@SELINUX=permissive@#SELINUX=permissive@" /etc/selinux/config
sed -i -e "/SELINUX=/aSELINUX=disabled" /etc/selinux/config

# 關閉防火牆
service iptables stop
chkconfig iptables off

# 移除 MySQL 相關,避免和 MariaDB 瑪麗亞資料庫系統衝突
#rpm -e --nodeps mysql-libs
yum -y remove mysql*

# 建立 yum 安裝 MariaDB 設定
vi /etc/yum.repos.d/MariaDB.repo

內容

# MariaDB 5.5 CentOS repository list - created 2013-02-08 00:52 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1



echo  "
# MariaDB 5.5 CentOS repository list - created 2013-02-08 00:52 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
"   >   /etc/yum.repos.d/MariaDB.repo

看看提供了些甚麼

[root@localhost ~]# yum list | grep MariaDB
MariaDB-Galera-server.x86_64              5.5.34-1                       mariadb
MariaDB-client.x86_64                     5.5.34-1                       mariadb
MariaDB-common.x86_64                     5.5.34-1                       mariadb
MariaDB-compat.x86_64                     5.5.34-1                       mariadb
MariaDB-devel.x86_64                      5.5.34-1                       mariadb
MariaDB-server.x86_64                     5.5.34-1                       mariadb
MariaDB-shared.x86_64                     5.5.34-1                       mariadb
MariaDB-test.x86_64                       5.5.34-1                       mariadb
[root@localhost ~]#


安裝 MariaDB Cluster Server

[root@localhost ~]# yum -y install MariaDB-Galera-server MariaDB-client galera

啟動

[root@centos1 ~]# service mysql start
Starting MySQL..                                           [  OK  ]
[root@centos1 ~]#

設定 reboot 後仍啟動
[root@centos1 ~]# chkconfig mysql on


設定 MariaDB 管理員 root 的密碼 (假設設定為 ABCDE )

[root@centos1 ~]# /usr/bin/mysqladmin -u root password 'ABCDE'

或 (-h 是指定主機名稱)

[root@centos1 ~]# /usr/bin/mysqladmin' -u root -h centos1 password 'ABCDE'

或 (交談方式,除了密碼設定,其他都按下 Enter)

[root@centos1 ~]# /usr/bin/mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

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):
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
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]
 ... Success!

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@centos1 ~]#

設 MySQL root 的密碼

#mysqladmin --user=root create cacti
#mysql -e "use cacti; show databases; "
#mysql cacti < cacti.sql
#mysql -e "use cacti; show tables; "
#mysql -e "GRANT ALL PRIVILEGES ON cacti.* TO cactiuser@localhost IDENTIFIED BY 'cactiuser' ; "
#mysql -e " flush privileges; "
#mysql -e "use mysql; select * from user where User='galerauser'; "

# 假設 MySQL root 的密碼為 ABCDE
# 建立使用者 galerauser,密碼 123456,給 Cluster 使用
mysql -e "GRANT ALL PRIVILEGES ON *.* TO galerauser@localhost IDENTIFIED BY '123456' ; " -pABCDE
mysql -e "GRANT ALL PRIVILEGES ON *.* TO galerauser@% IDENTIFIED BY '123456' ; " -pABCDE
mysql -e " flush privileges; " -pABCDE

驗證

[root@centos1 ~]# mysql -e "use mysql; select User,Password,Host from user where User='galerauser'; " -pABCDE
+------------+-------------------------------------------+-----------+
| User       | Password                                  | Host      |
+------------+-------------------------------------------+-----------+
| galerauser | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
+------------+-------------------------------------------+-----------+
[root@centos1 ~]#

修改 wsrep.cnf

[root@centos1 ~]# find / -name wsrep.cnf
/usr/share/mysql/wsrep.cnf
[root@centos1 ~]#

[root@centos1 ~]# cp /usr/share/mysql/wsrep.cnf /etc/my.cnf.d/
[root@centos1 ~]# vi /etc/my.cnf.d/wsrep.cnf
只需要修改如下4行:
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_sst_auth=galerauser:123456
wsrep_sst_method=rsync

或用下面方式修改

cp /usr/share/mysql/wsrep.cnf /etc/my.cnf.d/

sed -i -e "s@wsrep_provider=@#wsrep_provider=@"   /etc/my.cnf.d/wsrep.cnf
sed -i -e "/wsrep_provider=/awsrep_provider=/usr/lib64/galera/libgalera_smm.so"   /etc/my.cnf.d/wsrep.cnf

sed -i -e "s@wsrep_cluster_address=@#wsrep_cluster_address=@"   /etc/my.cnf.d/wsrep.cnf
sed -i -e "/wsrep_cluster_address=/awsrep_cluster_address=\"gcomm://\""   /etc/my.cnf.d/wsrep.cnf

sed -i -e "s@wsrep_sst_auth=@#wsrep_sst_auth=@"   /etc/my.cnf.d/wsrep.cnf
sed -i -e "/wsrep_sst_auth=/awsrep_sst_auth=galerauser:123456"   /etc/my.cnf.d/wsrep.cnf

sed -i -e "s@wsrep_sst_method=@#wsrep_sst_method=@"   /etc/my.cnf.d/wsrep.cnf
sed -i -e "/wwsrep_sst_method=/awsrep_sst_method=rsync"   /etc/my.cnf.d/wsrep.cnf

#驗證

cat /etc/my.cnf.d/wsrep.cnf | grep  wsrep_provider=
cat /etc/my.cnf.d/wsrep.cnf | grep  wsrep_cluster_address=
cat /etc/my.cnf.d/wsrep.cnf | grep  wsrep_sst_auth=
cat /etc/my.cnf.d/wsrep.cnf | grep  wsrep_sst_method=

重新啟動 MariaDB,讓 wsrep.cnf 生效

[root@centos1 ~]# service mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL.... SUCCESS!
[root@centos1 ~]#


驗證 cluster 狀態

[root@centos1 ~]# mysql -e "SHOW STATUS LIKE 'wsrep_%'; " -pABCDE




[root@centos1 ~]# mysql  -pABCDE
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.34-MariaDB-wsrep MariaDB Server, wsrep_23.7.6

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | f5abdcf1-7a66-11e3-b47d-e23ceae4aada |
| wsrep_protocol_version     | 4                                    |
| wsrep_last_committed       | 0                                    |
| wsrep_replicated           | 0                                    |
| wsrep_replicated_bytes     | 0                                    |
| wsrep_received             | 2                                    |
| wsrep_received_bytes       | 138                                  |
| wsrep_local_commits        | 0                                    |
| wsrep_local_cert_failures  | 0                                    |
| wsrep_local_bf_aborts      | 0                                    |
| wsrep_local_replays        | 0                                    |
| wsrep_local_send_queue     | 0                                    |
| wsrep_local_send_queue_avg | 0.000000                             |
| wsrep_local_recv_queue     | 0                                    |
| wsrep_local_recv_queue_avg | 0.000000                             |
| 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_incoming_addresses   | 192.168.128.101:3306                 |
| wsrep_cluster_conf_id      | 1                                    |
| wsrep_cluster_size         | 1                                    |
| wsrep_cluster_state_uuid   | f5abdcf1-7a66-11e3-b47d-e23ceae4aada |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_index          | 0                                    |
| wsrep_provider_name        | Galera                               |
| wsrep_provider_vendor      | Codership Oy <info@codership.com>    |
| wsrep_provider_version     | 23.2.7(r157)                         |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
40 rows in set (0.00 sec)

MariaDB [(none)]> \q
Bye
[root@centos1 ~]#

注意 wsrep_ready 是 ON,wsrep_cluster_size 目前是 1

驗證 port 傾聽狀態

[root@centos1 ~]# netstat -tulpn | grep -e 4567 -e 3306
tcp        0      0 0.0.0.0:4567                0.0.0.0:*                   LISTEN      4019/mysqld
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      4019/mysqld
[root@centos1 ~]#

目前 centos1, centos2, centos3 基本工作測試 OK。

*********************************************************************************

修改 centos2, centos3 的  wsrep.cnf,準備讓 centos2, centos3 都和 centos1 同 Cluster

也就如果 centos2, centos3 出了意外離開 Cluster 後,再次加入 Cluster 時候會以 centos1 資料為準做同步

[root@centos1 ~]# vi /etc/my.cnf.d/wsrep.cnf
只需要修改
wsrep_cluster_address="gcomm://"

wsrep_cluster_address="gcomm://192.168.128.101"

或用下面方式修改

sed -i -e "s@wsrep_cluster_address=@#wsrep_cluster_address=@"   /etc/my.cnf.d/wsrep.cnf
sed -i -e "/wsrep_cluster_address=/awsrep_cluster_address=\"gcomm://192.168.128.101\""   /etc/my.cnf.d/wsrep.cnf

#驗證

cat /etc/my.cnf.d/wsrep.cnf | grep  wsrep_cluster_address=

重新啟動 MariaDB,讓 wsrep.cnf 生效

[root@centos1 ~]# service mysql restart

隨便一台檢查  wsrep_cluster_size ,應該是 3

[root@centos1 ~]# mysql -e "SHOW STATUS LIKE 'wsrep_%'; " -pABCDE  | grep wsrep_cluster_size
wsrep_cluster_size      3
[root@centos1 ~]#

隨便一台檢查  wsrep_incoming_addresses ,發現 cluster 中包含 3 台電腦

[root@centos2 ~]# mysql -e "SHOW STATUS LIKE 'wsrep_%'; " -pABCDE | grep wsrep_incoming_addresses

wsrep_incoming_addresses        192.168.128.103:3306,192.168.128.102:3306,192.168.128.101:3306
[root@centos2 ~]#

*********************************************************************************

如果 centos1 掛了呢?

修改 centos1 的 wsrep.cnf,如果 centos1 出了意外離開 Cluster 後,再次加入 Cluster 時候會以 centos2 資料為準做同步,回復資料

[root@centos1 ~]# vi /etc/my.cnf.d/wsrep.cnf
只需要修改
wsrep_cluster_address="gcomm://"

wsrep_cluster_address="gcomm://192.168.128.102"

或用下面方式修改

sed -i -e "s@wsrep_cluster_address=@#wsrep_cluster_address=@"   /etc/my.cnf.d/wsrep.cnf
sed -i -e "/wsrep_cluster_address=/awsrep_cluster_address=\"gcomm://192.168.128.102\""   /etc/my.cnf.d/wsrep.cnf

#驗證

cat /etc/my.cnf.d/wsrep.cnf | grep  wsrep_cluster_address=

重新啟動 MariaDB,讓 wsrep.cnf 生效

[root@centos1 ~]# service mysql restart

*********************************************************************************

2014-10-11

發現 http://yum.mariadb.org/5.5/centos6-amd64 提供的 MariaDB 版本為 5.5.40,在 CentOS 6.5 x64 上 yum 安裝後, service  mysql   restart 會失敗,待研究

(完)

相關

[研究] MariaDB Galera Cluster 5.5.34 安裝(yum) (CentOS 6.5 x64)
http://shaurong.blogspot.com/2014/01/mariadb-galera-cluster-5534-yum-centos.html

[研究] MariaDB 5.5 安裝(yum)(CentOS 6.3 x64)
http://shaurong.blogspot.tw/2013/02/mariadb-55-yum-centos-63-x64.html
http://forum.icst.org.tw/phpbb/viewtopic.php?t=79306

[研究] MariaDB 5.1.42 安裝 (tar.gz)(CentOS 5.4 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=17988

[研究] MariaDB 5.2.4 安裝 (tar.gz)(CentOS 5.5 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=19434

MySQL 共同創辦人將離開昇陽自創公司(2009-02-06)
http://forum.icst.org.tw/phpbb/viewtopic.php?f=21&t=16331

3 則留言:

  1. sed -i -e "/wsrep_cluster_address=/awsrep_cluster_address=\"gcomm://192.168.128.102\"" /etc/my.cnf.d/wsrep.cnf 忘记了s 在第一个/之前

    回覆刪除
  2. ## RHEL/CentOS 6 32-Bit ##
    # wget http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm
    # rpm -ivh epel-release-6-8.noarch.rpm

    ## RHEL/CentOS 6 64-Bit ##
    # wget http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
    # rpm -ivh epel-release-6-8.noarch.rpm

    回覆刪除
  3. 請問一下目前 版本5.5.47-1 安裝CentOS 6.7 x64修改完wsrep.cnf也會無法restert,出現錯誤是否有解?

    回覆刪除