Mysql集群：读写分离(mysql-proxy)

迷南。 2023-05-29 02:47 12阅读 0赞

继上篇搭建完mysql主从集群[https://blog.csdn.net/eyeofeagle/article/details/102841995][https_blog.csdn.net_eyeofeagle_article_details_102841995], 现在使用mysql-proxy实现读写分离

# 1,安装mysql-proxy #

*  说明：“MySQL Proxy is not GA, and is not recommended for Production use”（mysql-proxy还不够稳定，不推荐生产环境使用)  
    [https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz][https_downloads.mysql.com_archives_get_file_mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz]

#1, 下载tar包，解压
    wget  https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
    tar -zxvf  mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
    
    [root@docker ~]# ls mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit
    bin  include  lib  libexec  licenses  mysql-proxy.log  share
    [root@docker ~]# cd mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/bin/
    [root@docker bin]# ls
    mysql-binlog-dump  mysql-myisam-dump  mysql-proxy
    
    #2, 创建配置文件
    [root@docker bin]# cat > mysql-proxy.cnf <<EOF
    [mysql-proxy]
    user=root
    admin-username=root
    admin-password=123456
    #配置代理角色 ip: 端口
    proxy-address=192.168.56.201:4040
    #配置master角色的 ip: 端口
    proxy-backend-addresses=172.20.0.2:3306
    #配置slave角色的 ip: 端口
    proxy-read-only-backend-addresses=172.20.0.3:3306,172.20.0.4:3306
    #配置代理角色需要执行的脚本
    proxy-lua-script=/root/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua
    log-file=/usr/local/mysql-proxy/mysql-proxy.log
    log-file=/root/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/mysql-proxy.log
    log-level=debug
    keepalive=true
    daemon=true
    EOF
    
    #3, 修改配置: (减小连接数, 方便测试）
    #修改 tar包解压厚的文件：/share/doc/mysql-proxy/rw-splitting.lua, 40和41行
    if not proxy.global.config.rwsplit then
            proxy.global.config.rwsplit = { 
                    min_idle_connections =1 , # 4,
                    max_idle_connections =2 , # 8,
                    is_debug = false
            }
    end
    
    # 4, 启动mysql-proxy服务
    ./mysql-proxy --defaults-file=mysql-proxy.cnf
    [root@docker ~]# ss -nltp |grep 404
    LISTEN     0      128    192.168.56.201:4040                     *:*                   users:(("mysql-proxy",pid=4260,fd=10))
    
    #5, 在远程机器上，测试登陆
    [root@eadage ~]# mysql -uroot -p123456 -hdocker -P4040
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 13
    Server version: 5.7.27-log MySQL Community Server (GPL)
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    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 databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    | test               |
    +--------------------+
    5 rows in set (0.01 sec)
    
    mysql> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | per            |
    +----------------+
    1 rows in set (0.00 sec)

# 2, 从mysql-proxy写数据 #

## mysql-proxy节点 ##

[root@cent6_server ~]# cat > a.sh <<EOF
    #!/bin/bash
    #测试mysql读写分离和复制
    #for i in {1..100}
    for i in $(seq 1 1000000)
    do
      echo $i
      sql=$(echo "\""insert into test.aa values"('name"$i"')\"")
      echo $sql
      echo mysql -uroot -p123456 -hdocker -P4040 -e $sql |sh
      #sleep 1
    done
    EOF
    
    #执行插入数据的脚本，然后分别在master,slave节点查看复制状态
    [root@cent6_server ~]# sh a.sh 
    ...........
    "insert into test.aa values('name120')"
    121
    "insert into test.aa values('name121')"
    122
    "insert into test.aa values('name122')"
    123
    "insert into test.aa values('name123')"
    124
    "insert into test.aa values('name124')"
    125
    "insert into test.aa values('name125')"
    126
    "insert into test.aa values('name126')"
    127
    "insert into test.aa values('name127')"
    128
    "insert into test.aa values('name128')"
    ...........

## master 节点 ##

mysql -uroot -p123456 -hmysql1
    mysql>  show processlist \G
    *************************** 1. row ***************************
         Id: 4
       User: test
       Host: 172.20.0.4:58294
         db: NULL
    Command: Binlog Dump
       Time: 89341
      State: Master has sent all binlog to slave; waiting for more updates
       Info: NULL
    *************************** 2. row ***************************
         Id: 8
       User: test
       Host: 172.20.0.3:60310
         db: NULL
    Command: Binlog Dump
       Time: 89003
      State: Master has sent all binlog to slave; waiting for more updates
       Info: NULL
    *************************** 3. row ***************************
         Id: 13
       User: root
       Host: 172.20.0.1:60586
         db: NULL
    Command: Sleep
       Time: 0
      State: 
       Info: NULL
    *************************** 4. row ***************************
         Id: 14
       User: root
       Host: 172.20.0.1:44764
         db: test
    Command: Query
       Time: 0
      State: query end
       Info: insert into test.aa values('name1656')

## slave 节点 ##

mysql>  show processlist \G
    *************************** 1. row ***************************
         Id: 9
       User: system user
       Host: 
         db: NULL
    Command: Connect
       Time: 89011
      State: Waiting for master to send event
       Info: NULL
    *************************** 2. row ***************************
         Id: 10
       User: system user
       Host: 
         db: NULL
    Command: Connect
       Time: 0
      State: Reading event from the relay log
       Info: NULL
    *************************** 3. row ***************************
         Id: 19
       User: root
       Host: localhost
         db: test
    Command: Query
       Time: 0
      State: starting
       Info: show processlist
    *************************** 4. row ***************************
         Id: 20
       User: root
       Host: 172.20.0.1:45608
         db: NULL
    Command: Sleep
       Time: 0
      State: 
       Info: NULL
    *************************** 5. row ***************************
         Id: 21
       User: root
       Host: 172.20.0.1:47014
         db: NULL
    Command: Sleep
       Time: 0
      State: 
       Info: NULL
    5 rows in set (0.00 sec)

# 3, 从mysql-proxy读数据 #

## mysql-proxy节点 ##

[root@cent6_server ~]# cat ~/a.sh 
    #!/bin/bash
    #测试mysql读写分离和复制
    
    #for i in {1..100}
    #for i in $(seq 1 1000000)
    while true :
    do
      echo mysql -uroot -p123456 -hdocker -P4040 -e "'select * from test.aa'" |sh
    done

## slave节点 ##

.... 
    *************************** 5. row ***************************
         Id: 21
       User: root
       Host: 172.20.0.1:47014
         db: NULL
    Command: Query
       Time: 0
      State: Sending data
       Info: select * from test.aa

[https_blog.csdn.net_eyeofeagle_article_details_102841995]: https://blog.csdn.net/eyeofeagle/article/details/102841995
[https_downloads.mysql.com_archives_get_file_mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz]: https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz