mysql ——读写分离

程序员文章站 2022-03-08 17:13:20

...

一、实验环境：

server1：master 172.24.54.1

server2：slave 172.25.54.2 主从复制

server3：mysql-proxy代理 172.25.54.3

此实验在mysql的主从复制的基础上进行, 需要配备好主从的环境

二、配置server3

[root@server3 ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 
mysql-proxy-0.8.5-linux-el6-x86-64bit  mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server3 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
[root@server3 ~]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# ls
bin  include  lib  libexec  licenses  share
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# mkdir logs
[root@server3 mysql-proxy]# vim ~/.bash_profile 
[root@server3 mysql-proxy]# cat ~/.bash_profile  
//修改第10行
 10 PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
[root@server3 mysql-proxy]# source ~/.bash_profile 
[root@server3 mysql-proxy]# vim conf/mysql-proxy.conf
[mysql-proxy]
user=root    //运行mysql-proxy进程的用户
proxy-address=0.0.0.0:3306  //监听本机所有地址的3306端口
proxy-backend-addresses=172.25.54.1:3306    //backend主   注意addresses
proxy-read-only-backend-addresses=172.25.54.2:3306   //backend从
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
//lua脚本地址
log-file=/usr/local/mysql-proxy/logs/mysql-proxy   //proxy日志路径
log-level=debug   //日志级别
daemon=true     //打入后台
keepalive=true   //在mysql-proxy崩溃时尝试重启之
[root@server3 mysql-proxy]# chmod 660 conf/mysql-proxy.conf 
[root@server3 mysql-proxy]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
//修改最大最小链接数，最大链接两个, 超过则会读写分离
 38 if not proxy.global.config.rwsplit then
 39         proxy.global.config.rwsplit = {
 40                 min_idle_connections = 1,  
 41                 max_idle_connections = 2,
 42 
 43                 is_debug = false
 44         }
[root@server3 mysql-proxy]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
//启动mysql-proxy
[root@server3 mysql-proxy]# netstat -antpl

mysql ——读写分离

主库授权

mysql> grant all on *.* to 'root'@'%' identified by 'aaa@qq.com#';
Query OK, 0 rows affected, 1 warning (0.37 sec)

三、测试

物理机

//客户端1
[root@foundation54 ~]# mysql -h 172.25.54.3 -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)] select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
+----------+----------+
1 row in set (0.00 sec)

MySQL [(none)] insert into test.userlist values ('user2','258');
Query OK, 1 row affected (0.34 sec)

MySQL [(none)] select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
+----------+----------+
2 rows in set (0.00 sec)

server1

mysql> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
+----------+----------+
2 rows in set (0.00 sec)

server2

mysql> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
+----------+----------+
2 rows in set (0.00 sec)

server1、server2

yum install -y lsof

//此时只有一个客户端开启
[root@server1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2367 mysql   33u  IPv6  11154      0t0  TCP server1:mysql->server2:51784 (ESTABLISHED)
mysqld  2367 mysql   34u  IPv6  11108      0t0  TCP *:mysql (LISTEN)
mysqld  2367 mysql   62u  IPv6  11186      0t0  TCP server1:mysql->server3:44796 (ESTABLISHED)
[root@server1 ~]#

[root@server2 ~]# yum install -y lsof
[root@server2 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2841 mysql   32u  IPv6  11743      0t0  TCP *:mysql (LISTEN)
mysqld  2841 mysql   60u  IPv4  11809      0t0  TCP server2:51784->server1:mysql (ESTABLISHED)
[root@server2 ~]#

物理机

此时增加一个客户端链接——客户端2

//客户端2
[root@foundation54 ~]# mysql -h 172.25.54.3 -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
+----------+----------+
2 rows in set (0.00 sec)

MySQL [(none)]> insert into test.userlist values ('user3','369');
Query OK, 1 row affected (0.15 sec)

MySQL [(none)]> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
| user3    | 369      |
+----------+----------+
3 rows in set (0.00 sec)

server1、server2

//server1、server2
mysql> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
| user3    | 369      |
+----------+----------+
3 rows in set (0.00 sec)
//客户端1
MySQL [(none)]> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
| user3    | 369      |
+----------+----------+
3 rows in set (0.00 sec)

[root@server1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2367 mysql   33u  IPv6  11154      0t0  TCP server1:mysql->server2:51784 (ESTABLISHED)
mysqld  2367 mysql   34u  IPv6  11108      0t0  TCP *:mysql (LISTEN)
mysqld  2367 mysql   50u  IPv6  11458      0t0  TCP server1:mysql->server3:44797 (ESTABLISHED)
mysqld  2367 mysql   62u  IPv6  11186      0t0  TCP server1:mysql->server3:44796 (ESTABLISHED)
[root@server1 ~]#

[root@server2 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2841 mysql   32u  IPv6  11743      0t0  TCP *:mysql (LISTEN)
mysqld  2841 mysql   60u  IPv4  11809      0t0  TCP server2:51784->server1:mysql (ESTABLISHED)
[root@server2 ~]#

物理机

此时增加一个客户端链接——客户端3

//客户端3
[root@foundation54 ~]# mysql -h 172.25.54.3 -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
| user3    | 369      |
+----------+----------+
3 rows in set (0.00 sec)

server1、server2

[root@server1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2367 mysql   33u  IPv6  11154      0t0  TCP server1:mysql->server2:51784 (ESTABLISHED)
mysqld  2367 mysql   34u  IPv6  11108      0t0  TCP *:mysql (LISTEN)
mysqld  2367 mysql   50u  IPv6  11458      0t0  TCP server1:mysql->server3:44797 (ESTABLISHED)
mysqld  2367 mysql   62u  IPv6  11186      0t0  TCP server1:mysql->server3:44796 (ESTABLISHED)
[root@server1 ~]#

[root@server2 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2841 mysql   32u  IPv6  11743      0t0  TCP *:mysql (LISTEN)
mysqld  2841 mysql   48u  IPv6  12507      0t0  TCP server2:mysql->server3:47313 (ESTABLISHED)
mysqld  2841 mysql   60u  IPv4  11809      0t0  TCP server2:51784->server1:mysql (ESTABLISHED)
[root@server2 ~]#

客户端3是与从库server2相连接的

在客户端3上写入，然后在server1即masetr*问

//客户端3
MySQL [(none)]> insert into test.userlist values ('user4','666');
Query OK, 1 row affected (0.09 sec)

MySQL [(none)]> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
| user3    | 369      |
| user4    | 666      |
+----------+----------+
4 rows in set (0.00 sec)

server1

mysql> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 147      |
| user2    | 258      |
| user3    | 369      |
| user4    | 666      |
+----------+----------+
4 rows in set (0.00 sec)

虽然客户端3是与从库server2相连接的, 但是当在客户端3写入时，还是写入到了master上

yum install -y tcpdump 抓包工具, 可以在proxy端安装，抓包查看

mysql ——读写分离

一、实验环境：

server1：master 172.24.54.1

server2：slave 172.25.54.2 主从复制

server3：mysql-proxy代理 172.25.54.3

二、配置server3

主库授权

三、测试

物理机

server1

server2

server1、server2

物理机

此时增加一个客户端链接——客户端2

server1、server2

物理机

此时增加一个客户端链接——客户端3

server1、server2

客户端3是与从库server2相连接的

在客户端3上写入，然后在server1即masetr*问

server1

mysql主从服务器同步心得体会第1/2页

Oracle与Mysql主键、索引及分页的区别小结

C#实现redis读写的方法

删除mysql数据库中的重复数据记录

VS2017调用MySQL 8.0的方法

详解MySQL中DROP,TRUNCATE 和DELETE的区别实现mysql从零开始

ms sql server中实现的unix时间戳函数（含生成和格式化，可以和mysql兼容）

PHP和MySql中32位和64位的整形范围是多少

提高MySQL 查询效率的三个技巧第1/2页

vs2015中mysql.h文件打不开的解决办法