Hello, since updating to 3.2 (or a bit earlier), I can't connect to the FTP server with the correct login. Syslog: Nov 15 14:57:11 server2 pure-ftpd: ([email protected]) [INFO] New connection from 1.1.1.1 Nov 15 14:57:11 server2 pure-ftpd: ([email protected]) [ERROR] The SQL server seems to be down [Can't connect to MySQL server on '127.0.0.1' (115)] Nov 15 14:57:17 server2 pure-ftpd: ([email protected]) [WARNING] Authentication failed for user [client_user] Nov 15 14:57:17 server2 pure-ftpd: ([email protected]) [INFO] Logout. Monitoring said that MySQL server is offline (but it's currently running and works great for sites). amavisd: amavis[22395]: (22395-01) (!)connect_to_sql: unable to connect to DSN 'DBI:mysql:database=mainispdb;host=127.0.0.1;port=3306': Can't connect to MySQL server on '127.0.0.1' (115) Nov 15 15:03:26 server2 amavis[22395]: (22395-01) (!!)TROUBLE in process_request: connect_to_sql: unable to connect to any dataset at (eval 110) line 253. Nov 15 15:03:26 server2 amavis[22395]: (22395-01) (!)Requesting process rundown after fatal error MariaDB 10.3.25, Debian 10 Where can I see the connection settings?
Take care that you did not disable name resolution (skip name resolve) in MariaDB and that MariaDB listens on 127.0.0.1 (localhost).
It works for sites on localhost. Where can I find configuration options to test the auth from commandline?
You can use Code: mysql -u root -p Verify your setup with the instructions from the Perfect Server guide from your OS to see if you've missed something, for example the options Till mentioned.
Thanks for the quick answer, but I don't remember root password (or allowed hosts for it). I can change it, but that way It can became wrong for ISPConfig, so I'm asking for the config path. Also, (as I remember) MariaDB default authification method is unix_socket, maybe incompatibility is near that.
On install of ISPConfig, a special user is created to access the database, so changing the password should not be a problem for ISPConfig. See the perfect server guide for your OS to see which changes are needed in what file.
I've read it before writing to the forum and I found my password at /etc/mysql, and it's connecting great from localhost. How can I debug it myself? "Perfect server" can't give me the answer first of all because there are no words about pure-ftpd and amavisd connections to mysql and it has commented mysql option strings in the config. But it works before!
Solved: for MySQL user@localhost and [email protected] are different users (no matter what contains in hosts file), so my mysql server allows only "localhost" connection. In that case I need to set /etc/amavis/conf.d/50-user and /etc/pure-ftpd/db/mysql.conf host to "localhost". It may became critical on mariadb update. As for now, I have no ideas where I can correct the monitoring page.
Instead of editing the ispconfig files, which were correct before, fix your MariaDB config. You must have disabled networking if the connection to the localhost IP is blocked, enable networking again.
Explain me please But to connect to MySQL I need to create [email protected] and user@::1 and give them priviledges for the main table. Or I need to create aliases anywhere in my.cnf? So that is security issue. Where is I'm not right?
Read ISPConfig Perfect Server Guide, the part about installing and configuring MariaDB. Seems you have not revealed what OS you are running ISPConfig on, so I do not know which Guide is appropriate. Choose from these: https://www.ispconfig.org/documentation/
Comletely right. But you didn't understand what I'm talking about! When I run: mysql_secure_installation, I'm cutting root@localhost root@{myip} [email protected] root@'%' to root@localhost [email protected] with selected password, no matter how I've been connected. I may use localhost or 127.0.0.1 Now for the main ispconfig db I have in [mysql]/users only user@localhost account (don't know why it worked before). Don't matter what interface mysql is listening (127.0,0.1/::1, name resolvation, 0.0.0.0, etc), I can connect, if db config contains "localhost", without alternatives (user@localhost account), otherwise I'll need to create two or more for each interface duplicating users. I don't know what is the best way. It may be trimmed next time mariadb update.
Still no OS mentioned. What have you put in mariadeb configuration for bind-address? Do you mean you choose to cut myip away or it gets cut no matter what you do?
And check your /etc/hosts file if it contains correct line for localhost and IP 127.0.0.1 and try to remember what you changed in the MySQL config before the issue started to undo that change.
I'm talking you about MySQL accounts, not about interfaces. Look: Then to connect from 127.0.0.1 I need user 127.0.0.1 in host colomn and ispuserconf in user, and specify it a password. In the listen specified 5.9.11.116 to connect from the neibourhood to c2_b3bot, but it wasn't problem before. The issue I think came due mariadb update. If I'll create such user, first of all it's not secure and may be deleted during next upgrade
You don't need a separate user for IP 127.0.0.1, the entry for localhost is enough. Please do what we suggested and post the required details if you want your problem to be solved. Example: Code: MariaDB [mysql]> select host, user from user; +-----------+-----------+ | host | user | +-----------+-----------+ | localhost | c0_test | | localhost | c1test | | localhost | ispconfig | | localhost | pma | | localhost | root | | localhost | roundcube | | localhost | tata | +-----------+-----------+ 7 rows in set (0.007 sec) Connection works fine with localhost and 127.0.0.1: Code: root@server1:~# mysql -h localhost -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 149 Server version: 10.3.23-MariaDB-0+deb10u1 Debian 10 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> quit Bye root@server1:~# mysql -h 127.0.0.1 -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 150 Server version: 10.3.23-MariaDB-0+deb10u1 Debian 10 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> quit Bye root@server1:~# As it does not work on your system, either network connections have been disabled in your MariaDB config or the hosts file contains wrong details for localhost.
Thanks. There are conflicted paragraphs in the howto: Code: "We want MySQL to listen on all interfaces, not just localhost. Therefore, we edit /etc/mysql/mariadb.conf.d/50-server.cnf and comment out the line bind-address = 127.0.0.1 by adding a # in front of it." # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 So, if we are commenting line, default must be listening only localhost due comment, not 0.0.0.0 Hosts: Code: 127.0.0.1 localhost ::1 localhost ip6-localhost ip6-loopback ff02::1 ip6-allnodes ff02::2 ip6-allrouters # --- BEGIN PVE --- 5.9.11.116 server2.my.com server2 # --- END PVE --- If bind-address = 5.9.11.116 MySQL listening it AND localhost (that is not 127.0.0.1, no matter what is set in hosts file) I set directly bind-address = 0.0.0.0 It works now
Disabling that line enables MariaDB to listen on all IP addresses here on my system, so the guide is ok. Probably you disabled networking somewhere in MariaDB. But if setting it to 0.0.0.0 works on your system, then that's fine as well.
