MyCat及主从分库分表方案
1、MyCAT基础架构准备
1.1 环境准备：
两台虚拟机 db01 db02
每台创建四个Mysql实例 3307 3308 3309 3310
数据目录：/data/
1.2 删除历史环境(在需要时执行)
pkill mysql
rm -rf /data/*
mv /etc/my.cnf /etc/my.cnf.bak
数据库准备
一、Mysql5.7.29服务器下载
[root@localhost local]# cd /usr/local
[root@localhost local]# wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
二、服务器安装过程
2.1、创建安装包存放目录
命令：mkdir -p /server/tools
[root@localhost local]# mkdir -p /server/tools
2.2、创建应用软件解压安装目录
命令：mkdir -p /application
[root@localhost local]# mkdir -p /application
2.3、在安装包存放目录中解压服务器程序
[root@localhost local]# tar -xf /usr/local/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
2.4、将解压的服务器目录移动到安装目录
命令：mv mysql-5.7.29-linux-glibc2.12-x86_64 /application/mysql
[root@localhost local]# mv /usr/local/mysql-5.7.29-linux-glibc2.12-x86_64 /application/mysql
服务器目录规划图：
暂时直接在主机上创建目录/data(可以根据需要决定是否需要挂载磁盘)
2.5 挂载新的磁盘相关操作：
2.5.1、添加磁盘新卷：略
2.5.2、查询服务器磁盘列表：
命令：fdisk -l
2.5.3、格式化需要挂载的新卷：/dev/sdc
命令：mkfs.xfs /dev/sdc
2.5.4、创建挂载目录
命令：mkdir /data
2.5.5、查询新磁盘的UUID
命令：blkid
2.5.6、添加磁盘自动挂载：
vim /etc/fstab
UUID="ba1ec30-e251-4ded-bc12-2d940f938dd5" /data xfs defaults 0 0
2.5.7、进行磁盘挂载
mount -a
df -h
2.6、创建数据库数据存放目录
mkdir -p /data/mysql/data
2.7、创建mysql启动用户
useradd -s /sbin/nologin mysql
2.8、修改环境变量，将mysqld命令添加到环境变量
vim /etc/profile
export PATH=/application/mysql/bin:$PATH
让环境变量生效：
source /etc/profile
2.9、查询Mysql服务器版本
命令：mysql -V
2.10、报错解决
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
解决方法：
安装libncurses相关依赖
yum install libncurses*
2.11 目录授权
chown -R mysql.mysql /application/*
chown -R mysql.mysql /data
三、实例初始化
3.1 在各主机上创建Mysql数据目录
===================db01=======================
mkdir /data/3307/data -p
mkdir /data/3308/data -p
mkdir /data/3309/data -p
mkdir /data/3310/data -p
===================db02=======================
mkdir /data/3307/data -p
mkdir /data/3308/data -p
mkdir /data/3309/data -p
mkdir /data/3310/data -p
3.2 在各主机上初始化Mysql数据
===================db01=======================
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3308/data
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3309/data
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3310/data
===================db02=======================
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3308/data
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3309/data
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3310/data
3.3 准备各主机上各节点数据库配置文件以及启动脚本
=================================DB01===================================
配置文件准备-3307：
cat >/data/3307/my.cnf <
[mysqld]
basedir=/application/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=on
log-slave-updates=1
EOF
配置文件准备-3308：
cat >/data/3308/my.cnf <
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
port=3308
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=on
log-slave-updates=1
EOF
配置文件准备-3309：
cat >/data/3309/my.cnf <
[mysqld]
basedir=/application/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=on
log-slave-updates=1
EOF
配置文件准备-3310：
cat >/data/3310/my.cnf <
[mysqld]
basedir=/application/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=on
log-slave-updates=1
EOF
启动服务-3307
cat >/etc/systemd/system/mysqld3307.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 10000
EOF
启动服务-3308:
cat >/etc/systemd/system/mysqld3308.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 10000
EOF
启动服务-3309:
cat >/etc/systemd/system/mysqld3309.service<
[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=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 10000
EOF
启动服务-3310:
cat >/etc/systemd/system/mysqld3310.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 10000
EOF
=================================DB02===================================
配置文件准备-3307:
cat >/data/3307/my.cnf <
[mysqld]
basedir=/application/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=on
log-slave-updates=1
EOF
配置文件准备-3308:
cat >/data/3308/my.cnf <
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
port=3308
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=on
log-slave-updates=1
EOF
配置文件准备-3309:
cat >/data/3309/my.cnf <
[mysqld]
basedir=/application/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=on
log-slave-updates=1
EOF
配置文件准备-3310:
cat >/data/3310/my.cnf <
[mysqld]
basedir=/application/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=on
log-slave-updates=1
EOF
启动服务-3307:
cat >/etc/systemd/system/mysqld3307.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 10000
EOF
启动服务-3308:
cat >/etc/systemd/system/mysqld3308.service<
[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=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 10000
EOF
启动服务-3309:
cat >/etc/systemd/system/mysqld3309.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 10000
EOF
启动服务-3310:
cat >/etc/systemd/system/mysqld3310.service<
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 10000
EOF
3.4 重载systemctl服务(各主机均执行)
systemctl daemon-reload
systemctl daemon-reexec
3.5 永久关掉SELINUX(各主机均执行)
需要修改 /etc/selinux/config 再重启操作系统
SELINUX=disabled
3.6 修改数据目录权限，启动多实例(各主机均执行)
chown -R mysql.mysql /data/*
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310
3.7 查询实例server_id(各主机均执行)
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'"
3.8 检查端口监听(各主机均执行)
ss -lnt
ss -lnt |grep 33
3.9 将Mysql各实例设置为自动启动(各主机均执行)
systemctl enable mysqld3307
systemctl enable mysqld3308
systemctl enable mysqld3309
systemctl enable mysqld3310
reboot操作系统可以验证。
四、出现问题后，查询问题的方法：
1、错误日志：/data/3307/mysql.log
2、systemctl status mysqld3307.service
3、journalctl -xe
解决办法：永久关掉需要修改 /etc/selinux/config 再重启机器就好
SELINUX=disabled
节点主从规划
一、规划设计
箭头指向谁，谁就是主库
172.16.93.21:3307 172.16.93.22:3307
172.16.93.21:3309 ————> 172.16.93.21:3307
172.16.93.22:3309 ————> 172.16.93.22:3307
172.16.93.22:3308 172.16.93.21:3308
172.16.93.22:3310 ————> 172.16.93.22:3308
172.16.93.21:3310 ————> 172.16.93.21:3308
分片规划
shard1:
Master：172.16.93.21:3307
Slave1：172.16.93.21:3309
Standby Master：172.16.93.22:3307
Slave2：172.16.93.22:3309
shard2:
Master：172.16.93.22:3308
Slave1：172.16.93.22:3310
Standby Master：172.16.93.21:3308
Slave2：172.16.93.21:3310
图示：
开始配置
Shard1_第一组四节点结构搭建(红色部分)
172.16.93.21:3307 172.16.93.22:3307
## db02(172.16.93.22)
mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to rep1@'%' identified by 'zone2018';"
mysql -S /data/3307/mysql.sock -e "grant all on *.* to root@'%' identified by 'zone2018' with grant option;"
##db01(172.16.93.21)
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.16.93.22', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='rep1', MASTER_PASSWORD='zone2018';"
mysql -S /data/3307/mysql.sock -e "start slave;"
mysql -S /data/3307/mysql.sock -e "show slave status \G"
##db02(172.16.93.22)
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.16.93.21', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='rep1', MASTER_PASSWORD='zone2018';"
mysql -S /data/3307/mysql.sock -e "start slave;"
mysql -S /data/3307/mysql.sock -e "show slave status \G"
#172.16.93.21:3309 ————> 172.16.93.21:3307
##db01(172.16.93.21)
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.16.93.21', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='rep1', MASTER_PASSWORD='zone2018';"
mysql -S /data/3309/mysql.sock -e "start slave;"
mysql -S /data/3309/mysql.sock -e "show slave status \G"
#172.16.93.22:3309 ————> 172.16.93.22:3307
##db02(172.16.93.22)
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.16.93.22', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='rep1', MASTER_PASSWORD='zone2018';"
mysql -S /data/3309/mysql.sock -e "start slave;"
mysql -S /data/3309/mysql.sock -e "show slave status \G"
Shard2_第二组四节点结构搭建(蓝色部分)
#172.16.93.22:3308 172.16.93.21:3308
## db01(172.16.93.21)
mysql -S /data/3308/mysql.sock -e "grant replication slave on *.* to rep1@'%' identified by 'zone2018';"
mysql -S /data/3308/mysql.sock -e "grant all on *.* to root@'%' identified by 'zone2018' with grant option;"
##db02(172.16.93.22)
mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.16.93.21', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='rep1', MASTER_PASSWORD='zone2018';"
mysql -S /data/3308/mysql.sock -e "start slave;"
mysql -S /data/3308/mysql.sock -e "show slave status \G"
##db01(172.16.93.21)
mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.16.93.22', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='rep1', MASTER_PASSWORD='zone2018';"
mysql -S /data/3308/mysql.sock -e "start slave;"
mysql -S /data/3308/mysql.sock -e "show slave status \G"
#172.16.93.22:3310 ————> 172.16.93.22:3308
##db02(172.16.93.22)
mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.16.93.22', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='rep1', MASTER_PASSWORD='zone2018';"
mysql -S /data/3310/mysql.sock -e "start slave;"
mysql -S /data/3310/mysql.sock -e "show slave status \G"
#172.16.93.21:3310 ————> 172.16.93.21:3308
##db01(172.16.93.21)
mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.16.93.21', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='rep1', MASTER_PASSWORD='zone2018';"
mysql -S /data/3310/mysql.sock -e "start slave;"
mysql -S /data/3310/mysql.sock -e "show slave status \G"
检测主从状态
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
二、问题处理：
如果中间出现错误，在每个节点执行以下命令可以清除主从结构配置，重新配置即可：
停止主从复制并删除主从关联
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;"
删除并重新创建数据库
mysql -S /data/3307/mysql.sock -e "DROP DATABASE X; CREATE DATABASE X DEFAULT CHARACTER SET utf8mb4;"
mysql -S /data/3308/mysql.sock -e "DROP DATABASE X; CREATE DATABASE X DEFAULT CHARACTER SET utf8mb4;"
mysql -S /data/3309/mysql.sock -e "DROP DATABASE X; CREATE DATABASE X DEFAULT CHARACTER SET utf8mb4;"
mysql -S /data/3310/mysql.sock -e "DROP DATABASE X; CREATE DATABASE X DEFAULT CHARACTER SET utf8mb4;"
查看数据库命令：
mysql -S /data/3307/mysql.sock -e "SHOW DATABASES;"
mysql -S /data/3308/mysql.sock -e "SHOW DATABASES;"
mysql -S /data/3309/mysql.sock -e "SHOW DATABASES;"
mysql -S /data/3310/mysql.sock -e "SHOW DATABASES;"
杀进程停止数据库命令：
pkill mysql
#使用sock直接连接数据库直接命令：
mysql -S /data/3307/mysql.sock
CREATE DATABASE X DEFAULT CHARACTER SET utf8mb4
扫一扫
962
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
