MySQL MMM high-availability architecture test_mysql

MySQL MMM high-availability architecture test bitsCN.com I. Architecture mode (Dual master Zero slave)

========================================================== ======================================



========================================================== ======================================
--------------------- [1] mmm_common.conf -------

Host:
Function IP host name
Monitor 192.168.9.159 proxy
DB db1 Server-id = 1 192.168.5.157 fbfldb01
Db2 Server-id = 2 192.168.9.158 fbfldb02
Virtual ip address:
IP0 192.168.9.154 (Write)
IP1 192.168.9.155 (Read)
IP2 192.168.9.156 (Read)

Active_master_role writer

Cluster_interface eth0
Pid_path/var/run/mysql-mmm/mmm_agentd.pid
Bin_path/usr/libexec/mysql-mmm/
Replication_user slave
Replication_password slave
Agent_user mmm_agent
Agent_password RepAgent


Ip 192.168.9.157
Mode master
Peer db2


Ip address 192.168.9.158
Mode master
Peer db1

#
# Ip address 192.168.100.51
# Mode slave
#

Hosts db1, db2 # host that can be used as writer
Ips 192.168.100.250 # writer's virtual IP address. the application's write request can directly access the ip address here.
Mode exclusive # There are only two modes: exclusive is exclusive. In this mode, only one host can have this role at any time. in balanced mode, multiple hosts can have this role at the same time. Writer is ex, reader is ba


Hosts db1, db2 # can be used as the host of reader
Ips 192.168.100.133, 192.168.100.252 # virtual IP address of reader. the application's read requests can directly access the ip address here.
Mode balanced



-------------------- [2] mmm_mon.conf --------
Include mmm_common.conf

Ip 127.0.0.1
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.9.157, 192.168.9.158, 192.168.9.254


Monitor_user mmm_monitor
Monitor_password RepMonitor

Debug 0

------------------------ [4] mmm_agent.conf -------
Include mmm_common.conf
This db1 # this is written on DB1. DB2 is written as this db2. if it is wrong, mysql-mmm-agent of a DB cannot be enabled! Remember!

View Status:
[Root @ Proxy ~] # Mmm_control show
Db1 (192.168.9.157) master/ONLINE. Roles: reader (192.168.9.156), writer (192.168.9.154)
Db2 (192.168.9.158) master/ONLINE. Roles: reader (192.168.9.155)
[Root @ Proxy ~] # Mmm_control checks
Db2 ping [last change: 14:04:41] OK
Db2 mysql [last change: 14:04:41] OK
Db2 rep_threads [last change: 14:04:41] OK
Db2 rep_backlog [last change: 14:04:41] OK: Backlog is null
Db1 ping [last change: 14:04:41] OK
Db1 mysql [last change: 2012/02/03 14:04:41] OK
Db1 rep_threads [last change: 14:04:41] OK
Db1 rep_backlog [last change: 14:04:41] OK: Backlog is null
[Root @ Proxy ~] # Mmm_control mode
ACTIVE

Simulate downtime switchover:
Find a client and perform the write operation:
@ Client [root @ mysql-1 ~] # Vi/usr/local/mysql/binlog/inserting-into-db.sh
#! /Bin/bash
While true;
Do
Mysql-ufbdb_user-pJc7ech-fB-me-h192.168.9.154 -- database = fbdb-e "insert into test values (null );"
Sleep 1;
Done;
[Root @ mysql-1 ~] #./Inserting-into-db.sh &

We can see that the binlog of the two databases shows that the server IDs are both 1, that is, db1 is used as the write database in the current situation.
Stop db1
[Root @ fbfldb01 ~] #/Etc/init. d/mysqld stop
Shutting down MySQL... SUCCESS!
Restore DB1 immediately and check the mmm cluster status on the proxy

Check the logs of mmmDB1:
[Root @ fbfldb01 binlog] # tail-f/var/log/mysql-mmm/mmm_agentd.log
17:46:10 FATAL Couldn't allow writes: ERROR: Can't connect to MySQL (host = 192.168.9.157: 3306, user = mmm_agent )! Lost connection to MySQL server at 'Reading initial communication packet ', system error: 111
2012/02/03 17:46:13 INFO We have some new roles added or old rules deleted!
2012/02/03 17:46:13 INFO Deleted: reader (192.168.9.156), writer (192.168.9.154)
17:46:13 FATAL Couldn't deny writes: ERROR: Can't connect to MySQL (host = 192.168.9.157: 3306, user = mmm_agent )! Lost connection to MySQL server at 'Reading initial communication packet ', system error: 111
2012/02/03 17:59:48 INFO We have some new roles added or old rules deleted!
2012/02/03 17:59:48 INFO Added: reader (192.168.9.155)
2012/02/03 18:01:12 INFO We have some new roles added or old rules deleted!

From the log, we can see that after db1 is stopped, mmm prompts a connect error. because the current database is db1, mmm considers that the data on db2 cannot be consistent with db1, therefore, db2 reader is migrated to db1. Changed:
Db1 (192.168.9.157) master/ONLINE. Roles: reader (192.168.9.155)
Db2 (192.168.9.158) master/ONLINE. Roles: reader (192.168.9.156), writer (192.168.9.154)
However, if DB1 does not recover immediately, an alarm will be triggered after 10 seconds for the "mysql" check item of mmm. if db1 is deemed to have completely failed, db1 will be set to hard_offline, switch db2 from replication_fail to online (because db2's mysql is at least still alive) and switch all the roles above to db2. The final status changes:
[Root @ Proxy mysql-mmm] # mmm_control show
Db1 (192.168.9.157) master/HARD_OFFLINE. Roles:
Db2 (192.168.9.158) master/ONLINE. Roles: reader (192.168.9.155), reader (192.168.9.156), writer (192.168.9.154)

Obviously, when either DB1 or DB2 goes down, mmm immediately converts all the roles of the host to another database.
After careful analysis, the Mmm processing steps are roughly as follows:
Db1 "mysql" check returns to normal, and then switch db1 to awaiting_recovery status. Then mmm determines that the downtime of db6 is within the normal range and is not abnormal. Therefore, the automatic switch is online.
Migrate a reader role in db2 to db1.
Currently, the write database is db2.
Note: Set prefer = db1, so that after db1 returns to normal, it can be switched to a write database again.

Observe the entire switching process and find that the switching process took 15 S!


This article is from the "Centi. Linux" blog bitsCN.com