Thursday, February 6, 2014

Open mysql to accept connection other then localhost

My current project have a multi layer architecture in which mysql is running on a machine and all the other machines have to take connection on that.

By default Mysql allow connection to localhost(127.0.0.1) only. So i need to open for others as well. I need to change the configuration file of mysql

we can found that file at /etc/mysql/my.cnf (in ubuntu)

We need to change a property(bind address)

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address        = 127.0.0.1
  bind-address        = 0.0.0.0


Now restart the Mysql service

sudo service mysql restart

It may be possible that after doing all this you might not be able to login from different machine. For make that working you have to reset the root user password by

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;