(一)安装
[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
(二)配置
[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
84
(三)重启,设置开机启动
[root@haproxy ~]# systemctl restart haproxy
[root@haproxy ~]# systemctl enable haproxy
(四)测试
(五)添加统计页面
# 定义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
(六)常见的错误
1.503错误,503service unavaliable
2.请求服务间歇性报错,一会儿正常,一会儿不正常
3.9090无效,写错了
4.haproxy无法正常启动,查看配置文件是否异常
(七)常见的调度算法
权重修改
backend web
balance static-rr
server web01 10.1.1.200:80 weight 8 check
server web02 10.1.1.201:80 weight 2 check
(八)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: [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 25
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 | 11 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> exit
Bye
[root@client bin]# cd bin/
(九)python管理mysql
1.方法一
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)
2.方法二
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
>>> import pandas as pd
>>> import pymysql
>>> conn=pymysql.connect(
... host='10.1.1.100'
,
... user='zhangmin'
,
... password='zhangmin'
,
... database='test'
,
... port=3306
... )
>>> conn
<pymysql.connections.Connection object at
0x7f9e24ba2c88>
>>> cursor=conn.cursor()
>>> cursor
<pymysql.cursors.Cursor object at
0x7f9e24ba2668>
>>> sql="select * from user"
>>> cursor.excute(sql)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
AttributeError: 'Cursor' object has no
attribute 'excute'
>>> cursor.execute(sql)
3
>>> cursor.description
(('id'
, 3, None, 11, 11, 0, False),
('username'
, 253, None, 180, 180, 0, False),
('password'
, 253, None, 180, 180, 0, False))
>>> desc=cursor.description
>>> res
3
>>> res=cursor.fetchall()
>>> res
((1,
'aaa'
,
'123'), (8,
'baba'
,
'pipi'),
(20,
'aaaaaaaa'
,
'bbbbbbbb'))
>>> desc
(('id'
, 3, None, 11, 11, 0, False),
('username'
, 253, None, 180, 180, 0, False),
('password'
, 253, None, 180, 180, 0, False))
>>> [item[0] for item in desc]
['id'
,
'username'
,
'password']
>>> col=[item[0] for item in desc]
>>> df=pd.DataFrame(res,columns=col)
>>> df
id username password
0 1 aaa 123
1 8 baba pipi
2 20 aaaaaaaa bbbbbbbb
(十)基于代码层级的读写分离
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
(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
logerror=/usr/local/mysql/data/mysql.log
relaylog=/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
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.
设置从数据库
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
3. master => unlock tables;
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
=
33
06
,
database
=
"test"
);
slave_conn
=
pymysql
.
connect
(
host
=
"10.1.1.12"
,
user
=
"zhangmin"
,
password
=
"zhangmin"
,
port
=
331
0
,
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'))