1.
设置主从从
mysql57
服务器
(
1
)配置主数据库
[root@msater_5 ~]
# systemctl stop filewalld
[root@msater_5 ~]
# setenforce 0
[root@msater_5 ~]
# systemctl disable filewalld
[root@msater_5 ~]
# ls
anaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@msater_5 ~]
# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@msater_5 ~]
# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql
[root@msater_5 ~]
# rm -rf /etc/my.cnf
[root@msater_5 ~]
# mkdir /usr/local/mysql/mysql-files
[root@msater_5 ~]
# useradd -r -s /sbin/nologin mysql
[root@msater_5 ~]
# chown mysql:mysql /usr/local/mysql/mysql-files
[root@msater_5 ~]
# chown 750 /usr/local/mysql/mysql-files
[root@msater_5 ~]
# /usr/local/mysql/bin/mysqld --initialize --user=mysql --
basedir=/usr/local/mysql
2024
-08-21T09
:39:16.036321Z
0
[Warning] CA certi2024-08-21T09:39:16.384066Z
1
[Note] A temporary password is generated
for
root@localhost: RV3DtuxCi;A-
[root@msater_5 ~]
# cp /usr/local/mysql/support-files/mysql.server
/etc/init.d/mysql57
[root@msater_5 ~]
# vim /usr/local/mysql/my.cnf
[mysqld]
basedir
=
/usr/local/mysql
datadir
=
/usr/local/mysql/data
socket
=
/tmp/mysql.sock
port
=
3306
log-error
=
/usr/local/mysql/data/db01-master.err
log-bin
=
/usr/local/mysql/data/binlog
server-id
=
10
character_set_server
=
utf8mb4
[root@msater_5 ~]
# service mysql57 start
[root@msater_5 ~]
# /usr/local/mysql/bin/mysql -p
Enter password:
mysql> alter user
'root'
@
'localhost'
identified by
'root'
;
Query OK,
0
rows affected (0.00 sec)
mysql> create user
'li'
@
'%'
identified by
'li'
;
Query OK,
0
rows affected (0.00 sec)
mysql> grant all on *.* to
'li'
@
'%'
;
uery OK,
0
rows affected (0.01 sec)
mysql> flush privileges;
Query OK,
0
rows affected (0.01 sec)
mysql> create user
'slave'
@
'%'
identified by
'slave'
;
Query OK,
0
rows affected (0.00 sec)
mysql> grant replication slave on *.* to
'slave'
@
'%'
;
Query OK,
0
rows affected (0.00 sec)
mysql> flush privileges;
Query OK,
0
rows affected (0.00 sec)
2
)配置
slave01
数据库
[root@slave01_5 ~]
# ls
anaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@slave01_5 ~]
# systemctl stop firewalld
[root@slave01_5 ~]
# systemctl disable firewalld
[root@slave01_5 ~]
# setenforce 0
[root@slave01_5 ~]
# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@slave01_5 ~]
# cp -r mysql-5.7.44-linux-glibc2.12-x86_64
/usr/local/mysql
[root@slave01_5 ~]
# rm -rf /etc/my.cnf
[root@slave01_5 ~]
# mkdir /usr/local/mysql/mysql-files
[root@slave01_5 ~]
# useradd -r -s /sbin/nologin mysql
[root@slave01_5 ~]
# chown mysql:mysql /usr/local/mysql/mysql-files/
[root@slave01_5 ~]
# chown 750 /usr/local/mysql/mysql-files/
[root@slave01_5 ~]
# yum -y install rsync
3
)配置
slave02
数据库
[root@slave01_5 ~]
# ls
anaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@slave01_5 ~]
# systemctl stop firewalld
[root@slave01_5 ~]
# systemctl disable firewalld
[root@slave01_5 ~]
# setenforce 0
[root@slave01_5 ~]
# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
[root@slave01_5 ~]
# cp -r mysql-5.7.44-linux-glibc2.12-x86_64
/usr/local/mysql
[root@slave01_5 ~]
# rm -rf /etc/my.cnf
[root@slave01_5 ~]
# mkdir /usr/local/mysql/mysql-files
[root@slave01_5 ~]
# useradd -r -s /sbin/nologin mysql
[root@slave01_5 ~]
# chown mysql:mysql /usr/local/mysql/mysql-files/
[root@slave01_5 ~]
# chown 750 /usr/local/mysql/mysql-files/
[root@slave01_5 ~]
# yum -y install rsync
4
)同步
data
[root@msater_5 ~]
# service mysql57 stop
[root@msater_5 ~]
# rm -rf /usr/local/mysql/data/auto.cnf
[root@msater_5 ~]
# yum -y install rsync
[root@msater_5 ~]
# rsync -av /usr/local/mysql/data
root@192.168.2.58:/usr/local/mysql/
[root@msater_5 ~]
# rsync -av /usr/local/mysql/data
root@192.168.2.59:/usr/local/mysql/
5
)修改
slave01
配置文件
[root@slave01_5 ~]
# ls /usr/local/mysql/
bin docs lib man README support-files
data include LICENSE mysql-files share
[root@slave01_5 ~]
# vim /usr/local/mysql/my.cnf
[mysqld]
basedir
=
/usr/local/mysql
datadir
=
/usr/local/mysql/data
socket
=
/tmp/mysql.sock
port
=
3310
log-error
=
/usr/local/mysql/data/err.log
relay-log
=
/usr/local/mysql/data/relaylog
character_set_server
=
utf8mb4
server-id
=
11
[root@slave01_5 ~]
# cp /usr/local/mysql/support-files/mysql.server
/etc/init.d/mysql57
[root@slave01_5 ~]
# sed -i '$aexport PATH=$PATH:/usr/local/mysql/bin'
/etc/profile
[root@slave01_5 ~]
# source /etc/profile
6
)修改
slave02
配置文件
[root@slave01_5 ~]
# ls /usr/local/mysql/
bin docs lib man README support-files
data include LICENSE mysql-files share
[root@slave01_5 ~]
# vim /usr/local/mysql/my.cnf
[mysqld]
basedir
=
/usr/local/mysql
datadir
=
/usr/local/mysql/data
socket
=
/tmp/mysql.sock
port
=
3310
log-error
=
/usr/local/mysql/data/err.log
relay-log
=
/usr/local/mysql/data/relaylog
character_set_server
=
utf8mb4
server-id
=
12
[root@slave01_5 ~]
# cp /usr/local/mysql/support-files/mysql.server
/etc/init.d/mysql57
[root@slave01_5 ~]
# sed -i '$aexport PATH=$PATH:/usr/local/mysql/bin'
/etc/profile
[root@slave01_5 ~]
# source /etc/profile
7
)主服务器锁表
[root@msater_5 ~]
# mysql -proot
mysql> select user,host from mysql.user;
+
---------------
+
-----------
+
| user | host |
+
---------------
+
-----------
+
| slave | % |
| li | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+
---------------
+
-----------
+
5
rows
in set
(0.00 sec)
mysql> flush tables with read lock;
Query OK,
0
rows affected (0.00 sec)
mysql> show master status;
+
---------------
+
----------
+
--------------
+
------------------
+
---------------
----
+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+
---------------
+
----------
+
--------------
+
------------------
+
---------------
----
+
| binlog.000002 |
154
| | |
|
+
---------------
+
----------
+
--------------
+
------------------
+
---------------
----
+
1
row
in set
(0.00 sec)
8
)配置主从服务
[root@slave01_5 ~]
# mysql -uroot -proot
mysql> change master to
master_host
=
"10.0.0.57"
,master_user
=
"slave"
,master_password
=
"slave"
,master_lo
g_file
=
"binlog.000002"
,master_log_pos
=
154
;
Query OK,
0
rows affected,
2
warnings (0.01 sec)
mysql>
start
slave;
Query OK,
0
rows affected (0.01 sec)
mysql> show slave status\G;
***************************
1
. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host:
192
.168.2.57
Master_User: slave
Master_Port:
3306
Connect_Retry:
60
Master_Log_File: binlog.000002
Read_Master_Log_Pos:
154
Relay_Log_File: relaylog.000002
Relay_Log_Pos:
317
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
9
)配置主从从服务
[root@slave01_5 ~]
# mysql -uroot -proot
mysql> change master to
master_host
=
"10.0.0.57"
,master_user
=
"slave"
,master_password
=
"slave"
,master_lo
g_file
=
"binlog.000002"
,master_log_pos
=
154
;
Query OK,
0
rows affected,
2
warnings (0.01 sec)
mysql>
start
slave;
Query OK,
0
rows affected (0.01 sec)
mysql> show slave status\G;
***************************
1
. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host:
192
.168.2.57
Master_User: slave
Master_Port:
3306
Connect_Retry:
60
Master_Log_File: binlog.000002
Read_Master_Log_Pos:
154
Relay_Log_File: relaylog.000002
Relay_Log_Pos:
317
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2.
在
mysql
中添加
eleme
数据库设置为
utf8mb4
[root@msater_5 ~]
# mysql -proot
mysql> unlock tables;
Query OK,
0
rows affected (0.00 sec)
mysql> create database
if
not exists eleme charset utf8mb4;
Query OK,
1
row affected (0.00 sec)
mysql> show databases;
+
--------------------
+
| Database |
+
--------------------
+
| information_schema |
| eleme |
| mysql |
| performance_schema |
| sys |
| test |
+
--------------------
+
6
rows
in set
(0.00 sec)
3.
添加表
t_user
(
master
)
mysql> use eleme;
Database changed
mysql> create table t_user(
-
> id int primary key auto_increment,
-
> name varchar(45) not null,
-
> username varchar(45) not null,
-
> password varchar(45) not null,
-
> remark varchar(45)
-
> );
Query OK,
0
rows affected (0.01 sec)
mysql> show tables;
+
-----------------
+
| Tables_in_eleme |
+
-----------------
+
| t_user |
+
-----------------
+
1
row
in set
(0.00 sec)
4.
添加
2
行记录(
master
)
mysql> insert into eleme.t_user values(1,
'
超级管理员
'
,
'admin'
,
'admin'
,
'
超级管理
员
'
),(2,
'
普通用户
'
,
'guest'
,
'guest'
,
'
普通用户
'
);
Query OK,
2
rows affected (0.00 sec)
Records:
2
Duplicates:
0
Warnings:
0
mysql> select * from eleme.t_user;
+
----
+
-----------------
+
----------
+
----------
+
-----------------
+
| id | name | username | password | remark |
+
----
+
-----------------
+
----------
+
----------
+
-----------------
+
|
1
|
超级管理员
| admin | admin |
超级管理员
|
|
2
|
普通用户
| guest | guest |
普通用户
|
+
----
+
-----------------
+
----------
+
----------
+
-----------------
+
2
rows
in set
(0.00 sec)
5.
使用
mycat
为三台数据库设置负载均衡(读写分离)
1
)前期
jdk
环境部署
[root@mycat ~]
# systemctl stop firewalld
[root@mycat ~]
# systemctl disable firewalld
[root@mycat ~]
# ls
anaconda-ks.cfg Mycat-server-1.6.5-release-20180122220033-
linux.tar.gz
jdk-8u192-linux-x64.tar.gz v
[root@mycat ~]
# tar -xf jdk-8u192-linux-x64.tar.gz
[root@mycat ~]
# tar -xf Mycat-server-1.6.5-release-20180122220033-
linux.tar.gz
[root@mycat ~]
# cp -r jdk1.8.0_192/ /usr/local/jdk
[root@mycat ~]
# cp -r mycat/ /usr/local/
[root@mycat ~]
# ls /usr/local/jdk/
bin lib src.zip
COPYRIGHT LICENSE THIRDPARTYLICENSEREADME-JAVAFX.txt
include man THIRDPARTYLICENSEREADME.txt
javafx-src.zip README.html
jre release
[root@mycat ~]
# sed -i '$aexport JAVA_HOME=/usr/local/jdk' /etc/profile
[root@mycat ~]
# source /etc/profile
[root@mycat ~]
# $JAVA_HOME
-bash
: /usr/local/jdk:
是一个目录
[root@mycat ~]
# sed -i '$aexport PATH=$PATH:$JAVA_HOME/bin' /etc/profile
[root@mycat ~]
# source /etc/profile
[root@mycat ~]
# $PATH
-bash
: /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/jdk/bin:
没有那个文件或目录
[root@mycat ~]
# javac -version
javac
1
.8.0_192
2
)修改
server.xml
配置文件
[root@mycat ~]
# vim /usr/local/mycat/conf/server.xml
93
<user
name
=
"li"
defaultAccount
=
"true"
>
94
<property
name
=
"password"
>li</property>
95
<property
name
=
"schemas"
>eleme</property>
#
注释
107
<!--
108
<user
name
=
"user"
>
109
<property
name
=
"password"
>user</property>
110
<property
name
=
"schemas"
>TESTDB</property>
111
<property
name
=
"readOnly"
>true</property>
112
</user>
113
--
>
3
)修改
schema.xml
配置文件
![](https://i-blog.csdnimg.cn/direct/1b5489f46db5420dab574f456c4efa5c.png)
4
)启动服务
[root@mycat ~]
# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat ~]
# netstat -lnput | grep 8066
tcp6
0 0
:::8066 :::* LISTEN
1336
/java
6.
客户端测试
mycat
![](https://i-blog.csdnimg.cn/direct/b4de5684c8e54290a0062e91a51d507c.png)
7.
部署
java17
环境
1
)
java01
[root@java01 ~]
# ls
anaconda-ks.cfg jdk-17_linux-x64_bin.tar.gz
[root@java01 ~]
# tar -xf jdk-17_linux-x64_bin.tar.gz
[root@java01 ~]
# ls
anaconda-ks.cfg jdk-17.0.12 jdk-17_linux-x64_bin.tar.gz
[root@java01 ~]
# cp -r jdk-17.0.12/ /usr/local/jdk
[root@java01 ~]
# vim /etc/profile
export
JAVA_HOME
=
/usr/local/jdk
export
PATH
=
$PATH
:
$JAVA_HOME
/bin
[root@java01 ~]
# source /etc/profile
[root@java01 ~]
# javac --version
javac
17
.0.12
[root@java01 ~]
# firewall-cmd --add-port=8080/tcp --permanent
success
[root@java01 ~]
# firewall-cmd --reload
success
[root@java01 ~]
# ls
anaconda-ks.cfg application.yml eleme_server-0.0.1-SNAPSHOT.jar jdk-
17.0.12 jdk-17_linux-x64_bin.tar.gz
[root@java01 ~]
# vim application.yml
server:
port:
8080
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://10.0.0.11:8066/eleme
username: li
password: li
[root@java01 ~]
# java -jar eleme_server-0.0.1-SNAPSHOT.jar
2
)浏览器访问:
10.0.0.12:8080
3
)
java02
[root@java01 ~]
# ls
anaconda-ks.cfg jdk-17_linux-x64_bin.tar.gz
[root@java01 ~]
# tar -xf jdk-17_linux-x64_bin.tar.gz
[root@java01 ~]
# ls
anaconda-ks.cfg jdk-17.0.12 jdk-17_linux-x64_bin.tar.gz
[root@java01 ~]
# cp -r jdk-17.0.12/ /usr/local/jdk
[root@java01 ~]
# vim /etc/profile
export
JAVA_HOME
=
/usr/local/jdk
export
PATH
=
$PATH
:
$JAVA_HOME
/bin
[root@java01 ~]
# source /etc/profile
[root@java01 ~]
# javac --version
javac
17
.0.12
[root@java01 ~]
# firewall-cmd --add-port=8080/tcp --permanent
success
[root@java01 ~]
# firewall-cmd --reload
success
[root@java01 ~]
# ls
anaconda-ks.cfg application.yml eleme_server-0.0.1-SNAPSHOT.jar jdk-
17.0.12 jdk-17_linux-x64_bin.tar.gz
[root@java01 ~]
# vim application.yml
server:
port:
8080
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://10.0.0.11:8066/eleme
username: li
password: li
[root@java01 ~]
# java -jar eleme_server-0.0.1-SNAPSHOT.jar
4
)浏览器访问:
10.0.0.13:8080
8.
部署
LVS-nat
模式
1
)
lvs
[root@lvs ~]
# ifconfig ens33:0 10.0.0.100 broadcast 10.0.0.100 netmask
255.255.255.255 up
[root@lvs ~]
# route add -host 10.0.0.100 dev ens33:0
[root@lvs ~]
# yum -y install ipvsadm
[root@lvs ~]
# ipvsadm -C
[root@lvs ~]
# ipvsadm -A -t 10.0.0.100:8080 -s rr
[root@lvs ~]
# ipvsadm -a -t 10.0.0.100:8080 -r 10.0.0.12 -g
[root@lvs ~]
# ipvsadm -a -t 10.0.0.100:8080 -r 10.0.0.13 -g
[root@lvs ~]
# systemctl stop firewalld
[root@lvs ~]
# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi
user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus
org.fedoraproject.FirewallD1.service.
[root@lvs ~]
# setenforce 0
[root@lvs ~]
# systemctl stop firewalld
[root@lvs ~]
# ipvsadm -Ln
TCP
10
.0.0.100:8080 rr
-
>
10
.0.0.12:8080 Route
1 0 1
-
>
10
.0.0.13:8080 Route
1 0 1
2
)
java01
[root@java01 ~]
# ifconfig lo:0 10.0.0.100 broadcast 10.0.0.100 netmask
255.255.255.255 up
[root@java01 ~]
# route add -host 10.0.0.100 dev lo:0
[root@java01 ~]
# echo 1 > /proc/sys/net/ipv4/conf/lo/arp_ignore
[root@java01 ~]
# echo 2 > /proc/sys/net/ipv4/conf/lo/arp_announce
[root@java01 ~]
# echo 1 > /proc/sys/net/ipv4/conf/all/arp_ignore
[root@java01 ~]
# echo 2 > /proc/sys/net/ipv4/conf/all/arp_announce
3
)
java02
[root@java01 ~]
# ifconfig lo:0 10.0.0.100 broadcast 10.0.0.100 netmask
255.255.255.255 up
[root@java01 ~]
# route add -host 10.0.0.100 dev lo:0
[root@java01 ~]
# echo 1 > /proc/sys/net/ipv4/conf/lo/arp_ignore
[root@java01 ~]
# echo 2 > /proc/sys/net/ipv4/conf/lo/arp_announce
[root@java01 ~]
# echo 1 > /proc/sys/net/ipv4/conf/all/arp_ignore
[root@java01 ~]
# echo 2 > /proc/sys/net/ipv4/conf/all/arp_announce
4
)浏览器访问:
10.0.0.100:8080(
外网地址
)
![](https://i-blog.csdnimg.cn/direct/0d6ed367da1a4dec81612e77ec35e21f.png)