Question

Cannot connect to MySQL database remotely

Posted July 20, 2021 255 views
MySQL

I provisioned this MySQL droplet: https://marketplace.digitalocean.com/apps/mysql

I am able to administer MySQL if I SSH via terminal, or if I go to http://my-ip-address/phpmyadmin

But I need to connect to my DB remotely. Whether I try to connect via terminal, or something like Sequel PRO, the connection fails with this error:

Host 'xxx.xxx.xxx' is not allowed to connect to this MySQL server

I followed every step of this guide (including setting bind-address to 0.0.0.0), but nothing changes and I’m still unable to connect: https://www.digitalocean.com/community/tutorials/how-to-allow-remote-access-to-mysql

Any ideas?

1 comment

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
1 answer

Hi @geochanto,

Have you created a new user that will only connect from the remote host? If not suggest you create a new user and try again.

You can get your local machine IP: https://whatismyipaddress.com/

CREATE USER 'sammy'@'remote_IP' IDENTIFIED BY 'password';

ALTER USER 'sammy'@'remote_IP' IDENTIFIED WITH mysql_native_password BY 'password';

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'remote_IP' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Regards,
Rajkishore

  • I did do this but I set ‘remote_IP’ to the droplet’s IP. You’re saying that should be my IP?

    If so, what can I do to enable someone else to connect as well? I have a couple of team members and we all need to be able to share this database.

    • Hi @geochanto,

      Then you can use a wildcard as a host which implies that the user can connect from any client host.

      CREATE USER 'sammy'@'%' IDENTIFIED BY 'password';
      
      ALTER USER 'sammy'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
      
      GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'%' WITH GRANT OPTION;
      
      FLUSH PRIVILEGES;
      

      I hope this helps!

      Regards,
      Rajkishore