Research on the inconsistency of MySQL MMM data

Research on the inconsistency of MySQL MMM data

Slave re-points to the key log output of the new master, and through these log outputs, we can roughly understand the process of MMM automatic failover or online switching.

--Auto Failover,slave node 2015/02/06 01:47:09 INFO changing active master to ' raugherdb1 ' 2015/02/06 01:47:09 DEBUG executing/u Sr/lib/mysql-mmm//agent/set_active_master raugherdb12015/02/06 01:47:09 DEBUG Result:ok

--online Switch--Main 1 node 2015/02/06 05:49:23 debug received command set_status|1|raugherdb1| Online|reader (10.5.6.103) |raugherdb22015/02/06 05:49:23  info we have some  New roles added or old rules deleted!2015/02/06 05:49:23  info  deleted: writer (10.5.6.100) 2015/02/06 05:49:23 debug executing /usr/lib/ mysql-mmm//agent/mysql_deny_write 2015/02/06 05:49:23 debug executing /usr/lib/ mysql-mmm//agent/clear_ip eth0 10.5.6.100--Primary 2 node 2015/02/06 05:49:22 debug daemon:  Command =  ' set_status|1|raugherdb2| Online|reader (10.5.6.101), writer (10.5.6.100) |raugherdb2 ' 2015/02/06 05:49:22 debug received  command set_status|1|raugherdb2| Online|reader (10.5.6.101), writer (10.5.6.100) |raugherdb22015/02/06 05:49:22  info we  have some new roles added or Old rules deleted!2015/02/06 05:49:22  info added:   writer ( 10.5.6.100) 2015/02/06 05:49:22 debug executing /usr/lib/mysql-mmm//agent/sync_with_master  2015/02/06 05:49:23 debug executing /usr/lib/mysql-mmm//agent/mysql_allow_write  2015/02/06 05:49:23 debug executing /usr/lib/mysql-mmm//agent/configure_ip eth0  10.5.6.100--slave node 2015/02/06 05:49:22 debug daemon: command =  ' SET_STATUS |1|raugherdb| Online|reader (10.5.6.102) |raugherdb2 ' 2015/02/06 05:49:22 debug received command set_ status|1|raugherdb| Online|reader (10.5.6.102) |raugherdb22015/02/06 05:49:22  info changing active  master to  ' RAUGHERDB2 ' 2015/02/06 05:49:22 debug executing /usr/lib/mysql-mmm// Agent/set_active_master raugherdb22015/02/06 05:49:23 debug result: ok

The following is the code for the Set_active_master method:

Sub set_active_master ($)  {my  $new _peer = shift;_exit_error (' Name of new  master is missing ')  unless  (defined ($new _peer));my  $this  = _get_this ( ); _exit_error (' new master is equal to local host!? ')  if  ($this  eq  $new _peer);# get local connection infomy  ($this _ host,  $this _port,  $this _user,  $this _password) = _get_connection_info ($this) _exit_error ("No  connection info for local host  ' $this _host ' ")  unless defined ($this _ Host);# get connection info for new peermy  ($new _peer_host,  $new _peer_ port,  $new _peer_user,  $new _peer_password) = _get_connection_info ($new _peer); _exit_error ("No  connection info for new peer  ' $new _peer ' ")  unless defined ($new _peer_ Host); # connect to local servermy  $this _dbh = _mysql_connect ($this _host,  $this _port,  $this _user,  $this _password); _exit_error ("Can ' t connect to mysql  (host =  $this _host: $this _port, user  =  $this _user)!  " .  $DBI:: errstr)  unless  ($this _dbh); # get slave  infomy  $slave _status =  $this _dbh->selectrow_hashref (' Show slave status '); _ Exit_error (' sql query error:  '  .  $this _dbh->errstr)  unless defined ($ Slave_status);my  $wait _log=  $slave _status->{master_log_file};my  $wait _pos=  $slave _ status->{read_master_log_pos};my  $old _peer_ip=  $slave _status->{master_host};_exit_error (' No  ip for old peer ')  unless  ($old _peer_ip); # get connection info  for old peermy  $old _peer = _find_host_by_ip ($old _peer_ip); _exit_error (' Invalid  master host in&nbSp;show slave status ')  unless  ($old _peer); _EXIT_OK (' we are already a  Slave of the new master ')  if  ($old _peer eq  $new _peer);my  ($old _ peer_host,  $old _peer_port,  $old _peer_user,  $old _peer_password) = _get_connection_info ($old _ Peer); _exit_error ("no connection info for new peer  ' $old _peer '")  unless  defined ($old _peer_host);my  $old _peer_dbh = _mysql_connect ($old _peer_host,  $old _peer_ port,  $old _peer_user,  $old _peer_password);if  ($old _peer_dbh)  {my  $old _master_status  =  $old _peer_dbh->selectrow_hashref (' Show master status ');if  (defined ($old _master_ Status)  {$wait _log =  $old _master_status->{file}; $wait _pos =  $old _master_status- >{position};} $old _peer_dbh->disconnect;} # sync with logsmy  $res  =  $this _dbh->do ("select  Master_pos_wait (' $wait _log ',  $wait _pos); _exit_error (' sql query error:  '  . $ THIS_DBH->ERRSTR)  unless ($res);# stop slave$res =  $this _dbh->do (' STOP  SLAVE '); _exit_error (' sql query error:  '  .  $this _dbh->errstr)  unless ($res); #  Connect to new peermy  $new _peer_dbh = _mysql_connect ($new _peer_host,  $new _peer_port,  $new _peer_user,  $new _peer_password) _exit_error ("Can" t connect to  mysql  (host =  $new _peer_host: $new _peer_port, user =  $new _peer_user)!  "  .  $DBI:: errstr)  unless  ($new _peer_dbh); # get log position of new  mastermy  $new _master_status =  $new _peer_dbh->selectrow_hashref (' show master  STATUS '); _exit_error (' sql query error:  '  .  $new _peer_dbh->errstr)  unless ($new _master_status);my  $master_log =  $new _master_status->{file};my  $master _pos =  $new _master_status->{ Position}; $new _peer_dbh->disconnect;# get replication credentialsmy  ($repl _user, $ Repl_password)  = _get_replication_credentials ($new _peer);# change mastermy  $sql   =  ' change master to '   .  '  master_host= ' $new _peer_host ', '   .  " master_port= $new _peer_port,"   .  " master_user= ' $repl _user ',"   .  "  master_password= ' $repl _password ', "  . "  master_log_file= ' $master _log ', "  .   " master_log_pos= $master _pos"; $res  =  $this _dbh->do ($sql); _exit_error (' Sql query  Error:  '  .  $this _dbh->errstr)  unless ($res); # start slave$res =   $this _dbh->do (' Start slave '); _exit_error (' sql query error:  '  .  $this _ DBH->ERRSTR)  unLess ($res);return  ' OK ';} 

As can be seen from the above code, in the following schema, as long as the main 2 and slave n delay, after automatic failover can cause the data inconsistency between the main 2 and slave n

Main 1----slave1,slave2

Main 2

If it is only a dual-master architecture, the instance of Master 1 is down, and after auto-failover, some data may be lost. If you switch online, you won't lose data.

Flapping problems may also cause data problems, the following is an explanation of flapping in the official documentation:

MMM Mond supports the detection of the hosts that is "flapping". Flapping occurs if a host

Which is ONLINE changes it state to Hard_offline/replication_fail/replication_

DELAY too often and each time gets switched back to ONLINE (because of auto set online or

Because it has been down for less than seconds). This may leads to roles getting switched

Between hosts very often.

To prevent this MMM mond have a built in flap-detection which can is tuned in the

Configuration file. If a host goes down for more than flap count times within flap duration

Seconds it is considered as flapping and would not be set ONLINE automatically. It'll stay in

State awaiting_recovery until it gets set online (with Mmm_control set_online host).

If Auto Set Online is > 0, flapping hosts would automatically be set to online after

Flap duration seconds.

To solve the flapping problem, we can set the Master-connect-retry parameter, but the official 2.2.1 version of this parameter is invalid, the official code:

# set ONLINE because of small downtimeif ($agent->last_uptime > 0 && $uptime _diff > 0 && $uptime _ Diff <) {FATAL sprintf ("State of the host '%s ' changed from%s to ONLINE because it is down for only%d seconds", $host , $state, $uptime _diff); $agent->state (' ONLINE '); $self->send_agent_status ($host); next;}

Change 60 to 0.0001, which prevents the original master online, and then manually execute the set_online command after we have processed the original master.

This article is from the "Days together with it" blog, so be sure to keep this source http://raugher.blog.51cto.com/3472678/1612440

Research on the inconsistency of MySQL MMM data