Day 23
Haproxy的配置及监控
# 环境准备
haproxy(192.168.2.212) web1(192.168.2.200) web2(192.168.2.201)
======web主机的配置======
[root@web1 ~]# systemctl stop firewalld
[root@web1 ~]# setenforce 0
[root@web1 ~]# nginx
[root@web2 ~]# systemctl stop firewalld
[root@web2 ~]# setenforce 0
[root@web2 ~]# nginx
======haproxy主机的配置======
# 基础配置
[root@haproxy01 ~]# systemctl stop firewalld
[root@haproxy01 ~]# systemctl disable firewalld
[root@haproxy01 ~]# setenforce 0
[root@haproxy01 ~]# yum -y ntpdate
[root@haproxy01 ~]# ntpdate cn.ntp.org.cn
14 Aug 10:15:36 ntpdate[1527]: adjust time server 203.107.6.88 offset 0.015370 sec
[root@haproxy01 ~]# yum -y install ntp
[root@haproxy01 ~]# systemctl start ntpd
[root@haproxy01 ~]# systemctl enable ntpd
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.
#安装haproxy
[root@haproxy01 ~]# yum -y install haproxy
# 修改配置文件
[root@haproxy01 ~]# rpm -ql haproxy
/etc/haproxy
/etc/haproxy/haproxy.cfg
[root@haproxy01 ~]# vim /etc/haproxy/haproxy.cfg
63 frontend main *:80
67 #use_backend static if url_static # (静态资源服务器)
68 default_backend web #(默认的后端服务)
# 轮询
86 backend web
87 balance roundrobin
88 server web1 192.168.2.200:80 check
89 server web2 192.168.2.201:80 check
# 加权轮询
86 backend web
87 balance static-rr
88 server web1 192.168.2.200:80 weight 8 check
89 server web2 192.168.2.201:80 weight 2 check
# 添加内容，用于定义web管理界面
listen statistics
bind *:9090 #定义监听端口
mode http #默认使用协议
stats enable #启用stats
stats uri /hadmin?stats #自定义统计页面的url
stats auth admin:admin #统计页面的账号密码
stats hide-version #隐藏在统计页面上的
stats refresh 30s #统计页面自动刷新时间
stats admin if TRUE #如果认证通过就做管理功能，可以管理后端服务器
stats realm hapadmin #统计页面密码框上提示文件，默认为haproxy\statistics
[root@haproxy01 ~]# systemctl status haproxy
● haproxy.service - HAProxy Load Balancer
Loaded: loaded (/usr/lib/systemd/system/haproxy.service; disabled; vendor preset: disabled)
Active: inactive (dead)
[root@haproxy01 ~]# systemctl start haproxy
[root@haproxy01 ~]# systemctl enable haproxy
Created symlink from /etc/systemd/system/multi-user.target.wants/haproxy.service to /usr/lib/systemd/system/haproxy.service.
=======测试========
# 访问代理主机发现web1和web2轮流提供服务并测试监控效果
[root@haproxy01 ~]# curl 192.168.2.212
i am web1
[root@haproxy01 ~]# curl 192.168.2.212
i am web2
HAProxy负载均衡双主mysql数据库
# 环境说明
mysql1(192.168.2.209 ) mysql2(192.168.2.210)
1.修改配置文件
[root@hap01 ~]# vim /etc/haproxy/haproxy.cfg
defaults
mode tcp
#use_backend static if url_static
default_backend mysql
87 backend mysql
88 balance roundrobin
89 server mysql1 192.168.2.209 check
90 server mysql2 192.168.2.210 check
91 listen statistics
92 bind *:9090 #定义监听端口
93 mode http #默认使用协议
94 stats enable #启用stats
95 stats uri /hadmin?stats #自定义统计页面的url
96 stats auth admin:admin #统计页面的账号密码
97 stats hide-version #隐藏在统计页面上的
98 stats refresh 30s #统计页面自动刷新时间
99 stats admin if TRUE #如果认证通过就做管理功能，可以管理后端服务器
100 stats realm hapadmin #统计页面密码框上提示文件，默认为haproxy\statistics
2.mysql-master、mysql-slave测试
[root@mysql1 bin]# ./mysql -h192.168.2.214 -P3306 -uhuajuan -phuajaun
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 10 |
+---------------+-------+
1 row in set (0.00 sec)
[root@mysql2 bin]# ./mysql -h192.1682.213 -P3306 -uhuajuan -phuajuan
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 11 |
+---------------+-------+
1 row in set (0.01 sec)
基于python代码层级的读写分离
# 思路：首先实现mysql的主从复制，然后yongpython管理mysql实现代码层级的读写分离。
====mysql的主从复制=====
# master的配置步骤
1.删除my.cnf文件
rm -rf /etc/my.cnf
2. mysql glibc绿色版本，下载解压（先解压.tar文件，得到.tar.xz包）
tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar
tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
3. 将解压后的文件移动的指定的/usr/local/mysql
mkdir -p /usr/local/mysql/
cp -r mysql-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysql
4. 在/usr/local/mysql/目录下创建mysql-files目录
mkdir /usr/local/mysql/mysql-files
5. 创建账户，在此之前查看是否有账户
useradd -r -s /sbin/nologin mysql
6. 修改mysql-files所属组和属主都是mysql
chown mysql:mysql /usr/local/mysql/mysql-files
7. 修改mysql-files的权限为750
chmod 750 /usr/local/mysql/mysql-files
8. /usr/local/mysql/bin/mysqld --initialize --
user=mysql --basedir=/usr/local/mysql/
9.查看data目录和初始密码
/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
ls /usr/local/mysql/
10.配置文件
[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
12.把mysql.server文件放到/etc/init.d/目录下，方便使用service mysql start命令启动
cp /usr/local/mysql/suport-files/msyql.server
/etc/init.d/mysql8
13.配置环境变量
sed -i '$aexport PATH=$PATH:/usr/local/mysql/bin' /etc/profile
source /etc/profile
14.设置远程登录
mysql -h192.168.2.209 -P3306 -uhuajaun -phuajuan
15.创建用户
create user 'aaaa'%'aaaa' identified by 'sn'
16.授权给用户
grant all on . to 'aaaa';
# slave的配置步骤
1.rm -rf /etc/my.cnf
2. glibc,下载解压
3. 将解压后的文件移动的指定的/usr/local/mysql
4. mkdir /usr/local/mysql/mysql-files
5. useradd -r -s /sbin/nologin mysql
6. chown mysql:mysql /usr/local/mysql/mysql-files
7. chmod 750 /usr/local/mysql/mysql-files
8. 配置文件
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3310
log_error=/usr/local/mysql/data/mysql.log
relay_log=/usr/local/mysql/data/relaylog
server-id=11
character_set_server=utf8mb4
9.cp /usr/local/mysql/suport-files/msyql.server /etc/init.d/mysql8
# 同步数据
1. yum -y install rsync
2. service mysql8 stop
3. master=> rm -rf /usrlocal/mysql/data/auto.cnf
4. rsync -av /usr/local/mysql/data
root@slaveip:/usr/loca/mysql
5. salve=>service mysql8 start
6. master=>service msyql8 start
# 设置主数据库
1. 创建远程slave账号
create user 'slave'@'%' identified by'slave';
grant replication slave on *.* to'slave'%'%';
flush privileges;
2.刷新表
flush tables with read lock;
show master status\G; # 查看文件名称和文件位置
# 设置从数据库
help change master to
1. change master to
change master to
MASTER_HOST = '10.1.1.11',
MASTER_USER = 'slave',
MASTER_PASSWORD = 'slave',
MASTER_PORT = 3306,
MASTER_LOG_FILE = 'binlog000006',
MASTER_LOG_POS = 873,
GET_MASTER_PUBLIC_KEY = 1;
2.启动slave并且查看查看主从同状态
start slave;
show slave status\G
# 在master服务器先解锁，然后创建表，插入数据
mysql> unlock tables;
====安装pymysql=====
1、安装pymysql，其是python管理mysql的驱动或称为管理器
pip3 install pymysql
2、在python3的命令行界面引入pymysql
>>> import pymysql
3、创建两个connection对象，一个指向master mysql，一个指向slave mysql
>>>master_conn=pymysql.connect(host="192.168.2.214",user="huajuan",password="huajuan",database="test",port=3306);
>>>slave_conn=pymysql.connect(host="192.168.2.215",user="huajuan",password="huajuan",database="test",port=3310);
4、获取数据游标
master_cursor=master_conn.cursor()
5、进行管理（增删改查）
# 增删改(insert delete update)使用master_conn
1)查询(rs输出即为查询结果)：
>>>select_sql='select * from user';
>>>master_cursor.execute(sql)
3
>>>res=master_cursor.fetchall()
((1, 'aaa', 'aaaa'), (2, 'bbb', 'bbbb'), (3, 'ccc', 'cccc'))
2)修改(需用master_conn.commit提交事务即可生效)：
账号为aaa的用户密码修改为000
>>> update_sql="update user set password='000' where username='aaa'"
>>> master_cursor.execute(update_sql)
>>> master_conn.commit()
3)删除：
删除用户名为aaa的记录
>>>delete_sql= 'delete from user where username="aaa"'
>>>master_cursor.execute(delete_sql)
>>>master_conn.commit()
4)新增：
>>>insert_sql="insert into user values(1004, 'tom', 'tom')"
>>>master_cursor.execute(insert_sql)
>>>master_conn.commit()
结论：查询不需要commit
# 查询(select)使用slave_conn
1)获取数据游标
slave_cursor=slave_conn.cursor()
2)执行查询语句
>>>select_sql='select * from user';
>>>slave_cursor.execute(sql)
3
>>>res=slave_cursor.fetchall()
((1, 'aaa', 'aaaa'), (2, 'bbb', 'bbbb'), (3, 'ccc', 'cccc'))
6、编写python代码实现自动化
class rwsplit(object):
def __init__ (self):
print("initialized")
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)
扫一扫
6974
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
