开源办公开发平台丨Mysql5.7两套四节点主从结构环境搭建教程(二)

O2OA企业信息化平台 2021-02-23 16:40:39
办公 开发 开源 平台 公开


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服务器下载

服务器下载链接:https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz

[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

image.png

 

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

 

服务器目录规划图:

image.png

暂时直接在主机上创建目录/data(可以根据需要决定是否需要挂载磁盘)

2.5 挂载新的磁盘相关操作:

2.5.1、添加磁盘新卷:略

2.5.2、查询服务器磁盘列表:

命令:fdisk -l

image.png

 

2.5.3、格式化需要挂载的新卷:/dev/sdc

命令:mkfs.xfs /dev/sdc

 

2.5.4、创建挂载目录

命令:mkdir /data

 

2.5.5、查询新磁盘的UUID

命令:blkid

image.png

 

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

image.png

让环境变量生效:

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*

image.png

 

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 <<EOF
[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 <<EOF
[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 <<EOF
[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 <<EOF
[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<<EOF
[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<<EOF
[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<<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=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 10000
EOF

启动服务-3310:

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 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 <<EOF
[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 <<EOF
[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 <<EOF
[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 <<EOF
[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<<EOF
[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<<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=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 10000
EOF

启动服务-3309:

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 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<<EOF
[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

image.png

3.9 将Mysql各实例设置为自动启动(各主机均执行)

systemctl enable mysqld3307
systemctl enable mysqld3308
systemctl enable mysqld3309
systemctl enable mysqld3310

image.png

reboot操作系统可以验证。

 

四、出现问题后,查询问题的方法:

1、错误日志:/data/3307/mysql.log

2、systemctl status mysqld3307.service

3、journalctl -xe

image.png

解决办法:永久关掉需要修改 /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 Master172.16.93.22:3307

Slave2172.16.93.22:3309

 

shard2:

Master172.16.93.22:3308

Slave1:172.16.93.22:3310

Standby Master172.16.93.21:3308

Slave2172.16.93.21:3310

 

图示:

image.png

 

 

开始配置

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

image.png

二、问题处理:

如果中间出现错误,在每个节点执行以下命令可以清除主从结构配置,重新配置即可:

停止主从复制并删除主从关联

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

 

版权声明
本文为[O2OA企业信息化平台]所创,转载请带上原文链接,感谢
https://my.oschina.net/o2oa/blog/4960842

  1. 头条面试官:说说Kafka的消费者提交方式,怎么实现的
  2. 什么是HTTPS以及如何实施HTTPS?
  3. vue使用sdk进行七牛上传
  4. k8s-dns
  5. JavaScript 邮箱验证 - 正则验证
  6. k8s-dashboard
  7. HashMap连环问你能答出几道?
  8. Where does memory overflow occur in the JVM? What are the reasons for this?
  9. How many questions can you answer?
  10. k8s-cronjob
  11. spring注解--Transactional
  12. k8s-cert
  13. Will the Spring Festival holiday be extended to February 27 in 2021? Here comes the response
  14. Headline Interviewer: talk about Kafka's consumer submission method, how to achieve it
  15. 【k8s集群】搭建步骤
  16. k8s-kubeadm
  17. k8s-etcd
  18. What is HTTPS and how to implement it?
  19. Java中使用HashMap改进查找性能
  20. maven发布jar包运行时找不到类问题
  21. J2EE
  22. Vue uses SDK to upload seven cows
  23. k8s-dns
  24. JavaScript mailbox verification - regular verification
  25. k8s-dashboard
  26. How many questions can you answer?
  27. Spring annotation -- transactional
  28. [k8s cluster] construction steps
  29. k8s-kubeadm
  30. k8s-etcd
  31. Using HashMap to improve search performance in Java
  32. There is no class problem when Maven publishes jar package
  33. JavaScriptBOM操作
  34. J2EE
  35. k8s-prometheus-memory
  36. k8s-prometheus disk
  37. k8s-prometheus
  38. JavaScript BOM operation
  39. k8s-prometheus-memory
  40. k8s-prometheus disk
  41. k8s-prometheus
  42. Linux Disk Command
  43. Linux FS
  44. 使用docker-compose &WordPress建站
  45. Linux Command
  46. This time, thoroughly grasp the depth of JavaScript copy
  47. Linux Disk Command
  48. Linux FS
  49. Using docker compose & WordPress to build a website
  50. Linux Command
  51. 摊牌了,我 HTTP 功底贼好!
  52. shiro 报 Submitted credentials for token
  53. It's a showdown. I'm good at it!
  54. Shiro submitted credentials for token
  55. Linux Stress test
  56. Linux Root Disk Extension
  57. Linux Stress test
  58. Linux Root Disk Extension
  59. Redis高级客户端Lettuce详解
  60. springboot学习-综合运用(一)