Exploration of MySQL MMM data inconsistency

Exploration of MySQL MMM data inconsistency

The slave points to the key log output of the new master. Through the log output, we can roughly understand the execution process of MMM automatic failover or online switching.

-- Automatic failover and slave nodes
01:47:09 INFO Changing active master to 'raugherdb1'
01:47:09 DEBUG Executing/usr/lib/mysql-mmm // agent/set_active_master raugherdb1
01:47:09 DEBUG Result: OK

-- Online Switch
-- Master 1 Node
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)
05:49:23 DEBUG Executing/usr/lib/mysql-mmm // agent/mysql_deny_write
05:49:23 DEBUG Executing/usr/lib/mysql-mmm // agent/clear_ip eth0 10.5.6.100

-- Master 2 nodes
05:49:22 DEBUG Daemon: Command = 'set _ STATUS | 1 | raugherdb2 | ONLINE | reader (10.5.6.101), writer (10.5.6.100) | raugherdb2'
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)
05:49:22 DEBUG Executing/usr/lib/mysql-mmm // agent/sync_with_master
05:49:23 DEBUG Executing/usr/lib/mysql-mmm // agent/mysql_allow_write
05:49:23 DEBUG Executing/usr/lib/mysql-mmm // agent/configure_ip eth0 10.5.6.100

-- Slave Node
05:49:22 DEBUG Daemon: Command = 'set _ STATUS | 1 | raugherdb | ONLINE | reader (10.5.6.102) | raugherdb2'
05:49:22 DEBUG Received Command SET_STATUS | 1 | raugherdb | ONLINE | reader (10.5.6.102) | raugherdb2
05:49:22 INFO Changing active master to 'raugherdbdb'
05:49:22 DEBUG Executing/usr/lib/mysql-mmm // agent/set_active_master raugherdb2
05:49:23 DEBUG Result: OK

The following code shows 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 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'
. "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 ';
}

From the code above, we can see that in the following architecture, as long as there is a delay between master 2 and slave n, data inconsistency between master 2 and slave n may occur after automatic failure.
Master 1 ---- slave1, slave2
Master 2

If only the dual-master architecture is used, some data may be lost after the master 1 instance is down and automatically fails. If you switch online, data will not be lost.

The Flapping issue may also cause data problems. The following is an explanation of Flapping in the official documentation:
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
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.

To solve the Flapping problem, we can set the master-connect-retry parameter, but setting this parameter in official 2.2.1 is invalid. The official code is as follows:
# 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;
}

Change 60 to 0.0001 to prevent the original master from online. After processing the original master, we can manually execute the set_online command.

Use MySQL-MMM for MySQL cluster deployment

Vip address not visible in MySQL MMM Architecture

Test the MMM high-availability architecture of MySQL

MySQL-MMM for MySQL High Availability

MySQL-MMM switching demonstration

Mysql proxy and MySQL-MMM achieve high availability of read/write splitting

Restore the MySQL-MMM Master from the REPLICATION_FAIL status

Use MySQL-MMM in CentOS to achieve high MySQL availability

This article permanently updates the link address: