No connection to mysql database from other subnet

[sBuchi]

Hi

I`ve installed a ISPConfig3 multi server setup with a webserver, a mailserver and a dns-server.
Then i installed a second dns-server in an other remote-subnet.
My problem is, that i can't connect it to my database, which is located on the web server.
When configuring the second dns-server, this question appears and i answer with "yes":

Shall this server join an existing ISPConfig multiserver setup (y,n)

I entered the correct master-server host, user and password, but the answer is always:

Unable to connect to mysql server

Although I'm working as root, i don't have permission to see/modify mysql.user

How can i get the permission to connect to my database?

thx for helping

[till]

1) Check that you opened the mysql port in the firewall on the master server.
2) Ensure that you added root users in the mysql server on the master for all IP addresses of the slave server that shall be used to connect. If you are unsure about the IP addresses, use % instaed of the IP when creating the mysql root user.

[sBuchi]

All Ports are open, the firewall doesn`t block any ports.

I tested to connect to other sql server and i was able to connect to all of them. Some of them are in the same subnet, some are not.
But connecting to the master-server isn`t allowed to any of my servers.
By using "telnet" i always get this message:

C:\User\name> telnet serverx.example.com
Connecting to serverx.example.com...unable to connect to host on port 23: Connection refused.

Could it be that there are other security settings except username and password?
Firewall and adding users in the master server can`t be the problem, I have tested it...

[till]

Could it be that there are other security settings except username and password?

Mysql uses a security concept which takes the IP address in account as I explained above. If you want to test the mysql connection, use the mysql command and for the test you have to run it on the salve and not on the amster or any other server.

So to test the connection, login on the shell of the slave server and issue this command:

mysql -h master.server.tld -u root -p

replace master.server.tld with the hostname of your master server.

Another important thing is that you edited the /etc/hosts files eaxctly as described in the installation instructions on all servers that belong to the clusetr. E.g.

123.123.123.123 sv1.server.tld

is ok while:

123.123.123.123. sv1.server.tld sv1

may cause the mysql connect to fail.

[sBuchi]

Hosts-file from slave-server siteA (host120):

127.0.0.1 localhost
x.x.132.120 host120.siteA.tld
x.x.35.172 node172.siteB.tld
x.x.35.173 node173.siteB.tld
x.x.35.174 node174.siteB.tld
x.x.35.175 node175.siteB.tld

# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

Hosts-file from master-server siteB (node172):


127.0.0.1 localhost
x.x.35.172 node172.siteB.tld
x.x.35.173 node173.siteB.tld
x.x.35.174 node174.siteB.tld
x.x.35.175 node175.siteB.tld
x.x.132.120 host120.siteA.tld

# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

Connecting from slave to master from siteA:

root@host120:~# mysql -h x.x.35.172 -u root -p
ERROR 1130 (HY000): Host 'x.x.132.20' is not allowed to connect to this MySQL server

Connecting from a working slave to master from siteB

root@node173:~# mysql -h node172.siteB.tld -u root -p

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

By trying to reconfigure the master-server, this lines appear:


>> Update

Operating System: Debian 6.0 (Squeeze/Sid) or compatible

This application will update ISPConfig 3 on your server.

Shall the script create a ISPConfig backup in /var/backup/ now? (yes,no) [yes]: no

ERROR 1142 (42000) at line 35: DROP command denied to user 'root'@'localhost' for table 'billing_invoice'
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'dbispconfig' when using LOCK TABLES
Checking ISPConfig database .. OK
Starting incremental database update.
Reconfigure Permissions in master database? (yes,no) [no]: yes

PHP Notice: Undefined variable: value in /etc/ispconfig3_install/install/lib/installer_base.lib.php on line 352
ERROR: Unable to get the user rights: Error: SELECT command denied to user 'root'@'localhost' for table 'user'
root@node172:/etc/ispconfig3_install/install#

[sBuchi]

I forgot to add the rights with the GRANT-command, now i can connect to the master-server without problems
Thank you