How To Fix MySQL Remote Login Errors

Published Date Author: , Posted August 25th, 2016 at 7:57:26am

In this case I had done a restore from a MySQL 5.1 server to a MySQL 5.6 server. That was not too smart, because it broke the mysql database table structures and prevented me from logging in remotely. Local login worked both via the socket and TCP.

mysql -u root -p -h remoteHostName
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'yourHost.com' (using password: YES)

I next tried to re-add the root@% user, but that failed too, giving me this error:
mysql> CREATE USER root@'%' IDENTIFIED BY 'yourPasswordHere';
ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'

So I executed the following to rebuild the databases:
mysql_upgrade -u root -p
Enter password:

Then re-added the remote root user:
mysql> CREATE USER root@'%' IDENTIFIED BY 'yourPasswordHere';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON *.* TO root@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

And all was well after that:
mysql -u root -p -h remoteHostName
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 545
Server version: 5.6.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

No comments as yet.

Leave Your Comment  Leave a comment

All fields marked with "*" are required.