189 8069 5689
这篇文章给大家分享的是MySQL MMM高可用的部署，相信大部分人都还没学会这个技能，为了让大家学会，给大家总结了以下内容，话不多说，一起往下看吧。
创新互联坚持“要么做到，要么别承诺”的工作理念，服务领域包括：网站制作、成都网站建设、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的石鼓网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！
1 环境：
1.1 OS and MYSQL verson:
[root@mysql01 ~]# uname -a
Linux 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 #master1
192.168.1.202 mysql02 #master2
192.168.1.247 slave1 #slave
192.168.1.243 monitor #monitor2 MySQL安装：
#在所有SERVER上安装MYSQL
#准备my.cnf, 注意所有SERVER的my.cnf中，server-id要不同
# cat my.cnf
[client]
default-character-set = utf8
port = 3309
socket = /data/57.3309/mysql.sock
[mysqld]
server-id = 4
collation-server = utf8_unicode_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
port = 3309
socket = /data/57.3309/mysql.sock
datadir = /data/57.3309/data
log-error = /data/57.3309/mysql.err
pid-file = /data/57.3309/mysql.pid
gtid_mode=on
#双主设定auto-increment-increment 和auto-increment-offset 避免主键冲突
auto-increment-increment = 2
auto-increment-offset = 1
#mysql02
#auto-increment-offset = 2
#slave上不设置auto-increment-increment 和auto-increment-offset
sync_binlog = 1
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
enforce-gtid-consistency=on
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin = /data/57.3309/data/mysql-bin
relay_log = /data/57.3309/data/relay-bin
##cascaded replication for slave to write binlog.
log_slave_updates = 1
read-only=1 #所有SERVER设定read-only
binlog_format = row
slow_query_log = 1
slow_query_log_file = /data/57.3309/log/slowquery.log
long_query_time = 1
general_log = off
general_log_file = /data/57.3309/log/general.log
#skip-grant-tables
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[root@mysql01 data]# /opt/mysql7/bin/mysql_install_db --basedir='/opt/mysql7' --datadir='/data/57.3310/data' --user=mysql
2020-01-29 16:16:50 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2020-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 instead
2020-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.noarch
yum install -y mysql-mmm-agent
yum 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.conf
active_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]# ll
total 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.conf
active_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]# ll
total 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.conf
active_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.conf
include 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_threads
1月 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_agentd
1月 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_agentd
1月 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_agentd
Jan 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 --help
Invalid 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 mysql02
OK: 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高可用的部署就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果喜欢这篇文章，不如把它分享出去让更多的人看到。