MySQL高可用MMM双主复制
参考资料: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
将备份复制到其它服务器,db2\db3\db4\db5
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;
安装复制
在db2\db3\db4\db5上配置复制
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 status\G
配置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 status\G
四:安装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