MySQL MMM installation management detailed

Machine Introduction:

Monitor192.168.146.131 Monitoring Server

master-001192.168.146.129 Read-write host 001

master-002192.168.146.130 Read-write host 002

slave-001192.168.146.131 read-only host 001

Virtual IP Assignment

master-001 192.168.146.135writer

master-002 192.168.146.136Reader

slave-001 192.168.146.137Reader

Three identical operations:

Shutting down the firewall

One, install MySQL

Two, MySQL permissions assignment

GRANT REPLICATION SLAVE on * * to ' myrepl ' @ ' 192.168.146.% ' identified by ' 123456 ';

GRANT REPLICATION CLIENT on * * to ' mmm_monitor ' @ ' 192.168.146.% ' identified by ' monitor ';

GRANT SUPER, REPLICATION CLIENT, PROCESS on * * to ' mmm_agent ' @ ' 192.168.146.% ' identified by ' agent ';

Second, install MMM

Installing Mysql-mmm

To install a dependency package:

Yum Install Epel-release

Yum Install Perl-log-log4perl-appender-socket-unix

RPM Installation

RPM-IVH mysql-mmm-2.2.1-2.el6.noarch.rpm

Installing Mysql-agent

To install a dependency package:

Yum install-y perl-algorithm-diff Perl-class-singleton perl-proc-daemon perl-net-arp perl-DBD-mysql

RPM Installation

RPM-IVH mysql-mmm-agent-2.2.1-2.el6.noarch.rpm

Chkconfig mysql-mmm-agent on

(131) Monitor package installed on monitor machine

RPM-IVH mysql-mmm-monitor-2.2.1-2.el6.noarch.rpm

Three. Configure MMM

Modify MY.CNF

Db1

Server-id= 1

Gtid_mode= on

Log_slave_updates=1 #当一个主故障, another immediate takeover

Enforce_gtid_consistency= 1

Auto-increment-increment = 2

Auto-increment-offset = 1

Replicate-ignore-db=mysql,information_schema #不同步的数据库, more than one write multiple lines

Db2

Server-id= 2

Gtid_mode= on

Log_slave_updates=1 #当一个主故障, another immediate takeover

Enforce_gtid_consistency= 1

Auto-increment-increment = 2

Auto-increment-offset = 2

Replicate-ignore-db=mysql,information_schema #不同步的数据库, more than one write multiple lines

Db3

Server-id= 3

Gtid_mode= on

Log_slave_updates=1 #当一个主故障, another immediate takeover

Enforce_gtid_consistency= 1

Replicate-ignore-db=mysql,information_schema #不同步的数据库, more than one write multiple lines

Read_only=1

Configure Master-Slave

Operation on 130 and 131

Change MASTER to master_host= ' 192.168.146.129 ', master_user= ' Myrepl ', master_password= ' 123456 ', master_port=3306, Master_log_file= ' mysql-bin.000022 ', master_connect_retry=311;

Start slave;

129 on Operation

Operation on 130 and 131

Change MASTER to master_host= ' 192.168.146.130 ', master_user= ' Myrepl ', master_password= ' 123456 ', master_port=3306, Master_log_file= ' mysql-bin.000032 ', master_connect_retry=234;

Start slave;

Configuring/etc/mysql-mmm/mmm_common.conf #三机器统一

Active_master_role writer

Cluster_interface eth0

Pid_path/var/run/mysql-mmm/mmm_agentd.pid

bin_path/usr/libexec/mysql-mmm/

Replication_user MYREPL

Replication_password 123456

Agent_user mmm_agent

Agent_password Agent

IP 192.168.146.129

Mode Master

Peer DB2

IP 192.168.146.130

Mode Master

Peer DB1

IP 192.168.146.131

mode slave

<role writer>

Hosts DB1, DB2

IPs 192.168.146.135

Mode Exclusive

</role>

<role reader>

Hosts DB1, DB2, DB3

IPs 192.168.146.136, 192.168.146.137, 192.168.146.138

Mode Balanced

</role>

Configure/etc/mysql-mmm/mmm_agent.conf

This db1 #是谁就写谁

Manage on-machine configuration mmm_mon.conf

Include mmm_common.conf

<monitor>

IP 127.0.0.1

Pid_path/var/run/mysql-mmm/mmm_mond.pid

Bin_path/usr/libexec/mysql-mmm

Status_path/var/lib/mysql-mmm/mmm_mond.status

Ping_ips 192.168.146.129,192.168.146.130 #其他机器IP

Auto_set_online 60

# The Kill_host_bin does not exist by default, though the monitor would

# 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

Monitor_password Monitor

Debug 0

Join self-booting:

Chkconfig Mysql-mmm-monitor on

Chkconfig mysql-mmm-agent on

Open service:

Start Mmm_agent

Service Mysql-mmm-agent Start

Start Mmm_monitor

Service Mysql-mmm-monitor Start

Manage commands

[Email protected] mysql-mmm]# Mmm_control--help

Invalid command '--help '

Valid Commands is:

Help-show this message

Ping-ping Monitor

Show-show status

Checks [

Set_online

Set_offline

Mode-print current mode.

Set_active-switch into active mode.

Set_manual-switch into manual mode.

Set_passive-switch into passive mode.

Move_role [--force] <role>

(Only use--force if you know, what is doing!)

SET_IP <ip>

[Email protected] mysql-mmm]# Mmm_control Show

DB1 (192.168.146.129) master/online. Roles:reader (192.168.146.138), writer (192.168.146.135)

DB2 (192.168.146.130) master/online. Roles:reader (192.168.146.137)

DB3 (192.168.146.131) slave/online. Roles:reader (192.168.146.136

[Email protected] mysql-mmm]# Mmm_control checks all

DB2 Ping [last CHANGE:2016/11/10 19:18:54] OK

DB2 MySQL [last CHANGE:2016/11/10 19:18:54] OK

DB2 rep_threads [last CHANGE:2016/11/10 19:18:54] OK

DB2 Rep_backlog [last CHANGE:2016/11/10 19:18:54] Ok:backlog is null

Db3 Ping [last CHANGE:2016/11/10 19:18:54] OK

DB3 MySQL [last CHANGE:2016/11/10 19:19:17] OK

DB3 rep_threads [last CHANGE:2016/11/10 19:19:17] OK

DB3 Rep_backlog [last CHANGE:2016/11/10 19:19:17] Ok:backlog is null

DB1 Ping [last CHANGE:2016/11/10 19:18:54] OK

DB1 MySQL [last CHANGE:2016/11/10 19:18:54] OK

DB1 rep_threads [last CHANGE:2016/11/10 19:18:54] OK

DB1 Rep_backlog [last CHANGE:2016/11/10 19:18:54] Ok:backlog is null

To test the transformation:

Operation on DB1:

[Email protected] mysql-mmm]# mysqladmin-uroot-p-s/tmp/mysql.sock shutdown

Enter Password:

View on the management machine:

Bye

[Email protected] mysql-mmm]# Mmm_control checks all

DB2 Ping [last CHANGE:2016/11/10 19:18:54] OK

DB2 MySQL [last CHANGE:2016/11/10 19:18:54] OK

DB2 rep_threads [last CHANGE:2016/11/10 19:18:54] OK

DB2 Rep_backlog [last CHANGE:2016/11/10 19:18:54] Ok:backlog is null

Db3 Ping [last CHANGE:2016/11/10 19:18:54] OK

DB3 MySQL [last CHANGE:2016/11/10 19:39:20] OK

DB3 rep_threads [last CHANGE:2016/11/10 19:39:20] OK

DB3 Rep_backlog [last CHANGE:2016/11/10 19:19:17] Ok:backlog is null

DB1 Ping [last CHANGE:2016/11/10 19:18:54] OK

DB1 MySQL [last change:2016/11/10 19:31:34] error:connect ERROR (host = 192.168.146.129:3306, user = Mmm_monito R)! Lost connection to MySQL server at ' reading initial communication packet ', System error:111

DB1 rep_threads [last CHANGE:2016/11/10 19:18:54] OK

DB1 Rep_backlog [last CHANGE:2016/11/10 19:18:54] Ok:backlog is null

[Email protected] mysql-mmm]# Mmm_control Show

DB1 (192.168.146.129) master/hard_offline. Roles:

DB2 (192.168.146.130) master/online. Roles:reader (192.168.146.137), writer (192.168.146.135)

DB3 (192.168.146.131) slave/online. Roles:reader (192.168.146.136), Reader (192.168.146.138)

Start DB1 wait 60s to see again:

[Email protected] mysql-mmm]# Mmm_control Show

DB1 (192.168.146.129) master/online. Roles:reader (192.168.146.136)

DB2 (192.168.146.130) master/online. Roles:reader (192.168.146.137), writer (192.168.146.135)

DB3 (192.168.146.131) slave/online. Roles:reader (192.168.146.138)

We want to cut the writer's character back to DB1:

[Email protected] mysql-mmm]# Mmm_control move_role writer DB1

Ok:role ' writer ' had been moved from ' DB2 ' to ' DB1 '. Now you can wait some time and check new roles info!

[Email protected] mysql-mmm]# Mmm_control Show

DB1 (192.168.146.129) master/online. Roles:reader (192.168.146.136), writer (192.168.146.135)

DB2 (192.168.146.130) master/online. Roles:reader (192.168.146.137)

DB3 (192.168.146.131) slave/online. Roles:reader (192.168.146.138)

Two questions encountered

1. The monitor server failed to start

On the management Server, when everything is done, view the status by Mmm_control and get the following error message:

Error:cant Connect to monitor daemon! Change Debug 0 To debug 1 by editing the/etc/mysql-mmm/mmm_mon.conf file to turn on the debug state of the monitor program. Restart the Monitoring Program (service Mysql-mmm-monitor restart) and you will see a detailed error message that the Perl time hires library could not be found. Execute yum-y install perl-time-hires* to resolve.

2. A firewall problem caused warning:agent on host db1 was not reachable.

After the console program starts correctly, execute Mmm_control show again, but see the following output:

# warning:agent on host db1 are not reachable

# Warning:agent on host DB2 are not reachable

DB1 (192.168.146.129) master/online. Roles:

DB2 (192.168.146.130) master/online. Roles:

DB3 (192.168.146.131) slave/online. Roles:reader (192.168.146.138)

Turn on debug again to see the error message

The error message for no route to host was obtained by checking the network connection under Telnet 192.168.146.130 9989. Log in to DB1, close iptables (this is not a good idea). Similarly, close the firewall on DB2, DB3, restart the monitoring program again, everything back to normal state!

MySQL MMM installation management detailed