Haproxy+mysql cluster( MySQL 集群) 配置

落日映苍穹つ 2022-08-05 18:28 131阅读 0赞

一、准备

1、准备服务器

建立有2个节点的MySQL CLuster体系，使用6台服务器建立Haproxy+mysql cluster( MySQL 集群) 体系

<table style="padding:0px; margin:0px auto 10px; font-size:12px; border-collapse:collapse; color:rgb(85,85,85); font-family:宋体,'Arial Narrow',arial,serif; height:213px">  节点配置说明  
 <tbody style="padding:0px; margin:0px"> 
  <tr style="padding:0px; margin:0px"> 
   <td style="padding:3px; margin:0px; border:1px solid rgb(192,192,192); border-collapse:collapse; background-color:white"> 节点</td> 
   <td style="padding:3px; margin:0px; border:1px solid rgb(192,192,192); border-collapse:collapse; background-color:white"> 对应的IP和端口</td> 
  </tr> 
  <tr style="padding:0px; margin:0px"> 
   <td colspan="1" style="padding:5px 10px; margin:0px; border-width:1px; border-style:solid; border-color:rgb(192,192,192) rgb(221,221,221) rgb(221,221,221) rgb(192,192,192); background-color:white"> Haproxy<span style="padding:0px; margin:0px">负载均衡(1个) centos 6.3</span></td> 
   <td colspan="1" style="padding:5px 10px; margin:0px; border-width:1px; border-style:solid; border-color:rgb(192,192,192) rgb(221,221,221) rgb(221,221,221) rgb(192,192,192); background-color:white"> 1.1.1.11</td> 
  </tr> 
  <tr style="padding:0px; margin:0px"> 
   <td style="padding:3px; margin:0px; border:1px solid rgb(192,192,192); border-collapse:collapse; background-color:white"> &nbsp;管理节点(1个)&nbsp;<span style="padding:0px; margin:0px">centos 6.3</span></td> 
   <td style="padding:3px; margin:0px; border:1px solid rgb(192,192,192); border-collapse:collapse; background-color:white"> &nbsp;1.1.1.30</td> 
  </tr> 
  <tr style="padding:0px; margin:0px"> 
   <td style="padding:3px; margin:0px; border:1px solid rgb(192,192,192); border-collapse:collapse; background-color:white"> &nbsp;SQL节点 (2个)&nbsp;<span style="padding:0px; margin:0px">centos 6.3</span></td> 
   <td style="padding:3px; margin:0px; border:1px solid rgb(192,192,192); border-collapse:collapse; background-color:white"> &nbsp;1.1.1.21</td> 
  </tr> 
  <tr style="padding:0px; margin:0px"> 
   <td style="padding:3px; margin:0px; border:1px solid rgb(192,192,192); border-collapse:collapse; background-color:white"> &nbsp;1.1.1.22</td> 
  </tr> 
  <tr style="padding:0px; margin:0px"> 
   <td style="padding:3px; margin:0px; border:1px solid rgb(192,192,192); border-collapse:collapse; background-color:white"> &nbsp;数据节点 (2个)&nbsp;<span style="padding:0px; margin:0px">centos 6.3</span></td> 
   <td style="padding:3px; margin:0px; border:1px solid rgb(192,192,192); border-collapse:collapse; background-color:white"> &nbsp;1.1.1.31</td> 
  </tr> 
  <tr style="padding:0px; margin:0px"> 
   <td style="padding:3px; margin:0px; border:1px solid rgb(192,192,192); border-collapse:collapse; background-color:white"> &nbsp;1.1.1.32</td> 
  </tr> 
 </tbody> 
</table>

二、Haproxy负载均衡安装配置

\[root@HAPROXY001 ~\]\# yum install  haproxy\*

在/etc/haproxy/目录下创建haproxy.cfg配置文件

\--------------------------------------------------------------------------------

\[root@HAPROXY001 ~\]\# vim /etc/haproxy/haproxy.cfg

\# this config needs haproxy-1.1.28 or haproxy-1.2.1

global

log 127.0.0.1   local0 info    \#日志相关

log 127.0.0.1   local1 notice

maxconn 4096

chroot /usr/local/haproxy

uid root

gid root

daemon

\#debug

\#quiet

pidfile /usr/local/haproxy/haproxy.pid

defaults

log     global

mode    http

\#option httplog

option  dontlognull

retries 3

option redispatch

maxconn         8000

contimeout      3000

clitimeout      30000

srvtimeout      30000

listen  mysql

bind 0.0.0.0:3306     \#代理端口

mode tcp              \#模式 TCP

option mysql-check \#user root   \#mysql健康检查  root为mysql登录用户名

balance roundrobin            \#调度算法

server mysql1 1.1.1.21:3306 check port 3306\#weight 1 check  inter 1s rise 2 fall 2 \#健康检查加上check

server mysql2 1.1.1.22:3306 check port 3306\#weight 1 check  inter 1s rise 2 fall 2

listen stats     \#监控

mode http

bind 0.0.0.0:8888

stats enable

stats uri /dbs

stats realm Global\\ statistics

stats auth admin:hello123

\--------------------------------------------------------------------------------

启动服务：/usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg

登录浏览器查看：  输入admin hello123登录

[![wKioL1R8RuOwLCEWAAYmE9A0L\_4625.jpg][wKioL1R8RuOwLCEWAAYmE9A0L_4625.jpg]][wKioL1R8RuOwLCEWAAYmE9A0L_4625.jpg]

三、mysql cluster安装

1，mysql cluster 各节点安装，以管理节点为例

\[root@MYSQLMANAGER ~\]\# wget [http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86\_64.tar.gz][http_cdn.mysql.com_Downloads_MySQL-Cluster-7.3_mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz]

\[root@MYSQLMANAGER ~\]\# groupadd mysql

\[root@MYSQLMANAGER ~\]\# useradd mysql -g mysql

\[root@MYSQLMANAGER ~\]\# tar -xvf mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86\_64.tar.gz

\[root@MYSQLMANAGER ~\]\# mv mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86\_64 /usr/local/mysql

\[root@MYSQLMANAGER ~\]\# cd /usr/local/

\[root@MYSQLMANAGER ~\]\# chown -R mysql:mysql mysql/

\[root@MYSQLMANAGER ~\]\# cd mysql/

\[root@MYSQLMANAGER ~\]\# scripts/mysql\_install\_db --user=mysql

2，节点配置

管理节点：

\[root@MYSQLMANAGER ~\]\# vi  /var/lib/mysql-cluster/config.ini

\--------------------------------------------------------------------------------

\[NDBD DEFAULT\]

NoOfReplicas=2 \#副本数量，建议使用默认的2

MaxNoOfConcurrentOperations=10000

DataMemory=1024M \#每个数据节点中给数据分配的内存

IndexMemory=500M \#每个数据节点中给索引分配的内存

BackupMemory: 50M

\[NDB\_MGMD\]

\#管理节点配置项

Nodeid= 1

HostName=1.1.1.30 \#管理节点IP

DataDir=/usr/local/mysql/data \#管理节点日志、配置目录

ArbitrationRank: 1 \#该节点的优先级别等级

\[NDBD\]

\#数据节点配置项

Nodeid = 2 \#第一个数据节点

HostName=1.1.1.31 \#数据节点IP

DataDir=/usr/local/mysql/data \#数据节点存放数据的目录

\[NDBD\]

Nodeid = 3 \#第二个数据节点

HostName=1.1.1.32

DataDir=/usr/local/mysql/data

\[mysqld\]

\#SQL节点配置项

Nodeid = 4 \#第一个SQL节点

HostName=1.1.1.21

\[mysqld\]

Nodeid = 5 \#第二个SQL节点

HostName=1.1.1.22

\[mysqld\]

Nodeid = 6 \#第三个SQL节点

HostName=1.1.1.254

\[mysqld\]

\#建议保留一个SQL节点配置口

\--------------------------------------------------------------------------------

数据节点： 两数据节点配置一样

\[root@MYSQLDB001 ~\]\# vim /etc/my.cnf

\--------------------------------------------------------------------------------

\[mysqld\]

datadir=/usr/local/mysql/data

socket=/usr/local//mysql/mysql.sock

user=mysql

\# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

\#运行NDB存储引擎

ndbcluster

\#指定管理节点

ndb-connectstring=1.1.1.30:1186

\[MYSQL\_CLUSTER\]

ndb-connectstring=1.1.1.30:1186

\[NDB\_MGM\]

connect-string=1.1.1.30

\[mysqld\_safe\]

log-error=/usr/local/mysql/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

\--------------------------------------------------------------------------------

SQL节点配置：

\[root@MYSQL001 ~\]\# cd /usr/local/mysql/

\[root@MYSQL001 ~\]\# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

\[root@MYSQL001 ~\]\# chmod +x /etc/rc.d/init.d/mysqld

\[root@MYSQL001 ~\]\# chkconfig --add mysqld

\[root@MYSQL001 ~\]\# vi /etc/my.cnf

\--------------------------------------------------------------------------------

\[mysqld\]

server-id=4

\#每个服务器的id不一样

datadir=/usr/local/mysql/data

socket=/var/lib/mysql/mysql.sock

user=mysql

\# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-bin = /usr/local/mysql/log/mysql-bin.log

max\_connections=1000

skip-name-resolve

\#以下为mysql 主主模式的配置文件

\# 忽略mysql数据库复制

binlog-ignore-db=mysql

\# 每次增长2

auto-increment-increment = 2

\# 设置自动增长的字段的偏移量，即初始值为2

auto-increment-offset = 1

\[mysqld\_safe\]

log-error=/usr/local/mysql/log/mysqld.log

pid-file=/usr/local/mysql/mysqld.pid

\[MYSQLD\]

ndbcluster

ndb-connectstring=1.1.1.30

\[MYSQL\_CLUSTER\]

ndb-connectstring=1.1.1.30

\[NDB\_MGM\]

connect-string=1.1.1.30

3，启动服务

CLUSTER启动的顺序依次为：管理节点—数据节点—SQL节点 关闭顺序相反

管理节点启动：

\[root@MYSQLMANAGER ~\]\# /usr/local/mysql/bin/ndb\_mgmd -f /var/lib/mysql-cluster/config.ini

添加新节点后，重新加载管理节点配置信息：

\[root@MYSQLMANAGER ~\]\# /usr/local/mysql/bin/ndb\_mgmd -f /etc/config.ini --initial  
或  
\[root@MYSQLMANAGER ~\]\# /usr/local/mysql/bin/ndb\_mgmd \-f /etc/config.ini --reload

数据节点启动：

\[root@MYSQLDB001 ~\]\# /usr/local/mysql/bin/ndbd --initial

注意：仅限第一次mysql节点启动使用–-initial，后面再启动不需要此参数

SQL节点启动：

\[root@MYSQL001 ~\]\# service mysqld start

四、mysql cluster 测试

1，查看cluster 信息

\[root@MYSQLMANAGER ~\]\#ndb\_mgm -e show

[![wKiom1R9M53gIIoEAAFvaL4okmM280.jpg][]][wKiom1R9M53gIIoEAAFvaL4okmM280.jpg]

2，测试SQL数据同步

从SQL节点1登录，创建数据库和表，进行简单测试。

\[root@MYSQL001 ~\]\# mysql -uroot -p

mysql> create database test ;

mysql> use test;  
Database changed

mysql> create table test1(id int,name varchar(10)) engine=ndb ;

mysql> insert into test1 values(1,'test');

mysql> select \* from test1 ;

\+------+---------+  
| id   | name    |  
\+------+---------+  
|    1     | test |   
\+------+---------+

从SQL节点2登录，查看效果，库，表和数据已经同步。

从SQL节点2节点插入一条数据，同样登陆SQL节点1，也能看到数据已经同步

3，测试HA服务

停止SQL节点2服务查看

\[root@MYSQL002 ~\]\# /etc/init.d/mysqld stop

Shutting down MySQL....                                    \[  OK  \]

[![wKioL1R9OILyn6L2AAXs559Vej8749.jpg][]][wKioL1R9OILyn6L2AAXs559Vej8749.jpg][![wKiom1R9OEfyn9AzAAF8FYaGkuk795.jpg][]][wKiom1R9OEfyn9AzAAF8FYaGkuk795.jpg]

mysql 服务 1.1.1.11：3306 依然可以正常使用。

[![041405283617635.gif][]][041405283617635.gif 1]

[wKioL1R8RuOwLCEWAAYmE9A0L_4625.jpg]: /images/20220806/3c46c827ba0e4d96b8f8a49a990ca163.png
[http_cdn.mysql.com_Downloads_MySQL-Cluster-7.3_mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz]: http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar.gz
[wKiom1R9M53gIIoEAAFvaL4okmM280.jpg]: /images/20220806/183330a93bbd4141ae734b53da5a53da.png
[wKioL1R9OILyn6L2AAXs559Vej8749.jpg]: http://s3.51cto.com/wyfs02/M01/54/3F/wKioL1R9OILyn6L2AAXs559Vej8749.jpg
[wKiom1R9OEfyn9AzAAF8FYaGkuk795.jpg]: /images/20220806/d551b077417542adbfb58c1076905623.png
[041405283617635.gif]: /images/20220806/da9db4ac5c954ec1abb8f577a855b1f8.png
[041405283617635.gif 1]: http://www.itmmd.com/boke.html