mysql之集群

客官°小女子只卖身不卖艺 2022-05-24 09:44 255阅读 0赞

# 一，Galera（集群） #

集群分类：(面试题)  
高可用  
负载均衡  
HPC 高性能计算  
分布式集群  
分布式文件系统(分布式存储)  
  
mysql官方集群：**mysql-cluster**  
  
5台机器  
manager节点  
  
sql节点 sql节点 接受客户端请求  
  
data节点 data节点 存储数据  
  
  
mysql的第三方集群：galera  
  
**Galera Replication**  
[http://galeracluster.com/downloads/][http_galeracluster.com_downloads]  
![20180524212315778][]  
  
  
**准备环境：**  
主机解析：  
\[root@admin ~\]\# cat /etc/hosts  
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4  
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6  
192.168.245.133 galera1  
192.168.245.136 galera2  
192.168.245.10 galera3  
192.168.245.135 galera4  
  
关闭防火墙和selinux:  
setenforce 0 && systemctl stop firewalld  
  
时间同步：  
修改时区：  
\# ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime  
  
准备时间服务器：  
\#vim /etc/ntp.conf  
server 127.127.1.0 \# local clock  
fudge 127.127.1.0 stratum 10  
  
\#systemctl restart ntpd  
客户端同步时间：  
\[root@galera2 ~\]\# ntpdate galera1  
  
**下载和安装Galera（每台都需要安装）**  
注意：Galera有独立的补丁包，也有在mysql基础上打好补丁的完整包  
我们要下载带wsrep扩展补丁的版本，比如：  
MySQL 5.7.20 extended with wsrep patch version 25.13  
  
**所以：删除原版本mysql**:  
\[root@wing yum.repos.d\]\# yum erase \`rpm -qa | grep mysql\` -y  
![2018052421235238][]  
  
  
根据官方下载提示找到下载路径，发现下载路径下是已经做好yum源的路径，所以可以直接修改yum配置文件使用yum安装  
  
**配置yum源：**  
\[root@wing yum.repos.d\]\# cat galera.repo  
\[galera\]  
name=galera  
baseurl=http://releases.galeracluster.com/mysql-wsrep-5.7/centos/7/x86\_64/  
enabled=1  
gpgcheck=0  
  
\[root@wing yum.repos.d\]\# yum list | grep 'galera'  
galera.x86\_64 25.3.12-2.el7 **epel**   
mysql-wsrep-5.7.x86\_64 5.7.20-25.13.el7 galera  
mysql-wsrep-client-5.7.x86\_64 5.7.20-25.13.el7 galera  
mysql-wsrep-common-5.7.x86\_64 5.7.20-25.13.el7 galera  
mysql-wsrep-devel-5.7.x86\_64 5.7.20-25.13.el7 galera  
mysql-wsrep-libs-5.7.x86\_64 5.7.20-25.13.el7 galera  
mysql-wsrep-libs-compat-5.7.x86\_64 5.7.20-25.13.el7 galera  
mysql-wsrep-server-5.7.x86\_64 5.7.20-25.13.el7 galera  
mysql-wsrep-test-5.7.x86\_64 5.7.20-25.13.el7 galera  
注意：需要epel源提供galera包  
  
安装：  
\[root@wing yum.repos.d\]\# yum install mysql-wsrep-5.7.x86\_64 galera **rsync** \-y  
  
**特别注意：**  
1.timezone,ceph和rsync  
2./var/lib/mysql下3个文件：grastate.dat gvwstate.dat galera.cache 如果出了错，把他们统统干掉  
3.如果一起添到集群中不行就一个节点一个节点的添加  
  
**启动数据库(所有机器)：**  
\# systemctl start mysqld  
  
修改密码：  
\#mysqladmin -u root -p'2rttwxb?3\_oP' password 'Qianfeng123!'  
\#mysql -u root -p'Qianfeng123!'  
  
**每台机器创建用于数据同步的用户:**  
mysql> grant all on \*.\* to 'syncuser'@'%' identified by '123';  
mysql> flush privileges;  
mysql> grant all on \*.\* to 'syncuser'@'%' identified by 'Qianfeng123!@';  
mysql> flush privileges;  
  
**配置Galera Replication**:  
注意：所有服务需要按顺序重启  
  
galera1配置：主配置文件my.cnf中\[mysqld\]标签内追加如下内容，黑体部分是和其他节点不同的地方  
**server-id=1**  
binlog\_format=row  
innodb\_file\_per\_table=1  
innodb\_autoinc\_lock\_mode=2  
  
wsrep\_on=ON  
wsrep\_provider=/usr/lib64/galera/libgalera\_smm.so  
wsrep\_cluster\_name='galera'  
**wsrep\_cluster\_address='gcomm://'  
wsrep\_node\_name='galera1'  
wsrep\_node\_address='192.168.245.133'**  
wsrep\_sst\_auth=syncuser:'Qianfeng123!@'  
wsrep\_sst\_method=rsync  
  
重启服务：  
\[root@galera1 ~\]\# systemctl restart mysqld  
  
**galera2配置：主配置文件my.cnf追加如下内容  
server-id=2**  
binlog\_format=row  
innodb\_file\_per\_table=1  
innodb\_autoinc\_lock\_mode=2  
  
wsrep\_on=ON  
wsrep\_provider=/usr/lib64/galera/libgalera\_smm.so  
wsrep\_cluster\_name='galera'  
**wsrep\_cluster\_address='gcomm://galera1'  
wsrep\_node\_name='galera2'  
wsrep\_node\_address='192.168.245.136'**  
wsrep\_sst\_auth=syncuser:'Qianfeng123!@'  
wsrep\_sst\_method=rsync  
  
重启服务：  
\[root@galera2 ~\]\# systemctl restart mysqld  
  
**galera3配置：主配置文件my.cnf追加如下内容  
server-id=3**  
binlog\_format=row  
innodb\_file\_per\_table=1  
innodb\_autoinc\_lock\_mode=2  
  
wsrep\_on=ON  
wsrep\_provider=/usr/lib64/galera/libgalera\_smm.so  
wsrep\_cluster\_name='galera'  
**wsrep\_cluster\_address='gcomm://galera1,galera2'  
wsrep\_node\_name='galera3'  
wsrep\_node\_address='192.168.245.10'**  
wsrep\_sst\_auth=syncuser:'Qianfeng123!@'  
wsrep\_sst\_method=rsync  
  
重启服务：  
\[root@galera3 ~\]\# systemctl restart mysqld  
  
**galera4配置：主配置文件my.cnf追加如下内容  
server-id=4**  
binlog\_format=row  
innodb\_file\_per\_table=1  
innodb\_autoinc\_lock\_mode=2  
  
wsrep\_on=ON  
wsrep\_provider=/usr/lib64/galera/libgalera\_smm.so  
wsrep\_cluster\_name='galera'  
**wsrep\_cluster\_address='gcomm://galera1,galera2,galera3'  
wsrep\_node\_name='galera4'  
wsrep\_node\_address='192.168.245.135'**  
wsrep\_sst\_auth=syncuser:'Qianfeng123!@'  
wsrep\_sst\_method=rsync  
  
重启服务：  
\[root@galera1 ~\]\# systemctl restart mysqld  
  
查看端口：galera端口4567 mysql端口3306  
\[root@galera1 ~\]\# ss -auntpl | grep -E '3306|4567'  
tcp LISTEN 0 128 \*:4567 \*:\* users:(("mysqld",pid=11068,fd=12))  
tcp LISTEN 0 80 :::3306 :::\* users:(("mysqld",pid=11068,fd=39))  
  
测试：  
mysql> show status like 'wsrep%';  
wsrep\_incoming\_addresses | 192.168.245.136:3306,192.168.245.135:3306,192.168.245.10:3306 |  
| wsrep\_cluster\_size | 3 //表示一共有3个节点，我少配置了一个  
  
**阶段测试**:  
在任何一台机器上写数据，在其他机器上全部会同步

# 二，给galera添加mycat代理 #

**Mycat  
添加数据库和账户：**  
mysql> create database shop;  
mysql> create database bbs;  
mysql> create database blog;  
mysql> grant all on shop.\* to shop@'%' identified by 'Qianfeng123!';  
mysql> grant all on shop.\* to bbs@'%' identified by 'Qianfeng123!';  
mysql> grant all on shop.\* to blog@'%' identified by 'Qianfeng123!';  
  
mysql> flush privileges;  
  
**设置3个账户和3个schema:**  
\[root@master conf\]\# cat server.xml  
<user name="shop">  
<property name="password">123456</property>  
<property name="schemas">shop</property>  
</user>  
<user name="bbs">  
<property name="password">123456</property>  
<property name="schemas">bbs</property>  
</user>  
<user name="blog">  
<property name="password">123456</property>  
<property name="schemas">blog</property>  
</user>  
  
\[root@master conf\]\# cat schemal.xml  
<?xml version="1.0"?>  
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
<mycat:schema xmlns:mycat="http://io.mycat/">  
  
<schema name="shop" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema>  
<schema name="bbs" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2"> </schema>  
<schema name="blog" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn3"> </schema>  
<dataNode name="dn1" dataHost="localhost1" database="shop" />  
<dataNode name="dn2" dataHost="localhost2" database="bbs" />  
<dataNode name="dn3" dataHost="localhost3" database="blog" />  
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"  
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">  
<heartbeat>show status like 'wsrep%'</heartbeat>  
<writeHost host="galera2" url="galera2:3306" user="shop" password="Qianfeng123!"> </writeHost>  
<writeHost host="galera3" url="galera3:3306" user="shop" password="Qianfeng123!"> </writeHost>  
<writeHost host="galera4" url="galera4:3306" user="shop" password="Qianfeng123!"> </writeHost>  
</dataHost>  
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="1"  
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">  
<heartbeat>show status like 'wsrep%'</heartbeat>  
<writeHost host="galera2" url="galera2:3306" user="bbs" password="Qianfeng123!"> </writeHost>  
<writeHost host="galera3" url="galera3:3306" user="bbs" password="Qianfeng123!"> </writeHost>  
<writeHost host="galera4" url="galera4:3306" user="bbs" password="Qianfeng123!"> </writeHost>  
</dataHost>  
<dataHost name="localhost3" maxCon="1000" minCon="10" balance="1"  
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">  
<heartbeat>show status like 'wsrep%'</heartbeat>  
<writeHost host="galera2" url="galera2:3306" user="blog" password="Qianfeng123!"> </writeHost>  
<writeHost host="galera3" url="galera3:3306" user="blog" password="Qianfeng123!"> </writeHost>  
<writeHost host="galera4" url="galera4:3306" user="blog" password="Qianfeng123!"> </writeHost>  
</dataHost>  
  
</mycat:schema>  
![20180524212538895][]  
  
balance=1 开启读写分离机制，所有读操作都发送到当前备用的 writeHost 上。  
wirteType=0 所有写操作发送到第一个writeHost，第一个挂了切换到第二个  
switchType=3 基于MySQL Galera cluster的切换机制，心跳语句为show status like 'wsrep%'  
  
随便找台机器做客户端：  
\[root@galera1 ~\]\# mysql -u shop -p'123456' -h 192.168.245.3 -P8066  
  
**结论：**  
1. 所有节点都正常writeHost负责写操作，备writeHost负责读操作  
2. 当第一个writeHost失效时，其中一个备的writeHost负责写操作，其他备的writeHost负责读操作  
3. 当只有一个writeHost时，同时负担读写

[http_galeracluster.com_downloads]: http://galeracluster.com/downloads/
[20180524212315778]: /images/20220524/3919c74592c24968a5b06d993ee12014.png
[2018052421235238]: /images/20220524/f30ce84c77b84e17912f7d1527f3e277.png
[20180524212538895]: /images/20220524/485462680bf74bad85a716b6c5c2b085.png