搭建 MMM 以及故障模拟
1. 环境配置
master01(db1) 192.168.145.15 mysql5.7、mysql-mmm
master02(db2) 192.168.145.45 mysql5.7、mysql-mmm
slave01(db3) 192.168.145.105 mysql5.7、mysql-mmm
slave02(db4) 192.168.145.120 mysql5.7、mysql-mmm
monitor 192.168.145.30 mysql-mmm
systemctl stop firewalld
setenforce 0
2. 搭建 MySQL 多主多从模式
2.1 修改 master01 配置文件
vim /etc/my.cnf
......
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
log-error=/usr/local/mysql/data/mysql_error.log
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
slow_query_log=ON
slow_query_log_file=mysql_slow_query.log
long_query_time=5
binlog-ignore-db=mysql,information_schema
log_bin=mysql_bin
log_slave_updates=true
sync_binlog=1
innodb_flush_log_at_trx_commit=1
auto_increment_increment=2
auto_increment_offset=1
2.2 把配置文件复制到其它 3 台数据库服务器
scp /etc/my.cnf root@192.168.80.30:/etc/
scp /etc/my.cnf root@192.168.80.13:/etc/
scp /etc/my.cnf root@192.168.80.14:/etc/
systemctl restart mysqld
2.3 配置主主复制,两台主服务器相互复制
grant replication slave on *.* to 'replication'@'192.168.145.%' identified by '123456';
show master status;
change master to master_host='192.168.145.45',master_user='replication',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=461;
start slave;
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
change master to master_host='192.168.145.15',master_user='replication',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=461;
start slave;
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2.4 配置主从复制,在两台从服务器上做
change master to master_host='192.168.145.15',master_user='replication',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=461;
start slave;
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
create database test_db;
use test_db;
create table test(id int);
insert into test(id) values (1);
3. 安装配置 MySQL-MMM
3.1 安装 MySQL-MMM
wget -O /etc/yum.repos.d/CentOS-Base.repo http:
yum -y install epel-release
yum -y install mysql-mmm*
3.2 在 master01 上对 MySQL-MMM 进行配置
cd /etc/mysql-mmm/
vim mmm_common.conf
……
<host default>
cluster_interface ens33
……
replication_user replication
replication_password 123456
agent_user mmm_agent
agent_password 123456
<host db1>
ip 192.168.145.15
mode master
peer db2
</host>
<host db2>
ip 192.168.145.45
mode master
peer db1
</host>
<host db3>
ip 192.168.145.105
mode slave
</host>
<host db4>
ip 192.168.145.120
mode slave
</host>
<role writer>
hosts db1, db2
ips 192.168.145.188
mode exclusive
</role>
<role reader>
hosts db3, db4
ips 192.168.145.198, 192.168.145.199
mode balanced
3.3 把配置文件复制到其它 4 台主机
scp mmm_common.conf root@192.168.145.45:/etc/mysql-mmm/
scp mmm_common.conf root@192.168.145.105:/etc/mysql-mmm/
scp mmm_common.conf root@192.168.145.120:/etc/mysql-mmm/
scp mmm_common.conf root@192.168.145.30:/etc/mysql-mmm/
3.4 修改所有数据库服务器的代理配置文件
vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db1
3.5 在 monitor 监控服务器上修改监控配置文件
vim /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
<monitor>
.....
ping_ips 192.168.145.15,192.168.145.45,192.168.145.105,192.168.145.120
auto_set_online 10
</monitor>
<host default>
monitor_user mmm_monitor
monitor_password 123456
</host>
3.6 在所有数据库上为授权
grant super, replication client, process on *.* to 'mmm_agent'@'192.168.145.%' identified by '123456';
grant replication client on *.* to 'mmm_monitor'@'192.168.145.%' identified by '123456';
flush privileges;
3.7 在所有数据库服务器上启动 mysql-mmm-agent
systemctl start mysql-mmm-agent.service
systemctl enable mysql-mmm-agent.service
3.8 在 monitor 服务器上启动 mysql-mmm-monitor
systemctl start mysql-mmm-monitor.service
3.9 在 monitor 服务器上测试群集
mmm_control show
mmm_control checks all
mmm_control move_role writer db2
3.10 故障测试
mmm_control move_role writer db1
#停止 master01 确认 VIP 是否移动到 master02 上。注意:master01 主服务器恢复服务后,不会抢占
mmm_control show
db1(192.168.145.15) master/HARD_OFFLINE. Roles:
db2(192.168.145.45) master/ONLINE. Roles: writer(192.168.145.188)
#停止一台从服务器,另一台将接管两个虚拟IP,以保证业务不停止
mmm_control show
3.11 客户端测试
#在 master01 服务器上为 monitor 服务器地址授权登录
grant all on *.* to 'testdba'@'192.168.145.30' identified by '123456';
flush privileges;
yum install -y mariadb-server mariadb
systemctl start mariadb.service
mysql -utestdba -p -h 192.168.145.188
create database testdba;