Dedicated database server, can't log into phpmyadmin with new users

Discussion in 'General' started by dontbanme, Jan 24, 2016.

  1. dontbanme

    dontbanme New Member

    First time poster.
    This is the first time using a multiserver setup. I am using the following servers right now. Will be adding a seconds dns server off site.

    1) web.domain.com
    2)ns1.domain.com
    3)db.domain.com

    I have added a new database user (c2_mybbadm), and database (c2_forums) through the panel. The action shows up in the job queue and then appears to be completed as it is no longer listed in the job queue for the server.

    When I attempt to log into phpmyadmin using the newly created user I cannot. Connecting at forum.domain.com/phpmyadmin, which is the site under which the database was created for. I can connect with the root user and password but I do not see the new database or user listed.

    I can ssh to the server and execute the sql command
    Code:
     select User,Host from mysql.user;
    which returns
    Code:
    +------------------+--------------------+
    | User                | Host               |
    +------------------+--------------------+
    | root                 | 127.0.0.1          |
    | c2_mybbadm    | xxx.xxx.xxx.xxx      |
    | root                 | db.domain.com |
    | c2_mybbadm       | localhost          |
    | debian-sys-maint | localhost          |
    | ispconfig             | localhost          |
    | root                   | localhost          |
    +------------------+--------------------+
    7 rows in set (0.00 sec)
    
    If I run
    Code:
    show databases;
    it returns
    Code:
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | c2_forums          |
    | dbispconfig        |
    | mysql              |
    +--------------------+
    4 rows in set (0.00 sec)
    
    So it appears that the database and user were created on the database server. Am I supposed to be able to modify the database through phpmyadmin? Have i done something wrong in the setup. Everything else seems to work.
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Ensure that you add the ip address of the server where you installed phpmyadmin to the allowed IP addresses of this database in ispconfig . Thats only nescessary if phpmyadmin is not installed on the web server and not on the db server.
     
  3. dontbanme

    dontbanme New Member

    Is that the remote access IP section of the database settings in ISP COnfig?
    Sites -> Databases -> Remote Access IPs (separate by , and leave blank for any)?
    If so the IP for the web.domain.com server is listed in there (which has phpmyadmin installed.) I did not install phpmyadmin on the db.domain.com server.
    Or do I need to add rules like I did for the master server adding users for the machines to the Mysql server?
    Code:
    CREATE USER 'root'@'192.168.0.106' IDENTIFIED BY 'myrootpassword';
    GRANT ALL PRIVILEGES ON * . * TO 'root'@'192.168.0.106' IDENTIFIED BY 'myrootpassword' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
    Also I followed this Perfect server guide, and had no problems during the installation. https://www.howtoforge.com/multiser...se-servers-on-debian-squeeze-with-ispconfig-3
    The Ispconfig 3 Installation had no problems on any of the machines during this step
    Code:
    Shall this server join an existing ISPConfig multiserver setup (y,n) [n]:
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    ok.

    No.

    Check that the mysql database server is listening on its external interface (check my.cnf file) and that you do not block the mysql port with a firewall
     
  5. dontbanme

    dontbanme New Member

    I did comment out the bind = 127.0.0.1 string in the my.cnf (did this for all machines).
    And did add the default ports to each machines firewall in ISPconfig panel.

    As an aside, will it cause problems with ispconfig to have multiple NICs. I have two in each machine, one public facing static IP, and the other local static IP. If I have this setup, can I use the local IP in the hosts file for each machine or should I use the public. I was hoping to obfuscate a little by having my DB server not directly public facing. This may be causing some of the issues? And maybe it is totally the wrong thing to do and makes no sense. However everything else seems to work as the data is showing up on the db server and the install worked fine with no hiccups.

    As an example here are what the hosts files look like atm (the db.domain.com server does have a public nic that is only enabled for updates)
    Code:
    web.domain.com -
    127.0.0.1       localhost
    xxx.xxx.xxx.xxx   web.domain.com     web (public IP)
    xx.xx.xx.xx   ns1.domain.com     ns1  (local IP)
    xx.xx.xx.xx  db.domain.com    db   (local IP)
    
    
    # The following lines are desirable for IPv6 capable hosts
    ::1     ip6-localhost ip6-loopback
    fe00::0 ip6-localnet
    ff00::0 ip6-mcastprefix
    ff02::1 ip6-allnodes
    ff02::2 ip6-allrouters
    
    db.domain.com -
    127.0.0.1       localhost
    xx.xx.xx.xx   db.domain.com    db (Local IP)
    xx.xx.xx.xx   web.domain.com    web (Local IP)
    xx.xx.xx.xx   ns1.domain.com     ns1 (Local IP)
    
    
    # The following lines are desirable for IPv6 capable hosts
    ::1     ip6-localhost ip6-loopback
    fe00::0 ip6-localnet
    ff00::0 ip6-mcastprefix
    ff02::1 ip6-allnodes
    ff02::2 ip6-allrouters
    
    
    ns1.domain.com -
    127.0.0.1       localhost
    xxx.xxx.xxx.xxx   ns1.domain.com     ns1 (Public IP)
    xx.xx.xx.xx   web.domain.com     web (Local IP)
    xx.xx.xx.xx   db.domain.com    db (Local IP)
    
    
    # The following lines are desirable for IPv6 capable hosts
    ::1     ip6-localhost ip6-loopback
    fe00::0 ip6-localnet
    ff00::0 ip6-mcastprefix
    ff02::1 ip6-allnodes
    ff02::2 ip6-allrouters
    
     
  6. dontbanme

    dontbanme New Member

    I also feel I should note, the database is use-able. I was able to run a forums software install, and it was able to create tables and add data. I just cannot access the stuff through phpmyadmin. So have I have to restore backups through mysql, which doesn't seem to be working...
     
    Last edited: Jan 25, 2016
  7. dontbanme

    dontbanme New Member

    Ok, I dug and dug around for a bit and stumbled across a different post form 2011 of the exact same problem. Don't know how I didn't find this before, but I did search I promise.

    https://www.howtoforge.com/communit...ot-connect-to-mysql-server.51549/#post-252888

    This is not a solution at all as I still cannot access the db server through phpmyadmin, but I was able to connect and successfully restore the database using Adminer (https://www.adminer.org/)

    I was able to throw the Adminer-for-Mysql.php file into the live directory for one of the sites I have hosted and connect to the db server at db.domain.com with the newly created user, password and database. I was then also able to finally restore my database from the phpmyadmin dump file. It was not working through cli at all.

    There must still be some thing wrong in the configs for phpmyadmin. I would like to figure out the problem since phpmyadmin is integrated into the panel.
     

Share This Page