Default database user host

Discussion in 'Installation/Configuration' started by ItsDom, Jan 15, 2014.

  1. ItsDom

    ItsDom New Member


    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/

    Find lines 211 and Lines 261:
    $host_list .= 'localhost';
    And also line 270 and 466:
    $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 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.
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    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.
  3. ItsDom

    ItsDom New Member

    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.)
  4. till

    till Super Moderator Staff Member ISPConfig Developer

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

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

    MaDJiK New Member


    This is an old topic, but I don't find more recent. I also find a very very old request in the Bugtracker
    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: Nov 26, 2014
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    This is described in the default multiserver setup:

    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.
  7. MaDJiK

    MaDJiK New Member

    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. :confused: 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.
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    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.
  9. MaDJiK

    MaDJiK New Member

    OK, I can't say for ISPConfig, but you're totally wrong the services.
    I can't say better than ItsDom 2d message.

  10. till

    till Super Moderator Staff Member ISPConfig Developer

    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.
  11. MaDJiK

    MaDJiK New Member

    Yes, of course! Only 2 small servers, no emails, Apache/PHP and MySQL as I said.
    OK, I understand your point of view now, but you must admit this is not the case of most ISPConfig users! ;)
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    But why leave out the option to scale when it has no drawbacks? With the setup I propose, ispconfig uses the local mysql database for its internal purposes and the external DB server is used for the websites.

    But do whatever setup you want, I can tell you only whats best practice after having done many setups on all kind on server sizes.

Share This Page