I created a MySql instance the other day, and today I wanted to connect my application server to that database server.
While connecting from my application server I kept getting this error.
Lost connection to MySQL server at ‘reading initial communication packet’, system error: 113
After searching many forum threads, I finally noticed that the issue was with MySql needing a bind address to know who is allowed to connect to the MySql instance.
So I edited /etc/my.cnf and this was the contents
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: # symbolic-links=0 bind-address=220.127.116.110 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Next I needed to restart MySql in order for these changes to take affect.
What I found is this was not the issue. The issue is that the default Firewall on CENTOS is enabled, but does not allow for port 3306 to be accessed outside of localhost.
I was able to fix this by modifying the file /etc/sysconfig/iptables
Just add a line similar to the following to the end of iptables
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT -A RH-Firewall-1-INPUT -j REJECT --reject
Then I restarted the firewall. run the command “service iptables restart” and you should be good to go. Must be root.
That takes care of the firewall, but now I need to tell MySql to allow the user to connect from a different machine.
I was getting this error
Could not connect to the server 'data.baselogic.com' Host '18.104.22.1680' is not allowed to connect to this MySQL server
And this tells me that MySql itself is not allowing the connection to be made.
mysql -u root -p
Then Issuing the grant:
mysql> GRANT ALL ON fooDatabase.* TO fooUser@'22.214.171.124' IDENTIFIED BY 'my_password';
Then I was able to connect
# mysql -u fooUser -p -h 126.96.36.199 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> _
Seemed to be quite a bit of trouble for an error