MMM高可用实验
发表于：2025-02-21 作者：千家信息网编辑
千家信息网最后更新 2025年02月21日，1 环境：1.1 OS and MYSQL verson:[root@mysql01 ~]# uname -aLinux mysql01 3.10.0-327.18.2.el7.x86_64 #1 S
千家信息网最后更新 2025年02月21日MMM高可用实验
1 环境：
1.1 OS and MYSQL verson:
[root@mysql01 ~]# uname -aLinux mysql01 3.10.0-327.18.2.el7.x86_64 #1 SMP Thu May 12 11:03:55 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux[root@mysql01 ~]# /opt/mysql7/bin/mysql --version/opt/mysql7/bin/mysql Ver 14.14 Distrib 5.7.28, for el7 (x86_64) using EditLine wrapper1.2 IP 规划：
192.168.1.201 mysql01 #master1192.168.1.202 mysql02 #master2192.168.1.247 slave1 #slave192.168.1.243 monitor #monitor2 MySQL安装：
#在所有SERVER上安装MYSQL
#准备my.cnf, 注意所有SERVER的my.cnf中，server-id要不同
# cat my.cnf [client]default-character-set = utf8port = 3309socket = /data/57.3309/mysql.sock[mysqld]server-id = 4collation-server = utf8_unicode_ciinit-connect = 'SET NAMES utf8'character-set-server = utf8port = 3309socket = /data/57.3309/mysql.sockdatadir = /data/57.3309/datalog-error = /data/57.3309/mysql.errpid-file = /data/57.3309/mysql.pidgtid_mode=on#双主设定auto-increment-increment 和auto-increment-offset 避免主键冲突auto-increment-increment = 2auto-increment-offset = 1#mysql02#auto-increment-offset = 2#slave上不设置auto-increment-increment 和auto-increment-offset sync_binlog = 1sync_master_info = 1sync_relay_log = 1sync_relay_log_info = 1enforce-gtid-consistency=onskip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Mlog-bin = /data/57.3309/data/mysql-binrelay_log = /data/57.3309/data/relay-bin##cascaded replication for slave to write binlog.log_slave_updates = 1read-only=1 #所有SERVER设定read-onlybinlog_format = rowslow_query_log = 1slow_query_log_file = /data/57.3309/log/slowquery.loglong_query_time = 1general_log = offgeneral_log_file = /data/57.3309/log/general.log#skip-grant-tables[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout[root@mysql01 data]# /opt/mysql7/bin/mysql_install_db --basedir='/opt/mysql7' --datadir='/data/57.3310/data' --user=mysql2020-01-29 16:16:50 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize2020-01-29 16:16:54 [WARNING] The bootstrap log isn't empty:2020-01-29 16:16:54 [WARNING] 2020-01-29T08:16:50.886558Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead2020-01-29T08:16:50.887365Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)2020-01-29T08:16:50.887370Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)#第一次：skip_grant_tables方式启动[root@mysql01 57.3310]# /opt/mysql7/bin/mysqld_safe --defaults-file='/data/57.3310/my.cnf' --skip-grant-tables --user=root &Logging to '/data/57.3310/mysql.err'.2020-01-29T08:39:04.537600Z mysqld_safe Starting mysqld daemon with databases from /data/57.3310/data#修改root密码 /opt/mysql7/bin/mysql -uroot -S /data/57.3310/mysql.soc #免密码登录 #update语句修改root密码 mysql> update mysql.user set authentication_string=password('password123') where user='root' and host='localhost';Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 1#update语句设置密码不过期mysql> update mysql.user set password_expired='N' where user='root';Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0#刷新权限mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)#正常关闭，重启MYSQL并登录MYSQL /opt/mysql7/bin/mysqladmin -uroot -ppassword123 -S /data/57.3310/mysql.sock shutdown /opt/mysql7/bin/mysqld_safe --defaults-file='/data/57.3310/my.cnf' --user=root &/opt/mysql7/bin/mysql -uroot -ppassword123 -S /data/57.3310/mysql.sock#权限,所有SERVER上。mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY 'password123';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)3 设定MYSQL主从复制
3.1 #复制架构：mysql01 <===>mysql02 主主复制，GTID方式，
mysql01===>slave 主从复制，传统方式，
3.2 #mysql01 <===>mysql02
#mysql02上：
mysql> change master to MASTER_HOST='192.168.1.201',MASTER_USER='rep',MASTER_PASSWORD='password123',MASTER_PORT=3309,master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql>mysql>mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.201 Master_User: rep Master_Port: 3309 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 76618 Relay_Log_File: relay-bin.000005 Relay_Log_Pos: 76831 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes#mysql01上：
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.202',MASTER_PORT=3309,MASTER_USER='rep',MASTER_PASSWORD='password123',MASTER_AUTO_POSITION = 1;Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.202 Master_User: rep Master_Port: 3309 Connect_Retry: 60 Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 391 Relay_Log_File: relay-bin.000012 Relay_Log_Pos: 454 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes#slave上
mysql> change master to MASTER_HOST='192.168.1.201',MASTER_USER='rep',MASTER_PASSWORD='password123',MASTER_PORT=3309,MASTER_LOG_FILE='mysql-bin.000014',MASTER_LOG_POS=65754；Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.201 Master_User: rep Master_Port: 3309 Connect_Retry: 60 Master_Log_File: mysql-bin.000014 Read_Master_Log_Pos: 65754 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000014 Slave_IO_Running: Yes Slave_SQL_Running: Yes4 安装MMM并配置：
4.1 #安装enpl源并安装MMM：
yum install epel-release.noarchyum install -y mysql-mmm-agentyum install -y mysql-mmm-monitor4.2 #配置mmm用户，由于是全库复制，只要在mysql01上配置，会自动同步到其他SERVER：
GRANT PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'mmm_agent'@'192.168.1.%' IDENTIFIED BY 'password123';GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.1.%' IDENTIFIED BY 'password123';4.3 #配置mmm设定档：
#mysql01(master1)
cd /etc/mysql-mmm/cat mmm_common.confactive_master_role writer cluster_interface eno33554992 pid_path /run/mysql-mmm-agent.pid bin_path /usr/libexec/mysql-mmm/ replication_user rep replication_password password123 agent_user mmm_agent agent_password password123 ip 192.168.1.201 mysql_port 3309 mode master peer mysql02 ip 192.168.1.202 mysql_port 3309 mode master peer mysql01 ip 192.168.1.247 mysql_port 3309 mode slave ## ip 192.168.1.242# mysql_port 3310 # mode slave# hosts mysql01, mysql02 ips 192.168.1.200 mode exclusive hosts mysql01, mysql02,slave1 ips 192.168.1.251, 192.168.1.252, 192.168.1.253 mode balanced [root@mysql01 mysql-mmm]# cat mmm_agent.conf include mmm_common.conf# The 'this' variable refers to this server. Proper operation requires# that 'this' server (db1 by default), as well as all other servers, have the# proper IP addresses set in mmm_common.conf.this mysql01 #mysql02(master2)上：
[root@mysql02 mysql-mmm]# lltotal 8-rw-r-----. 1 root root 235 Jan 29 20:54 mmm_agent.conf-rw-r-----. 1 root root 991 Jan 30 14:02 mmm_common.conf[root@mysql02 mysql-mmm]# cat mmm_common.confactive_master_role writer cluster_interface eno33554992 pid_path /run/mysql-mmm-agent.pid bin_path /usr/libexec/mysql-mmm/ replication_user rep replication_password password123 agent_user mmm_agent agent_password password123 ip 192.168.1.201 mysql_port 3309 mode master peer mysql02 ip 192.168.1.202 mysql_port 3309 mode master peer mysql01 ip 192.168.1.247 mysql_port 3309 mode slave ## ip 192.168.1.242# mysql_port 3310# mode slave# hosts mysql01, mysql02 ips 192.168.1.200 mode exclusive hosts mysql01, mysql02,slave1 ips 192.168.1.251, 192.168.1.252, 192.168.1.253 mode balanced [root@mysql02 mysql-mmm]# cat mmm_agent.conf include mmm_common.conf# The 'this' variable refers to this server. Proper operation requires# that 'this' server (db1 by default), as well as all other servers, have the# proper IP addresses set in mmm_common.conf.this mysql02 [root@mysql02 mysql-mmm]# #slave上：
[root@salve1 mysql-mmm]# pwd/etc/mysql-mmm[root@salve1 mysql-mmm]# lltotal 8-rw-r-----. 1 root root 234 Jan 30 14:11 mmm_agent.conf-rw-r-----. 1 root root 945 Jan 30 14:56 mmm_common.conf[root@salve1 mysql-mmm]# cat mmm_common.confactive_master_role writer cluster_interface eno16777736 pid_path /run/mysql-mmm-agent.pid bin_path /usr/libexec/mysql-mmm/ replication_user rep replication_password password123 agent_user mmm_agent agent_password password123 mysql_port 3309 ip 192.168.1.201 mode master peer mysql02 ip 192.168.1.202 mode master peer mysql01 ip 192.168.1.247 mode slave ## ip 192.168.1.242# mysql_port 3310# mode slave# hosts mysql01, mysql02 ips 192.168.1.200 mode exclusive hosts mysql01, mysql02,slave1 ips 192.168.1.251, 192.168.1.252, 192.168.1.253 mode balanced [root@salve1 mysql-mmm]# cat mmm_agent.conf include mmm_common.conf# The 'this' variable refers to this server. Proper operation requires# that 'this' server (db1 by default), as well as all other servers, have the# proper IP addresses set in mmm_common.conf.this slave1 [root@salve1 mysql-mmm]# #monitor上：
[root@mysql01 mysql-mmm]# cat mmm_mon.confinclude mmm_common.conf 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.1.201,192.168.1.202,192.168.1.247 auto_set_online 60 # The kill_host_bin does not exist by default, though the monitor will # throw a wning 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_user mmm_monitor monitor_password password123 debug 04.4 启动monitor和agent
#monitor:
systemctl enable mysql-mmm-monitor.service #加入启动项systemctl start mysql-mmm-monitor.service #启动monitor#状态：[root@mysql01 mysql-mmm]# systemctl status mysql-mmm-monitor.service * mysql-mmm-monitor.service - MySQL MMM Monitor Loaded: loaded (/usr/lib/systemd/system/mysql-mmm-monitor.service; enabled; vendor preset: disabled) Active: active (running) since 四 2020-01-30 19:52:04 CST; 21h ago Process: 1464 ExecStart=/usr/sbin/mmm_mond (code=exited, status=0/SUCCESS) Main PID: 2703 (mmm_mond) CGroup: /system.slice/mysql-mmm-monitor.service |-2703 mmm_mond |-2704 mmm_mond |-2848 perl /usr/libexec/mysql-mmm/monitor/checker ping_ip |-2851 perl /usr/libexec/mysql-mmm/monitor/checker mysql |-2853 perl /usr/libexec/mysql-mmm/monitor/checker ping |-2855 perl /usr/libexec/mysql-mmm/monitor/checker rep_backlog `-2858 perl /usr/libexec/mysql-mmm/monitor/checker rep_threads1月 30 19:51:53 mysql01 systemd[1]: Starting MySQL MMM Monitor...1月 30 19:52:04 mysql01 systemd[1]: Started MySQL MMM Monitor.#mysql01(master1):
systemctl enable mysql-mmm-agent.service #加入启动项systemctl start mysql-mmm-agent.service #启动mmm agent#状态：[root@mysql01 mysql-mmm]# systemctl status mysql-mmm-agent.service * mysql-mmm-agent.service - MySQL MMM agent Loaded: loaded (/usr/lib/systemd/system/mysql-mmm-agent.service; enabled; vendor preset: disabled) Active: active (running) since 四 2020-01-30 19:52:03 CST; 21h ago Process: 1459 ExecStart=/usr/sbin/mmm_agentd (code=exited, status=0/SUCCESS) Main PID: 2695 (mmm_agentd) CGroup: /system.slice/mysql-mmm-agent.service |-2695 mmm_agentd `-2909 mmm_agentd1月 30 19:51:53 mysql01 systemd[1]: Starting MySQL MMM agent...1月 30 19:52:03 mysql01 systemd[1]: Started MySQL MMM agent.1月 30 21:08:07 mysql01 systemd[1]: Started MySQL MMM agent.[root@mysql01 mysql-mmm]# #mysql02(master2):
systemctl enable mysql-mmm-agent.service #加入启动项systemctl start mysql-mmm-agent.service #启动mmm agent#状态：[root@mysql02 mysql-mmm]# systemctl status mysql-mmm-agent.service * mysql-mmm-agent.service - MySQL MMM agent Loaded: loaded (/usr/lib/systemd/system/mysql-mmm-agent.service; enabled; vendor preset: disabled) Active: active (running) since 四 2020-01-30 19:52:03 CST; 21h ago Process: 1459 ExecStart=/usr/sbin/mmm_agentd (code=exited, status=0/SUCCESS) Main PID: 2695 (mmm_agentd) CGroup: /system.slice/mysql-mmm-agent.service |-2695 mmm_agentd `-2909 mmm_agentd1月 30 19:51:53 mysql02 systemd[1]: Starting MySQL MMM agent...1月 30 19:52:03 mysql02 systemd[1]: Started MySQL MMM agent.1月 30 21:08:07 mysql02 systemd[1]: Started MySQL MMM agent.[root@mysql02 mysql-mmm]# #slave：
systemctl enable mysql-mmm-agent.service #加入启动项systemctl start mysql-mmm-agent.service #启动mmm agent#状态[root@salve1 mysql-mmm]# systemctl status mysql-mmm-agent.service [0m mysql-mmm-agent.service - MySQL MMM agent Loaded: loaded (/usr/lib/systemd/system/mysql-mmm-agent.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2020-01-30 21:07:52 CST; 20h ago Process: 5323 ExecStart=/usr/sbin/mmm_agentd (code=exited, status=0/SUCCESS) Main PID: 5325 (mmm_agentd) CGroup: /system.slice/mysql-mmm-agent.service 25 mmm_agentd 26 mmm_agentdJan 30 21:07:51 mysql01 systemd[1]: Starting MySQL MMM agent...Jan 30 21:07:52 mysql01 systemd[1]: Started MySQL MMM agent.[root@salve1 mysql-mmm]# 5 #查看MMM状态：（monitor上）
[root@mysql01 mysql-mmm]# mmm_control --helpInvalid command '--help'Valid commands are: help - show this message ping - ping monitor show - show status checks [|all [|all]] - show checks status set_online - set host online set_offline - set host offline mode - print current mode. set_active - switch into active mode. set_manual - switch into manual mode. set_passive - switch into passive mode. move_role [--force] - move exclusive role to host (Only use --force if you know what you are doing!) set_ip - set role with ip to host [root@mysql01 mysql-mmm]# #show 状态：
[root@mysql01 mysql-mmm]# mmm_control show mysql01(192.168.1.201) master/ONLINE. Roles: reader(192.168.1.251), writer(192.168.1.200) mysql02(192.168.1.202) master/ONLINE. Roles: reader(192.168.1.253) slave1(192.168.1.247) slave/ONLINE. Roles: reader(192.168.1.252)#尝试切换
#切换前检查slave复制状态：可以看到这个时候的主库是mysql01
mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.201 Master_User: rep Master_Port: 3309 Connect_Retry: 60 Master_Log_File: mysql-bin.000014 Read_Master_Log_Pos: 65754 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000014 Slave_IO_Running: Yes Slave_SQL_Running: Yes#切换：
[root@mysql01 mysql-mmm]# mmm_control move_role writer mysql02OK: Role 'writer' has been moved from 'mysql01' to 'mysql02'. Now you can wait some time and check new roles info![root@mysql01 mysql-mmm]# [root@mysql01 mysql-mmm]# mmm_control show mysql01(192.168.1.201) master/ONLINE. Roles: reader(192.168.1.251) mysql02(192.168.1.202) master/ONLINE. Roles: reader(192.168.1.253), writer(192.168.1.200) slave1(192.168.1.247) slave/ONLINE. Roles: reader(192.168.1.252)#可以看到已经把writer角色切换到mysql02上
#检查slave复制状态，自动切换到mysql02上
mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.202 Master_User: rep Master_Port: 3309 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 64494 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: Yes###至此mysql mmm的简单实验完成，
###开始犯了个错误，从库也是用的GTID复制，这个时候会发现MMM切换角色的时候，从库并不能自动切换主库来同步。所以从库必须用传统复制。
###这里我并没有启用半同步。
状态
切换
密码
配置
方式
时候
同步
主从
传统
权限
角色
语句
检查
登录
实验
不同
架构
环境
用户
第一次
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
数据库地址填写错误
工行软件开发中心属于国企吗
惠州支付软件开发咨询
在哪申诉网易服务器
双人夺宝网络技术平台
幻塔最新服务器开服时间
深圳的游戏软件开发工资
网络操作系统和服务器区别
网络安全员合格证
嵌入式服务器的设计
疫情信息网络技术建设
德惠网络技术咨询诚信合作
最强二次元网络技术
网络安全生产教育试题
制定网络安全政策和技术标准
西宁网络安全技术提升费用多少
数据库 活锁
软件开发团队队名
php获取数据库最新内容
《网络安全法》的意义是什么
注册资料与数据库
软件开发收费项目明细
维普数据库检索技术
金蝶kis标准版数据库数据
区块系统软件开发男
廊坊app软件开发
大学专业的json数据库
软件开发专业实训报告
网络安全等同于国家安全
服务器可以安装光驱吗
