Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > ISPConfig 3 > Installation/Configuration

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 15th January 2014, 11:50
ItsDom ItsDom is offline
Member
 
Join Date: Dec 2012
Posts: 41
Thanks: 2
Thanked 5 Times in 5 Posts
Default Default database user host

Hi.

tldr default allowed hosts for new db users is hard coded as "localhost" which isn't ideal if you used a remote database server during install. Here's how you can change that.


I've just done a new install of ISPConfig, using a remote DB server for IPConfig db and all client db's. To do this I simply entered the remote servers details when prompted for database credentials at the start of the ISPConfig install.

I had some minor issues, I had to recreate the ispconfig database user as it had an incorrect value in the "Host" field of the mysql.user table (although I think this is probably more down to my setup and DNS configurations)

However, the biggest issue I've found that wasn't a feature of my own setup is that the default host value for new db users is hard coded in as "localhost". When a new database user is created on the remote db server, it can't be used by ISPConfig because the ISPConfig server isn't "localhost" to the dbserver.
This can be changed in each instance by specifying ISPConfig server details in the "remote access" settings when creating the database but this isn't ideal for new clients.

To change this, I first defined $clientdb_default_user_host to be the default value in the "Hosts" field for new mysql users in /usr/local/ispconfig/server/lib/mysql_clientdb.conf

Next I had to make some small changes in /usr/local/ispconfig/server/plugins-enabled/mysql_clientdb_plugin.inc.php:

Find lines 211 and Lines 261:
PHP Code:
$host_list .= 'localhost'
And also line 270 and 466:
PHP Code:
$old_host_list .= 'localhost'
change 'localhost' to $clientdb_default_user_host

Now when new db users are created, their default host value will be the value of $mysqldb_default_user_host instead of 'localhost'

Does anyone know of any reason why I should NOT do it this way (e.g. does this conflict with any other code)? It seems the easiest and most intuitive way of creating a setup where all databases (including ISPC master) are on a remote db server.


It might be worth considering adding a check during the install, where if the database server entered during install isn't localhost (or 127.0.0.1 etc) then prompt the user for a default user host value, or add a setting somewhere in the interface for it.
Alternatively, if the root credentials provided allow a login, simply login with root, do "SELECT USER()" and parse the correct hostname from that.

Although this maybe more trouble than it's worth for the amount of people it will effect, and also will vary across DB types.
Reply With Quote
Sponsored Links
  #2  
Old 15th January 2014, 21:31
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 37,015
Thanks: 840
Thanked 5,651 Times in 4,461 Posts
Default

Your setup is not supported by ispconnfig s every ispconfig server must have a local mysql server instance and every remote database server in a ispconfig setup must have ispconfig installed. Please see multiserver setup instructions for details to install with one or more external database servers. If you follow the installation instructions, then you will see that ispconfig enters the correct permisions for new databases as well, independantly of the server thats hosts the website.

Thanks for point out that the installer allowed this invalid setup, I will add some code to the installer to prevent that external hosts can be entered.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #3  
Old 19th January 2014, 14:21
ItsDom ItsDom is offline
Member
 
Join Date: Dec 2012
Posts: 41
Thanks: 2
Thanked 5 Times in 5 Posts
Default

Quote:
Originally Posted by till View Post
Your setup is not supported by ispconnfig s every ispconfig server must have a local mysql server instance and every remote database server in a ispconfig setup must have ispconfig installed. Please see multiserver setup instructions for details to install with one or more external database servers. If you follow the installation instructions, then you will see that ispconfig enters the correct permisions for new databases as well, independantly of the server thats hosts the website.

Thanks for point out that the installer allowed this invalid setup, I will add some code to the installer to prevent that external hosts can be entered.
Hi Till.

Could you explain why you made this decision?

For my setup, which is fairly typical (1 web server, 1 scalable and already existing/in use database server - i.e. there will never be more than 1 database server) the multiserver setup is overkill, overly complex and inefficient.

Following the multi user setup would require me to install php, mysql and ISPconfig on the database server, as well as mysql on the main web server.

This wastes resources on the database server (php, ispconfig) and also adds more things to be exploited and to maintain (php, ispconfig.) It also adds things I don't need on the Web server (mysql-server) both consuming resources and opening up more things to be exploited on the web server.

Doing it the way I described avoids these issues and is also considerably quicker and easier to setup if you're working with an existing database server (since you don't need to install extra things on the server.)

I can't help but feel you're treating this as a problem, when it's a brilliant opportunity to add a new feature (e.g. making it a lot easier, safer, intuitive and more efficient to setup up a simple 1 database server setup.) I understand fully why it's not "officially supported" (makes it harder for you to remotely debug custom setups) but going as far as adding in restrictions so a user can't do something I feel seems to go against some of the ideals of open source software.

Also, regarding ISPConfig requiring a local mysql-server install, I have disabled mysql-server on my main server, and everything runs fine, even things that use mysql (such as dovecot) were already configured by ispconfig installer to use remote database. Only issue is ISPConfig control panel sees mysql-server not running as a problem (even though it isn't a problem on my setup.)
Reply With Quote
  #4  
Old 19th January 2014, 14:32
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 37,015
Thanks: 840
Thanked 5,651 Times in 4,461 Posts
Default

Your right. I guess I had a bad day on jan 15.

I've added your post as feature request to the bugtracker.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #5  
Old 26th November 2014, 17:09
MaDJiK MaDJiK is offline
Junior Member
 
Join Date: Sep 2013
Posts: 19
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Hello,

This is an old topic, but I don't find more recent. I also find a very very old request in the Bugtracker http://bugtracker.ispconfig.org/inde...ls&task_id=860
Is this still the case?


I want to separate Apache/PHP and MySQL, and of course not running at all MySQL on the web server will be better.


And can these main database settings be modified after install or should I uninstall/install?

Last edited by MaDJiK; 26th November 2014 at 17:13.
Reply With Quote
  #6  
Old 26th November 2014, 17:19
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 37,015
Thanks: 840
Thanked 5,651 Times in 4,461 Posts
Default

Quote:
I want to separate Apache/PHP and MySQL, and of course not running at all MySQL on the web server will be better.
This is described in the default multiserver setup:

http://www.howtoforge.com/multiserve...th-ispconfig-3

the mysql that is required as fast config cache in ispconfig only and not for the website databases. Without the caching instance on localhost, your syste will be slower, is less scalable and will loose all its fault tolerance features.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #7  
Old 26th November 2014, 17:56
MaDJiK MaDJiK is offline
Junior Member
 
Join Date: Sep 2013
Posts: 19
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Quote:
Originally Posted by till View Post
Your right. I guess I had a bad day on jan 15.

I've added your post as feature request to the bugtracker.

You say that!

It seems that ItsDom just modify some hard coded "localhost" to make it works.

I can't see why my system should be slower, or loose any feature. This will be the same DB, just running on another server! My sites are a lot faster than with a single server.
As ItsDom said, all programs can connect as well on another host.
Reply With Quote
  #8  
Old 26th November 2014, 18:05
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 37,015
Thanks: 840
Thanked 5,651 Times in 4,461 Posts
Default

The multiserver setup uses a external database server for the website databases, please read the tutorial.

Removing the fast config cache (and thats what ItsDom did by forcing the dbispconfig database to be on a external server instead of using a separate mysql instance as described in the multiserver guide) will make ispconfig and all services like postfix, amavis, dovecot and pure-ftpd to the external database which will slow your system down and remove the fault tolerance as well. But you seem to know the ispconfig system better then the person who wrote it, so do whatever you want but please dont complain later whenyour complete multiserver setup breaks down or is slow.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #9  
Old 26th November 2014, 18:18
MaDJiK MaDJiK is offline
Junior Member
 
Join Date: Sep 2013
Posts: 19
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Quote:
Originally Posted by till View Post
will make ispconfig and all services like postfix, amavis, dovecot and pure-ftpd to the external database which will slow your system down
OK, I can't say for ISPConfig, but you're totally wrong the services.
I can't say better than ItsDom 2d message.


Quote:
and remove the fault tolerance as well.
Reply With Quote
  #10  
Old 26th November 2014, 18:40
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 37,015
Thanks: 840
Thanked 5,651 Times in 4,461 Posts
 
Default

Maybe you are talking abot tiny setups that neither needs fault tolerance nor have to scale? E.g. one of my customers has a huge 6x ispconfig mail cluster. Each node has between 1 and 10 thousand simultanious connections to its local mysql caching instance from postfix, dovecot, amavisd). The setup is made in a way so that it can scale to 100 or more servers. If one of the nodes is put down for maintenance, no other node is affected. If you take a look the the best practice setup that I linked above, the client databases are on the db server and the config caching instance is available on each server, so the setup can scale much better as the mysql connections from postfix go only to the local mysql instance of the mailserver. Only the website databases connect to the external DB server, if this db server fails, the mail system is not affected.

With your setup:

The central database would have to handle between 6 and 60 thousand simultanios connections, in a 100 node cluster we are talking about 100 thousand to 1 million simultanious mysql connections. From MySQL manual "Linux or Solaris should be able to support at 500 to 1000 simultaneous connections routinely and as many as 10,000 connections if you have many gigabytes of RAM available and the workload from each is low or the response time target undemanding." So you will get a real problem with your setup as your central DB wont be able to handle that many connections. And what if the system scales even more? No way with your setup. Next, what happens in your setup if the central database server fails, all nodes an services are down. In my setup, nothing happens for users that are connected to the other 5 nodes.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Postfix - Sender address rejected: not owned by user pdxvwerik Installation/Configuration 16 9th January 2014 02:28
Getting Email Working ISPConfig3 Squirrelmail and Courier etc Ian Wilson Installation/Configuration 17 19th June 2013 23:58
Postfix not working after update SpeedyB ISPConfig 3 Priority Support 4 14th May 2013 17:11
Intel 82815 Video Card & Ubuntu 8.10 harvey527 Kernel Questions 8 5th November 2010 15:51
Email Could not send and receive piseth Installation/Configuration 16 17th July 2010 19:27


All times are GMT +2. The time now is 13:42.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.