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

时间:2022-09-19 20:00:06

(注意:

(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

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

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

3)