Hi,

Recently I started to migrate databases onto their own dedicated server. The design I have allows all "web" nodes to connect to a the delegated "sql" node.

My goal was to install "phpmyadmin" onto the "web" node, and have it manage the "sql" node accordingly, then disable "mysql" on the "web" node.

== THE PROBLEM ==

When installing "phpmyadmin" through the "script install" feature, it forces you to have a database on available on the local server. Because of my design, I don't intend to have mysql running locally, and was wondering if this could become optional in a future release, or if you could specify the hostname for which the script depends on.

I understand why phpmyadmin wants a database setup, and also know the added functionality that this is for is "optional", so I think something could be made available to either alter the default "hostname" or disable the feature if desired upon installation.

*** currently as a work around I have installed phpmyadmin manually, however I enjoy the benefits of the automation, and version tracking ***

-Peter

Status: 
Closed (fixed)

Comments

Have you moved all your databases to the remote system already? If so, you can configure Virtualmin to create and manage databases on a remote system, at Webmin -> Servers -> MySQL Database -> Module Config.

When this is done, a new phpMyAdmin install will be setup to connect to the remote system.

tpnsolutions's picture
Submitted by tpnsolutions on Tue, 01/08/2013 - 18:13

Jamie,

Nice, didn't know this. Will give that a try which would certainly address the issue.

-Peter

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 11:28

Jamie,

I took your advice, and setup MySQL configurations so that it uses the new dedicated sql node.

However, clicking "Install Script" still looks for a database on the local machine instead of checking the remote sql node.

*** note creating actual databases via Virtualmin works correctly and places new database on dedicated sql node. ***

Suggestions?

-Peter

What exact error are you getting about the database not being found?

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 15:37

Jamie,

Very strange, I just re-attempted to install the script, and it appears to be working as expect... LoLz

Either I'm going nuts, or it was an intermittent issue, hmmm... very strange...

What it "was" doing was finding locally hosted databases which were left behind during the migration just in case I needed to revert the settings.

Anyways, all seems good...

I'll close the ticket within the next hour if things continue to look good or update it accordingly.

-Peter

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 15:52

Jamie,

So, phpMyAdmin installed without complaining this time.

Here's the new issue.

phpMyAdmin on "web3.tpnservers.com" cannot connect to "sql3.tpnservers.com" spits back a 2002 error, and fails to login.

I've check the "database permissions" inside MySQL and it's set the host to "web3.tpnservers.com".

I then checked the "config.inc.php" file for phpMyAdmin and noticed that it set the Server variable to "localhost".

Thinking this might fix the problem, I adjusted the variable to read "sql3.tpnservers.com" and still no joy.

I can manage the databases through Virtualmin, but would rather do so via phpMyAdmin.

Suggestions? Solutions?

-Peter

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 16:12

Jamie,

I've found a "workaround" that perhaps needs to be addressed for a more "permanent" fix. Here's what I did.

  1. "Virtualmin > Edit Databases > Remote hosts", I set the remote host to the "IP Address" of "web3".

  2. Inside the "config.inc.php" for phpMyAdmin, I change the value of "$cfg['Servers'][$i]['host']" from "localhost" to "sql3.tpnservers.com".

Once this was done, connecting to that database was possible. However, there is still an issue means manually setting up the "Remote hosts" value for each database, which naturally would become very time consuming and a bit annoying naturally.

-Peter

Needing to make change (2) seems like a bug, as Virtualmin should do this automatically..

Can you confirm that in the file /etc/webmin/mysql/config on your system, the host= line is set to the IP or hostname of the MySQL server?

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 16:58

Jamie,

host=<IP Address>

*** I had switched it from Hostname to IP Address in the MySQL configuration screen while troubleshooting the matter earlier.

-Peter

Ok, I see the bug that is causing this now. I have released a fix, which you can get by going to System Settings -> Script Installers -> Installer Updates and clicking the "Save" button. This will also be included in the next Virtualmin release.

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 17:19

Jamie,

Excellent!

Any reason why "workaround #1" was required?

-Peter

I'm not sure about that .. is it still needed if you create a new domain?

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 17:53

Jamie,

It seems that when MySQL tries to authenticate against the "hostname" it has problems. If I update the "db" table in the "mysql" database to the "ip address" instead, it allows the authentication, and further add the same "ip address" to the "Remote hosts" field, the connection starts working.

*** the former step regarding the "db" table was part of my initial workaround but was missed when I wrote the comment on the topic ***

-Peter

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 18:09

Jamie,

Okay, here's what I've discovered.

== ISSUE ==

A) when you migrate an existing domain over to using the remote MySQL server, the "Remote hosts" defaults to being blank. This in turn causes the default "db" entries to read "localhost", and "web3.tpnservers.com" in the case of my server.

B) when you add a new domain after switching to the remote MySQL server, the "Remote hosts" defaults to having "localhost", and "web3.tpnservers.com" which essentially cause the same issue as above.

== RESOLUTION ==

To resolve this, if you change the value of "Remote hosts" before creating a database, in this case either adding or making the only entry the "IP Address" of "web3.tpnservers.com", when you create a database the "db" table have the entry of all three (localhost, web3.tpnservers.com, 184.75.242.177) or simply the ip address.

This effectively resolves the issue from the MySQL server end.

Going forward, I'd recommend for instance "B" that the default either include all three entries, or at the very least the "ip address". This will resolve so many issues. Perhaps when switching to to a remote MySQL server you could write an update to the "db" table and replace old entries with new ones for all databases on the server.

-Peter

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 18:16

Jamie,

Further to the above, I was able to issue an API call to correct the "remote hosts" for all domains, and poof! It solved all the problems, however as mentioned this is just a quick workaround, and the problem definitely needs to be plugged in a formal patch :-)

-Peter

I surprised that new domains aren't setup correctly actually. Virtualmin should grant access to it's own hostname, which in your case I assume is web3.tpnservers.com. Is it possible that perhaps the MySQL system cannot resolve that hostname to the correct IP address? Is is there maybe a NAT gateway between the two systems which is changing the IP the connection to MySQL appears to come from?

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 18:59

Jamie,

No NAT that I'm aware of, these nodes are in a data center, and however if MySQL is trying to do a reverse lookup of the IP address, that's the only problem that could be happening.

web3.tpnservers.com => 184.75.242.177

184.75.242.177 => 177-242-75-184.web3.tpnservers.com

*** this reverse lookup was by design, as there are multiple IPs which point to the same machine and each are named accordingly in their rDNS accordingly. ***

-Peter

That may explain it. What I will do in the next Virtualmin release is make sure the IP address is also include in the list of hosts to allow..

Till then, you can go to System Settings -> Server Templates -> Default Settings -> MySQL Database, and enter 184.75.242.177 in the "Allowed MySQL client hosts" field.

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 19:17

Jamie,

If what I read is correct, MySQL does a bit of pre-authentication by converting the requesting (client) IP address to hostname then checks against the mysql table to see if this matches.

In my case, "184.75.242.177" would resolve to "177-242-75-184.web3.tpnservers.com", but because the "db" table has "web3.tpnservers.com" it would fail to find a match.

*** correct me if I'm wrong, but if this is correct, the problem is as noted due to the way my rDNS is setup and therefore the ip address in the "db" table fixes this in theory. ***

Uggh, it's been a long day so my brain may be on overdrive, and therefore I may be completely wrong... LoLz

Thoughts?

-Peter

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 19:27

Jamie,

Argh, my suspicion was correct.

When I changed the "Remote hosts" to:

177-242-75-184.web3.tpnservers.com

It worked without issue...

Given this being the case, I believe adding the IP address as at least "one" of the allowed hosts would be appropriate.

Further by adding the IP as at least one of the allowed hosts, a person would be able to add "skip-name-resolve" which can slightly speed up MySQL.

-Peter

tpnsolutions's picture
Submitted by tpnsolutions on Wed, 01/09/2013 - 19:37

Jamie,

LoL!!! Eureka!

I just remembered about Allow MySQL client hosts under Server Settings > Server Templates > Default Settings > MySQL database.

Setting this option solves all problems!

Now the default Remote hosts reads: web3.tpnservers.com and 184.75.242.177

Woo hoo!

I'm jumpin' for joy! Heh Heh.

Thanks for the brainstorming session and for solving the phpMyAdmin typo :-)

-Peter

Automatically closed -- issue fixed for 2 weeks with no activity.