haproxy负载均衡双主MySQL数据库及基于代码基层的读写分离

一、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/

二、基于代码基层的读写分离

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

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