3M 高可用架构----拓展
3M 高可用架构
一、MMM
1. MMM的概述
MMM（Master-Master replication manager for MySQL,MySQL主主复制管理器）是一套支持双主故障切换和双主日常管理的脚本程序。MMM使用Perl语言开发，主要从来监控和管理MySQL Master-Master（双主）复制，虽然叫做双主复制，但是业务上同一时刻只允许一个主进行写入，另一台备选主上提供部分读服务，以加速在主主切换时备选主的预热，可以说MMM这套脚本程序一方面实现了故障切换的功能，另一方面其内部附加的工具也可以实现多个Slave的read负载均衡。
2. MMM的应用场景
MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip，同时它还可以备份数据，实现两节点之间的数据同步等。由于MMM无法完全保证数据的一致性，所以MMM适用于对数据的一致性要求不是很高的，但是又想最大程度地保证业务可用性的场景。对于那些对数据的一致性要求很高的业务，非常不建议采用MMM这种高可用架构。
3. MMM的特点
● MMM是一套灵活的脚本程序
● 基于perl语言实现
● 用来对mysql replication进行监控和故障迁移
● 管理MySQL Master-Master复制的配置
4. 关于MMM高可用架构的说明
● mmm_mon：监控进程，负责所有的监控工作，决定和处理所有节点角色活动。此脚本需要在监管机上运行。
● mmm_agent：运行在每个MySQL服务器上的代理进程，完成监控的探针工作和执行简单的远端服务设置。此脚本需要在被监管机上运行。
● mmm_control：一个简单的脚本，提供管理mmm_mond进程的命令。
● mysql-mmm的监管端会提供多个虚拟IP（VIP），包括一个可写VIP，多个可读VIP，通过监管的管理，这些IP会绑定在可用MySQL之上，当某一台MySQL宕机时，监管会将VIP迁移至其他MySQL。
5. 用户及授权
在整个监管过程中，需要在MySQL中添加相关授权yoghurt，以便让MySQL可以支持监理机的维护。授权的用户包括一个mmm_monitor用户和一个mmm_agent用户，如果想使用MMM的备份工具则还需要添加一个mmm_tools用户。
二、案例环境
1. 服务器配置
| 服务器 | 主机名 | 操作系统 | IP地址 | 主要软件 |
|---|---|---|---|---|
| Master1服务器 | master1 | CentOS 7.4 | 192.168.122.10 | MySQL 5.7、MySQL-MMM |
| Master2服务器 | master2 | CentOS 7.4 | 192.168.122.11 | MySQL 5.7、MySQL-MMM |
| Slave1服务器 | slave1 | CentOS 7.4 | 192.168.122.100 | MySQL 5.7、MySQL-MMM |
| Slave2服务器 | slave2 | CentOS 7.4 | 192.168.122.101 | MySQL 5.7、MySQL-MMM |
| Monitor服务器 | monitor | CentOS 7.4 | 192.168.122.12 | MySQL-MMM |
2. 服务器环境
| systemctl stop firewalld | |
| systemctl disable firewalld | |
| setenforce 0 |
3. 修改主机名称
Master1服务器（192.168.122.10）
[root@localhost ~]# hostnamectl set-hostname master1
Master2服务器（192.168.122.11）
[root@localhost ~]# hostnamectl set-hostname master2
Slave1服务器（192.168.122.100）
[root@localhost ~]# hostnamectl set-hostname slave1
Slave2服务器（192.168.122.101）
[root@localhost ~]# hostnamectl set-hostname slave2
Monitor服务器（192.168.122.12）
[root@localhost ~]# hostnamectl set-hostname monitor
三、案例实施
1. 搭建MySQL多主多从架构
1.1 master1、master2、slave1、slave2节点上安装mysql5.7
刷下列脚本，过程忽略
| #!/bin/bash | |
| systemctl stop firewalld | |
| systemctl disable firewalld | |
| setenforce 0 | |
| #--------mysql-------- | |
| #安装依赖包 | |
| yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake | |
| #配置软件模块 | |
| cd /opt/ | |
| tar zxvf mysql-5.7.17.tar.gz | |
| tar zxvf boost_1_59_0.tar.gz | |
| mv boost_1_59_0 /usr/local/boost | |
| cd /opt/mysql-5.7.17/ | |
| cmake \ | |
| -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ | |
| -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ | |
| -DSYSCONFDIR=/etc \ | |
| -DSYSTEMD_PID_DIR=/usr/local/mysql \ | |
| -DDEFAULT_CHARSET=utf8 \ | |
| -DDEFAULT_COLLATION=utf8_general_ci \ | |
| -DWITH_EXTRA_CHARSETS=all \ | |
| -DWITH_INNOBASE_STORAGE_ENGINE=1 \ | |
| -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ | |
| -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ | |
| -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \ | |
| -DMYSQL_DATADIR=/usr/local/mysql/data \ | |
| -DWITH_BOOST=/usr/local/boost \ | |
| -DWITH_SYSTEMD=1 | |
| #编译安装 | |
| make -j 2 && make install | |
| #创建mysql用户 | |
| useradd -M -s /sbin/nologin mysql | |
| #修改mysql 配置文件 | |
| echo '[client] | |
| port = 3306 | |
| socket=/usr/local/mysql/mysql.sock | |
| [mysql] | |
| port = 3306 | |
| socket = /usr/local/mysql/mysql.sock | |
| auto-rehash | |
| [mysqld] | |
| user = mysql | |
| basedir=/usr/local/mysql | |
| datadir=/usr/local/mysql/data | |
| port = 3306 | |
| socket=/usr/local/mysql/mysql.sock | |
| skip-name-resolve | |
| max_connections=2048 | |
| max_allowed_packet=16M | |
| sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES' > /etc/my.cnf | |
| #更改mysql安装目录和配置文件的属主属组 | |
| chown -R mysql:mysql /usr/local/mysql/ | |
| chown mysql:mysql /etc/my.cnf | |
| #设置路径环境变量 | |
| echo 'export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile | |
| source /etc/profile | |
| #初始化数据库 | |
| cd /usr/local/mysql/bin/ | |
| --initialize-insecure \ | |
| --user=mysql \ | |
| --basedir=/usr/local/mysql \ | |
| --datadir=/usr/local/mysql/data | |
| #添加mysqld系统服务 | |
| cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/ | |
| systemctl daemon-reload | |
| systemctl start mysqld.service | |
| systemctl enable mysqld | |
| yum -y install expect | |
| mima () { | |
| passwd=$1 | |
| spawn mysqladmin -u root -p password $passwd | |
| expect "Enter password:" | |
| send "\r" | |
| expect eof | |
| EOF | |
| } | |
| mima "123456" | |
| dl () { | |
| spawn mysql -u root -p | |
| expect "Enter password:" {send "123456\r"} | |
| expect "mysql>" {send "grant all privileges on *.* to 'root'@'%' identified by '123456';\r"} | |
| expect "mysql>" {send "quit\r"} | |
| expect eof | |
| EOF | |
| } | |
| dl |
1.2 修改master1配置文件
Master1服务器（192.168.122.10）
| [root@master1 ~]# vim /etc/my.cnf | |
| ...... | |
| [mysqld] | |
| user = mysql | |
| basedir = /usr/local/mysql | |
| datadir = /usr/local/mysql/data | |
| port = 3306 | |
| character_set_server=utf8 | |
| pid-file = /usr/local/mysql/mysqld.pid | |
| socket = /usr/local/mysql/mysql.sock | |
| server-id = 1 | |
| #每台 Mysql 主机的 server-id 不能相同 | |
| log-error=/usr/local/mysql/data/mysql_error.log | |
| #错误日志 | |
| general_log=ON | |
| #通用查询日志 | |
| general_log_file=/usr/local/mysql/data/mysql_general.log | |
| slow_query_log=ON | |
| #慢查询日志 | |
| slow_query_log_file=mysql_slow_query.log | |
| long_query_time=5 | |
| binlog-ignore-db=mysql,information_schema | |
| #不需要同步的库名 | |
| log_bin=mysql_bin | |
| #开启二进制日志用于主从数据复制 | |
| log_slave_updates=true | |
| #允许slave从master复制数据时可以写入到自己的二进制日志 | |
| sync_binlog=1 | |
| #"双1设置"，MySQL 在每写一次二进制日志时都会同步到磁盘中去 | |
| innodb_flush_log_at_trx_commit=1 | |
| #"双1设置"，每次事务提交时MySQL都会把缓存的数据写入日志文件，并且刷到磁盘中去 | |
| auto_increment_increment=2 | |
| #自增字段一次递增多少 | |
| auto_increment_offset=1 | |
| #自增字段的起始值 | |
| [root@master1 ~]# systemctl restart mysqld |
1.3 把配置文件复制到其他3台数据库服务器并重启mysql服务器
注意：配置文件中的server-id不可相同，需要修改。
| [root@master1 ~]# scp /etc/my.cnf root@192.168.122.11:/etc/ | |
| [root@master1 ~]# scp /etc/my.cnf root@192.168.122.100:/etc/ | |
| [root@master1 ~]# scp /etc/my.cnf root@192.168.122.101:/etc/ |
1.4 配置主主复制，两台主服务器相互复制
1.4.1 在两台主服务器上都执行授予从的权限，从服务器上不需要执行
Master1服务器（192.168.122.10）
| [root@master1 ~]# mysql -u root -p | |
| Enter password: | |
| mysql> grant replication slave on *.* to 'replication'@'192.168.122.%' identified by '123456'; | |
| Query OK, 0 rows affected, 1 warning (0.00 sec) |
Master2服务器（192.168.122.11）
| [root@master2 ~]# mysql -u root -p | |
| Enter password: | |
| mysql> grant replication slave on *.* to 'replication'@'192.168.122.%' identified by '123456'; | |
| Query OK, 0 rows affected, 1 warning (0.00 sec) |
1.4.2 在两台主服务器上查看
Master1服务器（192.168.122.10）
| mysql> show master status; | |
| +------------------+----------+--------------+--------------------------+-------------------+ | |
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | | |
| +------------------+----------+--------------+--------------------------+-------------------+ | |
| | mysql_bin.000001 | 154 | | mysql,information_schema | | | |
| +------------------+----------+--------------+--------------------------+-------------------+ | |
| 1 row in set (0.00 sec) |
Master2服务器（192.168.122.11）
| mysql> show master status; | |
| +------------------+----------+--------------+--------------------------+-------------------+ | |
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | | |
| +------------------+----------+--------------+--------------------------+-------------------+ | |
| | mysql_bin.000001 | 154 | | mysql,information_schema | | | |
| +------------------+----------+--------------+--------------------------+-------------------+ | |
| 1 row in set (0.00 sec) |
1.4.3 在master1上配置同步
Master1服务器（192.168.122.10）
| mysql> change master to | |
| -> master_host='192.168.122.11', | |
| -> master_user='replication', | |
| -> master_password='123456', | |
| -> master_log_file='mysql_bin.000001', | |
| -> master_log_pos=154; | |
| 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.122.11 | |
| Master_User: replication | |
| Master_Port: 3306 | |
| Connect_Retry: 60 | |
| Master_Log_File: mysql_bin.000001 | |
| Read_Master_Log_Pos: 154 | |
| Relay_Log_File: master1-relay-bin.000002 | |
| Relay_Log_Pos: 320 | |
| Relay_Master_Log_File: mysql_bin.000001 | |
| Slave_IO_Running: Yes | |
| Slave_SQL_Running: Yes | |
| Replicate_Do_DB: | |
| Replicate_Ignore_DB: | |
| Replicate_Do_Table: | |
| Replicate_Ignore_Table: | |
| Replicate_Wild_Do_Table: | |
| Replicate_Wild_Ignore_Table: | |
| Last_Errno: 0 | |
| Last_Error: | |
| Skip_Counter: 0 | |
| Exec_Master_Log_Pos: 154 | |
| Relay_Log_Space: 529 | |
| Until_Condition: None | |
| Until_Log_File: | |
| Until_Log_Pos: 0 | |
| Master_SSL_Allowed: No | |
| Master_SSL_CA_File: | |
| Master_SSL_CA_Path: | |
| Master_SSL_Cert: | |
| Master_SSL_Cipher: | |
| Master_SSL_Key: | |
| Seconds_Behind_Master: 0 | |
| Master_SSL_Verify_Server_Cert: No | |
| Last_IO_Errno: 0 | |
| Last_IO_Error: | |
| Last_SQL_Errno: 0 | |
| Last_SQL_Error: | |
| Replicate_Ignore_Server_Ids: | |
| Master_Server_Id: 2 | |
| Master_UUID: 51f017b7-0fe5-11ec-a583-000c299463f1 | |
| Master_Info_File: /usr/local/mysql/data/master.info | |
| SQL_Delay: 0 | |
| SQL_Remaining_Delay: NULL | |
| Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates | |
| Master_Retry_Count: 86400 | |
| Master_Bind: | |
| Last_IO_Error_Timestamp: | |
| Last_SQL_Error_Timestamp: | |
| Master_SSL_Crl: | |
| Master_SSL_Crlpath: | |
| Retrieved_Gtid_Set: | |
| Executed_Gtid_Set: | |
| Auto_Position: 0 | |
| Replicate_Rewrite_DB: | |
| Channel_Name: | |
| Master_TLS_Version: | |
| 1 row in set (0.00 sec) |
1.4.4 在master2上配置同步
Master2服务器（192.168.122.11）
| mysql> change master to | |
| -> master_host='192.168.122.10', | |
| -> master_user='replication', | |
| -> master_password='123456', | |
| -> master_log_file='mysql_bin.000001', | |
| -> master_log_pos=154; | |
| Query OK, 0 rows affected, 2 warnings (0.02 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.122.10 | |
| Master_User: replication | |
| Master_Port: 3306 | |
| Connect_Retry: 60 | |
| Master_Log_File: mysql_bin.000001 | |
| Read_Master_Log_Pos: 154 | |
| Relay_Log_File: master2-relay-bin.000002 | |
| Relay_Log_Pos: 320 | |
| Relay_Master_Log_File: mysql_bin.000001 | |
| Slave_IO_Running: Yes | |
| Slave_SQL_Running: Yes | |
| Replicate_Do_DB: | |
| Replicate_Ignore_DB: | |
| Replicate_Do_Table: | |
| Replicate_Ignore_Table: | |
| Replicate_Wild_Do_Table: | |
| Replicate_Wild_Ignore_Table: | |
| Last_Errno: 0 | |
| Last_Error: | |
| Skip_Counter: 0 | |
| Exec_Master_Log_Pos: 154 | |
| Relay_Log_Space: 529 | |
| Until_Condition: None | |
| Until_Log_File: | |
| Until_Log_Pos: 0 | |
| Master_SSL_Allowed: No | |
| Master_SSL_CA_File: | |
| Master_SSL_CA_Path: | |
| Master_SSL_Cert: | |
| Master_SSL_Cipher: | |
| Master_SSL_Key: | |
| Seconds_Behind_Master: 0 | |
| Master_SSL_Verify_Server_Cert: No | |
| Last_IO_Errno: 0 | |
| Last_IO_Error: | |
| Last_SQL_Errno: 0 | |
| Last_SQL_Error: | |
| Replicate_Ignore_Server_Ids: | |
| Master_Server_Id: 1 | |
| Master_UUID: 7587444a-0fe5-11ec-ac87-000c2959bebe | |
| Master_Info_File: /usr/local/mysql/data/master.info | |
| SQL_Delay: 0 | |
| SQL_Remaining_Delay: NULL | |
| Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates | |
| Master_Retry_Count: 86400 | |
| Master_Bind: | |
| Last_IO_Error_Timestamp: | |
| Last_SQL_Error_Timestamp: | |
| Master_SSL_Crl: | |
| Master_SSL_Crlpath: | |
| Retrieved_Gtid_Set: | |
| Executed_Gtid_Set: | |
| Auto_Position: 0 | |
| Replicate_Rewrite_DB: | |
| Channel_Name: | |
| Master_TLS_Version: | |
| 1 row in set (0.00 sec) |
1.5 配置主从复制，在两台从服务器上做
Slave1服务器（192.168.122.100）
| [root@slave1 ~]# mysql -u root -p | |
| Enter password: | |
| mysql> change master to | |
| -> master_host='192.168.122.10', | |
| -> master_user='replication', | |
| -> master_password='123456', | |
| -> master_log_file='mysql_bin.000001', | |
| -> master_log_pos=154; | |
| Query OK, 0 rows affected, 2 warnings (0.01 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.122.10 | |
| Master_User: replication | |
| Master_Port: 3306 | |
| Connect_Retry: 60 | |
| Master_Log_File: mysql_bin.000001 | |
| Read_Master_Log_Pos: 154 | |
| Relay_Log_File: slave1-relay-bin.000002 | |
| Relay_Log_Pos: 320 | |
| Relay_Master_Log_File: mysql_bin.000001 | |
| Slave_IO_Running: Yes | |
| Slave_SQL_Running: Yes | |
| Replicate_Do_DB: | |
| Replicate_Ignore_DB: | |
| Replicate_Do_Table: | |
| Replicate_Ignore_Table: | |
| Replicate_Wild_Do_Table: | |
| Replicate_Wild_Ignore_Table: | |
| Last_Errno: 0 | |
| Last_Error: | |
| Skip_Counter: 0 | |
| Exec_Master_Log_Pos: 154 | |
| Relay_Log_Space: 528 | |
| Until_Condition: None | |
| Until_Log_File: | |
| Until_Log_Pos: 0 | |
| Master_SSL_Allowed: No | |
| Master_SSL_CA_File: | |
| Master_SSL_CA_Path: | |
| Master_SSL_Cert: | |
| Master_SSL_Cipher: | |
| Master_SSL_Key: | |
| Seconds_Behind_Master: 0 | |
| Master_SSL_Verify_Server_Cert: No | |
| Last_IO_Errno: 0 | |
| Last_IO_Error: | |
| Last_SQL_Errno: 0 | |
| Last_SQL_Error: | |
| Replicate_Ignore_Server_Ids: | |
| Master_Server_Id: 1 | |
| Master_UUID: 7587444a-0fe5-11ec-ac87-000c2959bebe | |
| Master_Info_File: /usr/local/mysql/data/master.info | |
| SQL_Delay: 0 | |
| SQL_Remaining_Delay: NULL | |
| Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates | |
| Master_Retry_Count: 86400 | |
| Master_Bind: | |
| Last_IO_Error_Timestamp: | |
| Last_SQL_Error_Timestamp: | |
| Master_SSL_Crl: | |
| Master_SSL_Crlpath: | |
| Retrieved_Gtid_Set: | |
| Executed_Gtid_Set: | |
| Auto_Position: 0 | |
| Replicate_Rewrite_DB: | |
| Channel_Name: | |
| Master_TLS_Version: | |
| 1 row in set (0.00 sec) |
Slave2服务器（192.168.122.101）
| [root@slave2 ~]# mysql -u root -p | |
| Enter password: | |
| mysql> change master to | |
| -> master_host='192.168.122.10', | |
| -> master_user='replication', | |
| -> master_password='123456', | |
| -> master_log_file='mysql_bin.000001', | |
| -> master_log_pos=154; | |
| Query OK, 0 rows affected, 2 warnings (0.01 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.122.10 | |
| Master_User: replication | |
| Master_Port: 3306 | |
| Connect_Retry: 60 | |
| Master_Log_File: mysql_bin.000001 | |
| Read_Master_Log_Pos: 154 | |
| Relay_Log_File: slave2-relay-bin.000002 | |
| Relay_Log_Pos: 320 | |
| Relay_Master_Log_File: mysql_bin.000001 | |
| Slave_IO_Running: Yes | |
| Slave_SQL_Running: Yes | |
| Replicate_Do_DB: | |
| Replicate_Ignore_DB: | |
| Replicate_Do_Table: | |
| Replicate_Ignore_Table: | |
| Replicate_Wild_Do_Table: | |
| Replicate_Wild_Ignore_Table: | |
| Last_Errno: 0 | |
| Last_Error: | |
| Skip_Counter: 0 | |
| Exec_Master_Log_Pos: 154 | |
| Relay_Log_Space: 528 | |
| Until_Condition: None | |
| Until_Log_File: | |
| Until_Log_Pos: 0 | |
| Master_SSL_Allowed: No | |
| Master_SSL_CA_File: | |
| Master_SSL_CA_Path: | |
| Master_SSL_Cert: | |
| Master_SSL_Cipher: | |
| Master_SSL_Key: | |
| Seconds_Behind_Master: 0 | |
| Master_SSL_Verify_Server_Cert: No | |
| Last_IO_Errno: 0 | |
| Last_IO_Error: | |
| Last_SQL_Errno: 0 | |
| Last_SQL_Error: | |
| Replicate_Ignore_Server_Ids: | |
| Master_Server_Id: 1 | |
| Master_UUID: 7587444a-0fe5-11ec-ac87-000c2959bebe | |
| Master_Info_File: /usr/local/mysql/data/master.info | |
| SQL_Delay: 0 | |
| SQL_Remaining_Delay: NULL | |
| Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates | |
| Master_Retry_Count: 86400 | |
| Master_Bind: | |
| Last_IO_Error_Timestamp: | |
| Last_SQL_Error_Timestamp: | |
| Master_SSL_Crl: | |
| Master_SSL_Crlpath: | |
| Retrieved_Gtid_Set: | |
| Executed_Gtid_Set: | |
| Auto_Position: 0 | |
| Replicate_Rewrite_DB: | |
| Channel_Name: | |
| Master_TLS_Version: | |
| 1 row in set (0.00 sec) |
1.6 测试主主、主从同步情况
Master1服务器（192.168.122.10）
| mysql> create database test; | |
| Query OK, 1 row affected (0.00 sec) | |
| mysql> show databases; | |
| +--------------------+ | |
| | Database | | |
| +--------------------+ | |
| | information_schema | | |
| | mysql | | |
| | performance_schema | | |
| | sys | | |
| | test | | |
| +--------------------+ | |
| 5 rows in set (0.00 sec) |
Master2服务器（192.168.122.11）
| mysql> show databases; | |
| +--------------------+ | |
| | Database | | |
| +--------------------+ | |
| | information_schema | | |
| | mysql | | |
| | performance_schema | | |
| | sys | | |
| | test | | |
| +--------------------+ | |
| 5 rows in set (0.00 sec) |
Slave1服务器（192.168.122.100）
| mysql> show databases; | |
| +--------------------+ | |
| | Database | | |
| +--------------------+ | |
| | information_schema | | |
| | mysql | | |
| | performance_schema | | |
| | sys | | |
| | test | | |
| +--------------------+ | |
| 5 rows in set (0.00 sec) |
Slave2服务器（192.168.122.101）
| mysql> show databases; | |
| +--------------------+ | |
| | Database | | |
| +--------------------+ | |
| | information_schema | | |
| | mysql | | |
| | performance_schema | | |
| | sys | | |
| | test | | |
| +--------------------+ | |
| 5 rows in set (0.00 sec) |
2. 安装配置MySQL-MMM
2.1 在所有服务器上安装MySQL-MMM
Master1服务器（192.168.122.10）
| [root@master1 ~]# yum -y install epel-release | |
| [root@master1 ~]# yum -y install mysql-mmm* |
Master2服务器（192.168.122.11）
| [root@master2 ~]# yum -y install epel-release | |
| [root@master2 ~]# yum -y install mysql-mmm* |
Slave1服务器（192.168.122.100）
| [root@slave1 ~]# yum -y install epel-release | |
| [root@slave1 ~]# yum -y install mysql-mmm* |
Slave2服务器（192.168.122.101）
| [root@slave2 ~]# yum -y install epel-release | |
| [root@slave2 ~]# yum -y install mysql-mmm* |
Monitor服务器（192.168.122.12）
| [root@monitor ~]# yum -y install epel-release | |
| [root@monitor ~]# yum -y install mysql-mmm* |
注：若本地仓库中无以上软件，需先为各服务器配置在线源仓库。
2.2 阿里云源仓库配置方法
| wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo | |
| #将阿里云镜像下载到/etc/yum.repos.d/目录下 | |
| yum clean all && yum makecache | |
| #清理缓存并且生成新的缓存 |
2.3 在master1上对MySQL-MMM进行配置
Master1服务器（192.168.122.10）
| [root@master1 ~]# cd /etc/mysql-mmm/ | |
| [root@master1 mysql-mmm]# cp mmm_common.conf mmm_common.conf.bak | |
| #修改配置文件前，先备份 | |
| [root@master1 mysql-mmm]# vim 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 123456 | |
| agent_user mmm_agent | |
| ##指定monitor代理进程的用户名 | |
| agent_password 123456 | |
| </host> | |
| <host db1> | |
| ip 192.168.122.10 | |
| mode master | |
| peer db2 | |
| ##peer设置同级数据库 | |
| </host> | |
| <host db2> | |
| ip 192.168.122.11 | |
| mode master | |
| peer db1 | |
| </host> | |
| <host db3> | |
| ip 192.168.122.100 | |
| mode slave | |
| </host> | |
| <host db4> | |
| ip 192.168.122.101 | |
| mode slave | |
| </host> | |
| <role writer> | |
| hosts db1, db2 | |
| ips 192.168.122.200 | |
| ##设定写VIP | |
| mode exclusive | |
| #只有一个 host 可以进行写操作模式 | |
| </role> | |
| <role reader> | |
| hosts db3, db4 | |
| ips 192.168.122.201, 192.168.122.202 | |
| ##设定读VIP | |
| mode balanced | |
| ##多个 slave 主机可以进行读操作模式 | |
| </role> |
2.4 把配置文件复制到其他4台主机
所有主机该配置文件内容都是相同的
Master1服务器（192.168.122.10）
| [root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.122.11:/etc/mysql-mmm/ | |
| [root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.122.100:/etc/mysql-mmm/ | |
| [root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.122.101:/etc/mysql-mmm/ | |
| [root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.122.12:/etc/mysql-mmm/ |
注：因为scp将直接覆盖原配置文件，所以建议先将配置文件做好备份
2.5 修改所有数据库服务器的代理配置文件mmm_agent.conf
Master1服务器（192.168.122.10）
| [root@master1 ~]# vim /etc/mysql-mmm/mmm_agent.conf | |
| include mmm_common.conf | |
| this db1 | |
| ##根据不同的主机分别修改为db1/db2/db3/db4，默认配置为db1，因此master1无需修改 |
Master2服务器（192.168.122.11）
| [root@master2 ~]# vim /etc/mysql-mmm/mmm_agent.conf | |
| include mmm_common.conf | |
| this db2 |
Slave1服务器（192.168.122.100）
| [root@slave ~]# vim /etc/mysql-mmm/mmm_agent.conf | |
| include mmm_common.conf | |
| this db3 |
Slave2服务器（192.168.122.101）
| [root@slave2 ~]# vim /etc/mysql-mmm/mmm_agent.conf | |
| include mmm_common.conf | |
| this db4 |
2.6 在monitor监控服务器上修改监控配置文件mmm_mon.conf
Monitor服务器（192.168.122.12）
| include mmm_common.conf | |
| <monitor> | |
| 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.122.10,192.168.122.11,192.168.122.100,192.168.122.101 | |
| ##指定所有数据库服务器的IP | |
| 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> | |
| monitor_user mmm_monitor | |
| ##指定mmm_monitor的用户名 | |
| monitor_password 123456 | |
| ##指定mmm_monitor的密码 | |
| </host> | |
| debug 0 |
2.7 在所有数据库上为 mmm_agent（代理进程）授权
Master1服务器（192.168.122.10）
| [root@master1 ~]# mysql -u root -p | |
| Enter password: | |
| mysql> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.80.%' identified by '123456'; | |
| Query OK, 0 rows affected, 1 warning (0.01 sec) |
Master2服务器（192.168.122.11）
| [root@master2 ~]# mysql -u root -p | |
| Enter password: | |
| mysql> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.80.%' identified by '123456'; | |
| Query OK, 0 rows affected, 1 warning (0.00 sec) | |
Slave1服务器（192.168.122.100）
| [root@slave1 ~]# mysql -u root -p | |
| Enter password: | |
| mysql> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.80.%' identified by '123456'; | |
| Query OK, 0 rows affected, 1 warning (0.00 sec) |
Slave2服务器（192.168.122.101）
| [root@slave2 ~]# mysql -u root -p | |
| Enter password: | |
| mysql> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.80.%' identified by '123456'; | |
| Query OK, 0 rows affected, 1 warning (0.00 sec) |
注：
| 权限 | 功能 |
|---|---|
| process | 显示或杀死属于其他用户的服务线程 |
| super | 允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGS |
| replication client | 查询主服务器、从服务器状态 |
2.8 在所有数据库上为mmm_monitor（监控进程）授权
Master1服务器（192.168.122.10）
| mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.80.%' identified by '123456'; | |
| Query OK, 0 rows affected, 1 warning (0.01 sec) | |
| mysql> flush privileges; | |
| Query OK, 0 rows affected (0.01 sec) |
Master2服务器（192.168.122.11）
| mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.80.%' identified by '123456'; | |
| Query OK, 0 rows affected, 1 warning (0.01 sec) | |
| mysql> flush privileges; | |
| Query OK, 0 rows affected (0.01 sec) |
Slave1服务器（192.168.122.100）
| mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.80.%' identified by '123456'; | |
| Query OK, 0 rows affected, 1 warning (0.01 sec) | |
| mysql> flush privileges; | |
| Query OK, 0 rows affected (0.01 sec) |
Slave2服务器（192.168.122.101）
复制
| mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.80.%' identified by '123456'; | |
| Query OK, 0 rows affected, 1 warning (0.01 sec) | |
| mysql> flush privileges; | |
| Query OK, 0 rows affected (0.01 sec) |
2.8 在所有数据库服务器上启动mysql-mmm-agent
Master1服务器（192.168.122.10）
| [root@master1 ~]# systemctl start mysql-mmm-agent.service | |
| [root@master1 ~]# systemctl enable mysql-mmm-agent.service | |
| Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service. |
Master2服务器（192.168.122.11）
| [root@master2 ~]# systemctl start mysql-mmm-agent.service | |
| [root@master2 ~]# systemctl enable mysql-mmm-agent.service | |
| Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service. |
Slave1服务器（192.168.122.100）
| [root@slave1 ~]# systemctl start mysql-mmm-agent.service | |
| [root@slave1 ~]# systemctl enable mysql-mmm-agent.service | |
| Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service. |
Slave2服务器（192.168.122.101）
| [root@slave2 ~]# systemctl start mysql-mmm-agent.service | |
| [root@slave2 ~]# systemctl enable mysql-mmm-agent.service | |
| Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service. | |
2.9 在monitor服务器上启动mysql-mmm-monitor
Monitor服务器（192.168.122.12）
复制[root@monitor ~]# systemctl start mysql-mmm-monitor.service
2.10 在monitor服务器上测试群集
2.10.1 查看各节点的情况
Monitor服务器（192.168.122.12）
| [root@monitor ~]# mmm_control show | |
| db1(192.168.122.10) master/ONLINE. Roles: writer(192.168.122.200) | |
| db2(192.168.122.11) master/ONLINE. Roles: | |
| db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202) | |
| db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201) |
2.10.2 检测监控功能是否完善
Monitor服务器（192.168.122.12）
| [root@monitor ~]# mmm_control checks all | |
| db4 ping [last change: 2021/09/08 02:05:52] OK | |
| db4 mysql [last change: 2021/09/08 02:10:38] OK | |
| db4 rep_threads [last change: 2021/09/08 02:25:20] OK | |
| db4 rep_backlog [last change: 2021/09/08 02:10:38] OK: Backlog is null | |
| db2 ping [last change: 2021/09/08 02:05:52] OK | |
| db2 mysql [last change: 2021/09/08 02:10:38] OK | |
| db2 rep_threads [last change: 2021/09/08 02:25:20] OK | |
| db2 rep_backlog [last change: 2021/09/08 02:10:38] OK: Backlog is null | |
| db3 ping [last change: 2021/09/08 02:05:52] OK | |
| db3 mysql [last change: 2021/09/08 02:10:38] OK | |
| db3 rep_threads [last change: 2021/09/08 02:25:20] OK | |
| db3 rep_backlog [last change: 2021/09/08 02:10:38] OK: Backlog is null | |
| db1 ping [last change: 2021/09/08 02:05:52] OK | |
| db1 mysql [last change: 2021/09/08 02:10:38] OK | |
| db1 rep_threads [last change: 2021/09/08 02:25:20] OK | |
| db1 rep_backlog [last change: 2021/09/08 02:10:38] OK: Backlog is null |
2.10.3 指定绑定VIP的主机
Monitor服务器（192.168.122.12）
| [root@monitor ~]# mmm_control move_role writer db2 | |
| OK: Role 'writer' has been moved from 'db1' to 'db2'. Now you can wait some time and check new roles info! | |
| [root@monitor ~]# mmm_control show | |
| db1(192.168.122.10) master/ONLINE. Roles: | |
| db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200) | |
| db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202) | |
| db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201) | |
| [root@monitor ~]# mmm_control move_role writer db1 | |
| OK: Role 'writer' has been moved from 'db2' to 'db1'. Now you can wait some time and check new roles info! | |
| [root@monitor ~]# mmm_control show | |
| db1(192.168.122.10) master/ONLINE. Roles: writer(192.168.122.200) | |
| db2(192.168.122.11) master/ONLINE. Roles: | |
| db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202) | |
| db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201) |
3. 故障测试
3.1 模拟master宕机以及恢复
3.1.1 停止master1的mysql服务
Master1服务器（192.168.122.10）
复制[root@master1 ~]# systemctl stop mysqld
3.1.2 查看VIP漂移情况
Monitor服务器（192.168.122.12）
| [root@monitor ~]# mmm_control show | |
| db1(192.168.122.10) master/HARD_OFFLINE. Roles: | |
| db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200) | |
| db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202) | |
| db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201) |
VIP成功漂移至master2，且master1显示HARD_OFFLINE
3.1.3 重启master1的mysql服务
Master1服务器（192.168.122.10）
复制[root@master1 ~]# systemctl start mysqld
3.1.4 查看master1是否恢复
Monitor服务器（192.168.122.12）
| [root@monitor ~]# mmm_control show | |
| db1(192.168.122.10) master/AWAITING_RECOVERY. Roles: | |
| db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200) | |
| db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202) | |
| db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201) |
显示等待恢复，等待数秒后重新查看，发现Master1在线，但未能获得VIP
| [root@monitor ~]# mmm_control show | |
| db1(192.168.122.10) master/ONLINE. Roles: | |
| db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200) | |
| db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202) | |
| db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201) |
3.2 模拟从服务器宕机以及恢复
3.2.1 停止slave1的mysql服务
Slave1服务器（192.168.122.100）
复制[root@slave1 ~]# systemctl stop mysqld
3.2.2 查看VIP漂移情况
Monitor服务器（192.168.122.12）
| [root@monitor ~]# mmm_control show | |
| db1(192.168.122.10) master/ONLINE. Roles: | |
| db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200) | |
| db3(192.168.122.100) slave/HARD_OFFLINE. Roles: | |
| db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201), reader(192.168.122.202) |
slave1所对应的的VIP对slave2成功接管
3.2.3 重启slave1的mysql服务
Slave1服务器（192.168.122.100）
复制[root@slave1 ~]# systemctl start mysqld
3.2.4 查看slave1是否恢复
| [root@monitor ~]# mmm_control show | |
| db1(192.168.122.10) master/ONLINE. Roles: | |
| db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200) | |
| db3(192.168.122.100) slave/AWAITING_RECOVERY. Roles: | |
| db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201), reader(192.168.122.202) | |
| [root@monitor ~]# mmm_control show | |
| db1(192.168.122.10) master/ONLINE. Roles: | |
| db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200) | |
| db3(192.168.122.100) slave/ONLINE. Roles: | |
| db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201), reader(192.168.122.202) | |
| [root@monitor ~]# mmm_control show | |
| db1(192.168.122.10) master/ONLINE. Roles: | |
| db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200) | |
| db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202) | |
| db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201) |
在短暂的交接后，slave1重新获取到VIP，继续工作
3.3 客户端测试
3.3.1 在master1服务器上为monitor服务器地址授权登录
Master1服务器（192.168.122.10）
| [root@master1 ~]# mysql -u root -p | |
| Enter password: | |
| mysql> grant all on *.* to 'test'@'192.168.122.12' identified by '123456'; | |
| Query OK, 0 rows affected, 1 warning (0.00 sec) | |
| mysql> flush privileges; | |
| Query OK, 0 rows affected (0.00 sec) |
3.3.2 在monitor服务器上使用VIP登录
Monitor服务器（192.168.122.12）
| [root@monitor ~]# yum install -y mariadb-server mariadb | |
| [root@monitor ~]# systemctl start mariadb.service | |
| [root@monitor ~]# mysql -u test -p -h 192.168.122.200 | |
| Enter password: | |
| ##登录成功 | |
| MySQL [(none)]> |
3.3.3 客户端创建数据，测试同步情况
Monitor服务器（192.168.122.12）
| MySQL [(none)]> create database client_test; | |
| Query OK, 1 row affected (0.00 sec) | |
| MySQL [(none)]> show databases; | |
| +--------------------+ | |
| | Database | | |
| +--------------------+ | |
| | information_schema | | |
| | client_test | | |
| | mysql | | |
| | performance_schema | | |
| | sys | | |
| | test | | |
| +--------------------+ | |
| 6 rows in set (0.00 sec) |
Master1服务器（192.168.122.10）
| mysql> show databases; | |
| +--------------------+ | |
| | Database | | |
| +--------------------+ | |
| | information_schema | | |
| | client_test | | |
| | mysql | | |
| | performance_schema | | |
| | sys | | |
| | test | | |
| +--------------------+ | |
| 6 rows in set (0.00 sec) |
Master2服务器（192.168.122.11）
| mysql> show databases; | |
| +--------------------+ | |
| | Database | | |
| +--------------------+ | |
| | information_schema | | |
| | client_test | | |
| | mysql | | |
| | performance_schema | | |
| | sys | | |
| | test | | |
| +--------------------+ | |
| 6 rows in set (0.00 sec) |
Slave1服务器（192.168.122.100）
| mysql> show databases; | |
| +--------------------+ | |
| | Database | | |
| +--------------------+ | |
| | information_schema | | |
| | client_test | | |
| | mysql | | |
| | performance_schema | | |
| | sys | | |
| | test | | |
| +--------------------+ | |
| 6 rows in set (0.00 sec) |
Slave2服务器（192.168.122.101）
| mysql> show databases; | |
| +--------------------+ | |
| | Database | | |
| +--------------------+ | |
| | information_schema | | |
| | client_test | | |
| | mysql | | |
| | performance_schema | | |
| | sys | | |
| | test | | |
| +--------------------+ | |
| 6 rows in set (0.00 sec) |
自古英雄多磨难
