准备:
已有环境mysql主主从从:master1、master2、slave1、slave2和monitor主机
192.168.4.10 master1.tedu.cn master1
192.168.4.11 master2.tedu.cn master2
192.168.4.12 slave1.tedu.cn slave1
192.168.4.13 slave2.tedu.cn slave2
192.168.4.14 monitor.tedu.cn monitor
安装包:mysql-mmm.zip
下载地址:
https://download.csdn.net/download/platowg/10297539
**************MySQL-MMM高可用集群配置*************************
1、5台主机均需安装编译工具perl、perl-*、gcc、gcc-c++
[root@monitor mysql-mmm]# yum -y install perl perl-* gcc gcc-c++
2、拷贝mysql-mmm.zip到5台主机,并解压
[root@room1pc01 ~]# for i in `seq 10 14`;do scp mysql-mmm.zip 192.168.4.$i:/opt/; done
mysql-mmm.zip 100% 673KB 673.1KB/s 00:00
mysql-mmm.zip 100% 673KB 673.1KB/s 00:00
mysql-mmm.zip 100% 673KB 673.1KB/s 00:00
mysql-mmm.zip 100% 673KB 673.1KB/s 00:00
mysql-mmm.zip
[root@monitor opt]# ls mysql-mmm.zip
mysql-mmm.zip
[root@slave2 opt]# unzip mysql-mmm.zip
Archive: mysql-mmm.zip
creating: mysql-mmm/
extracting: mysql-mmm/Algorithm-Diff-1.1902.tar.gz
extracting: mysql-mmm/Net-ARP-1.0.8.tgz
extracting: mysql-mmm/Proc-Daemon-0.03.tar.gz
inflating: mysql-mmm/install.sh
extracting: mysql-mmm/mysql-mmm-2.2.1.tar.gz
inflating: mysql-mmm/mysql-mmm-L.docx
inflating: mysql-mmm/mysql-mmm-P.txt
inflating: mysql-mmm/perl-Log-Log4perl-1.26-1.el6.rf.noarch.rpm
3、安装mysql-mmm
通过脚本自动安装
[root@slave2 mysql-mmm]# sh install.sh
warning: perl-Log-Log4perl-1.26-1.el6.rf.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
Preparing... ########################################### [100%]
1:perl-Log-Log4perl ########################################### [100%]
Algorithm-Diff-1.1902/
Algorithm-Diff-1.1902/diffnew.pl
Algorithm-Diff-1.1902/t/
Algorithm-Diff-1.1902/t/oo.t
Algorithm-Diff-1.1902/t/base.t
Algorithm-Diff-1.1902/htmldiff.pl
Algorithm-Diff-1.1902/lib/
Algorithm-Diff-1.1902/lib/Algorithm/
Algorithm-Diff-1.1902/lib/Algorithm/Diff.pm
Algorithm-Diff-1.1902/lib/Algorithm/DiffOld.pm
Algorithm-Diff-1.1902/META.yml
Algorithm-Diff-1.1902/Changes
Algorithm-Diff-1.1902/cdiff.pl
Algorithm-Diff-1.1902/MANIFEST
...
mysql-mmm-2.2.1/sbin/mmm_clone
mkdir -p /usr/share/perl5/vendor_perl/MMM /usr/lib/mysql-mmm /usr/sbin /var/log/mysql-mmm /etc /etc/mysql-mmm /etc/init.d/
cp -r lib/Common/ /usr/share/perl5/vendor_perl/MMM
[ -f /etc/mysql-mmm/mmm_common.conf ] || cp etc/mysql-mmm/mmm_common.conf /etc/mysql-mmm/
mkdir -p /usr/lib/mysql-mmm/agent/
cp -r lib/Agent/ /usr/share/perl5/vendor_perl/MMM
cp -r bin/agent/* /usr/lib/mysql-mmm/agent/
cp -r etc/init.d/mysql-mmm-agent /etc/init.d/
cp sbin/mmm_agentd /usr/sbin
[ -f /etc/mysql-mmm/mmm_agent.conf ] || cp etc/mysql-mmm/mmm_agent.conf /etc/mysql-mmm/
mkdir -p /usr/lib/mysql-mmm/monitor/
cp -r lib/Monitor/ /usr/share/perl5/vendor_perl/MMM
cp -r bin/monitor/* /usr/lib/mysql-mmm/monitor/
cp -r etc/init.d/mysql-mmm-monitor /etc/init.d/
cp sbin/mmm_control sbin/mmm_mond /usr/sbin
[ -f /etc/mysql-mmm/mmm_mon.conf ] || cp etc/mysql-mmm/mmm_mon.conf /etc/mysql-mmm/
mkdir -p /usr/lib/mysql-mmm/tools/
cp -r lib/Tools/ /usr/share/perl5/vendor_perl/MMM
cp -r bin/tools/* /usr/lib/mysql-mmm/tools/
cp sbin/mmm_backup sbin/mmm_clone sbin/mmm_restore /usr/sbin
[ -f /etc/mysql-mmm/mmm_tools.conf ] || cp etc/mysql-mmm/mmm_tools.conf /etc/mysql-mmm/
4、检查安装目录确认配置文件、启动脚本已成功安装
[root@monitor mysql-mmm]# ls /etc/mysql-mmm/
mmm_agent.conf mmm_common.conf mmm_mon.conf mmm_tools.conf
[root@monitor mysql-mmm]# ls /etc/init.d/mysql*
/etc/init.d/mysql /etc/init.d/mysql-mmm-agent /etc/init.d/mysql-mmm-monitor
5、修改mmm_common.conf,并拷贝到其他四台主机
[root@monitor mysql-mmm]# vim /etc/mysql-mmm/mmm_common.conf
<host default>
cluster_interface eth0
pid_path /var/run/mmm_agentd.pid
bin_path /usr/lib/mysql-mmm/
replication_user slaveuser
replication_password abc123
agent_user agent
agent_password agent123
</host>
<host master1>
ip 192.168.4.10
mode master
peer master2
</host>
<host master2>
ip 192.168.4.11
mode master
peer master1
</host>
<host slave1>
ip 192.168.4.12
mode slave
</host>
<host slave2>
ip 192.168.4.13
mode slave
</host>
<role writer>
hosts master1, master2
ips 192.168.4.50
mode exclusive
</role>
<role reader>
hosts slave1, slave2
ips 192.168.4.12, 192.168.4.13
mode balanced
</role>
[root@monitor ~]# for i in `seq 10 13`;do scp mmm_common.conf 192.168.4.$i:/etc/mysql-mmm/mmm_common.conf;done
mmm_common.conf 100% 754 0.7KB/s 00:00
mmm_common.conf 100% 754 0.7KB/s 00:00
mmm_common.conf 100% 754 0.7KB/s 00:00
mmm_common.conf 100% 754 0.7KB/s 00:00
6、5台主机修mmm_agent.conf
[root@master1 mysql-mmm]# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this master1
[root@master2 mysql-mmm]# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this master2
[root@salve1 mysql-mmm]# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this salve1
[root@salve2 mysql-mmm]# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this salve2
[root@monitor mysql-mmm]# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this monitor
7、monitor主机修改mmm_mon.conf
[root@monitor ~]# vim /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
<monitor>
ip 192.168.4.14 //设置管理主机IP地址
pid_path /var/run/mmm_mond.pid
bin_path /usr/lib/mysql-mmm/
status_path /var/lib/misc/mmm_mond.status
ping_ips 192.168.4.10,192.168.4.11,192.168.4.12,192.168.4.13
//设置被监控数据库
</monitor>
<host default>
monitor_user monitor //监控数据库MySQL用户
monitor_password non123 //监控数据库MySQL用户密码
</host>
debug 0
8、在master1上对agent、monitor用户进行授权(slaveuser已授权)
mysql> grant replication client on *.* to monitor@'192.168.4.14' identified by "mon123";
Query OK, 0 rows affected (0.91 sec)
mysql> grant replication client,super,process on *.* to agent@"192.168.4.%" identified by "agent123";
Query OK, 0 rows affected (0.03 sec)
9、在master1、master2、slave1、slave2主机上开启agent
[root@master1 mysql-mmm]# /etc/init.d/mysql-mmm-agent start
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Starting MMM Agent daemon... already running.
10、在monitor主机上开启monitor,并查看集群中各个主机的状态
[root@monitor mysql-mmm]# /etc/init.d/mysql-mmm-monitor start
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Starting MMM Monitor daemon: already running.
[plain] view plain copy
[root@monitor mysql-mmm]# mmm_control show
master1(192.168.4.10) master/AWAITING_RECOVERY. Roles:
master2(192.168.4.11) master/AWAITING_RECOVERY. Roles:
slave1(192.168.4.12) slave/AWAITING_RECOVERY. Roles:
slave2(192.168.4.13) slave/AWAITING_RECOVERY. Roles:
11、在monitor主机上设置集群中的主机为online,再次查看集群中各个主机的状态
[root@monitor mysql-mmm]# mmm_control set_online master1
OK: State of 'master1' changed to ONLINE. Now you can wait some time and check its new roles!
[root@monitor mysql-mmm]# mmm_control set_online master2
OK: State of 'master2' changed to ONLINE. Now you can wait some time and check its new roles!
[root@monitor mysql-mmm]# mmm_control set_online slave1
OK: State of 'slave1' changed to ONLINE. Now you can wait some time and check its new roles!
[root@monitor mysql-mmm]# mmm_control set_online slave2
OK: State of 'slave2' changed to ONLINE. Now you can wait some time and check its new roles!
[root@monitor mysql-mmm]# mmm_control show
master1(192.168.4.10) master/ONLINE. Roles: writer(192.168.4.50)
master2(192.168.4.11) master/ONLINE. Roles:
slave1(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.13)
slave2(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.12)
**************MySQL-MMM高可用集群环境测试*************************
测试MySQL-MMM集群高可用效果
1)在master1数据库服务器上创建stu用户,并授权
mysql> grant all on *.* to stu@"%" identified by "stu123";
Query OK, 0 rows affected (0.22 sec)
2)通过客户机访问虚IPwriter(192.168.4.50)reader(192.168.4.13)reader(192.168.4.12),测试访问效果
[root@stu ~]# mysql -h192.168.4.50 -ustu -p -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbsdb |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@stu ~]# mysql -h192.168.4.13 -ustu -p -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbsdb |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@stu ~]# mysql -h192.168.4.12 -ustu -p -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbsdb |
| mysql |
| performance_schema |
| test |
+--------------------+
3)master1主机关闭数据库服务
[root@master1 ~]# /etc/init.d/mysql status
SUCCESS! MySQL running (17615)
[root@master1 ~]# /etc/init.d/mysql stop
Shutting down MySQL..... SUCCESS!
4)在monitor主机上查看当前MMM集群状态,发现“写虚IP”已自动转移到master2主机上
[root@monitor ~]# mmm_control show #master1主机关闭数据库服务前,MMM集群各个主机状态
master1(192.168.4.10) master/ONLINE. Roles: writer(192.168.4.50)
master2(192.168.4.11) master/ONLINE. Roles:
slave1(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.13)
slave2(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.12)
[root@monitor ~]# mmm_control show #master1主机关闭数据库服务后,MMM集群各个主机状态
master1(192.168.4.10) master/HARD_OFFLINE. Roles:
master2(192.168.4.11) master/ONLINE. Roles: writer(192.168.4.50)
slave1(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.13)
slave2(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.12)
5)通过stu客户机连接“写虚IP”,创建新数据库studb,测试同步效果
[root@stu ~]# mysql -h192.168.4.50 -ustu -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11046
Server version: 5.6.15-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbsdb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database studb;
Query OK, 1 row affected (0.13 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbsdb |
| mysql |
| performance_schema |
| studb |
| test |
+--------------------+
6 rows in set (0.01 sec)
6)在slave1、slave2上查看同步效果
[root@salve1 ~]# mysql -uroot -p -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbsdb |
| mysql |
| performance_schema |
| studb |
| test |
+--------------------+
[root@salve2 ~]# mysql -uroot -p -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbsdb |
| mysql |
| performance_schema |
| studb |
| test |
+--------------------+
**************MySQL-MMM高可用集群异常处理*************************
1)问题:slave1、slave2误删同步master主机的数据库导致Slave_SQL_Running: No
解决办法:stop slave;reset slave;startslave
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbsdb |
| mysql |
| performance_schema |
| studb |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database studb;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbsdb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
*******此时在往master主机中写入或修改数据*******
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.11
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master2.000003
Read_Master_Log_Pos: 537
Relay_Log_File: salve1-relay-bin.000002
Relay_Log_Pos: 562
Relay_Master_Log_File: master2.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1049
Last_Error: Error 'Unknown database 'studb'' on query. Default database: ''. Query: 'create table studb.stu_id(
id int(3) zerofill,
name varchar(10)
)'
Skip_Counter: 0
Exec_Master_Log_Pos: 401
Relay_Log_Space: 872
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1049
Last_SQL_Error: Error 'Unknown database 'studb'' on query. Default database: ''. Query: 'create table studb.stu_id(
id int(3) zerofill,
name varchar(10)
)'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: 1fcc51f4-2b04-11e8-b606-545201011101
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180320 09:55:43
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.67 sec)
mysql> start slave;
Query OK, 0 rows affected (0.39 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.11
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master2.000003
Read_Master_Log_Pos: 537
Relay_Log_File: salve1-relay-bin.000005
Relay_Log_Pos: 698
Relay_Master_Log_File: master2.000003
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: 537
Relay_Log_Space: 1033
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: 11
Master_UUID: 1fcc51f4-2b04-11e8-b606-545201011101
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbsdb |
| mysql |
| performance_schema |
| studb |
| test |
+--------------------+
6 rows in set (0.01 sec)
mysql> show tables from studb;
+-----------------+
| Tables_in_studb |
+-----------------+
| stu_id |
+-----------------+
1 row in set (0.03 sec)
2)两台master主机中一台master主机宕掉,宕掉的主机修复后数据同步
[root@monitor ~]# mmm_control show #master1在线
master1(192.168.4.10) master/ONLINE. Roles: writer(192.168.4.50)
master2(192.168.4.11) master/ONLINE. Roles:
slave1(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.13)
slave2(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.12)
[root@monitor ~]# mmm_control show #master1宕掉
master1(192.168.4.10) master/HARD_OFFLINE. Roles:
master2(192.168.4.11) master/ONLINE. Roles: writer(192.168.4.50)
slave1(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.13)
slave2(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.12)
[root@monitor ~]# mmm_control show #master修复,并开启mysql服务
master1(192.168.4.10) master/AWAITING_RECOVERY. Roles:
master2(192.168.4.11) master/ONLINE. Roles: writer(192.168.4.50)
slave1(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.12)
slave2(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.13)
[root@monitor ~]# mmm_control set_online master1 #monitor主机设置master1上线
OK: State of 'master1' changed to ONLINE. Now you can wait some time and check its new roles!
[root@monitor ~]# mmm_control show #master1主机恢复为online状态,查看master1数据已同步为最新数据
master1(192.168.4.10) master/ONLINE. Roles:
master2(192.168.4.11) master/ONLINE. Roles: writer(192.168.4.50)
slave1(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.12)
slave2(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.13)