0

Sequence of events:

  1. Disabled binary logs in MySQL 8 slave with the following relevant parameters in mysqld.cnf:
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
  1. Did not purge binary logs before disabling binary logging in MySQL 8 Slave

  2. 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

  1. There was no backup of binary logs in master

  2. 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 
  1. 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
  1. 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?

4
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. Commented Oct 21, 2022 at 9:45
  • I executed 'reset master' in master in stead of slave to purge binary logs. All binary logs in master are reset now. Replication is broken. Please help me to get replication back on track.
    – Sarma MV
    Commented Oct 21, 2022 at 11:28
  • @Marcello Miorelli, Please let me know what additional details you need for getting the broken replication back on track
    – Sarma MV
    Commented Oct 21, 2022 at 14:57
  • @SarmaMV please add SHOW MASTER STATUS, current SHOW SLAVE STATUS\G and also check you GTIDS from your backup, with something like this: grep -i "gtid_purged" alldbs.sql | head -1 Commented Oct 21, 2022 at 18:23

1 Answer 1

0

Configured replication with steps below:

  1. Purged and reinstalled MySQL 8 in slave

  2. Enabled binary logging with:

   binlog_expire_logs_seconds = 86400
   log-replica-updates = OFF 
  1. Took back-up from master:
   mysqldump -u root -p --all-databases --flush-logs --single-transaction \
    --routines    --triggers --events --master-data=2 > alldbs-23Oct2022

  1. restored back-up on slave

  2. started replication:

   CHANGE REPLICATION SOURCE TO SOURCE_HOST="w.x.y.z", 
    SOURCE_USER="username", SOURCE_PASSWORD="Password",   
    SOURCE_AUTO_POSITION=1;
1
  • I had observed that a slave thread was running in the master with itself as slave Steps I had taken before Configuring the replication on the slave mentioned in Answer I had posted: Executed the following on master: stop slave; reset slave; reset slave all;
    – Sarma MV
    Commented Oct 27, 2022 at 6:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.