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
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,"firstname.lastname@example.org","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@example.com","2020-06-07 16:40:13",NULL,NULL,NULL,NULL),(3,NULL,"firstname.lastname@example.org","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@example.com","2020-06-07 19:04:13",NULL,"2020-06-08 12:05:25","2020-06-08 01:42:53",NULL),(5,NULL,"firstname.lastname@example.org","2020-06-07 16:58:12",NULL,"2020-06-08 11:12:17","2020-06-08 11:03:38",NULL),(6,NULL,"email@example.com","2020-06-07 17:04:13",NULL,"2020-06-08 11:01:03",NULL,NULL),(7,NULL,"firstname.lastname@example.org","2020-06-08 16:59:56","2020-06-08 17:59:59",NULL,NULL,NULL),(8,NULL,"email@example.com","2020-06-07 17:16:12",NULL,"2020-06-08 00:56:23",NULL,NULL),(9,NULL,"firstname.lastname@example.org","2020-06-08 18:03:56",NULL,NULL,NULL,NULL),(10,NULL,"email@example.com","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:
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?
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)...
How can I make the MySQL_2 (secondary Primary) skip the "poisioned" events from binlog? (if 2. is not working)
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)...