本文详细介绍了如何在CentOS7环境下搭建MySQL双主双从(MM-SS)集群，并结合Mycat实现负载均衡。首先，关闭防火墙和SELinux，然后通过YUM安装MySQL5.7，配置主从复制，启用二进制日志和GTID。接着，部署Mycat，配置连接MySQL集群的用户和权限，最后测试数据同步和查询。整个过程涵盖了数据库集群和中间件的配置与管理。
centos7:
这里用来演示安装搭建的是两主两从的基础集群,共需要五台机器,其中四台安装MySQL，一台安装mycat
关闭防火墙和selinux：
# systemctl stop firewalld 立刻停止防火墙
# systemctl disable firewalld 开机禁用防火墙
# setenforce 0 立刻停止selinux
# vim /etc/selinux/config 开机禁用selinux
yum方式安装mysql5.7
# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
# rpm -ivh mysql80-community-release-el7-3.noarch.rpm
# yum -y install yum-utils #安装yum工具包
# yum-config-manager --enable mysql57-community 将禁用的yum源库启用
# yum-config-manager --disable mysql80-community 将启用的yum源库禁用
# yum install -y mysql-community-server
启动Mysql服务器：
# systemctl start mysqld
# systemctl enable mysqld
查询Mysql服务器默认密码：grep 'password' /var/log/mysqld.log
修改Mysql服务器密码：mysqladmin -uroot -p'此处为初始密码' password 'ZhuShen@123'
登录Mysql系统：mysql -uroot -p'ZhuShen@123'
获取mycat:
MyCat2
http://www.mycat.org.cn/wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
M-M-S-S
统一本地域名:
# vim /etc/hosts
10.1.1.100 master1
10.1.1.101 master2
10.1.1.102 slave1
10.1.1.103 slave2
10.1.1.104 mycat
启动二进制日志，服务器ID，GTID：
master1：
# vim /etc/my.cnf
log_bin server-id=1
gtid_mode=ON
enforce_gtid_consistency=1
systemctl restart mysqld
master2：
# vim /etc/my.cnf
log_bin server-id=2
gtid_mode=ON
enforce_gtid_consistency=1
双主双从（MM-SS）：
双主（MM）：
设置master2为master1的主服务器
在master2 上进行授权：
mysql> grant replication slave, replication client on *.* to 'rep'@'192.168.2.%' identified by 'Zhushen@123';
mysql> flush privileges;
master1：mysql> change master to master_host='master2', master_user='rep', master_password='Zhushen@123', master_auto_position=1;
start slave;
show slave status\G;
测试：master1上插入数据，在master2上观察
master2上插入数据，在master1上观察
双方同步成功，双主设置完成。
双从（SS）：
1同步现有数据库：
master1：mysqldump -p'Zhushen@123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql
scp -r 2020-1-1-mysql-all.sql slave1:/tmp
scp -r 2020-1-1-mysql-all.sql slave2:/tmp
slave1：mysql -p'Zhushen@123' < /tmp/2020-1-1-mysql-all.sql
slave2：mysql -p'Zhushen@123' < /tmp/2020-1-1-mysql-all.sql
启动从服务器ID，gtid：
slave1：vim /etc/my.cnf
server-id=3
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
systemctl restart mysqld
slave2：vim /etc/my.cnf
server-id=4
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
systemctl restart mysqld
设置主服务器：
slave1：mysql> change master to
master_host='master1',
master_user='rep',
master_password='QianFeng@123',
master_auto_position=1 for channel 'master1';
change master to
master_host='master2',
master_user='rep',
master_password='Zhushen@123',
master_auto_position=1 for channel 'master2';
start slave;
show slave status\G;
slave2：mysql> change master to
master_host='master1',
master_user='rep',
master_password='Zhushen@123',
master_auto_position=1 for channel 'master1';
change master to
master_host='master2',
master_user='rep',
master_password='Zhushen@123',
master_auto_position=1 for channel 'master2';
start slave;
show slave status\G;
测试：
master1 插入数据
master2插入数据
slave1查看
slave2查看
mycat：
mycat环境：
安装包： jdk-8u91-linux-x64.tar.gz
Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
解压java软件开发工具包 ：tar xf jdk-8u91-linux-x64.tar.gz -C /usr/local/
解压mycat软件工具包：tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/
创建链接文件：ln -s /usr/local/jdk1.8.0_91/ /usr/local/java
添加三行 设置JAVA变量，便于JAVA调用:
vim /etc/profile
JAVA_HOME=/usr/local/java
PATH=$JAVA_HOME/bin:$PATH
export JAVA_HOME PATH
source /etc/profile
java -version 查询到版本说明jdk安装成功
配置没有cat前端：vim /usr/local/mycat/conf/server.xml
注释掉多余用户：95行-99行附近
<!--
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
-->
81-85行附近
<property name="password">123456</property>
<property name="schemas">zhushen</property>
配置mycat后端：
# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="zhushen" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="zhushen" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master1" url="master1:3306" user="mycatproxy" password="Zhushen@123">
<readHost host="slave1" url="slave1:3306" user="mycatproxy" password="Zhushen@123" />
<readHost host="slave2" url="slave2:3306" user="mycatproxy" password="Zhushen@123" />
</writeHost>
<writeHost host="master2" url="master2:3306" user="mycatproxy" password="Zhushen@123">
<readHost host="slave1" url="slave1:3306" user="mycatproxy" password="Zhushen@123" />
<readHost host="slave2" url="slave2:3306" user="mycatproxy" password="Zhushen@123" />
</writeHost>
</dataHost>
</mycat:schema>
配置mysql群
M-M-S-S 准备Mycat连接的用户及权限
master1：grant all on *.* to 'mycatproxy'@'10.1.1.104' identified by 'Zhushen@123';
10.1.1.104是mycat服务器的IP
启动Mycat
在mycat服务器上：
/usr/local/mycat/bin/mycat start
（回显Starting Mycat-server... 说明启动成功，否则就是配置Mycat后端语法错误。）
netstat -anpt | grep java （监测端口是否启动）
ps aux | grep mycat （检测进程是否启动）
yum install -y mariadb （安装客户端）
mysql -hmycat -uroot -p123456 -P8066
show databases;
MySQL [(none)]> show databases;
+----------+ |
DATABASE |
+----------+
| zhushen |
+----------+
1 row in set (0.01 sec)
（看到的数据库是虚拟的。注意后方mysql群中应该创建该库）
测试：
在mysql-master1上创库创表
master1：create database zhushen;
master1：create table tianyun.t1 (id int);
master1：insert into zhushen.t1 values(3);
mycat：select * from zhushen.t1;
在mysql集群能 查询到数据。集群搭建完成。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
