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
网友评论