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

mysql linux centos iptables mysql-workbench
Jon picture Jon · May 6, 2011 · Viewed 61.9k times · Source

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

nobody picture nobody · May 6, 2011

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).

Related questions

Best way to install MySQL 5.6 on Centos 6.4

I just installed Centos 6.4, and installed MySQL using the version that came with the Centos distribution. To my dismay, it is MySQL 5.1.69 versus the current 5.6.12. As stated on http://dev.mysql.com/doc/refman/5.5/en/linux-installation-native.html, "the MySQL version …

Read More
MariaDB 10 CentOS 7 moving datadir woes

Brand new "minimal" install of CentOS 7 along with MariaDB 10. I have an additional mounted mirrored volume that I want to use for the datadir. Startup sequence is fine and completes normally when my.cnf [mysqld] is commented out. I've copied …

Read More
my.cnf file missing and cannot be found at any directory

I recently removed mysql 5.1 an installed mysql 5.6 on my CENTOS 6 x86 OS. When I try to look for the my.cnf file it is missing from these directories: mysqld --verbose --help | grep -A 1 "Default options" = /etc/my.cnf, /etc/mysql/…

Read More