Sample Header Ad - 728x90

Error 1236 in MySQL GTID Slave due to mistaken 'reset master' on MySQL 5.7 GTID master

0 votes
1 answer
663 views
Sequence of events: 1. Disabled binary logs in MySQL 8 slave with the following relevant parameters in mysqld.cnf:
-log-bin
log-replica-updates = OFF
gtid-mode = ON
enforce-gtid-consistency = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
relay_log_recovery = ON
sync-master-info = 1
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
slave-preserve-commit-order = ON
# binlog_expire_logs_seconds    = 2592000
#max_binlog_size   = 100M
2. Did not purge binary logs before disabling binary logging in MySQL 8 Slave 3. re-enabled binary logging by uncommenting:
# binlog_expire_logs_seconds    = 2592000
# max_binlog_size   = 100M
and commenting:
disable-log-bin
and instead of executing reset master in MySQL 8 slave, mistakenly executed the command reset master on MySQL 5.7 master while replication is running 4. There was no backup of binary logs in master 5. Purged and re-installed the MySQL 8 slave, restored the backup (executed on master:
mysqldump -u root -p --all-databases --flush-logs \
 --single-transaction --routines --triggers --events \
 --master-data=2 > alldbs.sql
6. Reconfigured GTID replication using MySQL master 5.7 and MySQL slave 8.0. Relevant parameters from the MySQL 5.7 master mysqld.cnf:
binlog-format = ROW
log-slave-updates = ON
gtid-mode = ON
enforce-gtid-consistency = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
binlog-transaction-dependency-tracking = COMMIT_ORDER
7. Observed an error: > 1236: Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. > >The master may or may not have rolled back transactions that were already replicated to the slave. > >Suggest to replicate any transactions that master has rolled back from slave to master, and/or commit empty transactions on master to account for transactions that have been How to reconfigure GTID replication?
Asked by Sarma MV (1 rep)
Oct 21, 2022, 07:50 AM
Last activity: Jun 30, 2025, 11:06 AM