LAMP/SSH - Cant login to MySQL

Posted April 1, 2020 1.3k views


I’ve been trying to connect to MySQL but I am unable.

This is how I go about the setup:

0 - I retrieve the MySQL root password in: /root/.digitalocean_password
1 - I set up 2x A records 3x NS records
2 - I out comment the bin-address or change it to in the MySQL config
3 - I log in to MySQL: mysql -u root -p

In step 3 is it normal that any password works?, and not only the one provided by the digital ocean one click service? as the MySQL root password?

4 - Within the MySQL terminal i:
4.1 - CREATE USER ‘myname’@'myip’ IDENTIFIED BY 'password’;
4.2 - GRANT ALL PRIVILEGES ON * . * TO 'myname’@'myip’;
4.4 - quit

In step 4, myip is my own IP, right? like '75.252.532.561’?

5 - Open up HeidiSQL and set the settings like:
5.0 Network MariaDB OR MySQL (TCP/IP)
5.1 Hostname / IP = My servers IP
5.2 User = myname
5.3 Password = password
5.4 Port = 3306

I tried restarting MySQL, I tried many different things, but the above I think is the correct way to set it up?.

However, I am unable to login.. there is not set a firewall and I don’t get it.

I’ve read several articles, questions, answers, solutions, etc etc..

I get this error code 10060 with the message Can’t connect to MySQL server on 'myserverip’ (10060)

Can someone please help me out here?

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 there @webwizard,

The steps that you’ve taken look correct. I could suggest a few extra things:

  • Make sure that your MySQL is actually binding on by running this command:
  • netstat -plant | grep 3306

Feel free to share the output here.

  • If you have a dynamic IP address, this might explain the problem that you are seeing. I would recommend visiting this site here and making sure that you’ve used the correct IP address when creating your MySQL user:

  • If you have a dynamic IP address, there are a couple of ways to solve the problem:
  1. Either use a wildcard and grant access to the whole IP range when creating your user:
CREATE USER 'myname'@'75.123.123.%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'myname'@'75.123.123.%';

That way if your IP changes you would still be allowed to connect as long as your new IP is from the same range.

  1. Rather than accessing MySQL directly, use SSH tunnel, you can that by following the steps from this tutorial here:

Hope that this helps!

by Jon Schwenn
Use MySQL Workbench to connect securely to your remote MySQL database using SSH.
  • Thanks for taking the time to make such a great reply.

    tcp 0 0* LISTEN 5108/mysqld

    What does that mean?

    Hmm i still can’t log in.

    I keep getting the error as stated in the post

    • Hi there @webwizard,

      I think that it is due to your new MySQL user not being allowed to access MySQL.

      What happens when you run:

      SELECT user,host FROM mysql.user WHERE user='your_mysql_user' \G

      I would still suggest trying to use SSH socket as well.

      Hope that this helps!