Galera Cluster for MySQL

標籤：cas   core   col   串連   _id   並行   star   失效   lease   

MySQL Galera介紹

主要功能:

• 同步複製
• 真正的multi-master，即所有節點可以同時讀寫資料庫
• 自動的節點成員控制，失效節點自動被清除
• 新節點加入資料自動複製
• 真正的並行複製，行級
• 使用者可以直接連接叢集，使用感受上與MySQL完全一致

優勢:

• 因為是多主，所以不存在Slave lag(延遲)
• 不存在丟失交易的情況
• 同時具有讀和寫的擴充能力
• 更小的用戶端延遲
• 節點間資料是同步的,而Master/Slave模式是非同步,不同slave上的binlog可能是不同的

  環境

 IP 位址         主機名稱         描述10.0.0.10       node1       第一個啟動10.0.0.11       node210.0.0.12       node3

版本資訊

[[email protected] ~]#  cat /etc/redhat-releaseCentOS Linux release 7.1.1503 (Core) [[email protected] ~]# uname -aLinux node2 3.10.0-229.el7.x86_64 #1 SMP Fri Mar 6 11:36:42 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

關閉防火牆和SElinux

[[email protected] ~]# systemctl stop firewalld.service[[email protected] ~]# setenforce 0

清除原有Mysql

[[email protected] ~]# yum erase mysql mysql-devel mysql-libs -y[[email protected] ~]# rpm -qa |grep mysql

組件

http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/http://releases.galeracluster.com/galera-3/centos/7/x86_64/

安裝組件（yum 安裝，依次安裝了,如果失敗，等其他安裝包安裝好以後，重新安裝一遍）

[[email protected] ~]# yum install -y http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/mysql-wsrep-client-5.6-5.6.38-25.21.el7.x86_64.rpm[[email protected] ~]# yum install -y http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/mysql-wsrep-devel-5.6-5.6.38-25.21.el7.x86_64.rpm[[email protected] ~]# yum install -y http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/mysql-wsrep-shared-5.6-5.6.38-25.21.el7.x86_64.rpm[[email protected] ~]# yum install -y http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/mysql-wsrep-test-5.6-5.6.38-25.21.el7.x86_64.rpm[[email protected] ~]# yum install -y http://releases.galeracluster.com/galera-3/centos/7/x86_64/galera-3-25.3.22-2.el7.x86_64.rpm[[email protected] ~]# yum install -y http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/mysql-wsrep-server-5.6-5.6.38-25.21.el7.x86_64.rpm

mysql 初次開機

[[email protected] ~]# service mysql start --skip-grant-tables

輸入 mysql 登入，添加使用者名稱、密碼和對應資料庫的使用者名稱、密碼許可權

mysql> use mysql;mysql> update user set password=PASSWORD("123456") where USER="root"mysql> quit

重啟資料庫

[[email protected] ~]# service mysql restart

重啟mysql服務，使用建立的使用者名稱和密碼登入.再次進入的時候回提示你設定密碼

 mysql> SET PASSWORD=PASSWORD("123456")

 給對應需要訪問MySQL的使用者名稱和密碼進行授權，如果沒有授權，是無法遠端連線MySQL的（兩條密令二選一）

授權指定連接埠使用者串連mysql資料庫的命令mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘10.0.0.10‘ IDENTIFIED BY ‘123456‘ WITH    GRANT OPTION;授權無連接埠限制的使用者登入mysql資料庫mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘%‘ IDENTIFIED BY ‘123456‘ WITH GRANT OPTION;　　

建立同步帳號

mysql> grant all on *.* to ‘wsrep‘@‘%‘ identified by ‘nx111111‘;mysql> quit

設定Mysql為開機啟動

[[email protected] ~]# chkconfig mysql on

去掉Postfix,這個可能跟MySQL配置有衝突

[[email protected] ~]# yum remove postfix -y

關閉Mysql

[[email protected] ~]# service mysql stop

設定檔

[[email protected] ~]# vim /etc/my.cnf !includedir /etc/my.cnf.d/

拷貝swrep.conf 檔案到/etc/my.cnf.d/下

[[email protected] ~]# cp -r /usr/share/doc/mysql-wsrep-server-5.6-5.6.38/wsrep.cnf /etc/my.cnf.d/

將以下配置替原有配置

vim /etc/my.cnf.d/wsrep.conf[mysqld]server_id = 10    # 每個節點都不一樣，分別是 10、11、12default_storage_engine = InnoDBdatadir =/var/lib/mysql/binlog_format = ROWlog-bin = mysql-binlog-slave-updates = 1              innodb_autoinc_lock_mode = 2lower_case_table_names = 1   # 修改這個配置必須重啟log_bin_trust_function_creators = 1wsrep_provider = /usr/lib64/galera-3/libgalera_smm.sowsrep_sst_auth = wsrep:nx111111wsrep_sst_method = xtrabackupwsrep_cluster_name = ‘nx_wsrep‘  # 叢集名，一定要一致。wsrep_node_name = node1  #本節點主機名稱wsrep_cluster_address = gcomm://10.0.0.11,10.0.0.12  # 其它節點 IPwsrep_node_address = 10.0.0.10  # 本節點 IPwsrep_sst_donor=node2,node3,  # 其它節點的名稱wsrep_sst_method = rsyncwsrep_slave_threads=8innodb_buffer_pool_size=10737418240innodb_log_file_size=134217728innodb_flush_log_at_trx_commit=2#innodb_buffer_pool_dump_at_shutdown=1#innodb_buffer_pool_load_at_startup=1#innodb_log_file_buffer_size=16777216skip-name-resolve[client]user = wsreppassword = nx111111

啟動 mysql-wsrep

叢集中的第一個節點，以空地址啟動

[[email protected] ~]# mysqld_safe --wsrep_cluster_address=gcomm:// >/dev/null &

其它節點，啟動方式

[[email protected] ~]# mysqld_safe > /dev/null &

查看叢集狀態

確認MySQL的3306連接埠和wsrep的4567連接埠處於監聽狀態

[[email protected] ~]# netstat -tulpn | grep -e 4567 -e 3306tcp        0      0 0.0.0.0:4567            0.0.0.0:*               LISTEN      5036/mysqld         tcp6       0      0 :::3306                 :::*                    LISTEN      5036/mysqld

叢集功能是否開啟

mysql> show status like ‘wsrep_ready‘; +---------------+-------+| Variable_name | Value |+---------------+-------+| wsrep_ready   | ON    |+---------------+-------+1 row in set (0.00 sec)

叢集ID號

mysql> SHOW  GLOBAL  STATUS  LIKE  ‘wsrep_cluster_state_uuid‘ ;+--------------------------+--------------------------------------+| Variable_name            | Value                                |+--------------------------+--------------------------------------+| wsrep_cluster_state_uuid | 176a8d84-e0ba-11e7-9e7b-d7b3a39130ff |+--------------------------+--------------------------------------+1 row in set (0.00 sec)

叢集中的節點數量

mysql> SHOW  GLOBAL  STATUS  LIKE  ‘wsrep_cluster_size‘ ;+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| wsrep_cluster_size | 3     |+--------------------+-------+1 row in set (0.00 sec)

叢集（其他）節點地址

mysql> show variables like ‘wsrep_cluster_address‘;+-----------------------+----------+| Variable_name         | Value    |+-----------------------+----------+| wsrep_cluster_address | gcomm:// |+-----------------------+----------+1 row in set (0.00 sec)

參考

http://blog.csdn.net/yangchuan_csdn91/article/details/52689981?locationNum=2

 

Galera Cluster for MySQL