[root@HE3 ~]# cat /etc/maxscale.cnf
# MaxScale documentation on GitHub:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Documentation-Contents.md
# Global parameters
#
# Number of threads is autodetected, uncomment for manual configuration
# Complete list of configuration options:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Getting-Started/Configuration-Guide.md
[maxscale]
threads=auto
# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#
[server1]
type=server
address=192.168.1.248
port=3306
protocol=MySQLBackend
myweight=5
[server2]
type=server
address=192.168.1.249
port=3306
protocol=MySQLBackend
myweight=5
[server3]
type=server
address=192.168.1.100
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/master/Documentation/Monitors/MySQL-Monitor.md
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=mysync
passwd=MANAGER
monitor_interval=10000
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadConnRoute.md
#[Read-Only Service] ###只读服务
#type=service
#router=readconnroute
#servers=server1,server2,server3
#user=sys_admin
#passwd=MANAGER
#router_options=slave
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadWriteSplit.md
[Read-Write Service] ####写服务
type=service
router=readwritesplit
enable_root_user=1
servers=server1,server2,server3
user=sys_admin
passwd=MANAGER
weightby=myweight
router_options=slave_selection_criteria=LEAST_GLOBAL_CONNECTIONS
max_slave_connections=1
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Reference/MaxAdmin.md
[MaxAdmin Service]
type=service
router=cli
#[Read-Only Listener]
#type=listener
#service=Read-Only Service
#protocol=MySQLClient
#port=4008 ##读服务启动监听 端口4008
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006 ####写服务启动监听 端口
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
port=6603 ###管理端口
[root@HE3~]# /etc/init.d/maxscale start
StartingMaxScale: maxscale (pid 28851) is running... [ OK ]
mysql> select * from helei;
+--------+
| a |
+--------+
| HE3 |
+--------+
3 rows in set (0.00 sec)
在slave1插入数据HE1
mysql> select * from helei;
+------+
| a |
+------+
| HE3 |
| HE1 |
+------+
在slave2插入数据HE2
mysql> select * from helei;
+------+
| a |
+------+
| HE3 |
| HE2 |
+------+
现在链接4006读写分离端口，进行数据写入
[root@HE3 ~]# mysql -h192.168.1.250 -P 4006 -usys_admin -pMANAGER maxscale -e"insert into helei values('写入');"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@HE3 ~]# mysql -h192.168.1.250 -P 4008 -usys_admin -pMANAGER maxscale -e"select * from helei;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| a |
+--------+
| HE3 |
| HE1 |
| 写入 |
+--------+
可以看到主库插入完毕后从库已经同步完成，这条查询完成在了HE1（slave1）上
Part2:负载均衡
我们配置的read比例为1:1
[root@HE3 ~]# maxadmin -pmariadb show service "Read-Write Service"
Service 0xef5570
Service: Read-Write Service
Router: readwritesplit (0x7ff5e8fa6ec0)
State: Started
Number of router sessions: 15
Current no. of router sessions: 0
Number of queries forwarded: 41
Number of queries forwarded to master:2 (4.88%)
Number of queries forwarded to slave: 39 (95.12%)
Number of queries forwarded to all: 0 (0.00%)
Connection distribution based on myweight server parameter.
Server Target % Connections Operations
Global Router
server1 50.0% 0 0 0
server2 50.0% 0 0 0
server3 100.0% 0 0 0
Started: Thu Nov 3 23:46:27 2016
Root user access: Enabled
Backend databases:
192.168.1.248:3306 Protocol: MySQLBackend
192.168.1.249:3306 Protocol: MySQLBackend
192.168.1.100:3306 Protocol: MySQLBackend
Routing weight parameter: myweight
Users data: 0xf09370
Total connections: 16
Currently connected: 1
[root@HE1 ~]# mysql
-uroot -p
Enter password:
Welcome to the MySQL
monitor. Commands end with ; or \g.
Your MySQL
connection id is 74
Server version:
5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000,
2016, 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> stop
slave;
Query OK, 0 rows
affected (0.05 sec)
[root@HE3 ~]#
maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port
| Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.1.248 |
3306 | 0 | Running
server2 | 192.168.1.249 |
3306 | 0 | Slave,
Running
server3 | 192.168.1.250 |
3306 | 0 | Master,
Running
-------------------+-----------------+-------+-------------+--------------------
[root@HE3 ~]# mysql -h192.168.1.250 -P 4006 -usys_admin
-pMANAGER maxscale -e"select * from helei;"
mysql: [Warning]
Using a password on the command line interface can be insecure.
+--------+
| a |
+--------+
| HE3 |
| HE2 |
| 写入
|
+--------+
[root@HE3 ~]# mysql -h192.168.1.250 -P 4006 -usys_admin
-pMANAGER maxscale -e"select * from helei;"
mysql: [Warning]
Using a password on the command line interface can be insecure.
+--------+
| a |
+--------+
| HE3 |
| HE2 |
| 写入
|
+--------+
可以看出，在slave1故障后，所有的读操作都进入了HE2(slave2)；
恢复HE1
mysql> start
slave;
Query OK, 0 rows
affected (0.00 sec)
[root@HE3 ~]#
maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port
| Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.1.248 |
3306 | 0 | Slave,
Running
server2 | 192.168.1.249 |
3306 | 0 | Slave,
Running
server3 | 192.168.1.250 |
3306 | 0 | Master,
Running
-------------------+-----------------+-------+-------------+--------------------
验证
[root@HE3 ~]# mysql -h192.168.1.250 -P 4006 -usys_admin
-pMANAGER maxscale -e"select * from helei;"
mysql: [Warning]
Using a password on the command line interface can be insecure.
+--------+
| a |
+--------+
| HE3 |
| HE2 |
| 写入
|
+--------+
[root@HE3 ~]# mysql -h192.168.1.250 -P 4006 -usys_admin
-pMANAGER maxscale -e"select * from helei;"
mysql: [Warning]
Using a password on the command line interface can be insecure.
+--------+
| a |
+--------+
| HE3 |
| HE1 |
| 写入
|
+--------+
在HE1（slave1）恢复完成后，重新有了负载均衡。 Part2:mysql down故障
[root@HE1 ~]#
/etc/init.d/mysqld stop
Shutting down
MySQL..... SUCCESS!
停止HE1(slave)可以看到转发到了HE2上
[root@HE3 ~]# maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port
| Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.1.248 |
3306 | 0 | Down
server2 | 192.168.1.249 |
3306 | 1 | Slave,
Running
server3 | 192.168.1.250 |
3306 | 1 | Master,
Running
-------------------+-----------------+-------+-------------+--------------------
[root@HE3
~]# mysql -h192.168.1.250 -P 4006 -usys_admin -pMANAGER
mysql: [Warning]
Using a password on the command line interface can be insecure.
Welcome to the MySQL
monitor. Commands end with ; or \g.
Your MySQL
connection id is 28948
Server version:
5.5.5-10.0.0 2.0.1-maxscale MySQL Community Server (GPL)
Copyright (c) 2000,
2016, 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> select
@@hostname;
+------------+
| @@hostname |
+------------+
| HE2 |
+------------+
1 row in set (0.00
sec)
停掉2台slave，观察maxscale的状态
[root@HE3 ~]#
maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port
| Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.1.248 |
3306 | 0 | Running
server2 | 192.168.1.249 |
3306 | 0 | Running
server3 | 192.168.1.250 |
3306 | 0 | Master, Stale
Status, Running
-------------------+-----------------+-------+-------------+--------------------
Java Druid注意事项
------------以下文章取材于贺春旸技术博客-------------------
http://hcymysql.blog.51cto.com/5223301/1869414
现象：
程序会不定时的出现连接错误，问题bug的异常信息如下：
The last packet successfully received from the server was 116 milliseconds ago. The last packet sent successfully to the server was 115 milliseconds ago.
java.sql.SQLException: No database selected
数据库
发布于：2021-07-03 21:38
|
阅读数：497
|
评论：0
QQ好友和群
QQ空间
