09340187a808db263325d23e0e8fe11514670ae2
By:
APPOcean

Can't connenct msyql remote server

July 18, 2016 1.1k views
MySQL Server Optimization Configuration Management Linux Commands Linux Basics

I use Ubuntu 14.04.4 x64 for App server (IP : 10.130.16.27)
and Ubuntu 14.04.4 x64 installed Mysql server 5.7.13 (IP : 10.130.40.80)

then I try to connect from App server to Mysql server with command

root@GatewayServer:~# mysql -u gps -h 10.130.40.80

and I got error below system show self ip not remote ip

Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'gps'@'10.130.16.27' (using password: YES)
root@GatewayServer:~#

2 Answers

You need to grant user gps access when connecting from 10.130.16.27. You can do that by running the following command in MySQL. You can also replace 10.130.16.27 with % to allow all hosts.

GRANT ALL PRIVILEGES ON *.* TO 'gps'@'10.130.16.27' IDENTIFIED BY 'password_for_user_gps' WITH GRANT OPTION;
FLUSH PRIVILEGES;
  • Yes I did

    +-------------------------+---------------+-------------------------+--------------+
    | GRANTEE                 | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
    +-------------------------+---------------+-------------------------+--------------+
    | 'root'@'localhost'      | def           | SELECT                  | YES          |
    | 'root'@'localhost'      | def           | INSERT                  | YES          |
    | 'root'@'localhost'      | def           | UPDATE                  | YES          |
    | 'root'@'localhost'      | def           | DELETE                  | YES          |
    | 'root'@'localhost'      | def           | CREATE                  | YES          |
    | 'root'@'localhost'      | def           | DROP                    | YES          |
    | 'root'@'localhost'      | def           | RELOAD                  | YES          |
    | 'root'@'localhost'      | def           | SHUTDOWN                | YES          |
    | 'root'@'localhost'      | def           | PROCESS                 | YES          |
    | 'root'@'localhost'      | def           | FILE                    | YES          |
    | 'root'@'localhost'      | def           | REFERENCES              | YES          |
    | 'root'@'localhost'      | def           | INDEX                   | YES          |
    | 'root'@'localhost'      | def           | ALTER                   | YES          |
    | 'root'@'localhost'      | def           | SHOW DATABASES          | YES          |
    | 'root'@'localhost'      | def           | SUPER                   | YES          |
    | 'root'@'localhost'      | def           | CREATE TEMPORARY TABLES | YES          |
    | 'root'@'localhost'      | def           | LOCK TABLES             | YES          |
    | 'root'@'localhost'      | def           | EXECUTE                 | YES          |
    | 'root'@'localhost'      | def           | REPLICATION SLAVE       | YES          |
    | 'root'@'localhost'      | def           | REPLICATION CLIENT      | YES          |
    | 'root'@'localhost'      | def           | CREATE VIEW             | YES          |
    | 'root'@'localhost'      | def           | SHOW VIEW               | YES          |
    | 'root'@'localhost'      | def           | CREATE ROUTINE          | YES          |
    | 'root'@'localhost'      | def           | ALTER ROUTINE           | YES          |
    | 'root'@'localhost'      | def           | CREATE USER             | YES          |
    | 'root'@'localhost'      | def           | EVENT                   | YES          |
    | 'root'@'localhost'      | def           | TRIGGER                 | YES          |
    | 'root'@'localhost'      | def           | CREATE TABLESPACE       | YES          |
    | 'mysql.sys'@'localhost' | def           | USAGE                   | NO           |
    | 'gps'@'localhost'       | def           | SELECT                  | NO           |
    | 'gps'@'localhost'       | def           | INSERT                  | NO           |
    | 'gps'@'localhost'       | def           | UPDATE                  | NO           |
    | 'gps'@'localhost'       | def           | DELETE                  | NO           |
    | 'gps'@'localhost'       | def           | CREATE                  | NO           |
    | 'gps'@'localhost'       | def           | DROP                    | NO           |
    | 'gps'@'localhost'       | def           | RELOAD                  | NO           |
    | 'gps'@'localhost'       | def           | SHUTDOWN                | NO           |
    | 'gps'@'localhost'       | def           | PROCESS                 | NO           |
    | 'gps'@'localhost'       | def           | FILE                    | NO           |
    | 'gps'@'localhost'       | def           | REFERENCES              | NO           |
    | 'gps'@'localhost'       | def           | INDEX                   | NO           |
    | 'gps'@'localhost'       | def           | ALTER                   | NO           |
    | 'gps'@'localhost'       | def           | SHOW DATABASES          | NO           |
    | 'gps'@'localhost'       | def           | SUPER                   | NO           |
    | 'gps'@'localhost'       | def           | CREATE TEMPORARY TABLES | NO           |
    | 'gps'@'localhost'       | def           | LOCK TABLES             | NO           |
    | 'gps'@'localhost'       | def           | EXECUTE                 | NO           |
    | 'gps'@'localhost'       | def           | REPLICATION SLAVE       | NO           |
    | 'gps'@'localhost'       | def           | REPLICATION CLIENT      | NO           |
    | 'gps'@'localhost'       | def           | CREATE VIEW             | NO           |
    | 'gps'@'localhost'       | def           | SHOW VIEW               | NO           |
    | 'gps'@'localhost'       | def           | CREATE ROUTINE          | NO           |
    | 'gps'@'localhost'       | def           | ALTER ROUTINE           | NO           |
    | 'gps'@'localhost'       | def           | CREATE USER             | NO           |
    | 'gps'@'localhost'       | def           | EVENT                   | NO           |
    | 'gps'@'localhost'       | def           | TRIGGER                 | NO           |
    | 'gps'@'localhost'       | def           | CREATE TABLESPACE       | NO           |
    | 'gps'@'%'               | def           | SELECT                  | YES          |
    | 'gps'@'%'               | def           | INSERT                  | YES          |
    | 'gps'@'%'               | def           | UPDATE                  | YES          |
    | 'gps'@'%'               | def           | DELETE                  | YES          |
    | 'gps'@'%'               | def           | CREATE                  | YES          |
    | 'gps'@'%'               | def           | DROP                    | YES          |
    | 'gps'@'%'               | def           | RELOAD                  | YES          |
    | 'gps'@'%'               | def           | SHUTDOWN                | YES          |
    | 'gps'@'%'               | def           | PROCESS                 | YES          |
    | 'gps'@'%'               | def           | FILE                    | YES          |
    | 'gps'@'%'               | def           | REFERENCES              | YES          |
    | 'gps'@'%'               | def           | INDEX                   | YES          |
    | 'gps'@'%'               | def           | ALTER                   | YES          |
    | 'gps'@'%'               | def           | SHOW DATABASES          | YES          |
    | 'gps'@'%'               | def           | SUPER                   | YES          |
    | 'gps'@'%'               | def           | CREATE TEMPORARY TABLES | YES          |
    | 'gps'@'%'               | def           | LOCK TABLES             | YES          |
    | 'gps'@'%'               | def           | EXECUTE                 | YES          |
    | 'gps'@'%'               | def           | REPLICATION SLAVE       | YES          |
    | 'gps'@'%'               | def           | REPLICATION CLIENT      | YES          |
    | 'gps'@'%'               | def           | CREATE VIEW             | YES          |
    | 'gps'@'%'               | def           | SHOW VIEW               | YES          |
    | 'gps'@'%'               | def           | CREATE ROUTINE          | YES          |
    | 'gps'@'%'               | def           | ALTER ROUTINE           | YES          |
    | 'gps'@'%'               | def           | CREATE USER             | YES          |
    | 'gps'@'%'               | def           | EVENT                   | YES          |
    | 'gps'@'%'               | def           | TRIGGER                 | YES          |
    | 'gps'@'%'               | def           | CREATE TABLESPACE       | YES          |
    +-------------------------+---------------+-------------------------+--------------+
    85 rows in set (0.00 sec)
    
    • Try deleting the gps local user

      DROP USER 'gps'@'localhost';

      And then try to login to MySQL using user gps

      mysql -u gps -p
      

      If you are unable to login then the password you are using for user 'gps'@'%' is most likely incorrect.

Have another answer? Share your knowledge.