Galera Cluster for MySQL

• 同步复制
• 真正的multi-master，即所有节点可以同时读写数据库
• 自动的节点成员控制，失效节点自动被清除
• 新节点加入数据自动复制
• 真正的并行复制，行级
• 用户可以直接连接集群，使用感受上与MySQL完全一致

优势:

• 因为是多主，所以不存在Slave lag(延迟)
• 不存在丢失交易的情况
• 同时具有读和写的扩展能力
• 更小的客户端延迟
• 节点间数据是同步的,而Master/Slave模式是异步的,不同slave上的binlog可能是不同的

环境

<span style="color: #000000"> IP 地址         主机名         描述
</span><span style="color: #800080">10.0</span>.<span style="color: #800080">0.10</span><span style="color: #000000">       node1       第一个启动
</span><span style="color: #800080">10.0</span>.<span style="color: #800080">0.11</span><span style="color: #000000">       node2
</span><span style="color: #800080">10.0</span>.<span style="color: #800080">0.12</span>       node3

版本信息

[root@node1 ~]#  cat /etc/redhat-<span style="color: #000000">release
CentOS Linux release </span><span style="color: #800080">7.1</span>.<span style="color: #800080">1503</span><span style="color: #000000"> (Core) 
[root@node1 </span>~]# uname -<span style="color: #000000">a
Linux node2 </span><span style="color: #800080">3.10</span>.<span style="color: #800080">0</span>-<span style="color: #800080">229</span>.el7.x86_64 #<span style="color: #800080">1</span> SMP Fri Mar <span style="color: #800080">6</span> <span style="color: #800080">11</span>:<span style="color: #800080">36</span>:<span style="color: #800080">42</span> UTC <span style="color: #800080">2015</span> x86_64 x86_64 x86_64 GNU/Linux

关闭防火墙和SElinux

[root@node1 ~<span style="color: #000000">]# systemctl stop firewalld.service
[root@node1 </span>~]# setenforce <span style="color: #800080">0</span>

清除原有Mysql

[root@node1 ~]# yum erase mysql mysql-devel mysql-libs -<span style="color: #000000">y
[root@node1 </span>~]# rpm -qa |grep mysql

组件下载地址

http:<span style="color: #008000">//</span><span style="color: #008000">releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64/</span>
http:<span style="color: #008000">//</span><span style="color: #008000">releases.galeracluster.com/galera-3/centos/7/x86_64/</span>

安装组件（yum 安装，依次安装了,如果失败，等其他安装包安装好以后，重新安装一遍）

[root@node1 ~]# yum install -y http:<span style="color: #008000">//</span><span style="color: #008000">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</span>
[root@node1 ~]# yum install -y http:<span style="color: #008000">//</span><span style="color: #008000">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</span>
[root@node1 ~]# yum install -y http:<span style="color: #008000">//</span><span style="color: #008000">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</span>
[root@node1 ~]# yum install -y http:<span style="color: #008000">//</span><span style="color: #008000">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</span>
[root@node1 ~]# yum install -y http:<span style="color: #008000">//</span><span style="color: #008000">releases.galeracluster.com/galera-3/centos/7/x86_64/galera-3-25.3.22-2.el7.x86_64.rpm</span>
[root@node1 ~]# yum install -y http:<span style="color: #008000">//</span><span style="color: #008000">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</span>

mysql 首次启动

[root@node1 ~]# service mysql start --skip-grant-tables

输入 mysql 登录，添加用户名、密码和对应数据库的用户名、密码权限

mysql><span style="color: #000000"> use mysql;
mysql</span>> update user <span style="color: #000000">set</span> password=PASSWORD(<span style="color: #800000">"</span><span style="color: #800000">123456</span><span style="color: #800000">"</span>)<span style="color: #000000"> where</span> USER=<span style="color: #800000">"</span><span style="color: #800000">root</span><span style="color: #800000">"</span><span style="color: #000000">
mysql</span>> quit

重启数据库

[root@node1 ~]# service mysql restart

重启mysql服务，使用创建的用户名和密码登录.再次进入的时候回提示你设置密码

 mysql> SET PASSWORD=PASSWORD(<span style="color: #800000">"</span><span style="color: #800000">123456</span><span style="color: #800000">"</span>)

 给对应需要访问MySQL的用户名和密码进行授权，如果没有授权，是无法远程连接MySQL的（两条密令二选一）

<strong>授权指定端口用户连接mysql数据库的命令</strong>
mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘10.0.0.10‘ IDENTIFIED BY ‘123456‘ WITH    GRANT OPTION;
<strong>授权无端口限制的用户登录mysql数据库</strong>
mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘%‘ IDENTIFIED BY ‘123456‘ WITH GRANT OPTION;　　

创建同步账号

mysql> grant all on *.* to <span style="color: #800000">‘</span><span style="color: #800000">wsrep</span><span style="color: #800000">‘</span>@<span style="color: #800000">‘</span><span style="color: #800000">%</span><span style="color: #800000">‘</span> identified by <span style="color: #800000">‘</span><span style="color: #800000">nx111111</span><span style="color: #800000">‘</span><span style="color: #000000">;
</span>mysql><span style="color: #000000"> quit<br></span>

设置Mysql为开机启动

[root@node1 ~]# chkconfig mysql on

去掉Postfix,这个可能跟MySQL配置有冲突

[root@node1 ~]# yum remove postfix -y

关闭Mysql

[root@node1 ~]# service mysql stop

配置文件

[root@node1 ~]# vim /etc/<span style="color: #000000">my.cnf 
</span>!includedir /etc/my.cnf.d/

拷贝swrep.conf 文件到/etc/my.cnf.d/下

[root@node1 ~]# cp -r /usr/share/doc/mysql-wsrep-server-<span style="color: #800080">5.6</span>-<span style="color: #800080">5.6</span>.<span style="color: #800080">38</span>/wsrep.cnf /etc/my.cnf.d/

将以下配置替原有配置

vim /etc/my.cnf.d/<span style="color: #000000">wsrep.conf
[mysqld]
server_id </span>= <span style="color: #800080">10</span>    # 每个节点都不一样，分别是 <span style="color: #800080">10</span>、<span style="color: #800080">11</span>、<span style="color: #800080">12</span><span style="color: #000000">
default_storage_engine </span>=<span style="color: #000000"> InnoDB
datadir </span>=/<span style="color: #0000ff">var</span>/lib/mysql/<span style="color: #000000">
binlog_format </span>=<span style="color: #000000"> ROW
log</span>-bin = mysql-<span style="color: #000000">bin
log</span>-slave-updates = <span style="color: #800080">1</span><span style="color: #000000">              
innodb_autoinc_lock_mode </span>= <span style="color: #800080">2</span><span style="color: #000000">
lower_case_table_names </span>= <span style="color: #800080">1</span><span style="color: #000000">   # 修改这个配置必须重启
log_bin_trust_function_creators </span>= <span style="color: #800080">1</span><span style="color: #000000">
wsrep_provider </span>= /usr/lib64/galera-<span style="color: #800080">3</span>/<span style="color: #000000">libgalera_smm.so
wsrep_sst_auth </span>=<span style="color: #000000"> wsrep:nx111111
wsrep_sst_method </span>=<span style="color: #000000"> xtrabackup
wsrep_cluster_name </span>= <span style="color: #800000">‘</span><span style="color: #800000">nx_wsrep</span><span style="color: #800000">‘</span><span style="color: #000000">  # 集群名，一定要一致。
wsrep_node_name </span>=<span style="color: #000000"> node1  #本节点主机名
wsrep_cluster_address </span>= gcomm:<span style="color: #008000">//</span><span style="color: #008000">10.0.0.11,10.0.0.12  <span style="color: #000000"># 其它节点 IP</span></span>
wsrep_node_address = 10.0.0.10<span style="color: #000000">  # 本节点 IP
wsrep_sst_donor</span>=node2,node3,  # <span style="color: #000000">其它节点的名称
wsrep_sst_method </span>=<span style="color: #000000"> rsync
wsrep_slave_threads</span>=<span style="color: #800080">8</span><span style="color: #000000">
innodb_buffer_pool_size</span>=<span style="color: #800080">10737418240</span><span style="color: #000000">
innodb_log_file_size</span>=<span style="color: #800080">134217728</span><span style="color: #000000">
innodb_flush_log_at_trx_commit</span>=<span style="color: #800080">2</span><span style="color: #000000">
#innodb_buffer_pool_dump_at_shutdown</span>=<span style="color: #800080">1</span><span style="color: #000000">
#innodb_buffer_pool_load_at_startup</span>=<span style="color: #800080">1</span><span style="color: #000000">
#innodb_log_file_buffer_size</span>=<span style="color: #800080">16777216</span><span style="color: #000000">
skip</span>-name-<span style="color: #000000">resolve
[client]
user </span>=<span style="color: #000000"> wsrep
password </span>= nx111111

启动 mysql-wsrep

集群中的第一个节点，以空地址启动

[root@node1 ~]# mysqld_safe --wsrep_cluster_address=gcomm:<span style="color: #008000">//</span><span style="color: #008000"> >/dev/null &</span>

其它节点，启动方式

[root@node1 ~]# mysqld_safe > /dev/<span style="color: #0000ff">null</span> &

查看集群状态

确认MySQL的3306端口和wsrep的4567端口处于监听状态

[root@node3 ~]# netstat -tulpn | grep -e <span style="color: #800080">4567</span> -e <span style="color: #800080">3306</span><span style="color: #000000">
tcp        </span><span style="color: #800080">0</span>      <span style="color: #800080">0</span> <span style="color: #800080">0.0</span>.<span style="color: #800080">0.0</span>:<span style="color: #800080">4567</span>            <span style="color: #800080">0.0</span>.<span style="color: #800080">0.0</span>:*               LISTEN      <span style="color: #800080">5036</span>/<span style="color: #000000">mysqld         
tcp6       </span><span style="color: #800080">0</span>      <span style="color: #800080">0</span> :::<span style="color: #800080">3306</span>                 :::*                    LISTEN      <span style="color: #800080">5036</span>/mysqld

集群功能是否开启

mysql> show status like <span style="color: #800000">‘</span><span style="color: #800000">wsrep_ready</span><span style="color: #800000">‘</span><span style="color: #000000">; 
</span>+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_ready   | ON    |
+---------------+-------+
<span style="color: #800080">1</span> row <span style="color: #0000ff">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800080">0.00</span> sec)

集群ID号

mysql> SHOW  GLOBAL  STATUS  LIKE  <span style="color: #800000">‘</span><span style="color: #800000">wsrep_cluster_state_uuid</span><span style="color: #800000">‘</span><span style="color: #000000"> ;
</span>+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_cluster_state_uuid | 176a8d84-e0ba-11e7-9e7b-d7b3a39130ff |
+--------------------------+--------------------------------------+
<span style="color: #800080">1</span> row <span style="color: #0000ff">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800080">0.00</span> sec)

集群中的节点数量

mysql> SHOW  GLOBAL  STATUS  LIKE  <span style="color: #800000">‘</span><span style="color: #800000">wsrep_cluster_size</span><span style="color: #800000">‘</span><span style="color: #000000"> ;
</span>+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | <span style="color: #800080">3</span>     |
+--------------------+-------+
<span style="color: #800080">1</span> row <span style="color: #0000ff">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800080">0.00</span> sec)

集群（其他）节点地址

mysql> show variables like <span style="color: #800000">‘</span><span style="color: #800000">wsrep_cluster_address</span><span style="color: #800000">‘</span><span style="color: #000000">;
</span>+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| wsrep_cluster_address | gcomm:<span style="color: #008000">//</span><span style="color: #008000"> |</span>
+-----------------------+----------+
<span style="color: #800080">1</span> row <span style="color: #0000ff">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800080">0.00</span> sec)

参考

http:<span style="color: #008000">//</span><span style="color: #008000">blog.csdn.net/yangchuan_csdn91/article/details/52689981?locationNum=2</span>

Galera Cluster for MySQL

标签：cas   core   col   连接   _id   并行   star   失效   lease