MySQL Group Replication Multi-Primary Setup

Question

We are running three MySQL Servers in a Group Replication Multi-Primary Setup.

Here are is one of my configuration of one member of that GR group:

# General replication settings
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1

# Shared replication group configuration
loose-group_replication_group_name = "3a3e0101-83a2-11ea-922f-000c29390e7a"
loose-group_replication_ip_whitelist = "10.0.1.XX,10.0.1.XX,10.0.1.XX"
loose-group_replication_group_seeds = "10.0.1.XX:33061,10.0.1.XX:33061,10.0.1.XX:33061"

# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

(IPs obfuscated)

Everything was running fine, until... I loaded a smiple SQL file from another MySQL (not a GR member) onto MySQL_1 (one of the three inside GR). The loaded file was created with 'mysqlpump --set-gtid-purged=Off'.

Here the content of that SQL file:

-- Dump created by MySQL pump utility, version: 8.0.11, FreeBSD11.1 (amd64)
-- Dump start time: Tue Jun  9 12:18:03 2020
-- Server version: 8.0.11

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `massmail` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */;
CREATE TABLE `massmail`.`emails` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`ext_id` bigint(20) DEFAULT NULL,
`email` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`sent` datetime DEFAULT NULL,
`bounced` datetime DEFAULT NULL,
`opened` datetime DEFAULT NULL,
`unsubscribed` datetime DEFAULT NULL,
`CTA` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
;
INSERT INTO `massmail`.`emails` VALUES (1,NULL,"[email protected]","2020-06-08 16:54:56",NULL,"2020-06-08 17:55:23","2020-06-08 17:55:34","2020-06-08 17:55:47"),(2,NULL,"[email protected]","2020-06-07 16:40:13",NULL,NULL,NULL,NULL),(3,NULL,"[email protected]","2020-06-08 16:57:56",NULL,"2020-06-08 23:54:21","2020-06-08 18:01:01","2020-06-08 18:01:51"),(4,NULL,"[email protected]","2020-06-07 19:04:13",NULL,"2020-06-08 12:05:25","2020-06-08 01:42:53",NULL),(5,NULL,"[email protected]","2020-06-07 16:58:12",NULL,"2020-06-08 11:12:17","2020-06-08 11:03:38",NULL),(6,NULL,"[email protected]","2020-06-07 17:04:13",NULL,"2020-06-08 11:01:03",NULL,NULL),(7,NULL,"[email protected]","2020-06-08 16:59:56","2020-06-08 17:59:59",NULL,NULL,NULL),(8,NULL,"[email protected]","2020-06-07 17:16:12",NULL,"2020-06-08 00:56:23",NULL,NULL),(9,NULL,"[email protected]","2020-06-08 18:03:56",NULL,NULL,NULL,NULL),(10,NULL,"[email protected]","2020-06-07 17:28:12",NULL,NULL,NULL,NULL);
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET SQL_MODE=@OLD_SQL_MODE;

(real emails obfuscated)

I also modified mysql.db table and insered a row that an existing DB user has access to the new table, and I flushed privileges (all this was done on MySQL_1), without using GRANT.

After this actions, MySQL_2 and MySQL_3 went into error mode.

In the logs I found the following:

2020-06-09T10:44:03.769050Z 996 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_recovery': Could not execute Update_rows event on table massmail.emails; Can't find record in 'emails', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000004, end_log_pos 162167168, Error_code: MY-001032
2020-06-09T10:44:03.769087Z 996 [Warning] [MY-010584] [Repl] Slave: Can't find record in 'emails' Error_code: MY-001032
2020-06-09T10:44:03.769140Z 996 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000004' position 162166781

I researched the above error and found the following thread: Some MySQL slave errors? But it's for Master-Slave replication, I could not find something similar for GR Multi-Primary replication...

There it's written by Max Vernon that you can stop the slave, and skip counters... So I checked on both MySQL_1 and MySQL_2 the latest binlog, and I found probably the place where all breaks...

Now I have several questions:

1. Why did the import of that SQL break the GR sync ? Or was it my manuall manipulation of the mysql.db table (without using the GRANT syntax) that broke GR sync? 1.1 What effect did the parameter '--set-gtid-purged=Off' have in this disaster? I guess none, am I correct?

2. Will the statements (from the above mentioned thread) skipping the binlog entries that breaks the replication work? As it is stated in this article that it is Master-Slave replication, and not GR Multi-Primary (like we have). The following commands are mentioned:

mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> show slave status \G;

For example, 'show slave status' gives me an empty response set... (because we use GR, and not Master-Slave replication)...

3. How can I make the MySQL_2 (secondary Primary) skip the "poisioned" events from binlog? (if 2. is not working)

4. I would like to give a brief summary how I think this could also be solved (by reseting MySQL_2/3 and resyncing all from MySQL_1):

   • make a backup of the my.cnf
   • uninstall mysql-server
   • reinstall mysql-server (should give a clean new mysql environment)
   • restore backuped my.cnf
   • start group replication Should work right? (the data to be synced from MySQL_1 is not that big yet ~1-200mb)

This 4. Solution is not my prefered one, as I would like to learn more about GR and how to resolve problem in a Multi-Primary GR setup. As last resort, I would have daily backups (of the VMs) that I could rollback, so this question here is more of philosophic nature...

Thank you for your support, and forgive me my stupidity, GR is quite new for me (also MySQL is part of my live for already a very long time)...

Stoney

Answer 1

So today I researched more and could fix the problem.

So the answers to my questions are:

1. The import File contained 'SET @@SESSION.SQL_LOG_BIN= 0;' which disabled to write to binlog. The inserts in the SQL file was executed therefore only on MySQL_1 and did not get replicated onto MySQL_2/3. The problem started after I updated one of the new rows on MySQL_1, that update got replicated onto MySQL_2/3, where it could not be executed, because the row that was updated was missing on that instances.

2. The approach in the mentioned link will not work, because we use Multi-Primary GTID (row based) replication.

3. How I fixed the problem: by injecting 'empty' GTIDs into MySQL_2/3 that will make the MySQL_2/3 to skip the problematic GTIDs from MySQL_1.

Here is a short How To:

Identify where exactly the problem occours (must be done on the Primary Master that went to ERROR mode):

less /var/db/mysql/MySQL_2.err

this will give you the GTID that failed:

2020-06-09T10:44:03.769050Z 996 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_recovery': Could not execute Update_rows event on table massmail.emails; Can't find record in 'emails', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000004, end_log_pos 162167168, Error_code: MY-001032
2020-06-09T10:44:03.769087Z 996 [Warning] [MY-010584] [Repl] Slave: Can't find record in 'emails' Error_code: MY-001032
2020-06-09T10:44:03.769140Z 996 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000004' position 162166781

As you can see, the error occurred at the following place: binlog.000004, end_log_pos 162167168

Execute now in mysql

SHOW BINLOG EVENTS IN 'binlog.000004';

search the statement with pos 162167168. (this was a COMMIT in my case). Scroll up and search the nearest 'Set @@SESSION.GTID_NEXT' statement. this will give you the GTID that has failed. (in my case it was '3a3e0101-83a2-11ea-922f-000c29390e7a:46648')

On the Primary Master that inserted that statement (in my case it was MySQL_1), you can do the following in a system terminal:

mysqlbinlog -v --include-gtids='3a3e0101-83a2-11ea-922f-000c29390e7a:46648' /var/db/mysql/binlog.000004 

This will show you even the query that caused the problem.

you can also do the following in mysql (on the error node again (MySQL_2)):

SHOW MASTER STATUS;

output:

root@localhost [(none)]> SHOW SESSION VARIABLES LIKE 'GTID_EXECUTED';
+---------------+----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                |
+---------------+----------------------------------------------------------------------------------------------------------------------+
| gtid_executed | 3a3e0101-83a2-11ea-922f-000c29390e7a:1-46647:1000007-1009647:2000007-2000010,
bbb7e668-8324-11ea-aada-000c29390e7a:1 |
+---------------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Also here you can see that execution stopped at 46647, what is consistant with the info that 46648 caused the problem.

now again on the error node (MySQL_2) again, do the following to create a valid empty GTID with that id:

STOP GROUP_REPLICATION; # just to make sure it's down!
SET GTID_NEXT="3a3e0101-83a2-11ea-922f-000c29390e7a:46648";
BEGIN; COMMIT;
SET GTID_NEXT="AUTOMATIC";

after this I restarted group replication with 'START GROUP_REPLICATION;' and it turned out that another row was causing problems. I needed to repeat that analysis and fix procedure once more (also on the third broken node MySQL_3), and then everything was up and in sync again.

I had other GTIDs (valid ones that must be replicated to all instances) inbetween the broken ones, and I checked after the fix on all instances that these valid (Inserts it was in my case) have been replicated to all servers. So I lost no data.

I hope this helps somebody to save some hours of work...