MySQL: allow remote conections

Very simple guide to allow remote connectios to your MySQL database. I use this for monitoring MySQL databases from 360°Viewpoint (a great preconfigured Nagios and Centreon Virtual App).

# vi /etc/my.cnf

  bind-address = ip_address_of_local_server
  # skip-networking

Allow access to all databases for one user:

mysql> GRANT ALL PRIVILEGES ON *.* TO user@ip_address_remote_server IDENTIFIED BY "password";

Allow access to one databases for one user:

mysql> update db set Host='ip_address_remote_server' where Db='database_name';
mysql> update user set Host='ip_address_remote_server' where user='username';

Continue configuration of the Nagios check_mysql_health plugin:

• download the plugin and compile it
• run the plugin using the following the commandline:
  ./check_mysql_health –hostname server_name –username user –password password –mode connection-time
  OK – 0.03 seconds to connect as user | connection_time=0.0333s;1;5

Zimbra (uses port 7306 for MySQL, not the standard port 3306):

as root:
vi /opt/zimbra/conf/my.cnf
 #bind-address = localhost


/opt/zimbra/mysql/bin/mysql -u root -p -S /opt/zimbra/db/mysql.sock
mysql> GRANT ALL PRIVILEGES ON *.* TO user@ip_address_remote_server IDENTIFIED BY "password";
mysql> GRANT ALL ON *.* TO 'zimbra'@'ip_address_of_zimbraserver' WITH GRANT OPTION;


To get the password for user root execute the following commands:
su - zimbra
zmlocalconfig -s | grep mysql | grep password

su - zimbra
zmcontrol restart