Testing the 5.6.11rc from Percona and after upgrading (replacing) via RPM on x86_64 (CentOS 6.4) and trying to restart replication I am getting this error:
We have been using old passwords for sometime and I don’t believe this problem existed in earlier releases of 5.6 and there is some indication that 5.6.11 broke this.
Can you confirm which server did you upgrade, master or slave or both?
Note that since 5.6.5 the secure_auth is by default enabled. You may double check this variable on master with
Also did you check the password is now in long format on master after changing?
Upgrading slave (5.5 → 5.6) first, and that [slave] will promoted to master and then the original master will be upgraded and become the slave.
I verified the show variables showed secure_auth as OFF.
As for making the change on the master for the password on the master we currently have old_passwords=1 and passwords are created in 16 byte format. It is my understanding that it is safe to turn off old_passwords as long as secure_auth isn’t enabled. Is that correct? I don’t want to end up in a state where slaves can no longer connect. All slaves and masters are 5.5.15 or higher.
We are at the early stages or our migration and this was the first obstacle that we couldn’t find a work around for [without making any change on the master]. If changing the password on the master is simply turning off old_passwords and clients connecting will be able to negotiate both as needed then that is an acceptable option. However if the auth system was broken in 5.6.11 then that is a larger concern.
Side note;
We tested the upgrade with Oracle 5.6.11 as well and it had the same problem.
I just wanted to follow up here. I wound up creating a similar user with the “new” format for a specific IP in the 5.5 master. This allowed both the 5.6 and any legacy clients to connect successfully. I was then able replicate from 5.5 master to 5.6 with no problems. On the 5.5 servers I did the following:
mysql> SET SESSION old_passwords = 0;
mysql> SET sql_log_bin = 0;
mysql> GRANT REPLICATION CLIENT ON . TO ‘replicator’@‘’ IDENTIFIED BY ‘secret’;
No changes to the secure_auth setting were made.
This worked in my case because were using 10.% style host for the “old” password user entry so the ip specific one was treated as a distinct entry.
mysql> SET old_passwords=1;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT USAGE ON *.* TO 'test1'@'10.%' IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.01 sec)
mysql> SET old_passwords=0;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT USAGE ON *.* TO 'test2'@'10.%' IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,password from mysql.user where user like "test%";
+-------+------+-------------------------------------------+
| user | host | password |
+-------+------+-------------------------------------------+
| test1 | 10.% | 6f8c114b58f2ce9e |
| test2 | 10.% | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------+------+-------------------------------------------+
2 rows in set (0.00 sec)
Also, the old password format is really needed only for pre-4.1 clients, so those created in prehistoric times of MySQL 4.0 and earlier
So I would suggest to update all the passwords into new, long hash format, test that all authentication works, and only then you can also enable secure_auth variable.
Basically, this variable will block using old format passwords in the future, so even if someone explicitly enables old_passwords and set short-hash password, it won’t work for authentication.
I get this same problem when trying to replicate from 5.0.x to 5.7, I am trying to upgrade. None of these solutions work as the 5.0 system only does the older form of authentication and everything I have tried for replication fails with error code 2049, implying secure auth is in use but vars show that it is not:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| secure_auth | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%old_pass%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | 1 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show slave status;
... 2049 | error connecting to master ...
We are in the exact same boat: the master is on 5.0.95 with old_passwords = 1 and secure_auth = OFF (can’t change that very easily )
Slave is on 5.6.27; it currently has the same old_passwords = 1 and secure_auth = OFF, but those slave settings, and any other slave settings, can be changed at will.
We’re stalled now at “2049 | error connecting to master” when running show slave status. The replication user on the master is showing the shorty 16 bit password.
If anyone has any suggestions given our limitations (we can make all the changes we need on the slave, and can add/change users on the master), it would be greatly appreciated!
Just adding my experience - after many tries I have deleted all replication users from master (I tried multiple hosts). And run FLUSH PRIVILEGES. Afterwards I have set old_passwords on master to 0 and created the replication user again. Now everything worked fine. Worth trying.
Thanks, brablc, I really appreciate your input. For future generations, here’s what we did that worked for a 5.0 master with 5.6 slaves on RHEL / Centos stuck on old_passwords and secure_auth off:
set the slave in my.cnf: old_passwords = 0 and skip_secure_auth . service mysqld restart it just to make sure that the changes have taken effect.
For what it’s worth, setting innodb_flush_log_at_trx_commit=0 on the slave for import can speed it up from a 3 day import to 6 hours. (120GB dump file on a 16 core 6-disk machine with 192GB RAM). Disabling binlog can make it faster, as well, by greatly reducing needless disk writes as you import. Make sure to set trx_commit back to 1 or 2 after you are done importing.
On the master, we retain the old_passwords = 1 and secure_auth=OFF settings as needed for the legacy setup. Legacy blows; upgrading both the master version and passwords is definitely worthwhile if you can. But if you cannot for whatever reason, read on.
Import your full dump to the slave. You won’t be able to import the mysql tables, including user, without doing the upgrade process, but adding a few new users again to the cleanly installed 5.6 slave isn’t a big deal. Enable binlogs and change the trx_commit setting back as mentioned above. Restart the slave mysql process.
Do your usual SET MASTER to MASTER_PASSWORD=‘yourpassword’ … settings on the slave. Make sure to set the password as it will store the password on the slave to the new password format locally on the slave.
On the master, run set old_passwords=0; GRANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO ‘your5.6replicationuser’@‘yourIPrange’ IDENTIFIED BY ‘yourreplicationuserpassword’;
On the slave, start slave and then show slave status. It should be connected and working perfectly (the time behind the master should be counting down as it catches up) assuming your firewalls are set up properly and your dump import was done correctly.
On the master, run set old_passwords=1; You should be all done. Good luck.
)