Search code examples
mysqlmariadbmysql-workbenchredhat

mysql not able to establish remote connection

Could not able to connect to the remote mysql server using mysql workbench or direct command line. When trying it resolves different hostname

Mysql workbench error message

When trying directly through command line 

mysql --host=10.37.1.92 --port=3306 --user=root --password=password
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'myaapvm.local' (using password: YES)

Tried, with password and without password, no luck.

I tried to connect to 10.37.1.92 server but my mysql client try to connect to different server. The only way I can try now is directly login to the machine and do the change in my DB. I have disabled firewall in my mysql DB. Does anyone faced this issue please help.

This server running with maria DB installation.

Solution

  • By default root is not allowed to access the database by using a remote connection. I recommend that you create a new user that will be used for remote connections. You can do that by using the following SQL commands:

        CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
    CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

    GRANT ALL ON *.* TO 'myuser'@'localhost';
    GRANT ALL ON *.* TO 'myuser'@'%';
    FLUSH PRIVILEGES;

    You also need to modify your my.cnf if you didn't do that already.

        #Replace xxx with your IP Address 
    bind-address        = xxx.xxx.xxx.xxx