Multiserver - SQL server

Discussion in 'ISPConfig 3 Priority Support' started by LIJE, Apr 13, 2019.

  1. LIJE

    LIJE New Member HowtoForge Supporter

    Hi,

    it was a while ago but I used this post to set up the master server:
    https://www.howtoforge.com/tutorial...-9-stretch-apache-bind-dovecot-ispconfig-3-1/
    At this point, the server wasn't meant to be a multiserver so I followed all steps and not only the first 6.

    Then I used this post to tweak the master server and to set up the SQL slave server:
    https://www.howtoforge.com/multiser...se-servers-on-debian-squeeze-with-ispconfig-3

    As many of you, I'm getting this error:
    ERROR 1130 (HY000): Host 'ns1.ip-xx.eu' is not allowed to connect to this MariaDB server

    /etc/hosts on master server
    127.0.0.1 localhost
    127.0.1.1 ns1.ip-xx.eu ns1
    192.168.0.100 ns2.ip-xx.eu
    192.168.0.101 ns1.ip-xx.eu

    /etc/hosts on slave server
    127.0.0.1 localhost
    127.0.1.1 ns2.ip-xx.eu ns2
    192.168.0.100 ns2.ip-xx.eu
    192.168.0.101 ns1.ip-xx.eu

    ping is working.

    /etc/mysql/mariadb.conf.d/50-server.cnf on master and slave servers
    bind-address = 0.0.0.0

    On the master server, I created root users like so, all with the same password and granted them full privileges:
    'root'@'192.168.0.100'
    'root'@'ns2.ip-xx.eu'
    'root'@'%'

    My issue is at this point. The slave server cannot connect to the master sql server, so I can't set up ispconfig correctly.
    I'm doing: mysql -h ns1.ip-xx.eu -u root -p or mysql -h 192.168.0.101 -u root -p but getting all the time the same message.

    I have a firewall on the master server (I've nothing configured in the firewall of ISPConfig though) but the command netstat -anp | grep 3306 on both servers gives me something similar to:
    tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 6649/mysqld

    Also, something weird, on the master server, when I do mysql -h 192.168.0.100 -u root -p or mysql -h ns2.ip-xx.eu -u root -p, it seems I'm connected to the master sql server and not the slave sql server. Indeed, SELECT User FROM mysql.user; gives me all my sql users created with ispconfig.

    And mysql -h 192.168.0.101 -u root -p gives me:
    ERROR 1130 (HY000): Host 'ns2.ip-xx.eu' is not allowed to connect to this MariaDB server

    I think I might have a problem with IPs. What do you think?
     
    Last edited: Apr 15, 2019
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    did you restart mysql on the master or run 'flush privileges' to apply the changes to activate the root users that you've added?
     
  3. LIJE

    LIJE New Member HowtoForge Supporter

    Exactly. On both servers.
    I flushed the privileges in the PhpMyAdmin console and then restarted the services.

    /etc/init.d/mysql restart

    On the master server why when I do
    mysql -h 192.168.0.100 -u root -p
    and then
    MariaDB [(none)]> SELECT User FROM mysql.user;

    I'm getting the users from the master instead of the slave.
    I shouldn't get the users from the slave? It doesn't explain my current issue?

    Still on the master server, when I do
    mysql -h 192.168.0.101 -u root -p
    I'm getting
    ERROR 1130 (HY000): Host 'ns2.ip-xx.eu' is not allowed to connect to this MariaDB server
     
  4. LIJE

    LIJE New Member HowtoForge Supporter

    I'd like to mention that /etc/hostname on the master contains: ns1
    And on the slave, it contains: ns2
     
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    Try to remove the emove the '127.0.1.1 ' lines from the hosts files. Then the test has to be performed on the slave, login to the slave and use the mysql command to connect to the Ip and hostname of the master from the slave.
     
  6. LIJE

    LIJE New Member HowtoForge Supporter

    I guess if I do that on the master, all websites using "localhost" as SQL host will break.

    Weird thing, I managed to log in from slave to 192.168.0.100 and to list all users.

    I continued the multistore configuration process but it didn't detect anything:

    Code:
    [WARN] autodetect for Apache failed
    Force configure Apache (y,n) [n]:
    
    Skipping Apache
    
    [WARN] autodetect for nginx failed
    Force configure nginx (y,n) [n]:
    
    Skipping nginx
    
    Adding ISPConfig server record to database.
    
    Configure Mail (y,n) [y]: n
    
    [WARN] autodetect for Jailkit failed
    Force configure Jailkit (y,n) [n]:
    
    Skipping Jailkit
    
    [WARN] autodetect for pureftpd failed
    Force configure pureftpd (y,n) [n]:
    
    Skipping pureftpd
    
    Configure DNS Server (y,n) [y]:
    
    Configuring BIND
    [INFO] haveged not detected - DNSSEC can fail
    The Web Server option has to be enabled when you want run a web server or when this node shall host the ISPConfig interface.
    Configure Web Server (y,n) [y]:
    
    [WARN] autodetect for OpenVZ failed
    Force configure OpenVZ (y,n) [n]:
    
    Skipping OpenVZ
    
    Configure Firewall Server (y,n) [y]:
    
    Configuring Bastille Firewall
    [WARN] autodetect for Metronome XMPP Server failed
    Force configure Metronome XMPP Server (y,n) [n]:
    
    Skipping Metronome XMPP Server
    
    Configuring Fail2ban
    Install ISPConfig Web Interface (y,n) [n]:
    
    I checked the databases and noticed the new ispconfig database. The new sever shows up in the ispconfig dashboard of the master.
    I went in the system tab and unchecked "dns" from the SQL slave server. This task has been stuck forever.
    I got 14 tasks in the queue now.
     
  7. till

    till Super Moderator Staff Member ISPConfig Developer

    No, you mix up 127.0.1.1 and 127.0.0.1. Localhost is 127.0.0.1 and I asked you to remove 127.0.1.1.
     
  8. LIJE

    LIJE New Member HowtoForge Supporter

    Done on both servers.

    I don't see anything special on the system logs for my issue.
     
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    Try to connect from slave to master with the mysql command by using IP of the master and then try using the hostname, the exact ispcsrv* username and password for the ispcsrv user can be found in the ispconfig config.inc.php on the slave. The setup will not start working until a database connection is possible from slave to master.
     
  10. LIJE

    LIJE New Member HowtoForge Supporter

    #private master IP
    mysql -h 192.168.0.101 -u ispconfig -p
    ERROR 1130 (HY000): Host 'ns1.ip-xx.eu' is not allowed to connect to this MariaDB server

    #private slave IP
    mysql -h 192.168.0.100 -u ispconfig -p
    ERROR 1045 (28000): Access denied for user 'ispconfig'@'ns1.ip-xx.eu' (using password: YES)

    # slave localhost
    mysql -u ispconfig -p
    WORKING

    #public master IP
    mysql -h xx.xx.xx.xx -u ispconfig -p
    ERROR 1045 (28000): Access denied for user 'ispconfig'@'xx.xx.xx.xx' (using password: YES)

    # public slave IP
    mysql -h xx.xx.xx.xx -u ispconfig -p
    ERROR 1130 (HY000): Host 'ns2.ip-xx.eu' is not allowed to connect to this MariaDB server

    If I try to log in the master with the slave ISPconfig credentials, it won't work. I guess that's ok because the master has not the same credentials than the slave.
     
    Last edited: Apr 16, 2019
  11. till

    till Super Moderator Staff Member ISPConfig Developer

    I asked you to test with the ispcsrv* user, you used the ispconfig user, so your test must fail,the ispconfig user is not used for the master login from slave so it can't work when you use the wrong username. Please redo your test and use the ispcsrv* user from ispconfig config.inc.php file with the password from that file.
     
  12. LIJE

    LIJE New Member HowtoForge Supporter

    Alright, sorry.

    #private slave IP
    mysql -h 192.168.0.100 -u ispcsrv2 -p
    ERROR 1045 (28000): Access denied for user 'ispcsrv2'@'ns1.ip-00.eu' (using password: YES)

    # private master IP
    mysql -h 192.168.0.101 -u ispcsrv2 -p
    ERROR 1130 (HY000): Host 'ns2.ip-xx.eu' is not allowed to connect to this MariaDB server

    # localhost on slave
    mysql -u ispcsrv2 -p
    ERROR 1698 (28000): Access denied for user 'ispcsrv2'@'localhost'

    # localhost on master
    mysql -u ispcsrv2 -p
    ERROR 1045 (28000): Access denied for user 'ispcsrv2'@'localhost' (using password: YES)

    # public master IP
    mysql -h xx.xx.xx.xx -u ispcsrv2 -p
    ERROR 1045 (28000): Access denied for user 'ispcsrv2'@'yy.yy.yy.yy' (using password: YES)
     
    Last edited: Apr 17, 2019
  13. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    Have you followed the instructions in ISPConfig Manual on how to set up database users for the hosts in the multiserver setup?
     
  14. LIJE

    LIJE New Member HowtoForge Supporter

    If you are writing about creating users and databases from the ISPConfig panel for the slave server, yes. You don't need much documentation about that. There is a dropdown menu which allow to select the server on which on want to create the database.

    If you are referring to the end of this post: https://www.howtoforge.com/multiser...se-servers-on-debian-squeeze-with-ispconfig-3
    When I must add root users to the master SQL server, yes, I did it.

    If you are referring to something else, please let me know.

    Since ispcsrv2 cannot log in. Maybe the configuration has not been able to add the slave ispcsrv2 user to the master SQL users?
     
  15. till

    till Super Moderator Staff Member ISPConfig Developer

    Please check if the ispcsrv2 user exists in the mysql database on the master with phpmyadmin and check that ixists twice, with correct IP address and with the correct hostname. In case you need to edit that user, use the user editor in phpmyadmin and don't edit the user directly in mysql.user database as it consists of many sub records, the phpmyadmin user editor will edit it correctly, that's why you better use it.
     
  16. LIJE

    LIJE New Member HowtoForge Supporter

    When I look at the mysql.user table, I've these records:
    Code:
    ('127.0.1.1', 'ispcsrv2', '*xxx', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', '', 'N', 'N', '', '0.000000'),
    ('ns2.ip-xx.eu', 'ispcsrv2', '*xxx', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', '', 'N', 'N', '', '0.000000');
    There is no record with the private slave IP. Using the user editor, I got this:
    upload_2019-4-17_16-27-53.png
    Should I edit the 127.0.1.1 and replace this IP with the private slave IP (192.168.0.100) ?
     
  17. till

    till Super Moderator Staff Member ISPConfig Developer

    Yes, please do that. the wrong IP there is caused by the wrong hosts file at the time you installed ISPConfig. But use the phpmyadmin user editor to change the IP, don't change it in the mysql.user table directly.
     
  18. LIJE

    LIJE New Member HowtoForge Supporter

    Yes, it did a bunch of queries as I see.

    upload_2019-4-17_16-46-40.png

    Flushed privileges, restart the sql service.
    I went back to my slave to test again. Here are the results.

    #private slave IP
    mysql -h 192.168.0.100 -u ispcsrv2 -p
    ERROR 1045 (28000): Access denied for user 'ispcsrv2'@'ns1.ip-00.eu' (using password: YES)

    # private master IP
    mysql -h 192.168.0.101 -u ispcsrv2 -p
    ERROR 1130 (HY000): Host 'ns2.ip-xx.eu' is not allowed to connect to this MariaDB server

    # localhost on slave
    mysql -u ispcsrv2 -p
    ERROR 1698 (28000): Access denied for user 'ispcsrv2'@'localhost'

    # localhost on master
    mysql -u ispcsrv2 -p
    ERROR 1045 (28000): Access denied for user 'ispcsrv2'@'localhost' (using password: YES)

    # public master IP
    mysql -h xx.xx.xx.xx -u ispcsrv2 -p
    ERROR 1045 (28000): Access denied for user 'ispcsrv2'@'yy.yy.yy.yy' (using password: YES)
     
  19. till

    till Super Moderator Staff Member ISPConfig Developer

    Run an ispconfig update on the slave server like this:

    Code:
    cd /tmp
    wget http://www.ispconfig.org/downloads/ISPConfig-3.1.13p1.tar.gz
    tar xvfz ISPConfig-3.1.13p1.tar.gz
    cd ispconfig3_install/install
    php -q update.php
    and choose to update permissions in master database when the updater asks.
     
  20. LIJE

    LIJE New Member HowtoForge Supporter

    Code:
    Checking ISPConfig database .. OK
    Starting incremental database update.
    Loading SQL patch file: /tmp/ispconfig3_install/install/sql/incremental/upd_dev_collection.sql
    Reconfigure Permissions in master database? (yes,no) [no]: yes
    [...]
    Updating Crontab
    Restarting services ...
    Failed to restart apache2.service: Unit apache2.service not found.
    Update finished.
    #private slave IP
    mysql -h 192.168.0.100 -u ispcsrv2 -p
    ERROR 1045 (28000): Access denied for user 'ispcsrv2'@'ns1.ip-00.eu' (using password: YES)

    # private master IP
    mysql -h 192.168.0.101 -u ispcsrv2 -p
    ERROR 1130 (HY000): Host 'ns2.ip-xx.eu' is not allowed to connect to this MariaDB server

    # localhost on slave
    mysql -u ispcsrv2 -p
    ERROR 1698 (28000): Access denied for user 'ispcsrv2'@'localhost'

    # public master IP
    mysql -h xx.xx.xx.xx -u ispcsrv2 -p
    WORKING!
     

Share This Page