mariadb+haproxy集群部署

       一、部署准备

1. 角色说明

172.16.2.220  Mysql代理

172.16.2.221  Mysql_A

172.16.2.222  Mysql_B

172.16.2.223  Mysql_C

2. 为了保证节点间相互通信，需要关闭防火墙设置和selinux。

2.1. 在三个节点分别执行命令：

systemctl stop firewalld

2.2. 修改selinux。

vim /etc/sysconfig/selinux

3. 修改/etc/hosts配置文件，添加服务和IP的对应关系。

二、开始安装MariaDB集群

1. 配置MariaDB安装源（三台节点都执行）

1.1. 在/etc/yum.repos.d/下建立 MariaDB.repo,内容如下: 

# MariaDB 10.1 CentOS repository list - created 2016-09-18 01:38 UTC

#  

[mariadb]

name = MariaDB

baseurl= 
      gpgkey= 

gpgcheck=0

1.2. 然后执行yum安装。

yum install MariaDB-server MariaDB-client

2. 完成安装后配置mariadb集群。

修改配置文件/etc/my.cnf.d/server.cnf，下面三图是服务器相应的配置。
"wsrep_cluster_address=gcomm://"参数配置说明：

第一个节点的配置是从MySQL_B和MySQL_C同步数据。

第二个节点的配置是从MySQL_A和MySQL_C同步数据。

第三个节点的配置是从MySQL_A和MySQL_B同步数据。

wsrep_node_name 本节点计算机名
      wsrep_node_address 本节点IP

下图是MySQL_A的/etc/my.cnf.d/server.cnf配置。

 

下图是MySQL_B的/etc/my.cnf.d/server.cnf配置。

 

下图是MySQL_C的/etc/my.cnf.d/server.cnf配置。

 

所有节点配置完成后。依次启动MySQL_A、MySQL_B、MySQL_C集群节点。

新建Galera集群，可以在任意节点执行。本次选择MySQL_A。

MySQL_A: mysqld  -uroot --wsrep-new-cluster

MySQL_B: systemctl restart mariadb

MySQL_C: systemctl restart mariadb

3. 执行mysql安全设置

 mysql_secure_installation

 详情请参考文档底部说明：附件二

4. 登入任意一个节点查看集群配置。

    登录系统，执行show status like 'wsrep%';

 

 

只要看见这些参数，说明集群创建成功，别的节点可以加入。

在其它服务器上，也可查看状态。

5. 验证同步

5.1. 任选一台mysql节点，登入执行

    create database abc;

5.2. 在集群的其他机器上测试

    show databases like 'abc';

    如果返回的结果存在abc这个库，则代表同步成功。

6. 集群关闭需要启动，遇到如下问题。

如果集群正常或者非常全部关闭时，可参考。详情请见附件一

三、安装haproxy负载均衡

1. 编译安装haproxy

    tar zxvf haproxy-1.7.2.tar.gz

    cd haproxy-1.7.2

    make TARGET=linux26 PREFIX=/usr/local/haproxy

    make install PREFIX=/usr/local/haproxy

2. 创建haproxy配置文件目录和配置文件。

2.1. 创建haproxy配置文件存放目录

mkdir /usr/local/haproxy/conf

2.2. 创建haproxy.cfg文件

vim haproxy.cfg 添加如下内容：

global

    #设置日志

    log 127.0.0.1 local2

    chroot /usr/local/haproxy

    pidfile /var/run/haproxy

    #用户与用户组

    user haproxy

    group haproxy

    #守护进程启动

    daemon

    #最大连接数

    maxconn 51200

    nbproc 1

    stats timeout 2m

    stats socket /var/run/haproxy.sock mode 600 level admin

 

#默认配置

defaults

    log global

    mode tcp

    option dontlognull

    option tcp-smart-accept

    option tcp-smart-connect

    retries 3

    option redispatch

    maxconn 8192

    timeout check 3500ms

    timeout queue 3500ms

    timeout connect 3500ms

    timeout client 10800ms

    timeout server 10800ms

 

#前端配置，http_front名称可自定义

#frontend STATSUSERS

userlist STATSUSERS

    group admin users admin

    user admin insecure-password admin

    user stats insecure-password yourpassword

listen admin_page

    bind 0.0.0.0:8888

    mode http

    stats enable

    stats refresh 60s

    stats uri /stats

    #haproxy的状态管理页面，通过/haproxy?stats来访问

    acl AuthOkay_ReadOnly http_auth(STATSUSERS)

    acl AuthOkay_Admin http_auth_group(STATSUSERS) admin

    stats http-request auth realm admin_page unless AuthOkay_ReadOnly

listen mysql_8080

    bind *:8080

    mode tcp

    timeout client 10800s

    timeout server 10800s

    balance leastconn

    option httpchk

    option allbackups

    default-server port 9200 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100

    server mysql-master-1 172.16.2.221:3306

    server mysql-master-2 172.16.2.222:3306
    server mysql-master-3 172.16.2.223:3306

3. 配置完成后，启动haproxy

/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/conf/haproxy.cfg  

4. 在其它服务上登入haproxy服务器上面的mysql。进行测试

mysql --host 172.16.2.220 --port 8080 -uroot -p  

附件一：

问题一

搭建galera cluster的时候设置sst为xtrabackup，启动node1报错。
[ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .
解决方式：
grastate.dat  file of the node you intend to use as the first node.
需要把该文件删除 重新启动即可，或者把grastate.dat的safe_to_bootstrap改为1，如下图：

vim /var/lib/mysql/grastate.dat

 

问题二：

A server somehow ended up with an empty gvwstate.dat file. When the MySQL service was restarted, Galera seemed to believe that it successfully restored the viewstate from the file. The viewstate is obviously invalid, so Galera fails with a non-intuitive error "invalid UUID: 00000000."

For example, let's truncate gvwstate.dat and then attempt to start MySQL:

解决办法：

mv /var/lib/mysql/gvwstate.dat /var/lib/mysql/gvwstate.dat.bak

然后再次启动。

其它常出现问题注意：

selinux和防火墙一定要关闭或者禁用。也可以设置防火墙策略，开放相应服务端口。

附件二：

[root@msyql-master-1~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL

SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current

password for the root user. If you've just installed MySQL, and

you haven't set the root password yet, the password will be blank,

so you should just press enter here.

Enter current password for root (enter for none):<–初次运行直接回车

OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MySQL

root user without the proper authorisation.

Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车

New password: <– 设置root用户的密码

Re-enter new password: <– 再输入一次你设置的密码

Password updated successfully!

Reloading privilege tables..

… Success!

By default, a MySQL installation has an anonymous user, allowing anyone

to log into MySQL without having to have a user account created for

them. This is intended only for testing, and to make the installation

go a bit smoother. You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n] <– 是否删除匿名用户,生产环境建议删除，所以直接回车

… Success!

Normally, root should only be allowed to connect from 'localhost'. This

ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] <–是否禁止root远程登录,根据需求选择Y/n并回车,建议禁止

… Success!

By default, MySQL comes with a database named 'test' that anyone can

access. This is also intended only for testing, and should be removed

before moving into a production environment.

Remove test database and access to it? [Y/n] <– 是否删除test数据库,直接回车

- Dropping test database…

… Success!

- Removing privileges on test database…

… Success!

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n] <– 是否重新加载权限表，直接回车

… Success!

Cleaning up…

All done! If you've completed all of the above steps, your MySQL

installation should now be secure.

Thanks for using MySQL!

[root@msyql-master-1~]#