3

Upon searching for this, the main cause of this error is usually when using snapshots or cloning the machine. But I did not do this. I perform mysqldump --master-data=2 on the master then restore the dump on my slave server. Then start the slave on the position indicated on the outfile.

Any idea on what caused this? I am running a multi-source replica and this error is from just 1 source.

here is the complete error:

Got fatal error 1236 FROM MASTER WHEN reading DATA FROM BINARY LOG: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'VM1-bin.000006' at 641809774, the last event read from '/binlogdata/VM1-bin.000009' at 146836375, the last byte read from '/binlogdata/VM1-bin.000009' at 146836375.';

3 Answers 3

2

Even if you server_id is different you may need to change the server_uuid which you can do manually in my.cnf or by deleting $datadir/auto.cnf, this file is created by MySQL when it stats and includes the server uuid.

So stop MySQL, remove the file, and start MySQL again.

2

The purpose of server_uuid and server_id are for a MySQL source (a.k.a. master) server to close a zombie replica (a.k.a. slave) connection after a network error. (Also the server_uuid in MySQL or server_id in MariaDB go into the GTID to allow a replica to know which source each transaction came from.) When a replica connects, the source presumes that any old connection is a “zombie” that can be closed (rpl_source.cc).

According to the server_uuid documentation, server_uuid is automatically generated the first time that MySQL starts and saved to data_dir/auto.cnf (e.g. /var/lib/mysql/auto.cnf). So some causes of duplicate auto.cnf might be:

  • Did you restore the datadir from a backup?
  • Did you create a Debian or Ubuntu image with mysql-server preinstalled, and then run multiple instances of it as replicas? Both the postinst script and the mysql.service ExecStartPre script run mysqld --initialize-insecure which puts the server-uuid in /var/lib/mysql/auto.cnf.

To fix the latter issue, for my mysql read replicas, I created a debian image with mysqld installed but without the datadir /var/lib/mysql/ initialized using this snippet:

# https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/
curl --fail --location https://dev.mysql.com/get/mysql-apt-config_0.8.24-1_all.deb --output /tmp/mysql-apt-config_0.8.24-1_all.deb
DEBIAN_FRONTEND=noninteractive dpkg -i /tmp/mysql-apt-config_0.8.24-1_all.deb
apt-get update

# disable starting mysql.service at install time
echo disable mysql.service | sudo tee /usr/lib/systemd/system-preset/01-disable-mysql-while-installing.preset
sudo DEBIAN_FRONTEND=noninteractive apt-get install -y mysql-server
sudo rm /usr/lib/systemd/system-preset/01-disable-mysql-while-installing.preset
# re-enable mysql service; it will start at the next boot
sudo systemctl enable mysql.service
# delete the datadir created by mysql-server postinst;
# mysql.service’s ExecStartPre will recreate it
rm -r /var/lib/mysql

Looking at the MySQL 8.0.31 source, this error may also be sent after a group replication error or when rejoining group replication, but I have not tested that (tracing duplicate_slave_id in the source rpl_binlog_sender.cc.

0

You must have a different value in the my.cnf on each server in a replication setup. server_id must be different on each machine, else replication will be trying to go in a loop.

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.