Maxscale中间件和MySQL主从搭配

MySQL主从设置
192.168.25.101  master
192.168.25.102  slave1
192.168.25.103  slave2
192.168.25.104  maxscale

时间同步
/etc/hosts文件：主机名解析

192.168.25.101  master
yum -y install mariadb-server
vim /etc/my.cnf.d/server.cnf
[server]
server-id = 1
log-bin = master-log
skip_name_resolve = on
innodb_file_per_table = on
sync_binlog = 1
systemctl start mariadb;ss -ntl
mysql
grant replication client,replication slave on *.* to 'repl'@'192.168.25.%' identified by 'repl';  主从同步用户
flush privileges;
show master status;
show binlog events in 'master-log.000003'\G
grant replication client,replication slave on *.* to 'maxscale_monitor'@'192.168.25.%' identified by 'maxscale';  监控用户
grant select,show databases on *.* to 'maxscale_router'@'192.168.25.%' identified by 'maxscale';  路由用户
grant all on *.* to test@'192.168.25.%' identified by 'test';  工作用户
create database maxscale_schema;  创建心跳信息记录库，库名字不能更改
grant all on maxscale_schema.* to maxscale_monitor@'192.168.25.%';
以下是简单的创建库和表，为最后的测试"insert into tbl1 values (2,'jerry',25)"做准备
create database mytest;
use mytest
create table tbl1 (id int,name char(20),age tinyint);
insert into tbl1 values (1,'tom',20);
select * from tbl1;
flush privileges;


192.168.25.102  slave1  和  192.168.25.103  slave2
yum -y install mariadb-server
vim /etc/my.cnf.d/server.cnf
[server]
server-id = 2
relay-log = relay-log
skip_name_resolve = on
innodb_file_per_table = on
read_only = on
systemctl start mariadb;ss -ntl
mysql
change master to master_host='192.168.25.101',master_user='repl',master_password='repl',master_log_file='master-log.000003',master_log_pos=490;
start slave;
show slave status\G


192.168.25.104  maxscale
下载maxscale-2.2.13-1.centos.7.x86_64包
网址：https://mariadb.com/downloads/mariadb-tx/maxscale
rz  上传maxscale-2.2.13-1.centos.7.x86_64.rpm包

vim /etc/security/limits.conf
*               soft    nofile          65535
*               hard    nofile          65535

vim /etc/sysctl.conf
fs.file-max=655350
net.ipv4.ip_local_port_range= 1025 65000
net.ipv4.tcp_tw_reuse= 1

修改完内核参数，重启服务器；
或者不用重启服务器，执行命令：sysctl -p

yum -y install maxscale-2.2.13-1.centos.7.x86_64.rpm
vim /etc/maxscale.cnf
[maxscale]
threads=auto
ms_timestamp=1
syslog=1
maxlog=1
log_to_shm=0
log_warning=1
log_notice=1
log_info=1
log_debug=0
log_augmentation=1

[server1]
type=server
address=192.168.25.101
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=192.168.25.102
port=3306
protocol=MariaDBBackend

[server3]
type=server
address=192.168.25.103
port=3306
protocol=MariaDBBackend

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3
user=maxscale_monitor
passwd=maxscale
monitor_interval=10000
detect_replication_lag=true
detect_stale_master=true

#########[Read-Only-Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=maxscale_router
passwd=maxscale
router_options=slave
enable_root_user=1
weightby=serv_weight

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale_router
passwd=maxscale
max_slave_connections=100%
max_slave_replication_lag=5
use_sql_variables_in=all

[MaxAdmin-Service]
type=service
router=cli

[Read-Only-Listener]
type=listener
service=Read-Only-Service
protocol=MariaDBClient
port=4008

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006

[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
socket=default
到此配置文件结束
/usr/share/maxscale/maxscale start  启动maxscale
ss -ntl  4006/4008端口

master服务器
mysql -utest -ptest -P4006 -h192.168.25.104
可能报错，kill掉进程，再执行一次
begin;select @@hostname;commit;
use mytest
insert into tbl1 values (2,'chu',22);
select * from maxscale_schema.replication_heartbeat;
如下图1

报错例子：（不需要执行）
mysql -utest -p  输入密码
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
报错，原因是test@'192.168.%.%'
mysql -utest -p -h192.168.x.x  
OK

1

maxscale服务器
maxadmin
list servers
list listeners
list sessions
如下图1
show service "Read-Only-Service"  如图2
show service "Read-Write-Service"  如图3

1
2
3

master服务器
for i in `seq 1 10`;do mysql -utest -ptest -P4006 -h192.168.25.104 -e 'select @@hostname;' 2> /dev/null & done
如下图4和5

4
5

maxscale服务器
tail /var/log/maxscale/maxscale.log  如下图6

6