mysql MMM高可用的部署
发表于：2025-01-24 作者：千家信息网编辑
千家信息网最后更新 2025年01月24日，这篇文章给大家分享的是mysql MMM高可用的部署，相信大部分人都还没学会这个技能，为了让大家学会，给大家总结了以下内容，话不多说，一起往下看吧。1 环境：1.1 OS and MYSQL vers
千家信息网最后更新 2025年01月24日mysql MMM高可用的部署
这篇文章给大家分享的是mysql 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高可用的部署就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果喜欢这篇文章，不如把它分享出去让更多的人看到。
状态
切换
密码
配置
方式
主从
内容
更多
权限
篇文章
语句
检查
登录
不同
传统
大部分
技能
时候
架构
环境
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
ntp授时服务器
给路由器设置代理服务器
法制网络安全手抄报简单又漂亮
虚拟机里的数据库怎么安装
怎样保证服务器的安全
中国工商银行客户服务器金融
django 共用数据库
中国最大的网络安全排名公司
网络安全的资料和问题
软件开发管理手法
打电话提示服务器出错怎么设置
江西网络安全委员会
河池im即时通讯软件开发
教师数据库 内容
电梯安全管理数据库
《网络安全法》网媒
数据库并发 数据锁
销售团队管理数据库
无级树数据库完美设计
君必行网络技术 叶婷婷
数据库中的数据长度指什么
计算机网络技术施晓秋第三版
杭州逸程网络技术有限公司
软件测试等软件开发之后开始
怎么进行数据库分片
炉石服务器维护
创业软件开发项目
海阳市网络安全等级培训会
华为服务器主板损坏判断
以太网中用什么网络技术
