MySQL 5.6 - [Note] Slave: received end packet from server, apparent master shutdown

Sep 5, 2013

This error was driving me crazy the past few weeks. I setup 3 new MySQL 5.6.13 servers, 1 master and 2 slaves. My error log was filling up with errors like this every second, sometimes multiple times a second, for each event run.

2013-09-05 09:56:03 12830 [Note] Slave: received end packet from server, apparent master shutdown:
2013-09-05 09:56:03 12830 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'db1-bin.000147' at position 1048865220
2013-09-05 09:56:03 12830 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2013-09-05 09:56:03 12830 [Note] Slave: received end packet from server, apparent master shutdown:
2013-09-05 09:56:03 12830 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'db1-bin.000147' at position 1048865220
2013-09-05 09:56:03 12830 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2013-09-05 09:56:03 12830 [Note] Slave: received end packet from server, apparent master shutdown:
2013-09-05 09:56:03 12830 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'db1-bin.000147' at position 1048865220
2013-09-05 09:56:03 12830 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

Whenever I Googled the error, every solution was to check the server-id and make sure each MySQL instance had a different one. I must have checked this a few times and they were all different. After searching for about an hour, I came across this older blog post:
Confusing MySQL Replication Error Message by Peter Zaitsev.

Although the blog post didn't have my solution, a comment at the very bottom did, by a Michael Wehrle:

I have observed this is still a problem with MySQL 5.6 today. The problem and resolution are slightly different though. My server-id were properly set differently, but the server-uuid was the same, because this new slave was a clone. My resolution was to change the server-uuid

MySQL 5.6 uses a new variable called the server-uuid, which previous versions of MySQL didn't.

I issued a show variables command for server-uuid on each of my 3 servers:

show variables like "%uuid%";

And there it was on each of my 2 slave servers, an identical uuid.

It surely happened because I performed a direct file copy of 1 slave to the other.

To change the uuid, I shut down one of my slave servers, changed into its MySQL data directory, and I renamed the auto.cnf file, which contains the servers uuid:

# /etc/init.d/mysql stop
# cd /var/lib/mysql
# mv auto.cnf auto.cnf.bk
# /etc/init.d/mysql start

After made the change and the restart, if you look at your error log, you will see a message similar to this, where MySQL is creating your new server UUID:

2013-09-05 10:08:57 15930 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.

It would be nice if the original error message wasn't so vague though. I should post it as a bug request.

In addition, I stopped seeing repeated messages for:

[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

Instead it only showed up once in the error log at the MySQL servers initial start up.

Comments

MOHD Kasid -- Oct 22, 2015 10:03 AM
Hey , I am facing same issue. I verified my server_id which are different in each slaves and there is no uuid as we are using 5.5.27 version. so please suggest what else can be done. Thanks in advance .
Reply
Ondrej Prochazka -- Dec 12, 2014 7:30 AM
Thank you, this help me.
Reply
Walter Heck -- Feb 25, 2014 9:28 AM
Thanks, you totally saved me a bunch of searching around just now. I was setting up a percona xtradb cluster as a slave of an existing cluster and ran into this problem. I moved the auto.cnf on the existing slave out of the way, it created a new UUID on server restart.
Reply
New Comment