mycat及主从复制的搭建
注意事项：安装前必须要看，要不然你就会踩坑
mysql的配置文件注意
my.cnf的注意：
| basedir=/opt/mysql | mysql 你自己的安装目录 |
|---|---|
| datadir=/data/3307/data | 存放数据的目录必须先创建好，属主为mysql |
| server-id=7 | 多实例，要组合起来server_id必须不一致 |
启动脚本注意：
| ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf | mysql的安装目录，配置文件的位置要根据的自己的实际情况更改 |
|---|---|
| /etc/systemd/system/mysqld3307.service | 启动脚本的名称自己定义 |
搭建mycat的读写分离的架构之前，你必须要了解主从复制的原理以及主从复制的搭建过程
不要盲目的根据文档进行操作，可能您会报错，但是不知道原因
1. 环境的准备
| 192.168.80.94 | sjk4 |
|---|---|
| 192.168.80.95 | sjk5 |
1.1 架构图
pkill mysqld
\rm -rf /data/330*
\mv /etc/my.cnf /etc/my.cnf.bak
1. 2 创建目录初始化(两台节点都做)
删除历史环境。没有的你要先安装mysql
pkill mysqld
\rm -rf /data/330*
\mv /etc/my.cnf /etc/my.cnf.bak
目录的创建
mkdir /data/33{07..10}/data -p
chown -R mysql. /data
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/opt/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3310/data --basedir=/opt/mysql
1.3 sjk4的多实例配置文件和启动脚本
1.3.1 配置文件
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=8
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=9
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=10
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
1.3.2 启动脚本
cat >/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=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cat >/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=/opt/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
cat >/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=/opt/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.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=/opt/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
1.4 sjk5的多实例配置文件和启动脚本
1.4.1 配置文件：
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=17
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=18
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=19
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=20
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
1.4.2 启动脚本：
cat >/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=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cat >/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=/opt/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
cat >/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=/opt/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.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=/opt/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
1.5 启动 （两台都做）
启动
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310
检查:
mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"
2. 主从节点的规划
箭头指向谁是主库
10.0.0.51:3307 <-----> 10.0.0.52:3307
10.0.0.51:3309 ------> 10.0.0.51:3307
10.0.0.52:3309 ------> 10.0.0.52:3307
10.0.0.52:3308 <-----> 10.0.0.51:3308
10.0.0.52:3310 -----> 10.0.0.52:3308
10.0.0.51:3310 -----> 10.0.0.51:3308
分片规划
shard1：
Master：10.0.0.51:3307
slave1：10.0.0.51:3309
Standby Master：10.0.0.52:3307
slave2：10.0.0.52:3309
shard2：
Master：10.0.0.52:3308
slave1：10.0.0.52:3310
Standby Master：10.0.0.51:3308
slave2：10.0.0.51:3310
2.2 配置第一组的四个节点
2.2.1 sjk4:3307与sjk5：3307互为主从
192.168.80.94:3307 <-----> 192.168.80.95:3307
sjk5
mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'192.168.80.%' identified by '123';"
mysql -S /data/3307/mysql.sock -e "grant all on *.* to root@'192.168.80.%' identified by '123' with grant option;"
sjk4
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.80.95', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3307/mysql.sock -e "start slave;"
mysql -S /data/3307/mysql.sock -e "show slave status\G"
sjk5
sjk5
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.80.94', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3307/mysql.sock -e "start slave;"
mysql -S /data/3307/mysql.sock -e "show slave status\G"
2.2.2 两个3307和3309的分别一主一从
sjk4:
192.168.80.94:3309 ------> 192.168.90.95:3307
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.80.94', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3309/mysql.sock -e "start slave;"
mysql -S /data/3309/mysql.sock -e "show slave status\G"
sjk5：
192.168.80.95:3309 ------> 192.168.80.95:3307
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.80.95', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3309/mysql.sock -e "start slave;"
mysql -S /data/3309/mysql.sock -e "show slave status\G"
2.3 配置第二组的四个节点
2.3.1 sjk4:3308与sjk5:3308互为主从
#192.168.80.95:3308 <-----> 192.168.80.94:3308
sjk4
mysql -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'192.168.80.%' identified by '123';"
mysql -S /data/3308/mysql.sock -e "grant all on *.* to root@'192.168.80.%' identified by '123' with grant option;"
sjk5：
sjk5
mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.80.94', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3308/mysql.sock -e "start slave;"
mysql -S /data/3308/mysql.sock -e "show slave status\G"
sjk4
mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.80.95', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3308/mysql.sock -e "start slave;"
mysql -S /data/3308/mysql.sock -e "show slave status\G"
2.3.2 两个节点的3308为主3310为从
sjk5：
mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.80.95', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3310/mysql.sock -e "start slave;"
mysql -S /data/3310/mysql.sock -e "show slave status\G"
sjk4:
mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.80.94', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3310/mysql.sock -e "start slave;"
mysql -S /data/3310/mysql.sock -e "show slave status\G"
2.4 检查：两台都做
mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes
==================================================================
注：如果中间出现错误，在每个节点进行执行以下命令,从新开始change master to
mysql -S /data/3307/mysql.sock -e "stop slave; reset slave all;"
mysql -S /data/3308/mysql.sock -e "stop slave; reset slave all;"
mysql -S /data/3309/mysql.sock -e "stop slave; reset slave all;"
mysql -S /data/3310/mysql.sock -e "stop slave; reset slave all;"
==================================================================
3. mycat的安装 这里安装在sjk4的上边
3.1 mycat的下载地址
下载完成上传：
cd /opt
rz -E
3.2 安装环境
安装java
yum install -y java
解压mycat文件
tar xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
软件的目录结构
[root@sjk4 opt]# cd mycat/
[root@sjk4 mycat]# ll
total 12
drwxr-xr-x 2 root root 190 Nov 14 21:58 bin
drwxrwxrwx 2 root root 6 Jun 24 2019 catlet
drwxrwxrwx 4 root root 4096 Nov 14 21:58 conf
drwxr-xr-x 2 root root 4096 Nov 14 21:58 lib
drwxrwxrwx 2 root root 6 Jun 26 2019 logs
-rwxrwxrwx 1 root root 227 Jun 27 2019 version.txt
配置环境变量
vim /etc/profile
export PATH=/opt/mycat/bin:$PATH
source /etc/profile
启动，连接测试
mycat默认的用户名root密码123456 管理端口8066
mycat start
mysql -uroot -p123456 -h 127.0.0.1 -P8066
成功：
[root@sjk4 mycat]# mysql -uroot -p123456 -h 127.0.0.1 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.1-release-20190627191042 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
默认使用的逻辑库TESTDB
到这里我们的mycat就安装完成了
下一章：mycat的具体使用