Shulou(Shulou.com)06/03 Report--

I. MMM architecture

MMM (Master-Master replication manger for MySQL) is a script program that supports dual-master failover and dual-master daily management. MMM is developed in Perl language.

It is mainly used to monitor and manage MySQL Master-Master (dual master) replication. Although it is called dual master replication, only one master is allowed to write at the same time in the business, and the other is provided by the alternative master.

Read-by-read service to speed up the warm-up of the backup master at the time of master-master switchover. It can be said that the MMM script program realizes the failover function on the one hand, and the additional tool scripts can on the other hand.

Achieve multiple slaves load balancing.

MMM provides both automatic and manual ways to remove the virtual IP of server servers with high replication latency in a group of servers. At the same time, it can also back up data and implement data between two nodes.

Synchronization and so on.

Because MMM cannot fully guarantee data consistency, MMM is suitable for scenarios where the requirement for data consistency is not very high, but you want to maximize business availability.

Example: three hosts

Role IP address hostname server id

Monitor host 192.168.110.130 db3

Master 1 192.168.110.128 db1 1 writer (192.168.110.132)

Master 2 192.168.110.130 db2 2 reader (192.168.110.133)

Slave 1 192.168.110.131 db3 3 reader (192.168.110.134)

1. Host configuration

[root@www ~] # cat / etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

:: 1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.110.128 db1.pancou.com db1

192.168.110.130 db2.pancou.com db2

192.168.110.131 db3.pancou.com db3

2. Installation and configuration of mysql

Db1:

Server-id = 1

Log-slave-updates=true

# gtid-mode=on

# enforce-gtid-consistency=true

Master-info-repository=TABLE

Relay-log-info-repository=TABLE

Sync-master-info=1

Slave-parallel-threads=2

Binlog-checksum=CRC32

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

Report-port=3306

Report-host=www.pancou.com

Db2:

Server-id = 2

Log-slave-updates=true

# gtid-mode=on

# enforce-gtid-consistency=true

Master-info-repository=TABLE

Relay-log-info-repository=TABLE

Sync-master-info=1

Slave-parallel-threads=2

Binlog-checksum=CRC32

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

Report-port=3306

Report-host=www.pancou.com

Db3:

Server-id = 3

Log-slave-updates=true

# gtid-mode=on

# enforce-gtid-consistency=true

Master-info-repository=TABLE

Relay-log-info-repository=TABLE

Sync-master-info=1

Slave-parallel-threads=2

Binlog-checksum=CRC32

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

Report-port=3306

Report-host=www.pancou.com

3. Master-slave replication, and double master replication. Look at the previous replication chapter.

4. Install mysql-mmm

1. Install monitor program

Run the mysql-mmm monitor and agent programs on the management server and the database server, respectively. Install the following separately:

The prerequisite is to install

# rpm-ivh epel-release-6-8.noarch.rpm

On the management server (192.168.110.130), execute the following command:

# yum-y install mysql-mmm-monitor*

All files that depend on monitor are also installed, with one exception, perl-Time-HiRes, so you also need to execute the following command:

[plain] view plain copy print?

# yum-y install perl-Time-HiRes*

two。 Install the agent

# yum-y install mysql-mmm-agent*

Install on 192.168.110.128 and 192.168.110.131, respectively:

# yum-y install mysql-mmm-agent*

5. Configure MMM

1. Configure the agent file, which needs to be configured separately in db1,db2,db3

After the installation is complete, all configuration files are placed under / etc/mysql-mmm/. Both the management server and the database server should contain a common

File mmm_common.conf of

Configure on db1:

Active_master_role writer

Cluster_interface eth0

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

Bin_path / usr/libexec/mysql-mmm/

Replication_user repl_user

Replication_password pancou

Agent_user mmm-agent

Agent_password mmm-agent

Ip 192.168.110.128

Mode master

Peer db2

Ip 192.168.110.130

Mode master

Peer db1

Ip 192.168.110.131

Mode slave

Hosts db1, db2

Ips 192.168.110.132

Mode exclusive

Hosts db2, db3

Ips 192.168.110.133, 192.168.110.134

Mode balanced

You can edit the file on db1 and copy it to monitor, db2, db3, and db4, respectively, through the scp command.

Copy to db2:

Scp / etc/mysql-mmm/mmm_com.conf db2:/etc/mysql-mmm/

Copy to db3:

Scp / etc/mysql-mmm/mmm_com.conf db3:/etc/mysql-mmm/

two。 Edit the mmm_agent.conf. On the database server, there is another mmm_agent.conf that needs to be modified

Db1:

# vim / etc/mysql-mmm/mmm_agent.conf

Include mmm_common.conf

# The 'this' variable refers to this server. Proper operation requires

# that 'this' server (db1 by default), as well as all other servers, have the

# proper IP addresses set in mmm_common.conf.

This db1

Db2:

# vim / etc/mysql-mmm/mmm_agent.conf

Include mmm_common.conf

# The 'this' variable refers to this server. Proper operation requires

# that 'this' server (db1 by default), as well as all other servers, have the

# proper IP addresses set in mmm_common.conf.

This db2

Db3:

# vim / etc/mysql-mmm/mmm_agent.conf

Include mmm_common.conf

# The 'this' variable refers to this server. Proper operation requires

# that 'this' server (db1 by default), as well as all other servers, have the

# proper IP addresses set in mmm_common.conf.

This db3

3. Edit the mmm_mon.confg. On the management server, modify the mmm_mon.conf file

On db2

# vim / etc/mysql-mmm/mmm_mon.conf

Include mmm_common.conf

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.110.128192.168.110.130192.168.110.131

Auto_set_online 60

# 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_user mmm_monitor

Monitor_password mmm_monitor

6. Create monitoring

MariaDB [(none)] > grant replication client on *. * to 'mmm_monitor'@'192.168.110.%' identified by' mmm_monitor'

MariaDB [(none)] > grant super,replication client,process on *. * to 'mmm-agent'@'192.168.110.%' identified by' mmm-agent'

MariaDB [(none)] > grant replication slave on *. * to 'repl_user'@'192.168.110.%' identified by' pancou'

MariaDB [(none)] > flush priviliges

7. Start MMM

1. Start the agent on the database server

# service mysql-mmm-agent start

Starting MMM Agent Daemon: [OK]

two。 Start the monitor on the management server

# service mysql-mmm-monitor start

Starting MMM Monitor Daemon: [OK]

8. Check the status of the cluster host on monitor

[root@www ~] # mmm_control checks all

Db2 ping [last change: 2016-07-04 08:54:52] OK

Db2 mysql [last change: 2016-07-04 08:54:52] OK

Db2 rep_threads [last change: 2016-07-04 08:54:52] ERROR: Replication is broken

Db2 rep_backlog [last change: 2016-07-04 08:54:52] OK: Backlog is null

Db3 ping [last change: 2016-07-04 08:54:52] OK

Db3 mysql [last change: 2016-07-04 08:55:57] OK

Db3 rep_threads [last change: 2016-07-04 08:55:54] OK

Db3 rep_backlog [last change: 2016-07-04 08:55:54] OK: Backlog is null

Db1 ping [last change: 2016-07-04 08:54:52] OK

Db1 mysql [last change: 2016-07-04 08:54:52] OK

Db1 rep_threads [last change: 2016-07-04 08:55:25] ERROR: Replication is broken

Db1 rep_backlog [last change: 2016-07-04 08:54:52] OK: Backlog is null

After the replication problem is resolved:

[root@www ~] # mmm_control checks all

Db2 ping [last change: 2016-07-05 03:54:20] OK

Db2 mysql [last change: 2016-07-05 03:54:20] OK

Db2 rep_threads [last change: 2016-07-05 03:54:20] OK

Db2 rep_backlog [last change: 2016-07-05 03:54:20] OK: Backlog is null

Db3 ping [last change: 2016-07-05 03:54:20] OK

Db3 mysql [last change: 2016-07-05 03:54:20] OK

Db3 rep_threads [last change: 2016-07-05 03:54:20] OK

Db3 rep_backlog [last change: 2016-07-05 03:54:20] OK: Backlog is null

Db1 ping [last change: 2016-07-05 03:54:20] OK

Db1 mysql [last change: 2016-07-05 03:54:20] OK

Db1 rep_threads [last change: 2016-07-05 03:54:20] OK

Db1 rep_backlog [last change: 2016-07-05 03:54:20] OK: Backlog is null

[root@www ~] # mmm_control show

# Warning: agent on host db1 is not reachable

# Warning: agent on host db3 is not reachable

Db1 (192.168.110.128) master/REPLICATION_FAIL. Roles:

Db2 (192.168.110.130) master/ONLINE. Roles: reader (192.168.110.128), reader (192.168.110.130), writer (192.168.110.132)

Db3 (192.168.110.131) slave/ONLINE. Roles:

After the replication problem is resolved:

[root@www ~] # mmm_control show

# Warning: agent on host db1 is not reachable

# Warning: agent on host db2 is not reachable

Db1 (192.168.110.128) master/ONLINE. Roles:

Db2 (192.168.110.130) master/ONLINE. Roles:

Db3 (192.168.110.131) slave/ONLINE. Roles:

Iptales-F

[root@www ~] # mmm_control show

Db1 (192.168.110.128) master/ONLINE. Roles:

Db2 (192.168.110.130) master/ONLINE. Roles: reader (192.168.110.133), writer (192.168.110.132)

Db3 (192.168.110.131) slave/ONLINE. Roles: reader (192.168.110.134)

9. MMM High availability Environment Test

On db2:

[root@www ~] # service mysqld stop

Shutting down MySQL.. SUCCESS!

[root@www] # iptables-F

On monitor:

[root@www ~] # mmm_control show

Db1 (192.168.110.128) master/ONLINE. Roles: writer (192.168.110.132)

Db2 (192.168.110.130) master/HARD_OFFLINE. Roles:

Db3 (192.168.110.131) slave/ONLINE. Roles: reader (192.168.110.133), reader (192.168.110.134)

[root@www] # tail-f / var/log/mysql-mmm/mmm_mond.log

07:38:33 on 2016-07-05 FATAL Agent on host 'db2' is reachable again

07:38:41 on 2016-07-05 FATAL Can't reach agent on host 'db2'

07:38:45 on 2016-07-05 FATAL Agent on host 'db2' is reachable again

07:45:43 on 2016-07-05 FATAL Agent on host 'db1' is reachable again

07:48:48 on 2016-07-05 FATAL Can't reach agent on host 'db3'

07:49:03 on 2016-07-05 FATAL Can't reach agent on host 'db2'

07:49:12 on 2016-07-05 FATAL Can't reach agent on host 'db1'

07:49:18 on 2016-07-05 FATAL Agent on host 'db1' is reachable again

07:49:34 on 2016-07-05 FATAL Agent on host 'db2' is reachable again

07:49:46 on 2016-07-05 FATAL Agent on host 'db3' is reachable again

07:56:00 on 2016-07-05 FATAL State of host 'db2' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)

At this point, the state of db2 is changed from ONLINE to HARD_OFFLINE, the read role of db2 is transferred to db3, and the write role is transferred to db1.

[root@www ~] # service mysqld start

Starting MySQL.. SUCCESS!

08:00:29 on 2016-07-05 FATAL State of host 'db2' changed from HARD_OFFLINE to AWAITING_RECOVERY

08:01:29 on 2016-07-05 FATAL State of host 'db2' changed from AWAITING_RECOVERY to ONLINE because of auto_set_online (60 seconds). It was in state AWAITING_RECOVERY for 60 seconds

View the cluster status:

[root@www ~] # mmm_control show

Db1 (192.168.110.128) master/ONLINE. Roles: writer (192.168.110.132)

Db2 (192.168.110.130) master/ONLINE. Roles: reader (192.168.110.133)

Db3 (192.168.110.131) slave/ONLINE. Roles: reader (192.168.110.134)

[root@www] # mysql-ummm-monitor-p-h292.168.110.132

Enter password:

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 9108

Server version: 10.0.15-MariaDB-log Source distribution

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] >

[root@www] # mysql-ummm-monitor-p-h292.168.110.133

Enter password:

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 184

Server version: 10.0.15-MariaDB-log Source distribution

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] >

[root@www] # mysql-ummm-monitor-p-h292.168.110.134

Enter password:

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 9446

Server version: 10.0.15-MariaDB-log Source distribution

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.