MySQL: allow remote conections

MySQL: allow remote conections

MySQL: allow remote conections 491 250 Roderick Derks

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

Roderick Derks

Liefhebber van fietsen, van het oplossen van IT puzzels, en van het delen van informatie om anderen te helpen.

All stories by:Roderick Derks

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Your Name (required)

Your Email (required)

Subject

Your Message

Your Name (required)

Your Email (required)

Subject

Your Message