0

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;

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!

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.