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
Last activity: Jun 30, 2025, 11:06 AM