Accessing a mysql database from external host/ip? (ie: mysql workbench)

Question

I have a mysql server running on x.x.x.x, and can access it internally no problem (of course). However when attempting to connect externally, ie using mysql workbench, or even from an external server, I get the error message "Host 'bla.bla.bla' is not allowed to connect to this MySQL server".

I have done:

• GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY "somepass";
• And I have opened port 3306 in iptables.

Is there another fundamental security issue I am missing?

Answer 1

You need to do

GRANT ALL PRIVILEGES ON *.* TO mysql@'bla.bla.bla' ...

The part after the @ is the host from which the connection is coming, so you have allowed only connections coming from localhost. You need to allow access from each remote host necessary (or all hosts - ... mysql@'%' ... - if applicable).

Answer 2

To solve this you needed to perform the following commands:

mysql -u root -p
[enter in your password]
CREATE USER 'mysqluser'@'%' IDENTIFIED BY 'aC0MPL3XPa33W0RD';
GRANT ALL PRIVILEGES ON *.* TO 'mysqluser'@'%' WITH GRANT OPTION;

Answer 3

I had the exactly similar situation.my MYSQL is installed on a centOS. The path to Nirvana is as below.

1. bind-address: DID NOT WORK
2. grant permission: DID NOT WORK
3. Iptables when turned off: DID work.

SOLUTION:I went fishing into the iptables and made following changes:

1. Access the iptables using the command : vim /etc/sysconfig/iptables

2. If you find the below statements COMMENT them out by adding a '#' at the beginning of the line.

   -A INPUT -s 123.123.123.123/32 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

   -A INPUT -j REJECT --reject-with icmp-host-prohibited

   -A OUTPUT -p tcp -m tcp --dport 3306 -j ACCEPT

3. Restart the iptables using the command: service iptables restart

Yep, that worked for me. Hope it is useful to someone.