Multiserver How to point phpMyAdmin on Web server to look at Db Server

Discussion in 'Installation/Configuration' started by cjsdfw, Apr 12, 2020.

  1. cjsdfw

    cjsdfw Member

    Hi guys,
    I configured three servers in a multiserver set up: Web/Mail/DB. The only server that has Apache configure is the Web server hence the only server with phpMyAdmin code is the Web server.
    Everything seems to be working fine but I will like to change the behaviour of phpMyAdmin in web server to work with the DB server databases instead of the Web server databases.
    From what I have been able to gather, I need to make a change in the phpMyAdmin configuration file:
    Code:
     
    To use phpMyAdmin to administer a MySQL database hosted on another server, adjust the following in /etc/phpmyadmin/config.inc.php:
    $cfg['Servers'][$i]['host'] = 'db_server';
    
    However I thinks I need more than just change this setting and would appreciate some guidance if possible.
    When I change '$db_server' for the URL of my DB Server, I get the following error messages:
    Code:
    mysqli_real_connect(): (HY000/1130): Host 'web.xxxx.com' is not allowed to connect to this MySQL server
    Connection for controluser as defined in your configuration failed.
    mysqli_real_connect(): (HY000/1130): Host 'web.xxxx.com' is not allowed to connect to this MySQL server
    
    I would like whatever changes I make to work for any authorized DB user in my DB server.
    Can you tell me what other changes I need to make in the Web or DB server for this to work?
    Thanks in advance
     
  2. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    Does ISPConfig support this configuration? Instructions in ISPConfig Manual for multiserver setup do not install PHPMyadmin on db server. Does the database replication work from db server to master server?
     
  3. OptimBro

    OptimBro Member

    Btw, I suggest the alternative idea to connect to remote MySQL servers. I use https://www.adminer.org/ (just all master server IP to connect to your other DB servers) and replace MySQL web config with adminer.
     
  4. Steini86

    Steini86 Active Member

    The user which you use to login on the remote mysql server does not allow connections from that host.
    You have to change the "grant" to your host (or all -> easy but unsafe)

    Allow IP 123.123.123.123
    Code:
    mysql -u root -p
    Enter password: <enter password>
    mysql>GRANT ALL ON *.* to [email protected]'123.123.123.123' IDENTIFIED BY 'put-your-password';
    mysql>FLUSH PRIVILEGES;
    mysql>exit
    Allow all IPs:
    Code:
    mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;
    mysql> FLUSH PRIVILEGES;
     
    cjsdfw likes this.
  5. cjsdfw

    cjsdfw Member

    Thanks to everyone for taking from your valuable time to help us in the forum, you are really awesome.
    Here is what I ended up doing after searching through the forums a little more intensively and mixing ideas found:

    1) In Web server: I updated the database definitions in ISPConfig to include an entry for "Remote Access IPs" pointed
    to the private network IP of my Web Server (which runs ISPConfig control panel) and 127.0.1.1

    2) In DB Server: I updated the MySQL to allow the phpmyadmin user to manage the databases:

    Code:
    CREATE USER 'phpmyadmin'@'web.server.private.ip' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON * . * TO 'phpmyadmin'@''web.server.private.ip'' IDENTIFIED BY 'password' WITH  GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
    
    CREATE USER 'phpmyadmin'@'web.domain.com' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON * . * TO 'root'@'web.domain.com' IDENTIFIED BY 'password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0  MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
    
    FLUSH PRIVILEGES;
    
    3)In Web Server: I created a new file in /etc/phpmyadmin/conf.d named multiserver.phpmyadmin
    Code:
    nano /etc/phpmyadmin/conf.d/multiserver.phpmyadmin
    
    <?php
    $cfg['Servers'][$i]['host'] = 'db.server.private.ip;

    And that is it. Now when I click on the db management button on the ISPConfig it opens phpMyAdmin with
    a drop down button for selection of the server.

    I will also look at OptimBro suggestion on Admirer.

    I figure I post what I did in case someone else looks for this situation and can benefit.
    Again thanks to everyone.
     
    ahrasis and Steini86 like this.
  6. cjsdfw

    cjsdfw Member

    I also wanted to answer Taleman's questions, you have always been very helpful to me and others and I don't like to leave questions posed in the air:
    You are right instructions do not install phpmyadmin or Apache on DB server.
    I wanted to keep the DB and Mail servers as lean as possible so I did not install Apache on those servers.
    It makes it a little more complex to configure but I guess it does support the configuration, it is just not that clear for rookies like me what other tweaks are needed.
    I did buy the ISPConfig manual but that did not make it any clearer.
    But evidently it is supported.
    Not sure about the question but I can create databases in the DB server without any issue.

    Thanks for always helping. I like your stile of making us think rather than just giving us a quick answer. "Teach me to fish and I will always eat"!
     
    ahrasis and Steini86 like this.

Share This Page