网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
| 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 mysql
mkdir -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
EOF
cat > /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
EOF
cat > /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
EOF
cat > /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
EOF
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=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/3307/my.cnf --user=mysql
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=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/3308/my.cnf --user=mysql
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=/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 | 439
grant all on *.* to root@'%' identified by '1234.Com';
exit
mysql -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 | 439
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;
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 mysql
mkdir -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
EOF
cat > /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
EOF
cat > /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
EOF
cat > /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
EOF
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=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/3307/my.cnf --user=mysql
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=/usr/local/mysql/bin/mysqld --defaults-file=/mysql/3308/my.cnf --user=mysql
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=/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 | 434
grant 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 | 434
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;
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
**网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。**
**[需要这份系统化的资料的朋友,可以点击这里获取!](https://bbs.csdn.net/topics/618635766)**
**一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!**