• 首页
  • Open source office development platform mysql5.7 two sets of four node master-slave structure environment building tutorial (2)

Open source office development platform mysql5.7 two sets of four node master-slave structure environment building tutorial (2)

O2oa enterprise information platform 2021-02-23 16:41:09
open source office development platform

MyCat And master-slave sub database and sub table scheme

 

1、MyCAT Infrastructure preparation

1.1 Environmental preparation ：

Two virtual machines db01 db02

Four for each Mysql example 3307 3308 3309 3310

Data directory ：/data/

 

1.2 Delete historical context （ Execute when needed ）

pkill mysql
rm -rf /data/*
mv /etc/my.cnf /etc/my.cnf.bak

 

Database preparation

 

One 、Mysql5.7.29 Server download

Server download link ：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

 

Two 、 Server installation process

2.1、 Create installation package storage directory

command ：mkdir -p /server/tools

[root@localhost local]# mkdir -p /server/tools

 

2.2、 Create an application unzip installation directory

command ：mkdir -p /application

[root@localhost local]# mkdir -p /application

 

2.3、 Unzip the server program in the installation package directory 

[root@localhost local]# tar -xf /usr/local/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz

image.png

 

2.4、 Move the extracted server directory to the installation directory

command ：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

 

Server directory plan ：

image.png

Temporarily create a directory directly on the host /data( You can decide if you want to mount the disk )

2.5 Mount new disk related operations ：

2.5.1、 Add disk new volume ： A little

2.5.2、 Query server disk list ：

command ：fdisk -l

image.png

 

2.5.3、 Format the new volume that needs to be mounted ：/dev/sdc

command ：mkfs.xfs /dev/sdc

 

2.5.4、 Create mount directory

command ：mkdir /data

 

2.5.5、 Query for the new disk UUID

command ：blkid

image.png

 

2.5.6、 Add disk auto mount ：

vim /etc/fstab
UUID="ba1ec30-e251-4ded-bc12-2d940f938dd5" /data xfs defaults 0 0

 

2.5.7、 Mount the disk 

mount -a
df -h

 

2.6、 Create database data storage directory 

mkdir -p /data/mysql/data

 

2.7、 establish mysql Start the user 

useradd -s /sbin/nologin mysql

 

2.8、 Modify environment variables , take mysqld Command to the environment variable 

vim /etc/profile
export PATH=/application/mysql/bin:$PATH

image.png

Let the environment variables take effect ：

source /etc/profile

 

2.9、 Inquire about Mysql Server version

command ：mysql -V

 

2.10、 Error reporting solution 

mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

resolvent ：

install libncurses Related dependencies 

yum install libncurses*

image.png

 

2.11 Directory authorization 

chown -R mysql.mysql /application/*
chown -R mysql.mysql /data

 

3、 ... and 、 Instance initialization

 

3.1 Create... On each host Mysql Data directory

===================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 Initialize on each host Mysql data

===================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 Prepare the database configuration file and startup script of each node on each host

=================================DB01===================================

Configuration file preparation -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

Configuration file preparation -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

Configuration file preparation -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

Configuration file preparation -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

Start the service -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

Start the service -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

Start the service -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

Start the service -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===================================

Configuration file preparation -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

Configuration file preparation -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

Configuration file preparation -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

Configuration file preparation -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

Start the service -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

Start the service -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

Start the service -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

Start the service -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 heavy load systemctl service （ All hosts execute ）

systemctl daemon-reload
systemctl daemon-reexec

 

3.5 Permanently shut down SELINUX（ All hosts execute ）

Need modification /etc/selinux/config Restart the operating system again 

SELINUX=disabled

 

3.6   Modify data directory permissions , Start multiple instances （ All hosts execute ）

chown -R mysql.mysql /data/*
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310

 

3.7 Query instance server_id（ All hosts execute ）

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 Check port listening （ All hosts execute ）

ss -lnt
ss -lnt |grep 33

image.png

3.9 take Mysql Each instance is set to start automatically （ All hosts execute ）

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

image.png

reboot The operating system can verify .

 

Four 、 When something goes wrong , The way to look up questions ：

1、 Error log ：/data/3307/mysql.log

2、systemctl status mysqld3307.service

3、journalctl -xe

image.png

terms of settlement ： Permanent shutdown needs to be modified /etc/selinux/config Just restart the machine again

SELINUX=disabled

 

Node master-slave planning

One 、 Planning and design

Who is the arrow pointing to , Who is the main library

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

 

Piecemeal planning

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

 

Icon ：

image.png

 

 

Start configuration

Shard1_ The first group of four node structure is built （ The red part ）

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_ The second set of four node structure is built （ The blue part ）

#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"

 

Detect master-slave status 

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

Two 、 Problem handling ：

If something goes wrong in the middle , Execute the following command at each node to clear the master-slave configuration , Reconfigure it ：

Stop master-slave replication and delete master-slave associations 

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;"

Delete and recreate the database 

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;"

View database command ：

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;"

 

Kill process stop database command ：

pkill mysql
# Use sock Direct connection to database direct command ：
mysql -S /data/3307/mysql.sock
CREATE DATABASE X DEFAULT CHARACTER SET utf8mb4

 

版权声明
本文为[O2oa enterprise information platform]所创，转载请带上原文链接，感谢
https://javamana.com/2021/02/20210223164017389r.html
  1. J2EE
  2. Vue uses SDK to upload seven cows
  3. k8s-dns
  4. JavaScript mailbox verification - regular verification
  5. k8s-dashboard
  6. How many questions can you answer?
  7. Spring annotation -- transactional
  8. [k8s cluster] construction steps
  9. k8s-kubeadm
  10. k8s-etcd
  11. Using HashMap to improve search performance in Java
  12. There is no class problem when Maven publishes jar package
  13. JavaScriptBOM操作
  14. J2EE
  15. k8s-prometheus-memory
  16. k8s-prometheus disk
  17. k8s-prometheus
  18. JavaScript BOM operation
  19. k8s-prometheus-memory
  20. k8s-prometheus disk
  21. k8s-prometheus
  22. Linux Disk Command
  23. Linux FS
  24. 使用docker-compose ＆WordPress建站
  25. Linux Command
  26. This time, thoroughly grasp the depth of JavaScript copy
  27. Linux Disk Command
  28. Linux FS
  29. Using docker compose & WordPress to build a website
  30. Linux Command
  31. 摊牌了，我 HTTP 功底贼好！
  32. shiro 报 Submitted credentials for token
  33. It's a showdown. I'm good at it!
  34. Shiro submitted credentials for token
  35. Linux Stress test
  36. Linux Root Disk Extension
  37. Linux Stress test
  38. Linux Root Disk Extension
  39. Redis高级客户端Lettuce详解
  40. springboot学习-综合运用（一）
  41. 忘记云服务器上MySQL数据库的root密码时如何重置密码？
  42. Detailed explanation of lettuce, an advanced client of redis
  43. Springboot learning integrated application (1)
  44. Linux File Recover
  45. Linux-Security
  46. How to reset the password when you forget the root password of MySQL database on the cloud server?
  47. Linux File Recover
  48. Linux-Security
  49. LiteOS：盘点那些重要的数据结构
  50. Linux Memory
  51. Liteos: inventory those important data structures
  52. Linux Memory
  53. 手把手教你使用IDEA2020创建SpringBoot项目
  54. Hand in hand to teach you how to create a springboot project with idea2020
  55. spring boot 整合swagger2生成API文档
  56. Spring boot integrates swagger2 to generate API documents
  57. linux操作系统重启后 解决nginx的pid消失问题
  58. Solve the problem of nginx PID disappearing after Linux operating system restart
  59. JAVA版本号含义
  60. The meaning of java version number