Ubuntu16.04环境下搭建基于三台主机的mysql galera cluster集群（实测有效）

（注意：

（1）文中红色字体部分不一定需要操作

（2）由于word文档编辑的原因，实际操作时部分命令需要手动输入！！直接复制粘贴会提示错误！！

）

一  搭建环境：

1 Ubuntu16.04版本（系统登录：root（199077））

2 mysql5.7（用户名root 密码199077）

二 安装环境：

首先，不论是在云服务器、物理机还是虚拟机上，至少需要3台主机（我们将这三台主机称为“Nodes”），对于每一个”Node”，硬件最低配置要求为： 

- 1GHz单核CPU 
- 512M的内存 
- 100Mbps的网络连接 
- 三台主机IP为： 
- Node1: 192.168.93.135（主节点）
- Node2: 192.168.93.132（从节点1）
- Node3: 192.168.93.134（从节点2）

其次要禁用SElinux、防火墙配置、禁用AppArmor，

这些在Ubuntu14.04下都是不用配置的，所以就不详细说明配置步骤。但在16.04环境下需严格配置：

（1）禁用SELinux的mysqld

         如果你启用了SELinux,它可能会阻止 mysqld从执行所需的操作。你必须禁用SELinux mysqld或允许配置它 mysqld上运行外部程序和开放的监听套接字端口外,非特权用户可以做的事情。

       禁用SELinux mysql运行以下命令:

semanage permissive -a mysqld_t

（2）防火墙配置：我们需要在每个节点上设置其防火墙规则，使得各个节点能与集群通信。在每个节点上均执行如下命令：

  iptables --append INPUT --protocol tcp \

      --source 192.168.93.135 --jump ACCEPT

 iptables --apend INPUT --protocol tcp \

      --source 192.168.93.132 --jump ACCEPT

 iptables --append INPUT --protocol tcp \

      --source 192.168.93.134 --jump ACCEPT

 

然后保存防火墙规则：

iptables-save

 

(3)禁用Ubuntu的apparmor：apparmor主要是Ubuntu系统中对mysql访问权限的控制，我们必须禁用AppArmor，以便配置mysql的mysqld能够运行外部程序。

禁用apparmor：

sudo ln -s /etc/apparmor.d/usr /etc/apparmor.d/disable/.sbin.mysqld

重启apparmor：

sudo service apparmor restart

注：如果不禁用，则在登录mysql时会出现如下错误：
apparmor="DENIED" operation="open" ........
对应解决方案：
第一种：依次执行如下命令：

apt install apparmor-utils

 sudo aa-complain/etc/apparmor.d/usr.sbin.mysqld

service apparmor restart

第二种：直接修改apparmor配置文件

nano /etc/apparmor.d/usr.sbin.mysqld

在配置文件中增加”flags=(complain)”

/usr/sbin/mysqld flags=(complain) {

  #include <abstractions/base>

  #include <abstractions/nameservice>

  #include <abstractions/user-tmp>

  #include <abstractions/mysql>

 ........

 

三 开始安装
（1）安装对应依赖

apt-get install python-software-properties  

apt-get install software-properties-common

（2）添加GunPG key

apt-key adv --keyserver keyserver.ubuntu.com --recv BC19DDBA

（3）配置Ubuntu下galera源

创建galera.list文件：

cd /etc/apt/sources.list.d

touch galera.list 

nano -w galera.list

在galera.list中添加内容：

#deb http://releases.galeracluster.com/mysql-wsrep-5.7/ubuntu xenial main  

#deb http://releases.galeracluster.com/galera-3/ubuntu xenial main  

deb http://releases.galeracluster.com/ubuntu xenial main

如果不清楚自己的Ubuntu详细信息，可通过命令lsb_release -a来查看。

（4）继续配置源

 cd /etc/apt/preferences.d

touch  galera.pref 

nano -w /etc/apt/preferences.d/galera.pref

在galera.pref文件中添加内容：

Package: *

Pin: origin releases.galeracluster.com

Pin-Priority: 1001

（5）接下来更新一下：apt-get update

(6) 一旦在所有三个服务器上更新了存储库，我们就可以在三台服务器上运行以下命令安装MySQL5.7的修补程序与Galera

apt-get install galera-3 galera-arbitrator-3 mysql-wsrep-5.7

（7）安装完成之后，在每一个节点上启动Mysql：

Service mysql start

（8）验证节点之间是否能互相ping通：

主节点135上：

Ping 192.168.93.132

Ping 192.168.93.134

从节点132上：

Ping 192.168.93.135

Ping 192.168.93.134

从节点134上：

Ping 192.168.93.135

Ping 192.168.93.132

能ping同则表示三节点之间能互相通信。

（9）分别在三个节点上修改mysql的配置文件/etc/mysql/my.cnf

主节点135上：

# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; version 2 of the License.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

# GNU General Public License for more details.

#

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#

# The MySQL database server configuration file.

#

# You can copy this to one of:

# - "/etc/mysql/my.cnf" to set global options,

# - "~/.my.cnf" to set user-specific options.

#

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

#

# For explanations see

# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

 

#

# * IMPORTANT: Additional settings that can override those from this file!

#   The files must end with '.cnf', otherwise they'll be ignored.

#

!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/mysql.conf.d/

[mysqld]

binlog_format=ROW

default-storage-engine=innodb

innodb_autoinc_lock_mode=2

wsrep_provider=/usr/lib/libgalera_smm.so

wsrep_provider_options="gcache.size=300M; gcache.page_size=1G"

wsrep_cluster_name="galeracluster1"

wsrep_cluster_address="gcomm://"

wsrep_sst_method=rsync

wsrep_sst_auth=wsrep:199077

wsrep_node_address="192.168.93.135"

wsrep_node_name=node135

bind-address=0.0.0.0

 

从节点132上：

# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; version 2 of the License.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

# GNU General Public License for more details.

#

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

 

#

# The MySQL database server configuration file.

#

# You can copy this to one of:

# - "/etc/mysql/my.cnf" to set global options,

# - "~/.my.cnf" to set user-specific options.

#

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

#

# For explanations see

# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#

# * IMPORTANT: Additional settings that can override those from this file!

#   The files must end with '.cnf', otherwise they'll be ignored.

#

 

!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/mysql.conf.d/

[mysqld]

binlog_format=ROW

default-storage-engine=innodb

innodb_autoinc_lock_mode=2

wsrep_provider=/usr/lib/libgalera_smm.so

wsrep_provider_options="gcache.size=300M; gcache.page_size=1G"

wsrep_cluster_name="galeracluster1"

wsrep_cluster_address="gcomm://192.168.93.135"

wsrep_sst_method=rsync

wsrep_sst_auth=wsrep:199077

wsrep_node_address="192.168.93.132"

wsrep_node_name=node132

bind-address=0.0.0.0

 

从节点134上：

# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; version 2 of the License.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

# GNU General Public License for more details.

#

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

 

#

# The MySQL database server configuration file.

#

# You can copy this to one of:

# - "/etc/mysql/my.cnf" to set global options,

# - "~/.my.cnf" to set user-specific options.

#

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

#

# For explanations see

#

# * IMPORTANT: Additional settings that can override those from this file!

#   The files must end with '.cnf', otherwise they'll be ignored.

#

!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/mysql.conf.d/

[mysqld]

binlog_format=ROW

default-storage-engine=innodb

innodb_autoinc_lock_mode=2

wsrep_provider=/usr/lib/libgalera_smm.so

wsrep_provider_options="gcache.size=300M; gcache.page_size=1G"

wsrep_cluster_name="galeracluster1"

wsrep_cluster_address="gcomm://192.168.93.135"

wsrep_sst_method=rsync

wsrep_sst_auth=wsrep:199077

wsrep_node_address="192.168.93.134"

wsrep_node_name=node134

bind-address=0.0.0.0

（10）

在每个节点上重启mysql：

Service mysql restart

(11) 试着在任意一个节点上登录其余两个节点，如果都能彼此登录，则表明三台服务器上的mysql彼此监听成功。

如在135上登录132，134

mysql -h 192.168.93.132 -u root -p

mysql -h 192.168.93.134 -u root -p

如果彼此登录不成功，则需要在每个节点上创建用户以实现mysql的远程登录。

。创建用户时，需要对应每个节点的mysql的配置文件/etc/mysql/my.cnf中的“wsrep_sst_auth=wsrep:199077”项，该项用来设置服务器之间mysql彼此访问的用户名和密码，其中wsrep为用户名，199077为密码。

具体创建命令如下：

每个节点数据库远程访问的创建

create user 'wsrep'@'%' identified by '199077';

create user 'root'@'%' identified by '199077';#这一句不是很清楚是否可以去掉

GRANT ALL PRIVILEGES ON *.* TO 'wsrep'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

创建完后重启各个节点的mysql,再试着在每个节点上登录其他节点是否成功。

如在从节点132上登录135、134的mysql

mysql -h 192.168.93.135 -u root -p  #成功

mysql -h 192.168.93.134 -u root -p  #成功

mysql -h 192.168.93.135 -u wsrep -p #成功

mysql -h 192.168.93.134 -u wsrep -p #成功

主节点135和从节点134也进行同样操作，如果均能登录，则设置成功！

 

（12）接下来，Ubuntu有一个特别的用户叫debian-sys-maint会来维持Mysql的运行任务，由于数据库的复制，这个维护的运行只会在第一个节点（即主节点）成功进行，在其他节点上则会失败，所以要将启动节点（即主节点）上的/etc/mysql/debian.cnf文件拷贝到其他节点上。

即在主节点135上执行如下命令：

scp /etc/mysql/debian.cnf root@192.168.93.132:/etc/mysql/ 

scp /etc/mysql/debian.cnf root@192.168.93.134:/etc/mysql/

 

执行过程中可能会失败，这个问题是因为在主节点的/etc/mysql/my.cnf文件中有一行要改为：

PermitRootLogin yes

这样就会成功了！当以上步骤都完成了之后，可以准备开始启动集群了！

注：步骤（12）我在实际中未操作过，但集群也成功了，Debian 对mysql集群的日常维护可能有一定影响，读者可自行研究。

 

四 启动集群

在主节点上将mysql服务先停掉然后重启，注意一定要在前面提到的wsrep_cluster_address="gcomm://"的节点上执行最开始的启动程序：

service mysql stop 

service mysql start --wsrep-new-cluster

在其他节点上执行：service mysql restart

 

五 验证是否成功

查看数据表

登录主节点135：

mysql -u root -p 

Enter password:

mysql> SHOW STATUS LIKE 'wsrep%';

重点关注的是下面这几行：

wsrep_ready     |     ON

如果状态是 ON，那么就要看：

wsrep_cluster_size    |   3

如果是3，那么是成功的，否则是不成功的！

六 数据库复制测试

在主节点135上创建一个表：

CREATE database test;

立刻到节点132和134上使用 show databases; 查看是否也有了test这个数据库，如果有则成功！

 

七 集群的状态监控相关参数

（1）wsrep_cluster_state_uuid显示了cluster的state UUID,由此可看出该节点是否还是集群的一员；

SHOW GLOBAL STATUS LIKE 'wsrep_cluster_state_uuid'

（2）wsrep_cluster_conf_id显示了整个集群的变化次数。所有节点都应相同，否则说明某个节点与集群断开了；

（3）wsrep_cluster_status显示集群里节点的主状态。标准返回primary。如返回non-Primary或其他值说明是多个节点改变导致的节点丢失或者脑裂。如果所有节点都返回不是Primary，则要重设quorum。具体参见http://galeracluster.com/documentation-webpages/quorumreset.html如果返回都正常，说明复制机制在每个节点都能正常工作；

show global status like 'wsrep_cluster_status';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| wsrep_cluster_status | Primary |
+----------------------+---------+

 

八 重启集群

偶尔，您可能不得不重新启动整个Galera集群。例如，在停电的情况下，每个节点都被关闭，并且根本没有mysqld进程，这可能会发生。 

 

要重新启动整个Galera集群，请完成以下步骤：

 

 

 

 

 

九  配置过程中可能遇到的错误：

（1）Failed to restart mysql.service: Unit mysql.service is masked.

systemctl unmask mysql.service

service mysql start

（2）Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

解决途径：http://blog.csdn.net/luoxia0805/article/details/30265479

（3）