上午
1
、练习
[root@nat ~]
# ipvsadm -d -t 192.168.10.101:3306 -r 10.0.0.22:3306 #
删除
真实主机
nat:
[root@nat ~]
# ifconfig
ens33:
flags
=
4163
<UP,BROADCAST,RUNNING,MULTICAST> mtu
1500
inet
10
.0.0.10 netmask
255
.255.255.0 broadcast
10
.0.0.255
ens36:
flags
=
4163
<UP,BROADCAST,RUNNING,MULTICAST> mtu
1500
inet
192
.168.10.101 netmask
255
.255.255.0 broadcast
192
.168.10.255
[root@nat ~]
# yum -y install ipvsadm.x86_64
[root@nat ~]
# ipvsadm -A -t 192.168.10.101:3306 -s rr
[root@nat ~]
# ipvsadm -a -t 192.168.10.101:3306 -r 10.0.0.21:3306 -m
[root@nat ~]
# ipvsadm -a -t 192.168.10.101:3306 -r 10.0.0.22:3306 -m
[root@nat ~]
# ipvsadm -Ln
IP Virtual Server version
1
.2.1
(size
=
4096
)
Prot LocalAddress:Port Scheduler Flags
-
> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP
192
.168.10.101:3306 rr
-
>
10
.0.0.21:3306 Masq
1 0 0
-
>
10
.0.0.22:3306 Masq
1 0 0
[root@nat ~]
# vim /etc/sysctl.conf
[root@nat ~]
# net.ipv4.ip_forward=1
sysctl
-p
mysql:
[root@master-mysql ~]
# route del default
[root@master-mysql ~]
# route add default gw 10.0.0.10
[root@mysql-slave ~]
# route del default
[root@mysql-slave ~]
# route add default gw 10.0.0.10
2
、同步时间
[root@haproxy ~]
# yum -y install ntpdate.x86_64
[root@haproxy ~]
# ntpdate cn.ntp.org.cn
14
Aug
15
:48:21 ntpdate[1858]: adjust time server
203
.107.6.88 offset
-0
.000690 sec
[root@haproxy ~]
# date
2024
年
08
月
14
日 星期三
15
:48:30 CST
[root@haproxy ~]
# yum -y install ntp.x86_64
[root@haproxy ~]
# vim /etc/ntp.conf
15
行加入
restrict
10
.0.0.0 mask
255
.255.255.0
#
允许
10.0.0.0
网段的主机访问
[root@haproxy ~]
# systemctl start ntpd
[root@web01 ~]
# yum -y install ntpdate.x86_64
[root@web01 ~]
# ntpdate 10.0.0.35
14
Aug
15
:58:19 ntpdate[1654]: adjust time server
10
.0.0.35 offset
-0
.003274
sec
[root@web02 ~]
# yum -y install ntpdate.x86_64
[root@web02 ~]
# ntpdate 10.0.0.35
14
Aug
15
:58:19 ntpdate[1654]: adjust time server
10
.0.0.35 offset
-0
.003274
sec
3
、
web haproxy
高可用代理
(
1
)打开
web01
和
web02
[root@web01 ~]
#nginx
[root@web02 ~]
#nginx
(
2
)下载安装
haproxy
[root@haproxy ~]# yum -y install haproxy
[root@haproxy ~]# rpm -ql haproxy
(
3
)编辑
haproxy
配置文件,实现代理负载均衡
[root@haproxy ~]
# vim /etc/haproxy/haproxy.cfg
43
行
http
#
表示代理
http
服务
63
行
5000
改
80
#
将端口号
5000
改为
80
#67
行
use_backend #
没有配置静态服务器,直接注释
68
行
app
换
web
#
将默认
app
模块改为
web
87
行
backend web
#
编写
web
模块
balance roundrobin
server weba
10
.1.1.200:80 check
server webb
10
.1.1.201:80 check
[root@haproxy ~]
# systemctl status haproxy
[root@haproxy ~]
# systemctl start haproxy
[root@haproxy ~]
# systemctl enable haproxy
[root@haproxy ~]
# curl 10.1.1.30
web01
[root@haproxy ~]
# curl 10.1.1.30
web02
(
4
)编辑
haproxy
配置文件,添加统计页面
[root@haproxy ~]
# vim /etc/haproxy/haproxy.cfg
#
配置文件最后追加
listen statistics
bind *:9090
#
定义监听端口
mode http
#
默认使用协议
stats enable
#
启用
stats
stats uri /hadmin?stats
#
自定义统计页面的
url
stats auth admin:admin
#
统计页面的账号密码
stats hide-version
#
隐藏在统计页面上的
haproxy
版本信息
stats refresh 30s
#
统计页面自动刷新时间
stats admin
if
TRUE
#
如果认证通过就做管理功能,可以管理后端服务器
stats realm hapadmin
#
统计页面密码框上提示文件,默认为haproxy\statistic
[root@haproxy ~]
# systemctl restart haproxy.service
[root@haproxy ~]
# netstat -lntup | grep haproxy
tcp
0 0 0
.0.0.0:9090
0
.0.0.0:* LISTEN
1665
/haproxy
tcp
0 0 0
.0.0.0:80
0
.0.0.0:* LISTEN
1665
/haproxy
udp
0 0 0
.0.0.0:37365
0
.0.0.0:*
1664
/haproxy
![](https://i-blog.csdnimg.cn/direct/2c89c0302e6c401db1de24a093e00c90.png)
(
5
)编辑
haproxy
配置文件,添加权重功能
[root@haproxy ~]
# vim /etc/haproxy/haproxy.cfg
backend web
balance static-rr
server web01
10
.0.0.11:80 weight
3
check
server web02
10
.0.0.12:80 weight
1
check
[root@haproxy ~]
# systemctl restart haproxy
4
、
mysql haproxy
高可用代理
(
1
)打开数据库
[root@master-mysql ~]
# service mysql8 start
[root@mysql-slave ~]
# service mysql8 start
(
2
)编辑
haproxy
配置文件,实现代理负载均衡
[root@haproxy ~]
# vim /etc/haproxy/haproxy.cfg
43
行
mode tcp
67
行
# use_backend static if url_static
68
行
default_backend mysql
86
行
backend mysql
balance static-rr
server mysql01
10
.0.0.21:3306 weight
3
check
server mysql02
10
.0.0.22:3306 weight
1
check
[root@haproxy ~]
# systemctl restart haproxy
[root@haproxy ~]
# netstat -lntup | grep haproxy
tcp
0 0 0
.0.0.0:9090
0
.0.0.0:* LISTEN
2045
/haproxy
tcp
0 0 0
.0.0.0:3306
0
.0.0.0:* LISTEN
2045
/haproxy
udp
0 0 0
.0.0.0:54557
0
.0.0.0:*
2044
/haproxy
[root@mysql-slave ~]
# mysql -u10.0.0.35 -p'123'
mysql> show variables like
'server_id'
;
+
---------------
+
-------
+
| Variable_name | Value |
+
---------------
+
-------
+
| server_id |
10
|
+
---------------
+
-------
+
1
row
in set
(0.09 sec)
[root@mysql-slave ~]
# mysql -u10.0.0.35 -p'123'
mysql> show variables like
'server_id'
;
+
---------------
+
-------
+
| Variable_name | Value |
+
---------------
+
-------
+
| server_id |
11
|
+
---------------
+
-------
+
1
row
in set
(0.09 sec)
下午
mysql
>
help change master to
#
查看连接
master
的帮助信息
1
、
python
代码实现
mysql
的读写分离
(
1
)安装
pymysql
是
python
管理
mysql
的驱动,或者成为连接器
[root@python ~]
# pip3 config set global.index-url
https://pypi.tuna.tsinghua.edu.cn/simple
[root@python ~]
# pip3 install pymysql #
安装
pymysql
(
2
)在
python3
的命令行界面引入
pymysql
[
root
@
python
~
]
# mysql3
>>>
import
pymysql
(
3
)创建两个
connenction
对象,一个指向
master mysql
,一个指向
slave
mysql
master_conn
=
pymysql
.
connect
(
host
=
"10.0.0.21"
,
user
=
"li"
,
port
=
3306
,
database
=
"tes
t"
,
password
=
"li"
)
slave_conn
=
pymysql
.
connect
(
host
=
"10.0.0.22"
,
user
=
"li"
,
port
=
3306
,
database
=
"test
"
,
password
=
"li"
)
(
4
)获取数据游标
>>>
master_cursor
=
master_conn
.
cursor
()
(
5
)执行查询
>>>
select_sql
=
"select * from student"
>>>
master_cursor
.
execute
(
select_sql
)
3
>>>
master_cursor
.
fetchall
()
((
1001
,
'
孙颖莎
'
,
'
云计算
2
班
'
,
'1002'
), (
1002
,
'
大头
'
,
'
云计算
2
班
'
,
'1003'
),
(
1003
,
'
小胖
'
,
'
云计算
2
班
'
,
'1004'
))
![](https://i-blog.csdnimg.cn/direct/11dd0f7c83314fbf8e70f93f13cf8085.png)
(
6
)执行修改
>>>
update_sql
=
"update student set name='
马龙
' where id=1002"
>>>
master_cursor
.
execute
(
update_sql
)
1
>>>
master_conn
.
commit
()
![](https://i-blog.csdnimg.cn/direct/b780641e2e4640659ff8523f43854410.png)
(
7
)执行删除
>>>
delete_sql
=
"delete from student where name='
小胖
'"
>>>
master_cursor
.
execute
(
delete_sql
);
1
>>>
master_conn
.
commit
()
![](https://i-blog.csdnimg.cn/direct/ffe6e34a72b24fd1b8646accfeb22ba4.png)
(
8
)执行新增
>>>
insert_sql
=
"insert into student values(1004,'
张翔
','
云计算
2
班
','1004')"
>>>
master_cursor
.
execute
(
insert_sql
)
1
>>>
master_conn
.
commit
()
![](https://i-blog.csdnimg.cn/direct/3968da0e04414e8583318267b921a1f2.png)
(
9
)执行查询
(slave)
>>>
slave_cursor
=
slave_conn
.
cursor
()
>>>
sql
=
"select * from student"
>>>
slave_cursor
.
execute
(
sql
)
3
>>>
slave_cursor
.
fetchall
()
((
1001
,
'
孙颖莎
'
,
'
云计算
2
班
'
,
'1002'
), (
1002
,
'
马龙
'
,
'
云计算
2
班
'
,
'1003'
),
(
1004
,
'
张翔
'
,
'
云计算
2
班
'
,
'1004'
))
![](https://i-blog.csdnimg.cn/direct/37f25540f37440c2bdcda0c574a198b6.png)
2
、
python
脚本实现
mysql
的读写分离
[
root
@
python
~
]
# vim rwpython.py
class
rwsplit
(
object
):
def
__init__
(
self
):
print
(
"initialize"
)
def
master_statment
(
self
,
sql
):
pass
def
slave_statment
(
self
,
sql
):
pass
if
__name__
==
"__main__"
:
demo
=
rwsplit
()
sql
=
input
(
"sign sql:"
)
if
sql
[:
6
]
==
"select"
:
demo
.
slave_statment
(
sql
)
else
:
demo
.
master_statment
(
sql
)