linux搭建mysql集群服务负载均衡搭建java连接mysql集群

雨点打透心脏的1/2处 2022-06-05 06:20 186阅读 0赞

mysql负载集群负载均衡主要是多台服务器共同管理数据同步，把数据节点和mysql服务器分离方式服务器宕机网络问题导致数据丢失，

单点故障不能提供正常,的服务，集群的优势是共享数据减轻单台服务器的压力，MySQL Cluster是Oracle官方提供的一种企业级解决方案。

其采用了无共享存储的数据存储模式，真正实现了数据在集群中的实时同步及数据库节点故障对应用的透明性。官方承诺MySQL Cluster

可以保证数据库系统99.999%的可用性，由于msql集群是采用的内存式引擎存储，服务节点的内存必须足够大，保证数据的正常存储可自己

编写脚本来监控内存的情况，做实时的内存同步本地磁盘释放占用的物理内存

关于定时清理内存脚本可参考 http://blog.csdn.net/liqi_q/article/details/78169732

关于mysqld的主从配置读写分离可参考 http://blog.csdn.net/liqi_q/article/details/78548481

## 安装要求 ##

安装环境：CentOS-6.3  
安装方式：源码编译安装   
软件名称：mysql-cluster-gpl-7.2.6-linux2.6-x86\_64.tar.gz  
下载地址：http://mysql.mirror.kangaroot.net/Downloads/  
软件安装位置：/usr/local/mysql  
数据存放位置：/var/mysql/data  
日志存放位置：/var/mysql/logs

## 集群设计 ##

首先设计集群的安装分配方式，我共需要5台服务器，服务器分配如下：   
管理节点：    10.10.10.173  
sql节点1：   10.10.10.176  
sql节点2：   110.10.10.176  
数据节点1：  10.10.10.174  
数据节点2：  10.10.10.175  
设备的连接方式如下图所示：   
![20171120113901118][]  
注意：目前这种设计是单节点管理的，正式环境的话会采用keepalive虚拟IP两台服务器配置同时管理节点服务，防止单点故障导致的机器宕机停止服务，首先把这个简单的MySQL集群先搭建起来。

## 检查安装的mysql ##

检查系统中已经安装过的mysql信息，操作如下：

如果安装过其他版本的mysql，请卸载，操作如下：

## 管理节点 ##

### 管理节点安装 ###

安装管理节点（192.168.15.231）

### 管理节点配置 ###

在config.ini 中添加以下内容:

<table style="margin:6px 0px; padding:8px 6px; border-collapse:collapse; border-spacing:0px; background-color:rgb(245,245,245); border:1px solid silver; overflow:auto; color:rgb(0,0,0); font-size:12px; line-height:1.5em; word-break:break-word; font-family:&quot;Courier New&quot;!important"> 
 <tbody style="margin:0px; padding:0px"> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> [NDBD DEFAULT]<br style="margin:0px; padding:0px"> NoOfReplicas=1<br style="margin:0px; padding:0px"> [TCP DEFAULT]<br style="margin:0px; padding:0px"> portnumber=3306 <p style="margin:10px auto; padding-top:0px; padding-bottom:0px">[NDB_MGMD]<br style="margin:0px; padding:0px"> #设置管理节点服务器&nbsp;<br style="margin:0px; padding:0px"> HostName=10.10.10.173<br style="margin:0px; padding:0px"> DataDir=/var/mysql/data</p> <p style="margin:10px auto; padding-top:0px; padding-bottom:0px">[NDBD]<br style="margin:0px; padding:0px"> #设置存储节点服务器(NDB节点)<br style="margin:0px; padding:0px"> HostName=10.10.10.174<br style="margin:0px; padding:0px"> DataDir=/var/mysql/data</p> <p style="margin:10px auto; padding-top:0px; padding-bottom:0px">[NDBD]<br style="margin:0px; padding:0px"> #第二个NDB节点&nbsp;<br style="margin:0px; padding:0px"> HostName=10.10.10.175<br style="margin:0px; padding:0px"> DataDir=/var/mysql/data</p> <p style="margin:10px auto; padding-top:0px; padding-bottom:0px">[MYSQLD]<br style="margin:0px; padding:0px"> #设置SQL节点服务器&nbsp;<br style="margin:0px; padding:0px"> HostName=10.10.10.176<br style="margin:0px; padding:0px"> [MYSQLD]<br style="margin:0px; padding:0px"> #第二个SQL节点&nbsp;<br style="margin:0px; padding:0px"> HostName=10.10.10.177</p> </td> 
  </tr> 
 </tbody> 
</table>

### 管理节点启动 ###

看到tcp 0 0 0.0.0.0:1186开放说明启动正常   
开启管理节点服务器的1186端口

### 管理节点检验 ###

执行以下操作：

<table style="margin:6px 0px; padding:8px 6px; border-collapse:collapse; border-spacing:0px; background-color:rgb(245,245,245); border:1px solid silver; overflow:auto; color:rgb(0,0,0); font-size:12px; line-height:1.5em; word-break:break-word; font-family:&quot;Courier New&quot;!important"> 
 <tbody style="margin:0px; padding:0px"> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> [root@localhost /]# ndb_mgm&nbsp;&nbsp;&nbsp;&nbsp; // 管理节点 <p style="margin:10px auto; padding-top:0px; padding-bottom:0px">-- NDB Cluster -- Management Client --<br style="margin:0px; padding:0px"> ndb_mgm&gt; show</p> <p style="margin:10px auto; padding-top:0px; padding-bottom:0px">Connected to Management Server at: localhost:1186<br style="margin:0px; padding:0px"> Cluster Configuration<br style="margin:0px; padding:0px"> ---------------------<br style="margin:0px; padding:0px"> [ndbd(NDB)]&nbsp;&nbsp;&nbsp;&nbsp; 2 node(s)<br style="margin:0px; padding:0px"> id=2 (not connected, accepting connect from 10.10.10.174)<br style="margin:0px; padding:0px"> id=3 (not connected, accepting connect from 10.10.10.175)</p> <p style="margin:10px auto; padding-top:0px; padding-bottom:0px">[ndb_mgmd(MGM)] 1 node(s)<br style="margin:0px; padding:0px"> id=1&nbsp;&nbsp;&nbsp; @192.168.15.231&nbsp; (mysql-5.5.22 ndb-7.2.6)</p> <p style="margin:10px auto; padding-top:0px; padding-bottom:0px">[mysqld(API)]&nbsp;&nbsp; 2 node(s)<br style="margin:0px; padding:0px"> id=4 (not connected, accepting connect from 10.10.10.176)<br style="margin:0px; padding:0px"> id=5 (not connected, accepting connect from 10.10.10.177)</p> </td> 
  </tr> 
 </tbody> 
</table>

### 管理节点关闭 ###

## 数据节点 ##

### 数据节点安装 ###

数据节点1： 10.10.10.174  
数据节点2： 10.10.10.175

<table style="margin:6px 0px; padding:8px 6px; border-collapse:collapse; border-spacing:0px; background-color:rgb(245,245,245); border:1px solid silver; overflow:auto; color:rgb(0,0,0); font-size:12px; line-height:1.5em; word-break:break-word; font-family:&quot;Courier New&quot;!important"> 
 <tbody style="margin:0px; padding:0px"> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> [root@localhost /]# groupadd mysql<br style="margin:0px; padding:0px"> [root@localhost /]# useradd mysql -g mysql<br style="margin:0px; padding:0px"> [root@localhost /]# cd /usr/local<br style="margin:0px; padding:0px"> [root@localhost local]# tar -zxv -f mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz<br style="margin:0px; padding:0px"> [root@localhost local]# mv mysql-cluster-gpl-7.2.6-linux2.6-x86_64 mysql<br style="margin:0px; padding:0px"> [root@localhost local]# chown -R mysql:mysql mysql<br style="margin:0px; padding:0px"> [root@localhost local]# cd mysql<br style="margin:0px; padding:0px"> [root@localhost mysql]# scripts/mysql_install_db --user=mysql<br style="margin:0px; padding:0px"> [root@localhost mysql]# cp support-files/my-medium.cnf /etc/my.cnf<br style="margin:0px; padding:0px"> [root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld</td> 
  </tr> 
 </tbody> 
</table>

### 数据节点配置 ###

对数据节点进行配置，执行以下操作：

向文件追加以下内容：

### 数据节点启动 ###

启动此处时，管理节点服务器防火墙必须开启1186,3306端口。

vi /etc/sysconfig/iptables

\-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT  
\-A INPUT -m state --state NEW -m tcp -p tcp --dport 1186 -j ACCEPT

service iptables restart  
注意：只是在第一次启动或在备份／恢复或配置变化后重启ndbd时，才加–initial参数！   
第一次启动如下：

正常启动方式：

### 数据节点关闭 ###

\[root@localhost /\]\# /etc/rc.d/init.d/mysqld stop  
或者   
\[root@localhost mysql\]\# /etc/init.d/mysql stop  
Shutting down MySQL.. SUCCESS!

/usr/local/mysql/bin/mysqladmin -uroot shutdown

使用service mysql管理方式启动

chkconfig mysqld on 设置mysql开机启动  
chmod 755 /etc/rc.d/init.d/mysqld  修改mysqld执行权限  
service mysqld start  搞定

## SQL节点安装 ##

### SQL节点安装 ###

SQL节点和存储节点(NDB节点)安装相同，都执行以下操作；   
sql节点1： 10.10.10.176  
sql节点2：  10.10.10.177

<table style="margin:6px 0px; padding:8px 6px; border-collapse:collapse; border-spacing:0px; background-color:rgb(245,245,245); border:1px solid silver; overflow:auto; color:rgb(0,0,0); font-size:12px; line-height:1.5em; word-break:break-word; font-family:&quot;Courier New&quot;!important"> 
 <tbody style="margin:0px; padding:0px"> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> [root@localhost /]# groupadd mysql<br style="margin:0px; padding:0px"> [root@localhost /]# useradd mysql -g mysql<br style="margin:0px; padding:0px"> [root@localhost /]# cd /usr/local<br style="margin:0px; padding:0px"> [root@localhost local]# tar -zxv -f mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz<br style="margin:0px; padding:0px"> [root@localhost local]# mv mysql-cluster-gpl-7.2.6-linux2.6-x86_64 mysql<br style="margin:0px; padding:0px"> [root@localhost local]# chown -R mysql:mysql mysql<br style="margin:0px; padding:0px"> [root@localhost local]# cd mysql<br style="margin:0px; padding:0px"> [root@localhost mysql]# scripts/mysql_install_db --user=mysql<br style="margin:0px; padding:0px"> [root@localhost mysql]# cp support-files/my-medium.cnf /etc/my.cnf<br style="margin:0px; padding:0px"> [root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld</td> 
  </tr> 
 </tbody> 
</table>

### SQL节点配置 ###

执行以下操作：

追加以下内容：

<table style="margin:6px 0px; padding:8px 6px; border-collapse:collapse; border-spacing:0px; background-color:rgb(245,245,245); border:1px solid silver; overflow:auto; color:rgb(0,0,0); font-size:12px; line-height:1.5em; word-break:break-word; font-family:&quot;Courier New&quot;!important"> 
 <tbody style="margin:0px; padding:0px"> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> [MYSQLD]<br style="margin:0px; padding:0px"> ndbcluster<br style="margin:0px; padding:0px"> ndb-connectstring=<span style="color:rgb(56,56,56); font-family:Tahoma,Verdana,Arial; background-color:rgb(245,245,245)">1</span><span style="color:rgb(56,56,56); font-family:Tahoma,Verdana,Arial; background-color:rgb(245,245,245)">0.10.10.173</span><br style="margin:0px; padding:0px"> [MYSQL_CLUSTER]<br style="margin:0px; padding:0px"> ndb-connectstring=<span style="color:rgb(56,56,56); font-family:Tahoma,Verdana,Arial; background-color:rgb(245,245,245)">1</span><span style="color:rgb(56,56,56); font-family:Tahoma,Verdana,Arial; background-color:rgb(245,245,245)">0.10.10.173</span><br style="margin:0px; padding:0px"> [NDB_MGM]<br style="margin:0px; padding:0px"> connect-string=<span style="color:rgb(56,56,56); font-family:Tahoma,Verdana,Arial; background-color:rgb(245,245,245)">1</span><span style="color:rgb(56,56,56); font-family:Tahoma,Verdana,Arial; background-color:rgb(245,245,245)">0.10.10.173</span></td> 
  </tr> 
 </tbody> 
</table>

### SQL节点启动 ###

执行以下操作：

### SQL节点关闭 ###

最直接的方式：

\[root@localhost /\]\# /etc/rc.d/init.d/mysqld stop  
或者   
\[root@localhost mysql\]\# /etc/init.d/mysql stop  
Shutting down MySQL.. SUCCESS!

## 功能测试 ##

在管理节点（10.10.10.173）上查看服务状态

<table style="margin:6px 0px; padding:8px 6px; border-collapse:collapse; border-spacing:0px; background-color:rgb(245,245,245); border:1px solid silver; overflow:auto; color:rgb(0,0,0); font-size:12px; line-height:1.5em; word-break:break-word; font-family:&quot;Courier New&quot;!important"> 
 <tbody style="margin:0px; padding:0px"> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <p style="margin:10px auto; padding-top:0px; padding-bottom:0px">[root@localhost ~]# /usr/local/mysql/bin/ndb_mgm</p> <p style="margin:10px auto; padding-top:0px; padding-bottom:0px">-- NDB Cluster -- Management Client --<br style="margin:0px; padding:0px"> </p> <p style="margin:10px auto; padding-top:0px; padding-bottom:0px">ndb_mgm&gt; show<br> Cluster Configuration<br> ---------------------<br> [ndbd(NDB)]<span style="white-space:pre"> </span>2 node(s)<br> id=2<span style="white-space:pre"> </span>@10.10.10.174 &nbsp;(mysql-5.5.57 ndb-7.2.30, Nodegroup: 0, *)<br> id=3<span style="white-space:pre"> </span>@10.10.10.175 &nbsp;(mysql-5.5.57 ndb-7.2.30, Nodegroup: 1)<br> <br> <br> [ndb_mgmd(MGM)]<span style="white-space:pre"> </span>1 node(s)<br> id=1<span style="white-space:pre"> </span>@10.10.10.173 &nbsp;(mysql-5.5.57 ndb-7.2.30)<br> <br> <br> [mysqld(API)]<span style="white-space:pre"> </span>2 node(s)<br> id=4<span style="white-space:pre"> </span>@10.10.10.176 &nbsp;(mysql-5.5.57 ndb-7.2.30)<br> id=5<span style="white-space:pre"> </span>@10.10.10.177 &nbsp;(mysql-5.5.57 ndb-7.2.30)<br> </p> </td> 
  </tr> 
 </tbody> 
</table>

可以看到这里的数据节点、管理节点、sql节点都是正常的。   
注意：

1.  在建表的时候一定要用ENGINE=NDB或ENGINE=NDBCLUSTER指定使用NDB集群存储引擎，或用ALTER TABLE选项更改表的存储引擎。
2.  NDB表必须有一个主键，因此创建表的时候必须定义主键，否则NDB存储引擎将自动生成隐含的主键。
3.  Sql节点的用户权限表仍然采用MYISAM存储引擎保存的，所以在一个Sql节点创建的MySql用户只能访问这个节点，如果要用同样的用户访问别的Sql节点，需要在对应的Sql节点追加用户。虽然在MySql Cluster7.2版本开始提供了”用户权限共享”。

### 数据同步性测试 ###

在一个数据节点上进行相关数据库的创建，然后到另外一个数据节点上看看数据是否同步。

1.  第1步：

SQL节点1（10.10.10.176）上增加数据：

<table style="margin:6px 0px; padding:8px 6px; border-collapse:collapse; border-spacing:0px; background-color:rgb(245,245,245); border:1px solid silver; overflow:auto; color:rgb(0,0,0); font-size:12px; line-height:1.5em; word-break:break-word; font-family:&quot;Courier New&quot;!important"> 
 <tbody style="margin:0px; padding:0px"> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> [root@localhost mysql]#&nbsp; /etc/rc.d/init.d/mysqld status&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //检验mysql是否运行&nbsp;<br style="margin:0px; padding:0px"> [root@localhost mysql]# /etc/rc.d/init.d/mysqld start&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //启动mysql<br style="margin:0px; padding:0px"> [root@localhost mysql]# /usr/local/mysql/bin/mysql -u root -p<br style="margin:0px; padding:0px"> Enter password:<br style="margin:0px; padding:0px"> mysql&gt; show databases;<br style="margin:0px; padding:0px"> mysql&gt; create database testdb2;<br style="margin:0px; padding:0px"> mysql&gt; use testdb2;<br style="margin:0px; padding:0px"> mysql&gt; CREATE TABLE td_test2 (i INT) ENGINE=NDB;<br style="margin:0px; padding:0px"> //这里必须指定数据库表的引擎为NDBCLUSTER，与配置文件中的名称相同&nbsp;<br style="margin:0px; padding:0px"> mysql&gt; INSERT INTO td_test2() VALUES (1);<br style="margin:0px; padding:0px"> mysql&gt; INSERT INTO td_test2() VALUES (152);<br style="margin:0px; padding:0px"> mysql&gt; SELECT * FROM td_test2;</td> 
  </tr> 
 </tbody> 
</table>

1.  第2步：

进入到SQL节点2（10.10.10.177）上查看数据

查看表的引擎是不是NDB：

1.  第3步：

反向测试，SQL节点2（10.10.10.177）上增加数据：

SQL节点1（10.10.10.176）上查看数据：

## 关闭集群 ##

先关闭管理节点，然后关闭SQL节点和数据节点。

## 集群启动操作顺序 ##

要再次启动集群，按照以下顺序执行：   
管理节点 -> 数据节点 –> SQL节点 注意：此次启动数据节点时不要加”–initial”参数。

javaweb程序调用mysql数据库集群服务要做以下处理

外部程序想要远程连接到mysql集群，还需要做的一个操作就是设置sql节点的mysql可以被远程连接。  
具体操作为：  
mysql> grant all privileges on MySQL\_Cluster\_Test.\* to root@'%' identified by 'root';    
mysql> flush privileges;

Mysql数据库“ERROR 1042 (HY000): Can't get hostname for your address”问题  
在配置Mysql数据库远程连接的过程中，防火墙和授权都已经做了，可是还是不能链接上去，并且发现连接的时候报这个错误ERROR 1042 (HY000): Can't get hostname for your address，  
查了下资料可能是Mysql的DNS解析有问题，只要我们在mysql的配置文件my.cnf中配置如下代码就可以连接成功了：  
\[mysqld\]  
skip-name-resolve  
此时你再连接，就会发现能够登陆远程机器的数据库了。

jdbc连接  
原先配置：jdbc:mysql://10.10.10.176:3306/mysq\_bb  
集群后配置：jdbc:mysql:loadbalance://10.10.10.176:3306,10.10.10.177:3306/mysq\_bb?roundRobinLoadBalance=true

更改允许IP访问（进入 mysql：2.使用 mysql库：3.查看用户表 ：4.更新用户表 ：5.强制刷新权限 ：）

/usr/local/mysql/bin/mysql -u root -p

use mysql;

SELECT \`Host\`,\`User\` FROM user;

UPDATE user SET \`Host\` = '%' WHERE \`User\` = 'root' LIMIT 1;

flush privileges;

更改mysql数据库登录用户密码  可使用navcat客户端连接进去更改登录用户的密码  
\# mysql -uroot -p  
Enter password: 【原始安装默认密码空直接回车】  
mysql>use mysql;  
mysql> update user set password=passworD("root") where user='root';  
mysql> flush privileges;  
mysql> exit;

使用客户端连接测试之前一定要开放防火墙3306的连接端口

vi /etc/sysconfig/iptables

在22端口之后即可  
\-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

重启防火墙即可生效

service iptables restart

## 修改数据库编码 ##

修改mysql数据库的编码

mysql> alter database bb CHARACTER SET utf8;

查看mysql数据库的编码

mysql> show variables like 'character\_set\_database';

修改表的编码方式：ALTER TABLE \`books\` DEFAULT CHARACTER SET utf8;该命令用于将表test的编码方式改为utf8；

[20171120113901118]: /images/20220605/689967553c1a455f8905acbfd4e6f744.png