haproxy负载均衡、mysql主从复制的读写分离（代码层级）

目录

1.haproxy负载均衡

1.haproxy介绍

2.安装配置 

1.时间同步

2.安装haproxy

3.配置/etc/haproxy/haproxy.cfg文件

4.启动、设置开机自动

5.测试

6.添加统计页面

3.常见错误

2.MySQL主从复制及基于代码层级的读写分离 

1.mysql主从复制

1.master

2.slave

3.数据同步

4.设置主数据库

5.设置从数据库

2.python代码的读写分离

---

1.haproxy负载均衡

1.haproxy介绍

官网地址： https://www.haproxy.com/

 HAProxy是一个使用C语言编写的自由及开放源代码软件，其提供高可用性、负载均衡，以及基于TCP和HTTP的应用程序代理。

2.安装配置 

1.时间同步

安装时间服务，同步时间，设置开机自启

[root@haproxy ~]# yum -y install ntpdate
[root@haproxy ~]# yum -y install ntp
[root@haproxy ~]# systemctl start ntpd
[root@haproxy ~]# systemctl enable ntpd
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.
[root@haproxy ~]# ntpdate cn.ntp.org.cn
14 Aug 14:39:09 ntpdate[2165]: adjust time server 203.107.6.88 offset 0.002888 sec

2.安装haproxy

[root@haproxy ~]# yum -y install haproxy
[root@haproxy ~]# rpm -ql haproxy

 

3.配置/etc/haproxy/haproxy.cfg文件

[root@haproxy ~]# vim /etc/haproxy/haproxy.cfg 

 

4.启动、设置开机自动

[root@haproxy ~]# systemctl start haproxy.service
[root@haproxy ~]# systemctl enable haproxy.service
Created symlink from /etc/systemd/system/multi-user.target.wants/haproxy.service to /usr/lib/systemd/system/haproxy.service.

5.测试

6.添加统计页面

# 定义web管理界面
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\statistics

3.常见错误

1.503错误，503service unavaliable

2.请求服务间歇性报错，一会儿正常，一会儿不正常

1. 两台服务有一台异常

2. 网关没有清除

3.9090无效，写错了

4.haproxy无法正常启动，查看配置文件是否异常

2.MySQL主从复制及基于代码层级的读写分离 

1.mysql主从复制

1.master

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. /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/

9. 查看data目录和初始密码

10. /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

11. 配置文件

[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.cp /usr/local/mysql/suport-files/msyql.server

/etc/init.d/mysql8

13.service mysql8 start

14.sed -i '$aexport

PATH=$PATH:/usr/local/mysql/bin' /etc/profile

15.source /etc/profile

16.mysql -h10.1.1.11 -P3306 -uzhangmin -

pzhangmin

17.create user 'aaaa'%'aaaa' identified by 'sn'

18.grant all on . to 'aaaa';

2.slave

shell脚本：

#!/bin/bash
yum list installed |grep libaio
if [ $? ne 0 ]; then
    yum -y install libaio
fi
echo libaio yes
rm -rf /etc/my.cnf
echo remo my.cnf yes

tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
echo tar zx yes

cp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql
echo copy file to /usr/local/mysql   yes

mkdir /usr/local/mysql/mysql-files
echo mysql-files yes

grep mysql /etc/passwd

    useradd -r -s /sbin/nologin mysql

chown mysql:mysql /usr/local/mysql/mysql-files
chmod 750 /usr/local/mysql/mysql-files

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile
source /etc/profile

配置文件

 vim /usr/local/mysql/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

port=3310

logerror=/usr/local/mysql/data/mysql.log

relaylog=/usr/local/mysql/data/relaylog

server-id=11

character_set_server=utf8mb4

3.数据同步

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

4.设置主数据库

1. 创建远程slave账号

create user 'slave'@'%' identified by 'slave';
grant replication slave on *.* to 'slave'%'%';
flush privileges;

2. flush tables with read lock;

3. show master status\G;

        1. 文件名称

        2. 文件位置

5.设置从数据库

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

3. master => unlock tables;

2.python代码的读写分离

1.安装pymysql 是python管理mysql的驱动，或者成为连接器

pip3 install pymysql

2.在python3的命令行界面引入pymysql

import pymysql

3.创建两个connenction对象，一个指向master mysql，一个指向slave msyql

master_conn=pymysql.connect(host="10.1.1.11",user="zhangmin",password="zhangmin",port=3306,database="test");
slave_conn=pymysql.connect(host="10.1.1.12",user="zhangmin",password="zhangmin",port=3310,database="test");

4. 获取数据游标 master

master_cursor=master_conn.cursor()

5. 执行查询 master

select_sql="select * from user";
master_cursor.execute(select_sql);
rs=cursor.fetchall()

6. 执行修改 master

update_sql="update user set password='000' where username='aaaa'"
master_cursor.execute(update_sql)
master_conn.commit()

7. 执行删除 master

delete_sql="delete from user where username='aaaa'"
master_cursor.execute(delete_sql)
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'))