unable to remotely connect to mysql on ubuntu server

Question

I have an Apache2 ubuntu AWS EC2 server which I just installed mysql on.

However I do not seem to be able to connect remotely to the database via mysql workbench.

I am wondering what step did I miss to be able to remotely access the mysql via mysql workbench

I am getting the below error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

OK so now I am able to connect to the ssh via ssh but still not able to connect via my mysql workbench

In ssh once connected to mysql I ran the below command which is then the credentials I use in my mysql workbench . Of course i have a real password instead of the ***** for the password

CREATE USER 'mysqladmin'@'localhost' IDENTIFIED BY '******'; 

GRANT ALL PRIVILEGES ON . TO 'mysqladmin'@'localhost' WITH GRANT OPTION;

Answer 1

In MySQL, local and remote users are handled as separate accounts. So, in addition to (for local access):

CREATE USER 'mysqladmin'@'localhost' IDENTIFIED BY '******'; 

You also need to do (to allow remote access from anywhere):

CREATE USER 'mysqladmin'@'%' IDENTIFIED BY '******'; 

Or alternately (to allow remote access from only a specific IP address):

CREATE USER 'mysqladmin'@'TheIPAddress' IDENTIFIED BY '******'; 

You will also need to grant permissions to these additional user accounts.

Good luck!

Answer 2

Mysql will accept a remote connection if:

1. the credentials are correct (username+password+hostname) and;
2. if mysql is listening on the ip you're connecting to.

So you need to replace localhost to your ip or the wildcard %, for example:

CREATE USER 'remote'@'1.2.3.4' IDENTIFIED BY 'xxxxxx'; 

Then, you need to tell mysql to 'listen' on the public ip of your server, by opening /etc/mysql/my.cnf and changing the line with bind-address=4.3.2.1. Ensure to use the public IP of your server here and restart mysql afterwards (local connections to localhost or 127.0.0.1 remain operational).

Last but not least, I really advice to implement some sort of firewall, because your mysql is prone to brute force attacks from remote when it is bound to your server's public ip. I personally use iptables to achieve this:

iptables -A INPUT -p tcp --dport 3306 -s 1.2.3.4 -j ACCEPT iptables -A INPUT -p tcp --dport 3306 -j DROP 

The first line accepts connections on port 3306 from your ip, the second blocks all other requests for port 3306.