Ubuntu16.04搭建三台主机的mysql galera cluster集群（实践版）

本文主要是介绍Ubuntu16.04搭建三台主机的mysql galera cluster集群（实践版），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

直接复制粘贴可能会提示错误，复制代码检查代码间的空格。

一搭建环境：

1 Ubuntu16.04版本

主 10.1.6.241 用户user admin@123 root admin@134
从 10.1.6.48 用户user admin@124 root admin@135
从 10.1.6.129 用户 user admin@125 root admin@136

2 网络

无论是物理机还是虚拟机都需在一个子网范围内，且ping通彼此。侧重说下虚拟机，虚拟机选择网络方式为桥接方式，最为便捷。直接加入实体内网。网络联网以及访问最为直接。其他网络方式可自行探讨。

二安装环境：

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

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

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

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

如果搭建的平台仅用于测试，可直接关闭防火墙

Sudo ufw disable

16.04默认情况下，是关闭防火墙的。因此此项防火墙设置不需要操作。

对于正式平台，则需要配置防火墙，以提高平台安全接入

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

iptables --append INPUT --protocol tcp \

--source 10.1.6.241 --jump ACCEPT

iptables --apend INPUT --protocol tcp \

--source 10.1.6.48 --jump ACCEPT

iptables --append INPUT --protocol tcp \

--source 10.1.6.129 --jump ACCEPT

然后保存防火墙规则：

iptables-save

2.禁用SElinux

默认16014 selinux是禁用的

因此也不需要执行 semanage permissive -a mysqld_t

3.禁用AppArmor

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

执行

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

重启apparmor：

sudo service apparmor restart

三开始安装
（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 [arch=amd64] http://releases.galeracluster.com/mysql-wsrep-5.7/ubuntu xenial main

deb [arch=amd64] http://releases.galeracluster.com/galera-3/ubuntu xenial main

（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

程序大概占用500M

下载执行完后，弹出输入root密码2次确认红框

输入密码执行

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

service mysql start

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

vi /etc/mysql/my.cnf

主节点241上

绿色为系统内容红色为需要添加修改内容

# 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=500M"

//此处size可根据实体机或虚拟机的实际情况配置。此处我配置500M

wsrep_cluster_name="galeracluster1"

wsrep_cluster_address="gcomm://"

wsrep_sst_method=rsync

wsrep_sst_auth=wsrep:199077

wsrep_node_address="10.1.6.241" //此IP为主IP

wsrep_node_name=node135

bind-address=0.0.0.0

从节点48上：

# 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=500M"

//同样此处500M

wsrep_cluster_name="galeracluster1"

wsrep_cluster_address="gcomm://10.1.6.241"

//此处填写主的IP地址

wsrep_sst_method=rsync

wsrep_sst_auth=wsrep:199077

wsrep_node_address="10.1.6.48"

//此处是从的本机地址

wsrep_node_name=node132

bind-address=0.0.0.0

从节点129上：

# 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=500M"

//同样此处500M

wsrep_cluster_name="galeracluster1"

wsrep_cluster_address="gcomm://10.1.6.129"

//此处填写主的IP地址

wsrep_sst_method=rsync

wsrep_sst_auth=wsrep:199077

wsrep_node_address="10.1.6.241"

//此处填本机IP地址

wsrep_node_name=node134

bind-address=0.0.0.0

（9）

在每个节点上重启mysql：

service mysql restart

注意：此处最好是按照主次依次启动

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

如在241上登录48，129

mysql -h 10.1.6.48 -u wsrep -p 不成功 deny

mysql -h 10.1.6.129 -u wsrep -p 不成功 deny

如果彼此登录不成功，则需要在每个节点上创建用户以实现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,

service mysql restart

如在241上登录48，129

mysql -h 10.1.6.48 -u wsrep -p 成功

mysql -h 10.1.6.129 -u wsrep -p 成功

启动集群

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

service mysql stop

service mysql start --wsrep-new-cluster

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

此时，你的主机可能会报错

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

Galera 集群中非常重要的一个文件

即grastate.dat，

分别登陆三个节点查看grastate.dat文件

cat /var/lib/mysql/grastate.date

主