mysql基于mysql-proxy的读写分离

川长思鸟来 2022-02-02 05:09 186阅读 0赞

此次环境基于之前的MySQL实验的基础：

**检测：**  
在server1上创建表及数据，查看server2是否同步成功

*  server1:

mysql> CREATE DATABASE redhat;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> USE redhat;
    Database changed
    mysql> CREATE table usertb (
        -> username varchar(10) not null,
        -> password varchar(15) not null);
    Query OK, 0 rows affected (0.14 sec)
    
    mysql> DESC usertb;
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | username | varchar(10) | NO   |     | NULL    |       |
    | password | varchar(15) | NO   |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    2 rows in set (0.01 sec)
    
    mysql> insert into usertb values ('user1','123');
    Query OK, 1 row affected (0.04 sec)
    
    mysql> select * from usertb;
    +----------+----------+
    | username | password |
    +----------+----------+
    | user1    | 123      |
    +----------+----------+
    1 row in set (0.00 sec)

*  server2查看：

mysql> select * from usertb;
    ERROR 1046 (3D000): No database selected
    mysql> select * from redhat.usertb;
    +----------+----------+
    | username | password |
    +----------+----------+
    | user1    | 123      |
    +----------+----------+
    1 row in set (0.00 sec)

主从复制配置成功！！！

### 一、server3部署mysql-proxy服务 ###

[root@server3 ~]# ls
    mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
    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 -C /usr/local/
    [root@server3 ~]# cd /usr/local/
    [root@server3 local]# ls
    bin  games    lib    libexec                                sbin   src
    etc  include  lib64  mysql-proxy-0.8.5-linux-el6-x86-64bit  share
    [root@server3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy
    [root@server3 local]# ll
    total 0
    drwxr-xr-x. 2 root root   6 Mar 10  2016 bin
    drwxr-xr-x. 2 root root   6 Mar 10  2016 etc
    drwxr-xr-x. 2 root root   6 Mar 10  2016 games
    drwxr-xr-x. 2 root root   6 Mar 10  2016 include
    drwxr-xr-x. 2 root root   6 Mar 10  2016 lib
    drwxr-xr-x. 2 root root   6 Mar 10  2016 lib64
    drwxr-xr-x. 2 root root   6 Mar 10  2016 libexec
    lrwxrwxrwx  1 root root  37 May 10 10:49 mysql-proxy -> mysql-proxy-0.8.5-linux-el6-x86-64bit
    drwxr-xr-x  8 7161 wheel 87 Aug 19  2014 mysql-proxy-0.8.5-linux-el6-x86-64bit
    drwxr-xr-x. 2 root root   6 Mar 10  2016 sbin
    drwxr-xr-x. 5 root root  49 May  1 20:38 share
    drwxr-xr-x. 2 root root   6 Mar 10  2016 src
    [root@server3 local]# cd mysql-proxy
    [root@server3 mysql-proxy]# ls
    bin  include  lib  libexec  licenses  share
    [root@server3 mysql-proxy]# cd bin/
    [root@server3 bin]# ls
    mysql-binlog-dump  mysql-myisam-dump  mysql-proxy
    [root@server3 bin]# cd ..
    [root@server3 mysql-proxy]# ls
    bin  include  lib  libexec  licenses  share
    [root@server3 mysql-proxy]# mkdir conf
    [root@server3 mysql-proxy]# cd conf/
    [root@server3 conf]# vim mysql-proxy.conf
    [root@server3 conf]# cd ../share/doc/mysql-proxy/
    [root@server3 mysql-proxy]# ls
    active-queries.lua       ro-balance.lua           tutorial-resultset.lua
    active-transactions.lua  ro-pooling.lua           tutorial-rewrite.lua
    admin-sql.lua            rw-splitting.lua         tutorial-routing.lua
    analyze-query.lua        tutorial-basic.lua       tutorial-scramble.lua
    auditing.lua             tutorial-constants.lua   tutorial-states.lua
    commit-obfuscator.lua    tutorial-inject.lua      tutorial-tokenize.lua
    commit-obfuscator.msc    tutorial-keepalive.lua   tutorial-union.lua
    COPYING                  tutorial-monitor.lua     tutorial-warnings.lua
    histogram.lua            tutorial-packets.lua     xtab.lua
    load-multi.lua           tutorial-prep-stmts.lua
    README                   tutorial-query-time.lua
    [root@server3 mysql-proxy]# vim rw-splitting.lua 
    if not proxy.global.config.rwsplit then
            proxy.global.config.rwsplit = { 
                    min_idle_connections = 1,
                    max_idle_connections = 2,		#大于两个请求启动读写分离，方便测试

### 二、通过编写lua脚本实现读写分离 ###

[root@server3 conf]# pwd
    /usr/local/mysql-proxy/conf
    [root@server3 conf]# vim mysql-proxy.conf 
    [mysql-proxy]
    proxy-address=0.0.0.0:3306
    proxy-backend-addresses=172.25.70.1:3306		#指定后端主master写入数据
    proxy-read-only-backend-addresses=172.25.70.2:3306		#指定后端从slave读取数据
    proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua	#指定读写分离配置文件位置
    pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
    log-file=/usr/local/mysql-proxy/log/mysql-proxy.log		#日志位置
    plugins=proxy
    log-level=debug		#定义log日志级别，由高到低分别有(error|warning|info|message|debug)
    daemon=true			#以守护进程方式运行
    keepalive=true		#mysql-proxy崩溃时，尝试重启
    [root@server3 conf]# mkdir /usr/local/mysql-proxy/log

### 三、测试启动mysql-proxy ###

[root@server3 bin]# pwd
    /usr/local/mysql-proxy/bin
    [root@server3 bin]# ls
    mysql-binlog-dump  mysql-myisam-dump  mysql-proxy
    [root@server3 bin]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf 
    [root@server3 bin]# ./mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
    [root@server3 bin]# cat /usr/local/mysql-proxy/log/mysql-proxy.log 
    2019-05-10 11:47:03: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=11472 alive
    2019-05-10 11:47:03: (debug) chassis-unix-daemon.c:157: waiting for 11472
    2019-05-10 11:47:03: (debug) chassis-unix-daemon.c:121: we are the child: 11472
    2019-05-10 11:47:03: (critical) plugin proxy 0.8.5 started
    2019-05-10 11:47:03: (debug) max open file-descriptors = 1024
    2019-05-10 11:47:03: (message) proxy listening on port 0.0.0.0:3306
    2019-05-10 11:47:03: (message) added read/write backend: 172.25.70.1:3306
    2019-05-10 11:47:03: (message) added read-only backend: 172.25.70.2:3306
    [root@server3 bin]# yum whatprovides /usr/bin/killall
    [root@server3 bin]# yum install psmisc-22.20-11.el7.x86_64 -y
    [root@server3 bin]# ps ax | grep mysql-proxy
    11471 ?        S      0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
    11472 ?        S      0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
    11484 pts/0    R+     0:00 grep --color=auto mysql-proxy
    [root@server3 bin]# killall -9 mysql-proxy
    [root@server3 bin]# ps ax | grep mysql-proxy
    11487 pts/0    S+     0:00 grep --color=auto mysql-proxy

### 四、启动mysql-proxy ###

*  server3：

[root@server3 bin]# ./mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

### 五、server1配置用户权限，测试 ###

mysql> grant insert,update,select on *.* to ma@'%' identified by 'Mahao+001';
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)

添加数据测试：

mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | redhat             |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> use redhat;
    Database changed
    mysql> select * from usertb;
    +----------+----------+
    | username | password |
    +----------+----------+
    | user1    | 123      |
    +----------+----------+
    1 row in set (0.00 sec)
    
    mysql> insert into usertb values ('user2','456');
    Query OK, 1 row affected (0.02 sec)
    
    mysql> select * from usertb;
    +----------+----------+
    | username | password |
    +----------+----------+
    | user1    | 123      |
    | user2    | 456      |
    +----------+----------+
    2 rows in set (0.00 sec)

### 六、测试远程登录mysql-proxy服务端能否同步： ###

[root@vits Desktop]# mysql -h 172.25.70.3 -uma -pMahao+001
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 12
    Server version: 5.7.24-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)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | redhat             |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    
    MySQL [(none)]> use redhat;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    MySQL [redhat]> select * from usertb;		#数据同步成功
    +----------+----------+
    | username | password |
    +----------+----------+
    | user1    | 123      |
    | user2    | 456      |
    +----------+----------+
    2 rows in set (0.00 sec)

### 七、测试是否读写成功分离 ###

#### 1、在客户端多开几个终端远程连接mysql-proxy ####

至少开三台才可以：

#### 2、在server3监听3306端口 ####

[root@server3 bin]# yum install lsof -y
    [root@server3 bin]# lsof -i:3306

#### 3、server2关闭异步复制，远程添加数据（可以看到实验效果） ####

*  server2：

mysql> stop slave;
    Query OK, 0 rows affected (0.01 sec)
    
    在客户端远程登录mysql-proxy，添加数据
    
    [kiosk@vits Desktop]$ mysql -h 172.25.70.3 -uma -pMahao+001
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 13
    Server version: 5.7.24-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)]> use redhat;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    MySQL [redhat]> select * from usertb;
    +----------+----------+
    | username | password |
    +----------+----------+
    | user1    | 123      |
    | user2    | 456      |
    +----------+----------+
    2 rows in set (0.00 sec)
    
    MySQL [redhat]> insert into usertb values ('user3','789');
    Query OK, 1 row affected (0.02 sec)
    
    MySQL [redhat]> select * from usertb;
    +----------+----------+
    | username | password |
    +----------+----------+
    | user1    | 123      |
    | user2    | 456      |
    +----------+----------+
    2 rows in set (0.00 sec)

可以发现数据写入成功，但是查看不到。因为我们把主从复制关掉了，写在了server1里面，但是读的内容却是server2，server2没有同步server1的数据，故查看不到。

#### 4、在server1查看数据是否添加成功： ####

mysql> select * from usertb;

可以看到数据确实添加到server1上面了

在server2查看数据：

mysql> select * from redhat.usertb;

server2确实没有数据。

发表评论取消回复

表情：

评论列表（有 0 条评论，186人围观）

还没有评论，来说两句吧...

相关阅读

相关使用 MySQLProxy实现基于主从复制(半同步复制)的 Mysql 读写分离

一.读写分离的作用 1.什么是读写分离？ MySQL的主从复制和MySQL的读写分离两者有着紧密联系，首先部署主从复制，只有主从复制完了，才能在此基础上进行数据的读

朴灿烈づ我的快乐病毒、/ 2023年07月13日 04:09/ 0 赞/ 85 阅读

相关 mysql 读写分离 proxy_mysql proxy读写分离

![76fae1d0576ec1f481835b2c4a623759.png][] 1、编译安装必备软件： libevent 1.x or higher (1.3b or

Dear 丶/ 2023年01月11日 03:56/ 0 赞/ 152 阅读

相关 mysql读写分离

为什么MySQL要做主从复制（读写分离）？通俗来讲，如果对数据库的读和写都在同一个数据库服务器中操作，业务系统性能会降低。为了提升业务系统性能，优化用户体验，可以

傷城~/ 2022年07月27日 15:00/ 0 赞/ 289 阅读

相关基于amoeba实现MySQL读写分离

说明：本配置基于centos6.4\_x86，两台mysql服务器均为源码编译（5.6.24版本），amoeba代理为2.2.0版本 <table> <thead>

╰半夏微凉°/ 2022年07月19日 00:07/ 0 赞/ 182 阅读

相关 MySQL读写分离基于Mycat实现

为什么需要读写分离至于为什么需要读写分离，在我之前的文章有介绍过了，相信看到这篇文章的人也知道为什么需要读写分离了，当然如果你也需要了解一下，那么欢迎查看我之前的文章[

柔情只为你懂/ 2022年05月29日 03:40/ 0 赞/ 312 阅读

相关 MySQL读写分离

读写分离一般应用对数据库而言都是读多写少也就说对数据库读取数据的压力比较大有一个思路，就是说采用数据库集群的方案其中一个是主库，负责写入数据，我们称之为

超、凢脫俗/ 2022年05月15日 04:48/ 0 赞/ 304 阅读

相关 mysql 的读写分离

mysql的读写分离的基本原理是：让master（主数据库）来响应事务性操作，让slave（从数据库）来响应select非事务性操作，然后再采用主从复制来把master

古城微笑少年丶/ 2022年05月07日 23:24/ 0 赞/ 214 阅读

相关 mysql基于mysql-proxy的读写分离

此次环境基于之前的MySQL实验的基础：检测：在server1上创建表及数据，查看server2是否同步成功 server1: mysql> CRE

川长思鸟来/ 2022年02月02日 05:09/ 0 赞/ 187 阅读

相关 Mysql 读写分离

一、介绍 1.什么是读写分离读写分离的基本原理是将数据库读和写操作分散到不同的节点上。Mysql 的读写分离需要建立在主从复制基础之上，Master 数据库处理事务性增、

Bertha 。/ 2022年02月01日 12:34/ 0 赞/ 383 阅读

相关 mysql读写分离

Mysql主从复制及读写分离主从复制什么是主从复制主从复制，是用来建立一个和主数据库完

快来打我*/ 2021年09月17日 04:00/ 0 赞/ 390 阅读