【mysql集群】三、MaxScale实现读写分离与负载均衡

﹏ヽ暗。殇╰゛Y 2022-05-14 06:42 206阅读 0赞

# MaxScale：实现MySQL读写分离与负载均衡的中间件利器 #

[http://ju.outofmemory.cn/entry/276547][http_ju.outofmemory.cn_entry_276547]  
[https://www.cnblogs.com/darren-lee/p/7591416.html][https_www.cnblogs.com_darren-lee_p_7591416.html]

测试读写分离：  
[https://blog.csdn.net/wjf870128/article/details/51218697/][https_blog.csdn.net_wjf870128_article_details_51218697]

*  maxscale 可以安装在任意一台服务器上
 *  在开始配置之前，需要在 master 中为 MaxScale 创建两个用户，用于监控模块和路由模块。

创建监控用户

mysql> create user scalemon@’%’ identified by “111111”;mysql> grant replication slave, replication client on *.* to scalemon@’%’;  
创建路由用户

mysql> create user route@’%’ identified by “111111”;  
mysql> grant select on mysql.\* to route@’%’;

## maxscale 中间件配置 ##

参考博客： [https://blog.csdn.net/LYK\_for\_dba/article/details/78351124][https_blog.csdn.net_LYK_for_dba_article_details_78351124]

maxscale 实现读写分离  
[https://blog.csdn.net/wjf870128/article/details/51218697/][https_blog.csdn.net_wjf870128_article_details_51218697]

maxscale.cnf 配置文件

# MaxScale documentation on GitHub:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md
    
    # Global parameters
    #
    # Complete list of configuration options:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md
    
    [maxscale]
    threads=1 #线程开启数，设置auto同cpu核数一致
    ms_timestamp=1 #timesstamp精确度 ts=秒
    syslog=1 #将日志写到syslog
    maxlog=1 #将日志写到maxscale的日志文件中
    log_to_shm=0 #日志不写入共享缓存
    log_warning=1 #记录警告信息
    log_notice=0 #不记录notice
    log_info=0 #不记录info
    log_debug=0 #不打开debug模式
    log_augmentation=1 #日志递增
    use_sql_variables_in=MASTER #带参数查询route 到master
    
    
    # 相关目录配置
    # #路径如果没有在外面手动创建好
    logdir=/data/maxscale/log/
    datadir=/data/maxscale/data/
    libdir=/usr/lib64/maxscale/
    cachedir=/data/maxscale/cache/
    piddir=/data/maxsc
    
    # Server definitions
    #
    # Set the address of the server to the network
    # address of a MySQL server.
    #
    
    [server1]
    type=server
    address=192.168.9.38
    port=3306
    protocol=MySQLBackend
    
    [server2]
    type=server
    address=192.168.9.81
    port=3306
    protocol=MySQLBackend
    
    [server3]
    type=server
    address=192.168.9.98
    port=3306
    protocol=MySQLBackend
    # Monitor for the servers
    #
    # This will keep MaxScale aware of the state of the servers.
    # MySQL Monitor documentation:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md
    
    [MySQL Monitor]
    type=monitor
    module=mysqlmon
    servers=server1,server2,server3
    user=maxscale
    passwd=123456
    monitor_interval=10000  # 监控mysql 的用户名和密码
    
    #detect_replication_lag=true #监控主从同步状态和信息
    #detect_stale_master=true #监控主从复制 slave全部挂掉时，所有访问全部指向master
    
    # Service definitions
    #
    # Service Definition for a read-only service and
    # a read/write splitting service.
    #
    
    # ReadConnRoute documentation:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md
    
    #读写服务器监听
    [Read-Write Listener]
    type=listener
    service=Read-Write Service
    protocol=MySQLClient
    port=4006
    
    [Read-Only Service]
    type=service
    router=readconnroute
    servers=server1,server2,server3
    user=maxscale
    passwd=123456
    router_options=slave
    #
    ## ReadWriteSplit documentation:
    ## https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadWriteSplit.md
    #
    [Read-Write Service]
    type=service
    router=readwritesplit
    servers=server1,server2,server3
    user=maxscale
    passwd=123456
    max_slave_connections=100%
    
    
    # This service enables the use of the MaxAdmin interface
    # MaxScale administration guide:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md
    
    [MaxAdmin Service]
    type=service
    router=cli
    
    # Listener definitions for the services
    #
    # These listeners represent the ports the
    # services will listen on.
    #
    
    [Read-Only Listener]
    type=listener
    service=Read-Only Service
    protocol=MySQLClient
    port=4008
    
    
    
    [MaxAdmin Listener]
    type=listener
    service=MaxAdmin Service
    protocol=maxscaled
    socket=default

*  maxpasswd 生成加密密码失效， 直接输入原始密码
 *  启动maxscale:

maxscale -f /etc/maxscale.cnf

(如果启动有报错，看下报错日志信息):  
netstat -naplt|grep maxscale 查看是否启动  
\* 使用maxadmin 查看服务

maxadmin -S /tmp/maxadmin.sock

list server
    list services

maxscale 启动成功

[root@iZwz9hl75gse44a4mjqc05Z ~]# maxadmin -S /tmp/maxadmin.sock
    MaxScale> list servers
    Servers. -------------------+-----------------+-------+-------------+--------------------
    Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+--------------------
    server1            | **************  |  3306 |           0 | Master, Running
    server2            | **************    |  3306 |           0 | Slave, Running
    server3 | ************** | 3306 | 0 | Slave, Running -------------------+-----------------+-------+-------------+--------------------

==注意事项：==

这个错误是由于 阿里云服务器 开启安全组规则 没有开启3306 端口：
    [root@iZwz9hl75gse44a4mjqc05Z ~]# maxadmin -S /tmp/maxadmin.sock
    MaxScale> list servers
    Servers. -------------------+-----------------+-------+-------------+--------------------
    Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+--------------------
    server1            | **************  |  3306 |           0 | Running
    server2            |**************   |  3306 |           0 | down
    server3 | ************* | 3306 | 0 | down -------------------+-----------------+-------+-------------+--------------------

*  阿里云服务器 开启安全组规则：3306、4006、4008 端口开放
 *  iptables 防火墙 3306、4006/4008 端口开放

[http_ju.outofmemory.cn_entry_276547]: http://ju.outofmemory.cn/entry/276547
[https_www.cnblogs.com_darren-lee_p_7591416.html]: https://www.cnblogs.com/darren-lee/p/7591416.html
[https_blog.csdn.net_wjf870128_article_details_51218697]: https://blog.csdn.net/wjf870128/article/details/51218697/
[https_blog.csdn.net_LYK_for_dba_article_details_78351124]: https://blog.csdn.net/LYK_for_dba/article/details/78351124