haproxy负载均衡及主从MySQL读写分离

lvs调度服务器
[root@localhost ~]# ipvsadm -A -t 192.168.1.46:3306 -s rr
[root@localhost ~]# ipvsadm -a -t 192.168.1.46:3306 -r 192.168.1.21:3306 -m
[root@localhost ~]# ipvsadm -a -t 192.168.1.46:3306 -r 192.168.1.22:3306 -m
MySQL服务器
[root@localhost ~]# route del default
[root@localhost ~]# route add default gw 192.168.1.46

2024/8/14
haproxy负载均衡
[root@haproxy ~]# yum -y install ntpdate
[root@haproxy ~]# ntpdate cn.ntp.org.cn
[root@haproxy ~]# yum -y install ntp
[root@haproxy ~]# systemctl start ntpd
[root@haproxy ~]# systemctl enable ntpd
[root@haproxy ~]# yum -y install haproxy
[root@haproxy ~]# vim /etc/haproxy/haproxy.cfg 
frontend  main *:80                            63修改
 # use_backend static          if url_static   67注释
 default_backend             web               68修改
backend web                                  
        balance         roundrobin             87添加
        server  weba    192.168.1.40:80 check
        server  webb    192.168.1.41:80 check
[root@haproxy ~]# systemctl start haproxy
[root@haproxy ~]# systemctl enable haproxy
[root@haproxy ~]# curl 192.168.1.50
web1
[root@haproxy ~]# curl 192.168.1.50
web2
[root@haproxy ~]# vim /etc/haproxy/haproxy.cfg（配置文件时注释最好删掉）
#定义web管理界面
 listen statistics
 93         bind *:9090 #定义监听端口
 94         mode http #默认使用协议
 95         stats enable #启用stats
 96         stats uri /hadmin?stats #自定义统计页面的url
 97         stats auth admin:admin #统计页面的账号密码
 98         stats hide-version #隐藏在统计页面上的haproxy版本信息
100         stats refresh 30s #统计页面自动刷新时间
101         stats admin if TRUE #如果认证通过就做管理
102         stats realm hapadmin #统计页面密码框上提示文件，默认为haproxy\statistics
[root@haproxy ~]# systemctl restart haproxy
浏览器访问：http://192.168.1.50:9090/hadmin?stats
加权
[root@haproxy ~]# vim /etc/haproxy/haproxy.cfg
 87 backend web
 88         balance         static-rr
 89         server  weba    192.168.1.40:80 weight 8 check
 90         server  webb    192.168.1.41:80 weight 2 check
systemctl restart haproxy
haproxy负载均衡MySQL
vim /etc/haproxy/haproxy.cfg
43 mode tcp
63 frontend main *:3306
67 # use_backend static if url_static
68 default_backend mysql
85 backend mysql
86 balance roundrobin
87 server master 192.168.1.21:3306 check
88 server slave 192.168.1.22:3310 check
systemctl restart haproxy
测试
[root@client bin]# ./mysql -h192.168.1.34 -P3306 -uzhangmin -pzhangmin
mysql> show variables like 'server_id';

python3
[root@localhost ~]# yum -y install python3
[root@localhost ~]#  pip3 config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple
[root@localhost ~]# pip3 install pymysql
[root@localhost ~]# python3
>>> import pymysql
主MySQL：
mysql> SELECT host FROM mysql.user WHERE user = 'zhangmin';
mysql> GRANT ALL PRIVILEGES ON test.* TO 'zhangmin'@'%';
mysql> FLUSH PRIVILEGES;

3.创建两个connenction对象，一个指向mastermysql，一个指向slave msyql
>>> master_conn=pymysql.connect(host="192.168.1.21",user="zhangmin",password="zhangmin",database="test",port=3306)
>>> slave_conn=pymysql.connect(host="192.168.1.22",user="zhangmin",password="zhangmin",database="test",port=3310)
4. 获取数据游标 master
master_cursor=master_conn.cursor()
5. 执行查询 master
select_sql="select * from user";
master_cursor.execute(select_sql);
rs=cursor.fetchall()
6. 执行修改 master
>>> updatesql="update user set password='000' where username='aaa'"
>>> master_cursor.execute(updatesql)
7. 执行删除 master
>>> master_conn.commit()
>>> delete_sql="delete from user where username='aaa'"
>>> master_cursor.execute(delete_aql)
master_conn.commit()
8. 执行新增 master
insert_sql="insert into user values(1004,'dddddd','ddddddd')"
master_cursor.execute(insert_sql);
master_conn.commit()
9. 执行查询 slave
>>> # 执行查询 获得获得slave 游标
...
>>> slave_cursor=slave_conn.cursor()
>>> sql
'select * from user'
>>> slave_cursor.execute(sql)
3
>>> slave_cursor.fetchall()
((2,'bbb','bbbb'), (3,'ccc','cccc'),(1004,'ddddd','ddddddd'))