实训日记day28

haproxy安装与部署

​1.安装

[root@haproxy ~]# yum -y install ntpdate.x86_64 

[root@haproxy ~]# yum -y install ntp 

[root@haproxy ~]# ntpdate cn.ntp.org.cn 13 Aug 19:39:27 ntpdate[1955]: adjust time server 120.197.116.202 offset 0.059032 sec 

[root@haproxy ~]# systemctl start ntpd 

[root@haproxy ~]# systemctl enable ntpd 

[root@haproxy ~]# yum -y install haproxy18.x86_64

2.配置

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

60 #----------------------------------------------------- ---------------- 

61 # main frontend which proxys to the backends 

62 #----------------------------------------------------- ---------------- 

63 frontend main *:80 

64 acl url_static path_beg -i /static /images /javascript /stylesheets 

65 acl url_static path_end -i .jpg .gif .png .css .js 

66 

67 # use_backend static if url_static 

68 default_backend web

77 #---------------------------------------------------- -----------------

78 # round robin balancing between the various backends 

79 #----------------------------------------------------- ---------------- 

80 backend web 

81 balance roundrobin 

82 server web01 10.1.1.200:80 check 

83 server web02 10.1.1.201:80 check

3.重启，设置开机自启

[root@haproxy ~]# systemctl restart haproxy 

[root@haproxy ~]# systemctl enable haproxy

4.测试

[root@dns ~]#vim /var/named/el.zzj.zone

[root@dns ~]# systemctl restart named

客户机测试

5.添加统计页面

常见错误

1.503错误，503service unavaliable

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

1. 两台服务有一台异常 

2. 网关没有清除

3.9090无效，写错了 

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

HAProxy负载均衡双主mysql数据库

1.修改见配置文件

​42 defaults 

43 mode tcp 

44 log global 

45 option httplog 

46 option dontlognull 

...... 

63 frontend main *:3306 

64 acl url_static path_beg -i /static /images /javascript /stylesheets 

65 acl url_static path_end -i .jpg .gif .png .css .js 

66 

67 # use_backend static if url_static 

68 default_backend mysql 

.... 

85 backend mysql 

86 balance roundrobin 

87 server master 10.1.1.11:3306 check 

88 server slave 10.1.1.12:3310 check

2.测试

[root@client bin]# ./mysql -h10.1.1.30 -P3306 -uzhangmin - pzhangmin

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19

Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'server_id'; 

+---------------+-------+ 

| Variable_name | Value | 

+---------------+-------+ 

| server_id     | 10    | 

+---------------+-------+ 

1 row in set (0.00 sec)

mysql> exit 

Bye

[root@client bin]# ./mysql -h10.1.1.30 -P3306 -uzhangmin - pzhangmin

mysql> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id           |   11    |

+---------------+-------+

1 row in set (0.01 sec)

mysql> exit

Bye

[root@client bin]# cd bin/

python管理mysql

方法一：

pip3 config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple

yum -y install sqlalchemy

pip3 install pandas

import pandas as pd

from sqlalchemy import create_engine

class Python_Mysql(object):

            def __init__(self):

                    print("test")

            def getEngine(seft):

                    host=input("sign mysql server host:")

                    username=input("sign mysql username:")

                    password=input("sign mysql password:")

                    databasename=input("sign database name:")

                    port=input("sign mysql port:")

engine=create_engine(f"mysql+pymysql://{use rname}:{password}@{host}:

{port}/{databasename}")

                    return engine

            def querySql(self,conn):

                    sql=input("sign your sql:")

                    return

pd.read_sql(sql=sql,con=conn)

if __name__=="__main__":

            demo=Python_Mysql()

            #sql=input("sign sql:")

            # sql="select * from user"

            rs=demo.querySql(demo.getEngine())

            print(rs)

方法二：

1. 设置清华镜像站（从国内下载安装包，提高下载和安 装速度）

2. 安装pandas数据分析工具(pandas是知名的数据分析 工具，pandas有完整的读取数据的工具，以及 DateFrame数据框架，用于保存从数据库中读取的数 据)

3. 安装pymysql连接器（oracle为开发者提供的python 管理mysql的工具，通过这个工具，就恶意在不替原 有代码的情况下，应对数据库软件的升级）

​pip3 config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple 

yum -y install pandas

yum -y install pymysql

mysql的主从复制

主：

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. 配置文件

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';

从：

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. 配置文件

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; 

3. show master status\G;

设置从数据库 help change master to

1. change master to

2. 启动slave并且查看状态

start slave; 

show slave status\G

3. .master => unlock tables;

python代码的读写分离

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

pip3 install pymysql

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

import pymysql

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

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'))