博学谷 - mysql数据库性能优化笔记05 - 读写分离中间件MaxScale

1.maxscale下载地址
https://downloads.mariadb.com/files/MaxScale

2.安装maxscale命令

yum install libaio.x86_64 libaio-devel.x86_64 novacom-server.x86_64 libedit -y
wget https://downloads.mariadb.com/MaxScale/1.4.5/centos/7/x86_64/maxscale-1.4.5-1.centos.7.x86_64.rpm
rpm -ivh maxscale-1.4.5-1.centos.7.x86_64.rpm

2.配置maxscale
创建监控用户:

create user 'maxmon'@'%' identified by '123456';
grant replication slave,replication client on *.* to 'maxmon'@'%';

创建路由用户:

create user 'maxrou'@'%' identified by '123456';
grant select on mysql.* to 'maxrou'@'%';
flush privileges;

配置maxscale.cnf文件:

vi /etc/maxscale.cnf

配置server:

[server1]
type=server
#自己的服务器地址
address=111.111.235.111
#自己的服务器端口
port=3306
protocol=MySQLBackend

[server2]
type=server
#自己的服务器地址
address=111.111.187.111
#自己的服务器端口
port=3306
protocol=MySQLBackend
[MySQL Monitor]
type=monitor
module=mysqlmon
#改成上述配置的服务器
servers=server1,server2
#主数据库的监听用户的账号
user=maxmon
#主数据库监听用户的密码
passwd=123456
monitor_interval=10000
#因为配置了Read-Write Service模块,所以该模块的servers、user、passwd清空
#因为假如去掉该段代码的话,会导致启动maxscale失败
[Read-Only Service]
type=service
router=readconnroute
servers=
user=
passwd=
router_options=slave

# ReadWriteSplit documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadWriteSplit.md

[Read-Write Service]
type=service
router=readwritesplit
#服务器的名称
servers=server1,server2
#主数据库路由用户的账号
user=maxrou
#主数据库路由用户的密码
passwd=123456
max_slave_connections=100%

3.启动 maxscale
执行命令:

maxscale --config=/etc/maxscale.cnf

查看maxscale启动状态:
命令:

netstat -ntelp

结果:

[root@iZbp1e9mxelwe7pwimpw8sZ ~]# netstat -ntelp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       User       Inode      PID/Program name    
tcp        0      0 0.0.0.0:4006            0.0.0.0:*               LISTEN      0          504699     21342/maxscale      
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      997        22771      1153/mysqld         
tcp        0      0 0.0.0.0:6603            0.0.0.0:*               LISTEN      0          504700     21342/maxscale      
tcp        0      0 0.0.0.0:22222           0.0.0.0:*               LISTEN      0          26262      1573/sshd           
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      0          26264      1573/sshd           
tcp6       0      0 :::80                   :::*                    LISTEN      0          24734      1483/docker-proxy   

4006 是 Read-Write Listener 使用的端口,用于连接 MaxScale
6603 是 MaxAdmin Listener 使用的端口,用于 MaxScale 管理器
登 录 MaxScale 管 理 器 , 查 看 一 下 数 据 库 连 接 状 态 , 默 认 的 用 户 名 和 密 码 是
admin/mariadb

登录maxscale查看状态:

 maxadmin --user=admin --password=mariadb
 list servers
[root@iZbp1e9mxelwe7pwimpw8sZ ~]# maxadmin --user=admin --password=mariadb
MaxScale>  list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 111.111.235.111  |  3306 |           1 | Master, Running
server2            | 111.111.187.111  |  3306 |           1 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> 

4.测试maxscale
在主数据库master创建测试用户:

create user 'rtest'@'%' identified by '111111';
grant ALL PRIVILEGES on *.* to 'rtest'@'%';

使用mysql客户端到连接maxscale:

mysql -urtest -p'111111' -h'192.168.33.11' -P4006

测试命令:

select @@hostname;
start transaction;
select @@hostname;
rollback;
select @@hostname;
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| ljy        |
+------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@hostname;
+-------------------------+
| @@hostname              |
+-------------------------+
| iZbp1e9mxelwe7pwimpw8sZ |
+-------------------------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| ljy        |
+------------+
1 row in set (0.00 sec)

mysql> 

测试步骤及结果解释:
第一次select @@hostname;使用的是salve进行读操作,开启事务后start transaction;使用的是master进行写操作,事务回滚后rollback;使用的是salve进行读操作.

5.文章参考链接
a. https://www.boxuegu.com
b. https://downloads.mariadb.com/files/MaxScale

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页