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