Accessing a mysql database from external host/ip?

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 mysql@x.x.x.x IDENTIFIED BY "somepass";
• And I have opened port 3306 in iptables.

Is there another fundamental security issue I am missing?

Answer 1

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 2

I dont know the ins and outs of security behind this bind-addressthing, just learning by installing a debian server on a virtual-box machine. This guest has a virtual network card set up as a bridge, so the rest of the house can see it. Its IP is 192.168.1.4. From another computer (192.168.1.3), connection failed with bind-address = 127.0.0.1. Set up bind-address = 192.168.1.4 works fine. (its own address, litterally) It must be the interpretation of 127.0.0.1 inside a virtual config, not sure...

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.

Answer 4

Comment out the line:

bind-address = localhost
#bind-address = localhost < this is what it should look like.

in your MySQL my.conf file. It is normally located in /etc/mysql/my.conf.

Answer 5

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 6

When I Got my server,even I had the same problem accessing the mysql from MySQL client application, Then I granted the Mysql permission, with following query.

it worked Great

**GRANT ALL PRIVILEGES ON db_base.* TO db_user @'%' IDENTIFIED BY 'db_passwd';** 

db_base is the database Name
db_user is database User
db_passwd is the database password 

Once you execute this flush it, by the following command FLUSH PRIVILEGES;

Suppose if you are looking to give privileges to access certain tables in the Database you can use the following command

GRANT ALL PRIVILEGES ON db_base.HELLOWORLD TO db_user @'%' IDENTIFIED BY 'db_passwd';

Where HELLOWORLD is the table Name