0

MySQL Server - 5.7.44

I have a master and three slaves running on MySQL 5.7.44. The GTID is enabled and I am using gtid_enforce_consistency.

I took a backup from a slave and built a fourth slave using xtrabackup. Following is the command I used to backup the data.

xtrabackup --defaults-file=/etc/my.cnf --user="bkp_user" --password="password" --port=3306 --socket=/var/lib/mysql/mysql.sock --backup --use-memory=2048MB --tmpdir=/tmp/

after copying the backup to the data directory, I applied the logs

xtrabackup --apply-log --use-memory=10G .

and then once the logs are applied, after changing the ownership of the files, I started the new slave. I added skip-slave-start in the config file before starting the new slave.

I, then, used the following command to connect the slave to the server from where I had taken backup.

change master to master_host='IP', MASTER_USER='replica', MASTER_PASSWORD='password', MASTER_AUTO_POSITION=1;

I kept receiving the Duplicate entry errors 1062. I skipped one GTID and started the replication, but it kept happening and I kept receiving 1062 error.

LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '8e36a7e9-4bb6-11ea-9989-0aab56f5ae8e:4967477654' at master log binlog.000454, end_log_pos 638; Could not execute Write_rows event on table db.table; Duplicate entry '97193728' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 638 LAST_ERROR_TIMESTAMP: 2024-11-22 22:38:18

I had to switch back to BINLOG POSITION based replication.

STOP SLAVE; RESET SLAVE ALL; change master to master_host='IP', MASTER_USER='replica', MASTER_PASSWORD='password', MASTER_LOG_FILE='binlog.000454', MASTER_LOG_POS=772253840;

And replication is working fine. I am out of thought, what did I miss? Also how do I safely switch back to MASTER_AUTO_POSITION?

This link gives a simple procedure to do so, but really not sure and haven't tried it yet https://thedataguy.in/mysql-convert-binlog-based-replication-to-gtid-replication-without-downtime/

This percona blog talks about doing that on MariaDB, reading, searching further

https://www.percona.com/blog/convert-mariadb-binary-log-file-and-position-based-replication-to-gtid-replication/

Improve this question
4
  • Something like this is simpler: Stop one rejplica; clone its disk start replica; change the new replica's server_id; start replica. (Reset and Change Master scare me.) Commented Dec 1, 2024 at 20:00
  • I will have to try that, but why reset and change master is scary? @RickJames Commented Dec 13, 2024 at 3:41
  • Replication must be started at the place where it left off. Both RESET and CHANGE alter that; blindly applied will lead to a mess on the Replica. Carefully applied, one and/or the other may achieve your goal. I do not have experience with GTID, which adds another dimension to the issue. Commented Dec 13, 2024 at 19:18
  • yes, so I can make the slave single threaded, let it catch up for a minute or two, stop slave, record the position, reset master, change master and start. then I can enable mtr. Commented Dec 14, 2024 at 5:33

1 Answer 1

Reset to default
0

Since you are taking the backup from slave, it is highly recommended that when running xtrabackup command, make sure you add the parameter or argument --safe-slave-backup. This means that, in order to assure a consistent replication state, this option stops the replication SQL thread and waits to start backing up until Slave_open_temp_tables in SHOW STATUS is zero. Another option to add is also --slave-info to make sure what log position has been recorded if in case you need to do it manually.

Improve this answer
2
  • yes, thanks, but how do I switch to master auto position? @Severalnines also here it says it's not necessary to use it with row based replication, I am using row based replication docs.percona.com/percona-xtrabackup/2.4/xtrabackup_bin/… Commented Jan 13 at 23:38
  • Hey @AvinashPawar, If that’s the case, then you can retry it again without it but this time use the replica as your source master. Change the master once it caught up and switch it to the original source/master. Connecting to the source or master is still with MASTER_AUTO_POSITION unless it’s 8.0.23 and later, shall be SOURCE_AUTO_POSITION=1. Commented Jan 17 at 12:24

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.