MySQL MMM 資料不一致探究

MySQL MMM 資料不一致探究

slave重新指向新的master 的關鍵日誌輸出，通過這些日誌輸出，我們大致可以明白MMM自動failover或線上切換時的執行流程。

--自動failover，slave節點
2015/02/06 01:47:09  INFO Changing active master to 'raugherdb1'
2015/02/06 01:47:09 DEBUG Executing /usr/lib/mysql-mmm//agent/set_active_master raugherdb1
2015/02/06 01:47:09 DEBUG Result: OK

--線上切換
--主1節點
2015/02/06 05:49:23 DEBUG Received Command SET_STATUS|1|raugherdb1|ONLINE|reader(10.5.6.103)|raugherdb2
2015/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
 
--主2節點
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)|raugherdb2
2015/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節點
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)|raugherdb2
2015/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 raugherdb2
2015/02/06 05:49:23 DEBUG Result: OK

以下是set_active_master方法的代碼：

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 info
    my ($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 peer
    my ($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 server
    my $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 info
    my $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 peer
    my $old_peer = _find_host_by_ip($old_peer_ip);
    _exit_error('Invalid master host in 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 logs
    my $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 peer
    my $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 master
    my $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 credentials
    my ($repl_user, $repl_password) = _get_replication_credentials($new_peer);
 
    # Change master
    my $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';
}

從上面的代碼可以看出，在以下的架構中，只要是 主2和slave n出現延遲,在自動failover後就可能會造成主2和slave n的資料不一致現象
主1  ----slave1,slave2
主2

如果僅僅是雙主的架構，主1的執行個體down，自動failover後，可能會丟失部分資料。線上切換的話，不會遺失資料。

Flapping問題也可能會使資料出現問題，下面是官方文檔中對Flapping的解釋：
mmm mond supports the detection of hosts that are "flapping". Flapping occurs if a host
which is ONLINE changes its 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 60 seconds). This may lead to roles getting switched
between hosts very often.
To prevent this mmm mond has a built in flap-detection which can be 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 will not be set ONLINE automatically. It will stay in
state AWAITING_RECOVERY until it gets set online (with mmm_control set_online host ).
If auto set online is > 0, flapping hosts will automatically be set to ONLINE after
flap duration seconds.

要解決Flapping問題，我們可以設定master-connect-retry參數，但是官方2.2.1版本中設定這個參數是無效的，官方的代碼：
# set ONLINE because of small downtime
if ($agent->last_uptime > 0 && $uptime_diff > 0 && $uptime_diff < 60) {
    FATAL sprintf("State of host '%s' changed from %s to ONLINE because it was down for only %d seconds", $host, $state, $uptime_diff);
    $agent->state('ONLINE');
    $self->send_agent_status($host);
    next;
}

把60改為0.0001，這樣就防止了原master online,我們處理完原master後，再手動執行set_online命令。

使用MySQL-MMM實現MySQL叢集部署 

MySQL MMM架構看不到vip地址 

MySQL的MMM高可用架構測試

MySQL-MMM實現MySQL高可用

MySQL-MMM切換示範

mysql proxy、MySQL-MMM實現讀寫分離高可用性

將MySQL-MMM Master從REPLICATION_FAIL狀態恢複

CentOS下利用MySQL-MMM實現MySQL高可用

本文永久更新連結地址：