Debian9 安装 MaxScale 最新

2020年5月14日

相关准备
Mysql  主从
192.168.11.120 33001 master
192.168.11.120 33002 slave
192.168.11.120 33004 slave

192.168.11.120  MaxScale

安装 MaxScale

参考：https://mariadb.com/kb/en/mariadb-package-repository-setup-and-usage/

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup |     bash -s
apt-get  update
apt-get install maxscale

MaxScale 创建用户
创建监控用户
##########################################################################
mysql> create user maxscale@'%' identified by "123456";
mysql> grant replication slave, replication client on *.* to maxscale@'%';
创建路由用户
mysql> create user scaleroute@'%' identified by "123456";
mysql> grant select on mysql.* to scaleroute@'%';
#########################################################################

###  创建监控用户 可以只创建一个示例：
CREATE USER maxscale@'%' IDENTIFIED BY "123456";
GRANT replication slave, replication client ON *.* TO maxscale@'%';
GRANT SELECT ON mysql.* TO maxscale@'%';
GRANT ALL ON maxscale_schema.* TO maxscale@'%';
GRANT SHOW DATABASES ON *.* TO maxscale@'%';

在创建一个程序使用的 mysql  用户
Master  读写
slave   只读

grant all privileges on *.* to "mysql_user"@"IP" identified by "Gwr0cfca43WoAF";
grant select on *.* to "mysql_user"@"IP" identified by "Gwr0cfca43WoAF"

建议限定创建限定 IP 的连接用户 ；  如不想限定 IP 改为 % 即可


MaxScale 配置文件

# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-24/

# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-maxscale-configuration-guide/


cat  /etc/maxscale.cnf

  1. [maxscale]
  2. threads=auto
  3.  
  4. maxlog=1 #将日志写入到maxscale的日志文件中
  5. #log_to_shm=0 #不将日志写入到共享缓存中，开启debug模式时可打开加快速度
  6. log_warning=1 #记录告警信息
  7. log_notice=1 #记录notice
  8. log_info=1 #记录info
  9. log_debug=0 #不打开debug模式
  10.  
  11. # Server definitions
  12. # Set the address of the server to the network
  13. # address of a MariaDB server.
  14. #
  15. [server1]
  16. type=server
  17. address=192.168.11.120
  18. port=33001
  19. protocol=MariaDBBackend
  20.  
  21. [server2]
  22. type=server
  23. address=192.168.11.120
  24. port=33002
  25. protocol=MariaDBBackend
  26.  
  27. [server3]
  28. type=server
  29. address=192.168.11.120
  30. port=33004
  31. protocol=MariaDBBackend
  32.  
  33. # Monitor for the servers
  34. #
  35. # This will keep MaxScale aware of the state of the servers.
  36. # MariaDB Monitor documentation:
  37. # https://mariadb.com/kb/en/mariadb-maxscale-24-mariadb-monitor/
  38.  
  39. [MariaDB-Monitor]
  40. type=monitor
  41. module=mariadbmon
  42. servers=server1,server2,server3
  43. user=maxscale
  44. password=123456
  45. monitor_interval=2000
  46. # 检查复制延迟的情况
  47. detect_replication_lag=true
  48. # 当所有的slave都不可用时，select查询请求会转发到master。
  49. detect_stale_master=true
  50.  
  51. # Service definitions
  52. #
  53. # Service Definition for a read-only service and
  54. # a read/write splitting service.
  55. #
  56.  
  57. # ReadConnRoute documentation:
  58. # https://mariadb.com/kb/en/mariadb-maxscale-24-readconnroute/
  59.  
  60. [Read-Only-Service]
  61. type=service
  62. router=readconnroute
  63. servers=server1,server2,server3
  64. user=maxscale
  65. password=123456
  66. router_options=slave
  67.  
  68. # ReadWriteSplit documentation:
  69. # https://mariadb.com/kb/en/mariadb-maxscale-24-readwritesplit/
  70.  
  71. [Read-Write-Service]
  72. type=service
  73. router=readwritesplit
  74. servers=server1,server2,server3
  75. user=maxscale
  76. password=123456
  77. ##所有的slave提供select查询服务
  78. max_slave_connections=100%
  79. ### slave超时5秒，就把请求转发到其他slave
  80. max_slave_replication_lag=5
  81. ###动态参数可以走全部的数据库  [all|master]  如果设置为master的话前台看到的中文是乱码，一般建议设置为all
  82. #use_sql_variables_in=all
  83. #weightby=serversize #权重设置
  84. ##enable_root_user=1 #允许root用户登录执行
  85. #auth_all_servers=true #
  86. #log_auth_warnings=true #身份验证失败和警告的日志记录，记录那些试图连接到MaxScale和来自哪里
  87.  
  88. # Listener definitions for the services
  89. #
  90. # These listeners represent the ports the
  91. # services will listen on.
  92. #
  93.  
  94. #[Read-Only-Listener]
  95. #type=listener
  96. #service=Read-Only-Service
  97. #protocol=MariaDBClient
  98. #port=4008
  99.  
  100. [Read-Write-Listener]
  101. type=listener
  102. service=Read-Write-Service
  103. protocol=MariaDBClient
  104. port=4006
  105.  
  106. [MaxAdmin-Service]
  107. type=service
  108. router=cli
  109.  
  110. [MaxAdmin-Listener]
  111. type=listener
  112. service=MaxAdmin-Service
  113. protocol=maxscaled
  114. socket=default


service  maxscale start   
报错 根据提示查找问题

正常通过 netstat -nltp |  grep  4006  就可以看到已启动

查看 maxscale 列表
[root@local-debain:~]# maxctrl
 maxctrl list servers

 

 

Server Address Port Connections State GTID
server1 192.168.11.120 33001 0 Master, Running  0-1000-732
server2 192.168.11.120 33002 0 Slave, Running  
server3 192.168.11.120 33004 0 Slave, Running 0-1000-732

 

OR

[root@local-debain:~]# maxadmin
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.11.120  | 33001 |           0 | Master, Running
server2            | 192.168.11.120  | 33002 |           0 | Slave, Running
server3            | 192.168.11.120  | 33004 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> show servers
Server 0x560389fc8ab0 (server1)
        Server:                              192.168.11.120
        Status:                              Master, Running
        Protocol:                            MariaDBBackend
        Port:                                33001
        Server Version:                      10.4.12-MariaDB-1:10.4.12+maria~stretch-log
        Node Id:                             1000
        Master Id:                           -1
        Last event:                          server_up
        Triggered at:                        Thu, 14 May 2020 02:52:52 GMT
        Server Parameters:
                                               address  192.168.11.120
                                               extra_port       0
                                               persistmaxtime   0
                                               persistpoolmax   0
                                               port     33001
                                               protocol MariaDBBackend
                                               proxy_protocol   false
                                               rank     primary
                                               ssl      false
                                               ssl_cert_verify_depth    9
                                               ssl_verify_peer_certificate      false
                                               ssl_version      MAX
                                               type     server
        Number of connections:               2
        Current no. of conns:                0
        Current no. of operations:           0
        Number of routed packets:            1498
        Adaptive avg. select time:           1.91765ms
Server 0x560389fc74a0 (server2)
        Server:                              192.168.11.120
        Status:                              Slave, Running
        Protocol:                            MariaDBBackend
        Port:                                33002
        Server Version:                      5.7.29-log
        Node Id:                             1023
        Master Id:                           1000
        Last event:                          server_up
        Triggered at:                        Thu, 14 May 2020 02:52:52 GMT
        Slave delay:                         0
        Server Parameters:
                                               address  192.168.11.120
                                               extra_port       0
                                               persistmaxtime   0
                                               persistpoolmax   0
                                               port     33002
                                               protocol MariaDBBackend
                                               proxy_protocol   false
                                               rank     primary
                                               ssl      false
                                               ssl_cert_verify_depth    9
                                               ssl_verify_peer_certificate      false
                                               ssl_version      MAX
                                               type     server
        Number of connections:               2
        Current no. of conns:                0
        Current no. of operations:           0
        Number of routed packets:            23
        Adaptive avg. select time:           367.72us
Server 0x560389fc5ea0 (server3)
        Server:                              192.168.11.120
        Status:                              Slave, Running
        Protocol:                            MariaDBBackend
        Port:                                33004
        Server Version:                      10.4.12-MariaDB-1:10.4.12+maria~stretch-log
        Node Id:                             1024
        Master Id:                           1000
        Last event:                          server_up
        Triggered at:                        Thu, 14 May 2020 02:52:52 GMT
        Slave delay:                         0
        Server Parameters:
                                               address  192.168.11.120
                                               extra_port       0
                                               persistmaxtime   0
                                               persistpoolmax   0
                                               port     33004
                                               protocol MariaDBBackend
                                               proxy_protocol   false
                                               rank     primary
                                               ssl      false
                                               ssl_cert_verify_depth    9
                                               ssl_verify_peer_certificate      false
                                               ssl_version      MAX
                                               type     server
        Number of connections:               2
        Current no. of conns:                0
        Current no. of operations:           0
        Number of routed packets:            3
        Adaptive avg. select time:           not available


使用mysql账号(上述中用于程序的账号)连接到maxscale的4006端口上，例如：

mysql -u mysql_user -p -P 4006 -h 192.168.11.120

 

[root@local-debain:~]# mysql  -uadmin -p -P 4006 -h 192.168.11.120
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.29-log MySQL Community Server (GPL)

 

 

 

Copyright (c) 2000, 2018, 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 |
| mydb               |
+--------------------+
2 rows in set (0.00 sec)

参考：
http://www.ttlsa.com/mysql/maxscale-right-read-write-split/
https://blog.51cto.com/lee90/1945504
https://longsheng.org/post/15113.html
https://mariadb.com/kb/en/mariadb-maxscale-23-maxadmin-admin-interface/
https://mariadb.com/docs/deploy/maxscale-debian9-mxs23/
https://mariadb.com/docs/reference/mxs2.4/maxctrl/

 

 

标签：、、

没有评论

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注