由 dawei 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 wrapper
1.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 #monitor
2 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)
4 安装MMM并配置：
4.1 #安装enpl源并安装MMM：
yum install epel-release.noarch
yum install -y mysql-mmm-agent
yum 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';