Symptom:
Unable to access MySQL database remotely using root
account. Any attempt to access MySQL database will result in error:
ERROR 1045 (28000): Access denied for user 'root'@'ip-address' (using password: YES)
Solution:
The above MySQL error message is a default behavior of the MySQL server to disallow a Root user to connect remotely as by default the Root user is allowed to connect to MySQL server on from localhost
that is 127.0.0.1
. The solution is to create a new admin user. The below SQL commands will create new user called admin
and grant remote access:
mysql> CREATE USER 'admin'@'%' IDENTIFIED BY ''; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec)
Alternative but not recommended solution is to grant remote MySQL access to root user:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec)
The above line will grant a privilege to the root user to connect remotely:
$ mysql -u root -ppassword -h 172.17.0.14 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 41 Server version: 5.5.43-0+deb8u1 (Debian) Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> Bye