Related

Product Now Available: Managed MySQL Databases Product
Install MySQL on New Droplet Question Question
How can I solve error "SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)" Question Question

Question

LAMP/SSH - Cant login to MySQL

Posted April 1, 2020 276 views
MySQL

Hi

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 0.0.0.0 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.3 - FLUSH PRIVILEGES;
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?

Add a comment
webwizard
By:
webwizard
Add a 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.

×
1 answer
bobbyiliev April 2, 2020

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 0.0.0.0:3306 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:

https://ifconfig.io

  • 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.%';
FLUSH PRIVILEGES;

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:

https://www.digitalocean.com/community/tutorials/how-to-connect-to-a-mysql-server-remotely-with-mysql-workbench

Hope that this helps!
Regards,
Bobby

How to Connect to a MySQL Server Remotely with MySQL Workbench
by Jon Schwenn
Use MySQL Workbench to connect securely to your remote MySQL database using SSH.
Reply Report
  • webwizard April 2, 2020

    Thanks for taking the time to make such a great reply.

    tcp 0 0 0.0.0.0:3306 0.0.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

    Reply Report
    • bobbyiliev April 3, 2020

      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!
      Regards,
      Bobby

      Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help