参考资料：https://www.cnblogs.com/sunnydou/p/28f747c35471965e0610eda5efa06a15.html
一：环境
MySQL 5.7
Centos7
监控机：
192.168.6.143 mon01
数据库：
192.168.6.139 db1 （主节点）
192.168.6.138 db2 （备用主节点）
192.168.6.140 db3 （读节点）
192.168.6.141 db4 （读节点）
192.168.6.142 db5 （读节点）
虚拟IP：
192.168.6.100 writer
192.168.6.101 reader
192.168.6.102 reader
二：配置服务器上的MySQL
安装MySQL，使用yum源安装
echo '''[mysql-release-$basearch] name = Percona-Release YUM repository - $basearch baseurl = https://mirror.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/ gpgcheck=0''' > /etc/yum.repos.d/mysql.repo
安装MySQL
yum -y install mysql mysql-server
编辑配置文件/etc/my.cnf ，注意server-id 值每个数据库节点要不一致
[client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] basedir=/var/lib/mysql datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock pid-file=/var/run/mysqld/mysqld.pid # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 default-storage-engine = InnoDB ########basic settings######## #服务器ID，不能重复 server-id = 1 character_set_server=utf8mb4 skip_name_resolve = 1 max_connections = 1000 #MySQL读入缓冲区的大小 read_buffer_size = 16M #MySQL的随机读缓冲区大小 read_rnd_buffer_size = 8M #MySQL的顺序读缓冲区大小 sort_buffer_size = 8M ####innodb配置################# innodb_buffer_pool_size=2G innodb_log_file_size=256M innodb_flush_method=O_DIRECT #innodb独享表空间，优点很多，缺点会导致单个表文件过大 innodb_file_per_table = 1 # 默认monitor会控制mmm_agent会将writer db服务器read_only修改为OFF，其它的db服务器read_only修改为ON
# 所以为了严谨可以在所有的服务器的my.cnf文件中加入read_only=1由monitor控制来控制writer和read,root用户和复制用户不受read_only参数的影响 read_only=1 ########log settings######## log_error = /var/log/mysql/error.log #开启慢查询日志 slow_query_log = 1 #超出次设定值的SQL即被记录到慢查询日志 long_query_time = 5 slow_query_log_file = /var/log/mysql/slow.log #表示记录下没有使用索引的查询 log_queries_not_using_indexes = 1 #记录管理语句 log_slow_admin_statements = 1 #开启复制从库复制的慢查询的日志 log_slow_slave_statements = 1 #设置每分钟增长的没有使用索引查询的日志数量 log_throttle_queries_not_using_indexes = 10 expire_logs_days = 90 min_examined_row_limit = 100 log-bin = mysql-bin log-bin-index = mysql-bin.index max-binlog-size = 100M # binlog-do-db = test # binlog-ignore-db = mysql sync-binlog = 1 relay-log = mysql-relay relay-log-index = mysql-relay.index log-slave-updates = 1 # replicate-do-db = test # replicate-ignore-db = mysql #设置为主节点人数 auto_increment_increment = 2 #在每台服务器上设置为唯一的递增数字，小于auto_increment_increment（主节点为1，备用主节点为2） auto_increment_offset = 1
启动MySQL
mkdir /var/log/mysql && chown -R mysql.mysql /var/log/mysql systemctl start mysqld systemctl status mysqld systemctl stop mysqld # 关闭mysql服务 systemctl start mysqld # 开启mysql服务 systemctl enable mysqld # 将mysql设置为服务
开放3306端口
iptables -I INPUT -s 0/0 -p tcp --dport 3306 -j ACCEPT
查看mysql初始密码
grep 'temporary password' /var/log/mysql/error.log
登录并修改密码
mysql -uroot -p
set password for 'root'@'localhost'=password('123456');
设置其它用于复制和监听的账号
GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.6.%' IDENTIFIED BY 'monitor_password'; GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.6.%' IDENTIFIED BY 'agent_password'; GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.6.%' IDENTIFIED BY 'replication_password'; flush privileges; exit;
三：设置数据库节点之间的复制关系
所有服务器节点配置Host
vim /etc/hosts
192.168.6.143 mon01
192.168.6.139 db1 192.168.6.138 db2 192.168.6.140 db3 192.168.6.141 db4 192.168.6.142 db5
数据准备
db1主库安装测试用例库
yum install -y wget unzip
cd /tmp wget https://downloads.mysql.com/docs/world.sql.zip -O world.sql.zip unzip world.sql.zip mysql -u root -p < /tmp/world.sql
db1主库，获取二进制日志文件的位置
SHOW MASTER STATUS;
导出全量备份
mysqldump -u root -p --all-databases> /tmp/database-backup.sql
将备份复制到其它服务器，db2db3db4db5
scp /tmp/database-backup.sql root@192.168.6.138:/tmp scp /tmp/database-backup.sql root@192.168.6.140:/tmp scp /tmp/database-backup.sql root@192.168.6.141:/tmp scp /tmp/database-backup.sql root@192.168.6.142:/tmp
然后在其它服务器上进行数据恢复
mysql -u root -p < /tmp/database-backup.sql mysql -u root -p < /tmp/database-backup.sql mysql -u root -p < /tmp/database-backup.sql mysql -u root -p < /tmp/database-backup.sql
并刷新权限
flush privileges;
flush privileges;
flush privileges;
flush privileges;
安装复制
在db2db3db4db5上配置复制
CHANGE MASTER TO master_host ='192.168.6.139', master_port = 3306, master_user ='replication', master_password ='replication_password', master_log_file ='mysql-bin.000006', master_log_pos = 692946;
并启动slave进程
START SLAVE;
检查复制是否正确运行：
show slave statusG
配置db1和db2的双主复制
在db2上，获取二进制日志文件的位置
SHOW MASTER STATUS;
在db1上配置slave进程，并启动
CHANGE MASTER TO master_host ='192.168.6.138', master_port = 3306, master_user ='replication', master_password ='replication_password', master_log_file ='mysql-bin.000003', master_log_pos = 1014732; START SLAVE;
检查复制是否正确运行：
show slave statusG
四：安装MMM
epel源安装
CentOS默认没有mysql-mmm软件包，推荐使用epel的网络源，6台都按照epel:
yum -y install wget wget https://mirror.tuna.tsinghua.edu.cn/epel/7/x86_64/Packages/e/epel-release-7-12.noarch.rpm wget https://mirror.tuna.tsinghua.edu.cn/epel/RPM-GPG-KEY-EPEL-7 rpm --import RPM-GPG-KEY-EPEL-7 rpm -ivh epel-release-7-12.noarch.rpm
在监控主机mon01上安装控制器
yum install mysql-mmm mysql-mmm-agent mysql-mmm-tools mysql-mmm-monitor -y
在所有数据库节点上安装监听器
yum install -y mysql-mmm-agent
配置MMM
通用配置文件/etc/mysql-mmm/mmm_common.conf，所有主机上使用相同的配置
active_master_role writer <host default> cluster_interface ens33 # 群集的网络接口 pid_path /run/mysql-mmm-agent.pid bin_path /usr/libexec/mysql-mmm/ replication_user replication replication_password replication_password agent_user mmm_agent agent_password agent_password </host> <host db1> ip 192.168.6.139 mode master peer db2 </host> <host db2> ip 192.168.6.138 mode master peer db1 </host> <host db3> ip 192.168.6.140 mode slave </host> <host db4> ip 192.168.6.141 mode slave </host> <host db5> ip 192.168.6.142 mode slave </host> <role writer> hosts db1, db2 ips 192.168.6.100 mode exclusive # exclusive代表只允许存在一个主，也就是只能提供一个写的IP </role> <role reader> hosts db3, db4, db5 ips 192.168.6.101,192.168.6.102 mode balanced # balanced代表负载均衡 </role>
将配置拷贝到其它服务器（包括监控节点）
scp /etc/mysql-mmm/mmm_common.conf root@192.168.6.138:/etc/mysql-mmm/ scp /etc/mysql-mmm/mmm_common.conf root@192.168.6.140:/etc/mysql-mmm/ scp /etc/mysql-mmm/mmm_common.conf root@192.168.6.141:/etc/mysql-mmm/ scp /etc/mysql-mmm/mmm_common.conf root@192.168.6.142:/etc/mysql-mmm/ scp /etc/mysql-mmm/mmm_common.conf root@192.168.6.143:/etc/mysql-mmm/
在各数据库节点，编辑/etc/mysql-mmm/mmm_agent.conf，在其它数据库节点上修改为对应的名称db2等等
include mmm_common.conf
# 设置为本机的名称
this db1
在监控节点（mon01)上，编辑/etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf <monitor> ip 192.168.6.143 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.6.139, 192.168.6.138, 192.168.6.140, 192.168.6.141, 192.168.6.142 auto_set_online 10 # The kill_host_bin does not exist by default, though the monitor will # throw a warning 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_user mmm_monitor monitor_password monitor_password </host> debug 0
启动MMM
在所有数据库节点上，启动agent
systemctl start mysql-mmm-agent
systemctl enable mysql-mmm-agent
查看端口
iptables -I INPUT -s 0/0 -p tcp --dport 9989 -j ACCEPT # 开放9989端口 yum install -y net-tools netstat -anop |grep 9989 # 查看9989端口进程
在监控节点（mon01)上，启动monitor
systemctl start mysql-mmm-monitor
systemctl enable mysql-mmm-monitor
等待几秒让mmm启动(配置文件中auto_set_online=10，等待10秒就会自动联机）。几秒之后，检查集群状态
mmm_control show
五：验证MMM
查看集群节点状态
mmm_control checks all
对读节点进行offline/online操作
mmm_control set_offline db3
mmm_control show
mmm_control set_online db3
mmm_control show
对写节点进行offline/online操作
db1的master自动切换到db2，db1重新online，master不会逆转，需要手动切换
手动切换writer
mmm_control move_role writer db1
