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/mysqlVer 14.14 Distrib 5.7.28, for el7 (x86_64) usingEditLine wrapper

1.2 IP 规划：

192.168.1.201 mysql01#master1192.168.1.202 mysql02#master2192.168.1.247slave1#slave192.168.1.243 monitor #monitor

2 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: 1Changed: 1Warnings: 1#update语句设置密码不过期mysql> update mysql.user set password_expired='N' where user='root';Query OK, 1 row affected (0.00 sec)Rows matched: 1Changed: 1Warnings: 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 toMASTER_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 eventMaster_Host: 192.168.1.201Master_User: repMaster_Port: 3309Connect_Retry: 60Master_Log_File: mysql-bin.000004Read_Master_Log_Pos: 76618 Relay_Log_File: relay-bin.000005Relay_Log_Pos: 76831Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: YesSlave_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 eventMaster_Host: 192.168.1.202Master_User: repMaster_Port: 3309Connect_Retry: 60Master_Log_File: mysql-bin.000009Read_Master_Log_Pos: 391 Relay_Log_File: relay-bin.000012Relay_Log_Pos: 454Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: YesSlave_SQL_Running: Yes

#slave上

mysql> change master toMASTER_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 eventMaster_Host: 192.168.1.201Master_User: repMaster_Port: 3309Connect_Retry: 60Master_Log_File: mysql-bin.000014Read_Master_Log_Pos: 65754 Relay_Log_File: relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000014 Slave_IO_Running: YesSlave_SQL_Running: Yes

4 安装MMM并配置：
4.1 #安装enpl源并安装MMM：

yum install epel-release.noarchyum install -y mysql-mmm-agentyum install -y mysql-mmm-monitor

4.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_rolewriter<host default>cluster_interface eno33554992pid_path/run/mysql-mmm-agent.pidbin_path/usr/libexec/mysql-mmm/replication_userrepreplication_passwordpassword123agent_usermmm_agentagent_passwordpassword123</host><host mysql01>ip192.168.1.201mysql_port3309modemasterpeermysql02</host><host mysql02>ip192.168.1.202mysql_port3309modemasterpeermysql01</host><host slave1>ip192.168.1.247mysql_port3309modeslave</host>#<host slave2>#ip192.168.1.242#mysql_port3310 # modeslave#</host><role writer>hosts mysql01, mysql02ips 192.168.1.200modeexclusive</role><role reader>hosts mysql01, mysql02,slave1ips 192.168.1.251, 192.168.1.252, 192.168.1.253modebalanced</role>[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_rolewriter<host default>cluster_interface eno33554992pid_path/run/mysql-mmm-agent.pidbin_path/usr/libexec/mysql-mmm/replication_userrepreplication_passwordpassword123agent_usermmm_agentagent_passwordpassword123</host><host mysql01>ip192.168.1.201mysql_port3309modemasterpeermysql02</host><host mysql02>ip192.168.1.202mysql_port3309modemasterpeermysql01</host><host slave1>ip192.168.1.247mysql_port3309modeslave</host>#<host slave2>#ip192.168.1.242#mysql_port3310#modeslave#</host><role writer>hosts mysql01, mysql02ips 192.168.1.200modeexclusive</role><role reader>hosts mysql01, mysql02,slave1ips 192.168.1.251, 192.168.1.252, 192.168.1.253modebalanced</role>[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_rolewriter<host default>cluster_interface eno16777736pid_path/run/mysql-mmm-agent.pidbin_path/usr/libexec/mysql-mmm/replication_userrepreplication_passwordpassword123agent_usermmm_agentagent_passwordpassword123mysql_port3309</host><host mysql01>ip192.168.1.201modemasterpeermysql02</host><host mysql02>ip192.168.1.202modemasterpeermysql01</host><host slave1>ip192.168.1.247modeslave</host>#<host slave2>#ip192.168.1.242#mysql_port3310#modeslave#</host><role writer>hosts mysql01, mysql02ips 192.168.1.200modeexclusive</role><role reader>hosts mysql01, mysql02,slave1ips 192.168.1.251, 192.168.1.252, 192.168.1.253modebalanced</role>[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<monitor>ip127.0.0.1pid_path/run/mysql-mmm-monitor.pidbin_path/usr/libexec/mysql-mmmstatus_path /var/lib/mysql-mmm/mmm_mond.statusping_ips192.168.1.201,192.168.1.202,192.168.1.247auto_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><host default>monitor_usermmm_monitormonitor_passwordpassword123</host>debug 0

4.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 agoProcess: 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 agoProcess: 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 agoProcess: 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 agoProcess: 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 messageping- ping monitorshow- show statuschecks [<host>|all [<check>|all]] - show checks statusset_online <host> - set host <host> onlineset_offline <host>- set host <host> offlinemode- print current mode.set_active- switch into active mode.set_manual- switch into manual mode.set_passive - switch into passive mode.move_role [--force] <role> <host> - move exclusive role <role> to host <host>(Only use --force if you know what you are doing!)set_ip <ip> <host>- set role with ip <ip> to host <host>[root@mysql01 mysql-mmm]# 

#show 状态：

[root@mysql01 mysql-mmm]# mmm_control showmysql01(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 eventMaster_Host: 192.168.1.201Master_User: repMaster_Port: 3309Connect_Retry: 60Master_Log_File: mysql-bin.000014Read_Master_Log_Pos: 65754 Relay_Log_File: relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000014 Slave_IO_Running: YesSlave_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 showmysql01(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 eventMaster_Host: 192.168.1.202Master_User: repMaster_Port: 3309Connect_Retry: 60Master_Log_File: mysql-bin.000010Read_Master_Log_Pos: 64494 Relay_Log_File: relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: YesSlave_SQL_Running: Yes

###至此mysql mmm的简单实验完成，
###开始犯了个错误，从库也是用的GTID复制，这个时候会发现MMM切换角色的时候，从库并不能自动切换主库来同步。所以从库必须用传统复制。
###这里我并没有启用半同步。