Fix ERROR 1045 (28000): Access denied for user 'root'@'ip-address' (using password: YES)

Problem :

It is a standard procedure for database administrator to ssh into database server and work on localhost level. However, there are times when you will need to connect to the database server remotely from another machine that happen to have the same version of mysql tool.

If you try to connect for the first time, chances are mysql will generated ERROR 1045 while attempting to connect remotely to a MariaDB/MySQL database with this command :

>mysql -u root -p -h ip-address

ERROR 1045 (28000): Access denied for user 'root'@'ip-address' (using password: YES)

Diagnostic :

By default and as a good security measure, MariaDB/MySQL server will not allow root user to connect from remote. Root user is allowed to connect to the server either from localhost or 127.0.0.1.

Sometimes the mysql version is different from the client machine to the host machine and this will cause unpredictable error when connect from remote as root.

Solution :

You can either ssh to the database server directly and execute mysql -u root -p command there.

or

You can change the default MariaDB/MySQL setting to allow remote root connection. Not recommended, but it is your system. :-)

You can do that by adding the additional privilege to root. First you need to use mysql tool at localhost level. Then :

mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'password';

Query OK, 0 rows affected (0.00 sec)

Once you have granted the additional privileges to root originating from % ( wildcard for any IP address ) , try to connect to the database server with mysql tool as root from client machine. It should allow root access this time.

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 3108

Server version: 5.5.46-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>