MYSQL 主主配置-MMM高可用架构

最新推荐文章于 2021-09-24 00:52:00 发布

Drien

最新推荐文章于 2021-09-24 00:52:00 发布

阅读量319

点赞数

分类专栏： liunx mysql 文章标签： mysql centos 数据库运维

本文链接：https://blog.csdn.net/weixin_52184735/article/details/115751784

版权

liunx 同时被 2 个专栏收录

25 篇文章 0 订阅

订阅专栏

mysql

13 篇文章 0 订阅

订阅专栏

文章目录

MYSQL 主主配置-MMM高可用架构

MYSQL 主主配置-MMM高可用架构

一，安装mysql

二，修改配置文件

主1：

vim /etc/my.cnf

log-slave-updates    # 此数据库宕机，备用数据库接管
auto-increment-increment = 2    #字段一次递增多少
auto-increment-offset = 1		# 自增字段的起始值

重启

systemctl restart mysqld

mysql -u root
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.211.%' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;  //刷新
mysql> show master status;  //查看日志

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      870 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

开启同步功能

change master to master_host='192.168.211.222',master_user='myslave',master_password='123456',master_log_file='master2
.000001',master_log_pos=120;



start slave;

在这里插入图片描述

主2：

vim /etc/my.cnf

log-slave-updates
auto-increment-increment = 2
auto-increment-offset = 1

重启

systemctl restart mysqld

mysql -u root
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.211.%' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;  //刷新
mysql> show master status;  //查看日志

+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| master2.000001 |      120 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+

开启同步功能

change master to master_host='192.168.211.135',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=870;



start slave

;

在这里插入图片描述

三，在从机上执行同步

现在主机上查看状态值

mysql [(none)]>mysql [(none)]>show master status;
±------------------±---------±-------------±-----------------±------------------+

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set

±------------------±---------±-------------±-----------------±------------------+

master-bin.000003	1594

±------------------±---------±-------------±-----------------±------------------+

再从机上

change master to master_host=‘192.168.211.135’,mast
er_user=‘myslave’,master_password=‘123456’,master_log_file=‘master-bin.000003’,master_log_pos=1594;

mysql [(none)]>start slave;
Query OK, 0 rows affected (0.48 sec)

mysql [(none)]>show slave status\G;

。。。。。。

	Slave_IO_Running: Yes
	Slave_SQL_Running: Yes

。。。。。。

三，安装MMM

每个服务器都安装

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo

yum install -y mysql-mmm*

四，调度器配置

vim /etc/mysql-mmm/mmm_common.conf

active_master_role      writer

<host default>
    cluster_interface       eth0
    pid_path                /run/mysql-mmm-agent.pid
    bin_path                /usr/libexec/mysql-mmm/
    replication_user        replicant
    replication_password    slave
    agent_user              mmm_agent  # mysql授权用户
    agent_password          RepAgent	# 密码
</host>

<host db1>      # 主mysql服务器
    ip      192.168.211.135
    mode    master
    peer    db1
</host>

<host db2> 		# 主mysql服务器
    ip      192.168.211.222
    mode    master
    peer    db2
</host>

#<host db3>		# 从mysql服务器

ip     192.168.100.51

mode    slave

#</host>

<role writer>
    hosts   db1, db2
    ips     192.168.100.250		# 那个ip的主机可以执行写操作
    mode    exclusive
</role>

<role reader>
    hosts   db1, db2
    ips     192.168.100.251, 192.168.100.252		#那个ip的主机可以执行读操作
    mode    balanced
</role>

五，在从主机上编辑

vim /etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf

The 'this' variable refers to this server.  Proper operation req

uires# that 'this' server (db1 by default), as well as all other server
s, have the# proper IP addresses set in mmm_common.conf.
this db2  # 写成每个服务器的db序号

调度器配置

include mmm_common.conf

<monitor>
    ip                  127.0.0.1
    pid_path            /run/mysql-mmm-monitor.pid
    bin_path            /usr/libexec/mysql-mmm
    status_path         /var/lib/mysql-mmm/mmm_mond.status
    ping_ips            192.168.211.222,192.168.211.135	 。。。。		# 监控服务器ip
    auto_set_online     10（上线时间调整为10秒）

    # The kill_host_bin does not exist by default, though the monitor will
    # throw a warning about it missing.  See the section 5.10 "Kill Host
    # Functionality" in the PDF documentation.
    #
    # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host
    #

</monitor>

<host default>
    monitor_user        mmm_monitor		#用户
    monitor_password    RepMonitor		#密码
</host>

启动

mysql服务器

systemctl start mysql-mmm-agent

监控服务器

systemctl start mysql-mmm-monitor.service

测试群集

[root@~]# mmm_control show
db1(192.168.211.135) master/ONLINE. Roles: writer(192.168.100.250)
db2(192.168.211.222） master/ONLINE. Roles:
db3(192.168.188.138) slave/ONLINE. Roles: reader(192.168.100.251,192.168.100.252)

nitor.service