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?
did you restart mysql on the master or run 'flush privileges' to apply the changes to activate the root users that you've added?
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
I'd like to mention that /etc/hostname on the master contains: ns1 And on the slave, it contains: ns2
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.
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.
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.
#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.
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.
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)
Have you followed the instructions in ISPConfig Manual on how to set up database users for the hosts in the multiserver setup?
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?
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.
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: Should I edit the 127.0.1.1 and replace this IP with the private slave IP (192.168.0.100) ?
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.
Yes, it did a bunch of queries as I see. 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)
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.
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!
