How to enable remote access of mysql in centos?

Question

My apache is running on 8113 port instead of 80.

I want to access my mysql Database remotely. I have tried following steps.

Bind-address XXX.XX.XX.XXX in /etc/my.cnf
Create Database 
and run the command 
GRANT ALL PRIVILEGES ON *.* TO  'USERNAME'@'IP'  IDENTIFIED  BY  'PASSWORD';

But not able to connect. I am using heidi sql to connect.

Answer 1

Bind-address XXX.XX.XX.XXX in /etc/my.cnf

comment line:

skip-networking

or

skip-external-locking

after edit hit service mysqld restart

login into mysql and hit this query:

GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'%' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;
quit;

add firewall rule:

iptables -I INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

Answer 2

so do the following edit my.cnf:

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = xxx.xxx.xxx.xxx
# skip-networking

after edit hit service mysqld restart

login into mysql and hit this query:

GRANT ALL ON foo.* TO bar@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'PASSWORD';

thats it make sure your iptables allow connection from 3306 if not put the following:

iptables -A INPUT -i lo -p tcp --dport 3306 -j ACCEPT

iptables -A OUTPUT -p tcp --sport 3306 -j ACCEPT