Mycat中间件综合部署（1.6）

实施拓扑

请添加图片描述

一，环境搭建

1.服务分配

主机	服务
192.168.2.1	MySQL-cluster-1
192.168.2.2	MySQL-cluster-2
192.168.2.3	Mycat

2.MySQL-cluster1分配

server-id	端口	角色
1	3306	master1
2	3307	slave1
3	3308	master2
4	3309	slave2

3.MySQL-cluster2分配

server-id	端口	角色
5	3306	master1
6	3307	slave1
7	3308	master2
8	3309	slave2

4.结构

请添加图片描述

二，MySQL-cluster-1搭建

1.搭建MySQL单台多实例

rpm -e mariadb-libs-5.5.52-1.el7.x86_64 --nodeps
yum install -y ncurses-devel libaio-devel autoconf
tar zxf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.36-linux-glibc2.12-x86_64 mysql
mv mysql /usr/local/
useradd -s /sbin/nologin -M -r mysqlmkdir -p /mysql/330{6,7,8,9}/data
chown -R mysql:mysql  /mysql/
chown -R mysql:mysql /usr/local/mysql
ln -s /usr/local/mysql/bin/* /usr/local/bin/

2.MySQL配置文件

cat > /mysql/3306/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir= /mysql/3306/data/
socket= /mysql/3306/mysql.sock
log_error= /mysql/3306/
port=3306
server_id=1
log_bin= /mysql/3306/data/mysql-bin
log-slave-updates=1
EOFcat > /mysql/3307/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir= /mysql/3307/data/
socket= /mysql/3307/mysql.sock
log_error= /mysql/3307/
port=3307
server_id=2
log_bin= /mysql/3307/data/mysql-bin
EOFcat > /mysql/3308/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir= /mysql/3308/data/
socket= /mysql/3308/mysql.sock
log_error= /mysql/3308/
port=3308
server_id=3
log_bin= /mysql/3308/data/mysql-bin
log-slave-updates=1
EOFcat > /mysql/3309/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir= /mysql/3309/data/
socket= /mysql/3309/mysql.sock
log_error= /mysql/3309/
port=3309
server_id=4
log_bin= /mysql/3309/data/mysql-bin
EOF

3.数据库初始化

mysqld --initialize-insecure  --user=mysql --datadir=/mysql/3306/data --basedir=/usr/local/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/mysql/3307/data --basedir=/usr/local/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/mysql/3308/data --basedir=/usr/local/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/mysql/3309/data --basedir=/usr/local/mysql

4.数据库启动配置

cat > /etc/systemd/system/mysqld3306.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/mysql/3306/my.cnf --user=mysql
LimitNOFILE = 5000
EOFcat > /etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/mysql/3307/my.cnf --user=mysql
LimitNOFILE = 5000
EOFcat > /etc/systemd/system/mysqld3308.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/mysql/3308/my.cnf --user=mysql
LimitNOFILE = 5000
EOFcat > /etc/systemd/system/mysqld3309.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/mysql/3309/my.cnf --user=mysql
LimitNOFILE = 5000
EOF

5.启动所有MySQL

systemctl start mysqld3306.service
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

6.主从配置

注意：master的二进制日志的名称和位置，开启从后检查状态

mysql -S /mysql/3306/mysql.sock
mysql> show master status;mysql-bin.000004 |      439grant all on *.* to root@'%' identified by '1234.Com';
exitmysql -S /mysql/3307/mysql.sock
change master to master_host='192.168.2.1',master_user='root',master_password='1234.Com',master_port=3306 ,master_log_file='mysql-bin.000004',master_log_pos=439;
start slave;
grant all on *.* to root@'%' identified by '1234.Com';mysql -S /mysql/3308/mysql.sock
grant all on *.* to root@'%' identified by '1234.Com';
mysql> show master status;
mysql-bin.000001 |      439change master to master_host='192.168.2.1',master_user='root',master_password='1234.Com',master_port=3306 ,master_log_file='mysql-bin.000004',master_log_pos=439;
start slave;mysql -S /mysql/3309/mysql.sock
change master to master_host='192.168.2.1',master_user='root',master_password='1234.Com',master_port=3308 ,master_log_file='mysql-bin.000001',master_log_pos=439;
start slave;
grant all on *.* to root@'%' identified by '1234.Com';mysql -S /mysql/3306/mysql.sock
change master to master_host='192.168.2.1',master_user='root',master_password='1234.Com',master_port=3308 ,master_log_file='mysql-bin.000001',master_log_pos=439;

三，MySQL-cluster-2搭建

1.搭建MySQL单台多实例

rpm -e mariadb-libs-5.5.52-1.el7.x86_64 --nodeps
yum install -y ncurses-devel libaio-devel autoconf
tar zxf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.36-linux-glibc2.12-x86_64 mysql
mv mysql /usr/local/
useradd -s /sbin/nologin -M -r mysqlmkdir -p /mysql/330{6,7,8,9}/data
chown -R mysql:mysql  /mysql/
chown -R mysql:mysql /usr/local/mysql
ln -s /usr/local/mysql/bin/* /usr/local/bin/

2.MySQL配置文件

cat > /mysql/3306/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir= /mysql/3306/data/
socket= /mysql/3306/mysql.sock
log_error= /mysql/3306/
port=3306
server_id=5
log_bin= /mysql/3306/data/mysql-bin
log-slave-updates=1
EOFcat > /mysql/3307/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir= /mysql/3307/data/
socket= /mysql/3307/mysql.sock
log_error= /mysql/3307/
port=3307
server_id=6
log_bin= /mysql/3307/data/mysql-bin
EOFcat > /mysql/3308/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir= /mysql/3308/data/
socket= /mysql/3308/mysql.sock
log_error= /mysql/3308/
port=3308
server_id=7
log_bin= /mysql/3308/data/mysql-bin
log-slave-updates=1
EOFcat > /mysql/3309/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir= /mysql/3309/data/
socket= /mysql/3309/mysql.sock
log_error= /mysql/3309/
port=3309
server_id=8
log_bin= /mysql/3309/data/mysql-bin
EOF

3.数据库初始化

mysqld --initialize-insecure  --user=mysql --datadir=/mysql/3306/data --basedir=/usr/local/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/mysql/3307/data --basedir=/usr/local/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/mysql/3308/data --basedir=/usr/local/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/mysql/3309/data --basedir=/usr/local/mysql

4.数据库启动配置

cat > /etc/systemd/system/mysqld3306.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/mysql/3306/my.cnf --user=mysql
LimitNOFILE = 5000
EOFcat > /etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/mysql/3307/my.cnf --user=mysql
LimitNOFILE = 5000
EOFcat > /etc/systemd/system/mysqld3308.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/mysql/3308/my.cnf --user=mysql
LimitNOFILE = 5000
EOFcat > /etc/systemd/system/mysqld3309.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/mysql/3309/my.cnf --user=mysql
LimitNOFILE = 5000
EOF

5.启动所有MySQL

systemctl start mysqld3306.service
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

6.主从配置

mysql -S /mysql/3306/mysql.sock
mysql> show master status;
mysql-bin.000001 |       434grant all on *.* to root@'%' identified by '1234.Com';mysql -S /mysql/3307/mysql.sock
change master to master_host='192.168.2.2',master_user='root',master_password='1234.Com',master_port=3306 ,master_log_file='mysql-bin.000001',master_log_pos= 434;
start slave;
grant all on *.* to root@'%' identified by '1234.Com';mysql -S /mysql/3308/mysql.sock
grant all on *.* to root@'%' identified by '1234.Com';
mysql> show master status;
mysql-bin.000001 |      434change master to master_host='192.168.2.2',master_user='root',master_password='1234.Com',master_port=3306 ,master_log_file='mysql-bin.000001',master_log_pos=434;
start slave;mysql -S /mysql/3309/mysql.sock
change master to master_host='192.168.2.2',master_user='root',master_password='1234.Com',master_port=3308 ,master_log_file='mysql-bin.000001',master_log_pos=434;
start slave;
grant all on *.* to root@'%' identified by '1234.Com';mysql -S /mysql/3306/mysql.sock
change master to master_host='192.168.2.2',master_user='root',master_password='1234.Com',master_port=3308 ,master_log_file='mysql-bin.000001',master_log_pos=434;
start slave;

四，MyCat搭建

1.安装MyCat

[root@localhost ~]# ls
anaconda-ks.cfg  jdk-8u171-linux-x64.tar.gz  Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz  mysql-connector-java-8.0.22.jar 
tar -zxf jdk-8u171-linux-x64.tar.gz
mv jdk1.8.0_171/ /usr/local/java
vi /etc/profile
source /etc/profile
tar zxf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz -C /usr/local/
mv mysql-connector-java-8.0.22.jar /usr/local/mycat/lib/
chmod 777 /usr/local/mycat/lib/* -R

2.schema结构

请添加图片描述

3.schema配置

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/"><schema name="DB01" checkSQLschema="true" sqlMaxLimit="100"><table name="TB_ORDER" dataNode="dn1,dn2" rule="mod-long" /><table name="TB01" dataNode="dn1,dn2" rule="mod-long" /></schema><dataNode name="dn1" dataHost="dhost1" database="db01" /><dataNode name="dn2" dataHost="dhost2" database="db01" /><dataHost name="dhost1" maxCon="1000" minCon="10" balance="1"writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="master1" url="jdbc:mysql://192.168.2.1:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234.Com"><readHost host="slave1" url="jdbc:mysql://192.168.2.1:3307?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234.Com" /></writeHost><writeHost host="master2" url="jdbc:mysql://192.168.2.1:3308?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234.Com"><readHost host="slave2" url="jdbc:mysql://192.168.2.1:3309?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234.Com" /></writeHost></dataHost><dataHost name="dhost2" maxCon="1000" minCon="10" balance="1"writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="master3" url="jdbc:mysql://192.168.2.2:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234.Com"><readHost host="slave3" url="jdbc:mysql://192.168.2.2:3307?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234.Com" /></writeHost><writeHost host="master4" url="jdbc:mysql://192.168.2.2:3308?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234.Com"><readHost host="slave4" url="jdbc:mysql://192.168.2.2:3309?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234.Com" /></writeHost></dataHost></mycat:schema>

4.server.xml配置

<user name="root" defaultAccount="true"><property name="password">123456</property><property name="schemas">DB01</property>

5.rule.xml配置

<function name="mod-long" class="io.mycat.route.function.PartitionByMod"><!-- how many data nodes --><property name="count">2</property></function>

五，测试

create database db01;use db01;create table tb01(id int(11) not null,name varchar(50) not null,sex varchar(1),primary key (id)
)engine=innodb default charset=utf8;insert into tb01(id,name,sex) values(1,'Tom','1');
insert into tb01(id,name,sex) values(2,'Trigger','0');
insert into tb01(id,name,sex) values(3,'Dawn','1');insert into tb01(id,name,sex) values(4,'Jack Ma','1');
insert into tb01(id,name,sex) values(5,'Coco','0');
insert into tb01(id,name,sex) values(6,'Jerry','1');insert into tb01(id,name,sex) values(7,'JackKK Ma','1');
insert into tb01(id,name,sex) values(8,'CCCoco','0');
insert into tb01(id,name,sex) values(9,'JerryYY','1');insert into tb01(id,name,sex) values(10,'JackKK MaAA','1');
insert into tb01(id,name,sex) values(11,'CCCocoAA','0');
insert into tb01(id,name,sex) values(12，'JerryYYAA','1');

Dawn’,‘1’);

insert into tb01(id,name,sex) values(4,‘Jack Ma’,‘1’);
insert into tb01(id,name,sex) values(5,‘Coco’,‘0’);
insert into tb01(id,name,sex) values(6,‘Jerry’,‘1’);

insert into tb01(id,name,sex) values(7,‘JackKK Ma’,‘1’);
insert into tb01(id,name,sex) values(8,‘CCCoco’,‘0’);
insert into tb01(id,name,sex) values(9,‘JerryYY’,‘1’);

insert into tb01(id,name,sex) values(10,‘JackKK MaAA’,‘1’);
insert into tb01(id,name,sex) values(11,‘CCCocoAA’,‘0’);
insert into tb01(id,name,sex) values(12，‘JerryYYAA’,‘1’);