lazlo
By:
lazlo

Cannot connect to MySQL via Sequel Pro

March 10, 2017 769 views
MySQL Ubuntu 16.04

I am having trouble connecting to MySQL on my new Ubuntu 16.04 droplet via Sequel Pro.

I Installed MySQL via the DigitalOcean tutorial. I set a root password in mysql_secure_installation, then restarted the service.

Checking for status shows the service is running.

My Sequel Pro configuration looks like this:

Configuration

This exact configuration worked on previous droplets, but now I get:

MySQL said: Access denied for user 'root'@'localhost'

Note that I don't have the usual (using password: YES/NO).

All the solutions I found through Google don't seem to help. They say to comment out bind-adress 127.0.0.1 or set it to bind-adress 0.0.0.0 in the MySQL config then restart, which I've done, but I still can't connect.

What am I doing wrong? This is driving me mad...

2 Answers
lazlo March 10, 2017
Accepted Answer

Well, I uninstalled and re-installed MySQL completely, and now it works. Something must have gone wrong in the previous configuration, but now we'll never know.

  • @lazlo

    Generally you want to set bind-address to the IP of the Droplet if you need to allow remote connections. When it's set to localhost or 127.0.0.1, then only local connections can be made.

    One important thing to remember is that by opening up remote connections, you need to setup proper firewall rules. Ideally, those rules should only allow you to connect remotely as without them, anyone can attempt to connect on port 3306.

    Without firewall rules in place to block others from connecting, or attempting to connect, you're inviting potential brute-force attacks in.

    Since you're using Ubuntu, this can be done with ufw, though you really need to set it up before just adding random rules.

    For example, to start, you'd check the status to make sure it's currently disabled:

    sudo ufw status
    

    If it's not disabled, then:

    sudo ufw disable
    

    Followed by:

    sudo ufw reset
    

    The above will reset the rules and allow us to start fresh. Now we can setup default policies. The first will to be to deny all incoming. Since the firewall is disabled, these won't affect out current session, so you won't get kicked or blocked.

    sudo ufw default deny incoming
    
    sudo ufw default allow outgoing
    

    With those in place, we now need to set rules to allow incoming connections to only ports that we actually want connections on. SSH would be the first rule since we need to use SSH to connect to the CLI, so we'll start with:

    sudo ufw allow 22/tcp
    

    Now if this is a web server, we should allow ports 80 and 443 as well as those are our HTTP and HTTPS ports.

    sudo ufw allow 80/tcp
    
    sudo ufw allow 443/tcp
    

    Now, when it comes to port 3306 (MySQL), as noted above, for security, we need to limit who can access it. The hard part here is that if you have a Dynamic IP instead of a Static IP (assigned by your ISP), this may be hard to handle. You can set a range, but if your ISP bounces from one to another, you may have to remove the rule and repeat for the new one.

    So for this example I'm going to provide three methods, one is to openly allow anyone to connect (the same as would be possible right now), another to allow a range, and the other is how to setup single-IP restriction.

    To allow anyone to connect:

    sudo ufw allow 3306/tcp
    

    To only allow IP's in a range you can use something like the following. You would change this to match an IP range that is associated with your actual IP. The following isn't real and has to be modified to work.

    sudo ufw allow from 15.15.15.0/24 to any port 3306
    

    To only allow one specific IP. Much like the above, this needs to be modified and in place of 15.x.x.x you'd use your real IP.

    sudo ufw allow from 15.15.15.0 to any port 3306
    

    With everything added, you'd flip the switch by running:

    sudo ufw enable
    

    It'll ask you to confirm and warn that you could be disconnected, though since we did add port 22, that won't be an issue. Type y, hit enter, and your firewall is now setup.

can you ssh in to your droplet, or use the website terminal and connect to your mysql database from that?

Have another answer? Share your knowledge.