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
Comments
Submitted by JamieCameron on Tue, 01/08/2013 - 16:57 Comment #1
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.
Jamie,
Nice, didn't know this. Will give that a try which would certainly address the issue.
-Peter
Submitted by JamieCameron on Tue, 01/08/2013 - 21:36 Comment #3
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
Submitted by JamieCameron on Wed, 01/09/2013 - 11:45 Comment #5
What exact error are you getting about the database not being found?
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
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
Jamie,
I've found a "workaround" that perhaps needs to be addressed for a more "permanent" fix. Here's what I did.
"Virtualmin > Edit Databases > Remote hosts", I set the remote host to the "IP Address" of "web3".
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
Submitted by JamieCameron on Wed, 01/09/2013 - 16:54 Comment #9
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/configon your system, thehost=line is set to the IP or hostname of the MySQL server?Jamie,
host=<IP Address>*** I had switched it from
HostnametoIP Addressin the MySQL configuration screen while troubleshooting the matter earlier.-Peter
Submitted by JamieCameron on Wed, 01/09/2013 - 17:06 Comment #11
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.
Jamie,
Excellent!
Any reason why "workaround #1" was required?
-Peter
Submitted by JamieCameron on Wed, 01/09/2013 - 17:21 Comment #13
I'm not sure about that .. is it still needed if you create a new domain?
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
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
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
Submitted by JamieCameron on Wed, 01/09/2013 - 18:39 Comment #17
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?
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
Submitted by JamieCameron on Wed, 01/09/2013 - 19:16 Comment #19
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.
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
Jamie,
Argh, my suspicion was correct.
When I changed the "Remote hosts" to:
177-242-75-184.web3.tpnservers.comIt 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
Jamie,
LoL!!! Eureka!
I just remembered about
Allow MySQL client hostsunderServer Settings > Server Templates > Default Settings > MySQL database.Setting this option solves all problems!
Now the default
Remote hostsreads:web3.tpnservers.comand184.75.242.177Woo hoo!
I'm jumpin' for joy! Heh Heh.
Thanks for the brainstorming session and for solving the phpMyAdmin typo :-)
-Peter
Submitted by JamieCameron on Wed, 01/09/2013 - 21:17 Comment #23
Great!
Submitted by Issues on Wed, 01/23/2013 - 21:18 Comment #24
Automatically closed -- issue fixed for 2 weeks with no activity.