postfix, mysql, pure-ftp setup error with localhost on external server

Discussion in 'Installation/Configuration' started by theflame, Aug 12, 2010.

  1. theflame

    theflame New Member

    postfix don't connect to mysql

    Following the step-by-step tutorial http://www.howtoforge.com/perfect-server-ubuntu-10.04-lucid-lynx-ispconfig-3 I wanted to create external hosting server for my own tasks. Therefore I used external static IP and my own domain name. Instead of server1.example.com I typed server.myrealdomain.com. Hostname: server. Maybe in /etc/hosts file I was wrong but I added localhost.localdomain like this:
    127.0.0.1 localhost.localdomain localhost
    123.456.789.012 server.myrealdomen.com server

    I made echo server.myrealdomain.com > /etc/hostname and hostname and hostname -f returned server.myrealdomain.com

    System mail name postfix was server.myrealdomain.com too.

    Visually all worked. But the first trouble was when I tried to connect through pure-ftp. Instead 'localhost' pure-ftp tried to connect using 'server.myrealdomain.com', i. e. 'ispconfig'@'server.myrealdomain.com'.
    When I changed string 'MYSQLServer 127.0.0.1' on 'MYSQLServer localhost' in /etc/pure-ftpd/db/mysql.conf files all started to work.

    But now the same problem with postfix. It receives error when try to connect for user 'ispconfig'@'server.myrealdomain.com'.

    In /etc/postfix directory I've found some config files where I can try change 'host 127.0.0.1' into 'host localhost'. But I feel it is not correctly.

    Now I have next errors:
    postfix/qmgr[2451]: warning: connect to mysql server 127.0.0.1: Access denied for user 'ispconfig'@'server.myrealdomain.com' (using password: YES)
    postfix/qmgr[14976]: warning: connect to mysql server 127.0.0.1: Host 'server.myrealdomain.com' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
    postfix/qmgr[14976]: fatal: mysql:/etc/postfix/mysql-virtual_relaydomains.cf(0,lock|fold_fix): table lookup problem
    postfix/master[14973]: warning: process /usr/lib/postfix/qmgr pid 14976 exit status 1

    Where is my problem and how can I fix it on Ubuntu Server 10.04? May I make 'server.myrealdomain.com' equal to 'localhost'? How? Thanks.
     
    Last edited: Aug 12, 2010
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    You entered a wrong mysql hostname as you installed ispconfig. I recommend that you uninstall ispconfig by using the uninstall.php script which is in the install folder and then reinstall ispconfig and make sure that you use "localhost" as mysql hostname when the installer asks you.
     
  3. theflame

    theflame New Member

  4. till

    till Super Moderator Staff Member ISPConfig Developer

    Plese see tutorial:

    MySQL server hostname [localhost]: <-- ENTER
     
  5. theflame

    theflame New Member

    Sorry, but I did it right. It is not difficult to push Enter when it ask me. OK, I reinstall but I'm not sure it will help me.
     
  6. theflame

    theflame New Member

    I twice totally reinstall ISPConfig 3 but there is no changes. FTP works only after edit in /etc/pure-ftpd/db/mysql.conf. Mail don't work. I would like to ask. If I create host with name as server.el-dom.net.ua then should it load in browser such way: server.el-dom.net.ua:8080 ? What computer name should I input when Ubuntu installer ask me? Maybe I very stupid but when I do all step-by-step in tutorial I get errors. All of them concern host name.
     
  7. till

    till Super Moderator Staff Member ISPConfig Developer

    Post the content of the /etc/pure-ftpd/db/mysql.conf before you edited it.
    Then post the output of:

    netstat -tap | grep mysql

    and the /etc/hosts file of your server.

    Also post the output of:

    ifconfig
     
  8. theflame

    theflame New Member

    My /etc/pure-ftpd/db/mysql.conf before edit
    Code:
    ##############################################
    #                                            #
    # Sample Pure-FTPd Mysql configuration file. #
    # See README.MySQL for explanations.         #
    #                                            #
    ##############################################
    
    
    # Optional : MySQL server name or IP. Don't define this for unix sockets.
    
    MYSQLServer     127.0.0.1
    
    
    # Optional : MySQL port. Don't define this if a local unix socket is used.
    
    # MYSQLPort       3306
    
    
    # Optional : define the location of mysql.sock if the server runs on this host.
    
    # MYSQLSocket      /var/run/mysqld/mysqld.sock
    
    
    # Mandatory : user to bind the server as.
    
    MYSQLUser       ispconfig
    
    
    # Mandatory : user password. You must have a password.
    
    MYSQLPassword   **********************************
    
    
    # Mandatory : database to open.
    
    MYSQLDatabase   dbispconfig
    
    
    # Mandatory : how passwords are stored
    # Valid values are : "cleartext", "crypt", "md5" and "password"
    # ("password" = MySQL password() function)
    # You can also use "any" to try "crypt", "md5" *and* "password"
    
    MYSQLCrypt      crypt
    
    
    # In the following directives, parts of the strings are replaced at
    # run-time before performing queries :
    #
    # \L is replaced by the login of the user trying to authenticate.
    # \I is replaced by the IP address the user connected to.
    # \P is replaced by the port number the user connected to.
    # \R is replaced by the IP address the user connected from.
    # \D is replaced by the remote IP address, as a long decimal number.
    #
    # Very complex queries can be performed using these substitution strings,
    # especially for virtual hosting.
    
    
    # Query to execute in order to fetch the password
    
    MYSQLGetPW      SELECT password FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="\L"
    
    
    # Query to execute in order to fetch the system user name or uid
    
    MYSQLGetUID     SELECT uid FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="\L"
    
    
    # Optional : default UID - if set this overrides MYSQLGetUID
    
    #MYSQLDefaultUID 1000
    
    
    # Query to execute in order to fetch the system user group or gid
    
    MYSQLGetGID     SELECT gid FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="\L"
    
    
    # Optional : default GID - if set this overrides MYSQLGetGID
    
    #MYSQLDefaultGID 1000
    
    
    # Query to execute in order to fetch the home directory
    
    MYSQLGetDir     SELECT dir FROM ftp_user WHERE active = 'y' AND server_id = '1' AND username="\L"
    
    
    # Optional : query to get the maximal number of files 
    # Pure-FTPd must have been compiled with virtual quotas support.
    
    MySQLGetQTAFS  SELECT quota_files FROM ftp_user WHERE active = 'y' AND server_id = '1' AND quota_files != '-1' AND username="\L"
    
    
    # Optional : query to get the maximal disk usage (virtual quotas)
    # The number should be in Megabytes.
    # Pure-FTPd must have been compiled with virtual quotas support.
    
    MySQLGetQTASZ  SELECT quota_size FROM ftp_user WHERE active = 'y' AND server_id = '1' AND quota_size != '-1' AND username="\L"
    
    
    # Optional : ratios. The server has to be compiled with ratio support.
    
    MySQLGetRatioUL SELECT ul_ratio FROM ftp_user WHERE active = 'y' AND server_id = '1' AND ul_ratio != '-1' AND username="\L"
    MySQLGetRatioDL SELECT dl_ratio FROM ftp_user WHERE active = 'y' AND server_id = '1' AND dl_ratio != '-1' AND username="\L"
    
    
    # Optional : bandwidth throttling.
    # The server has to be compiled with throttling support.
    # Values are in KB/s .
    
    MySQLGetBandwidthUL SELECT ul_bandwidth FROM ftp_user WHERE active = 'y' AND server_id = '1' AND ul_bandwidth != '-1' AND username="\L"
    MySQLGetBandwidthDL SELECT dl_bandwidth FROM ftp_user WHERE active = 'y' AND server_id = '1' AND dl_bandwidth != '-1' AND username="\L"
    
    # Enable ~ expansion. NEVER ENABLE THIS BLINDLY UNLESS :
    # 1) You know what you are doing.
    # 2) Real and virtual users match.
    
    # MySQLForceTildeExpansion 1
    
    
    # If you upgraded your tables to transactionnal tables (Gemini,
    # BerkeleyDB, Innobase...), you can enable SQL transactions to
    # avoid races. Leave this commented if you are using the
    # traditionnal MyIsam databases or old (< 3.23.x) MySQL versions.
    
    MySQLTransactions On
    netstat -tap | grep mysql
    Code:
    tcp        0      0 *:mysql                 *:*                     LISTEN      783/mysqld      
    tcp        0      0 localhost:mysql         server.el-dom.net:42807 TIME_WAIT   -               
    tcp        0      0 localhost:mysql         server.el-dom.net:42810 TIME_WAIT   -               
    tcp        0      0 localhost:mysql         server.el-dom.net:42809 TIME_WAIT   -               
    tcp        0      0 localhost:mysql         server.el-dom.net:42808 TIME_WAIT   -               
    tcp        0      0 localhost:mysql         server.el-dom.net:42806 TIME_WAIT   -               
    tcp        0      0 localhost:mysql         server.el-dom.net:42805 TIME_WAIT   -               
    /etc/hosts file of my server
    Code:
    127.0.0.1       localhost.localdomain   localhost
    188.231.191.148 server.el-dom.net.ua    server
    
    # The following lines are desirable for IPv6 capable hosts
    ::1     localhost ip6-localhost ip6-loopback
    fe00::0 ip6-localnet
    ff00::0 ip6-mcastprefix
    ff02::1 ip6-allnodes
    ff02::2 ip6-allrouters
    My ifconfig
    Code:
    eth0      Link encap:Ethernet  HWaddr 00:1d:92:51:c2:ea  
              inet addr:188.231.191.148  Bcast:188.231.191.255  Mask:255.255.255.0
              inet6 addr: fe80::21d:92ff:fe51:c2ea/64 Scope:Link
              UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
              RX packets:246 errors:0 dropped:0 overruns:0 frame:0
              TX packets:49 errors:0 dropped:0 overruns:0 carrier:0
              collisions:0 txqueuelen:1000 
              RX bytes:24961 (24.9 KB)  TX bytes:4115 (4.1 KB)
              Interrupt:20 Base address:0x4000 
    
    eth1      Link encap:Ethernet  HWaddr 00:10:16:48:de:42  
              inet addr:192.168.0.10  Bcast:192.168.0.255  Mask:255.255.255.0
              inet6 addr: fe80::210:16ff:fe48:de42/64 Scope:Link
              UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
              RX packets:72 errors:0 dropped:0 overruns:0 frame:0
              TX packets:53 errors:0 dropped:0 overruns:0 carrier:0
              collisions:0 txqueuelen:1000 
              RX bytes:6787 (6.7 KB)  TX bytes:7883 (7.8 KB)
    
    lo        Link encap:Локальная петля (Loopback)  
              inet addr:127.0.0.1  Mask:255.0.0.0
              inet6 addr: ::1/128 Scope:Host
              UP LOOPBACK RUNNING  MTU:16436  Metric:1
              RX packets:140 errors:0 dropped:0 overruns:0 frame:0
              TX packets:140 errors:0 dropped:0 overruns:0 carrier:0
              collisions:0 txqueuelen:0 
              RX bytes:10226 (10.2 KB)  TX bytes:10226 (10.2 KB)
     
    Last edited: Aug 12, 2010
  9. theflame

    theflame New Member

    It was not useful?
     
  10. falko

    falko Super Moderator ISPConfig Developer

    What exactly did you change in /etc/pure-ftpd/db/mysql.conf?

    Are there any errors in your mail log?
     
  11. theflame

    theflame New Member

    As I said early in /etc/pure-ftpd/db/mysql.conf I only change 'MYSQLServer 127.0.0.1' on 'MYSQLServer localhost'. And it works good. After this pure-ftp connects to mysql as 'ispconfig'@'localhost'. Now I wand do it for postfix.
    My full mail log (tail -f /var/log/mail.log return):
    Code:
    Aug 13 18:36:11 server postfix/qmgr[17170]: warning: transport retry failure -- see a previous warning/fatal/panic logfile record for the problem description
    Aug 13 18:36:11 server postfix/master[1662]: warning: process /usr/lib/postfix/error pid 17177 exit status 1
    Aug 13 18:36:11 server postfix/master[1662]: warning: /usr/lib/postfix/error: bad command startup -- throttling
    Aug 13 18:36:11 server postfix/master[1662]: warning: process /usr/lib/postfix/error pid 17179 exit status 1
    Aug 13 18:36:11 server postfix/master[1662]: warning: process /usr/lib/postfix/error pid 17181 exit status 1
    Aug 13 18:36:11 server postfix/master[1662]: warning: process /usr/lib/postfix/error pid 17178 exit status 1
    Aug 13 18:36:11 server postfix/qmgr[17170]: 5E74C24101C: to=<[email protected]>, orig_to=<root>, relay=none, delay=129895, delays=129893/2.4/0/0, dsn=4.3.0, status=deferred (unknown mail transport error)
    Aug 13 18:36:11 server postfix/qmgr[17170]: warning: connect to mysql server 127.0.0.1: Access denied for user 'ispconfig'@'server.el-dom.net.ua' (using password: YES)
    Aug 13 18:36:11 server postfix/qmgr[17170]: fatal: mysql:/etc/postfix/mysql-virtual_relaydomains.cf(0,lock|fold_fix): table lookup problem
    Aug 13 18:36:12 server postfix/master[1662]: warning: process /usr/lib/postfix/qmgr pid 17170 exit status 1
    Aug 13 18:38:21 server postfix/anvil[17155]: statistics: max connection rate 1/60s for (smtp:188.231.191.148) at Aug 13 18:35:01
    Aug 13 18:38:21 server postfix/anvil[17155]: statistics: max connection count 1 for (smtp:188.231.191.148) at Aug 13 18:35:01
    Aug 13 18:38:21 server postfix/anvil[17155]: statistics: max cache size 1 at Aug 13 18:35:01
    My /etc/network/interfaces
    Code:
    # This file describes the network interfaces available on your system
    # and how to activate them. For more information, see interfaces(5).
    
    # The loopback network interface
    auto lo
    iface lo inet loopback
    
    # The primary network interface
    auto eth0
    iface eth0 inet static
    	address 188.231.191.148
    	netmask 255.255.255.0
    	network 188.231.191.0
    	broadcast 188.231.191.255
    	gateway 188.231.191.1
    	# dns-* options are implemented by the resolvconf package, if installed
    	dns-nameservers 193.24.25.1 193.24.25.250
    	dns-search el-dom.net.ua
    	pre-up iptables-restore < /etc/iptables.up.rules
    
    # The secondary network interface
    auto eth1
    iface eth1 inet static
    	address 192.168.0.10
    	netmask 255.255.255.0
    	network 192.168.0.0
    	broadcast 192.168.0.255
    OK. What should I change in this tutorial http://www.howtoforge.com/perfect-server-ubuntu-10.04-lucid-lynx-ispconfig-3 if I want external and not local server? I have static IP and domain name. As you see it's 188.231.191.148 and el-dom.net.ua. Where and in what files should I replace by them? Maybe have I mistakes in it? Please, I really need help. I can totally reinstall Ubuntu Server but I need to know where I should do otherwise. Maybe does it help me when I create 'server.el-dom.net.ua' MySQL server hostname instead of 'localhost'? Also I can edit /etc/courier/imapd.cnf and /etc/courier/pop3d.cnf because there are some modifications where we change 'localhost' on our server domain name. What should I do? I very need decision.
     
  12. falko

    falko Super Moderator ISPConfig Developer

    Can you try if you can connect ot MySQL from the command line as follows?

    Code:
    mysql -h 127.0.0.1 -u root -p
    and

    Code:
    mysql -h localhost -u root -p
     
  13. theflame

    theflame New Member

    I have tried the both ways. All of them connect good to mysql. What does it mean? What next?

    Code:
    [email protected]:~$ mysql -h 127.0.0.1 -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7221
    Server version: 5.1.41-3ubuntu12.6 (Ubuntu)
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> \q
    Bye
    [email protected]:~$ mysql -h localhost -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7223
    Server version: 5.1.41-3ubuntu12.6 (Ubuntu)
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> \q
    Bye
    [email protected]:~$ 
    But
    Code:
    mysql -h el-dom.net.ua -u root -p
    and
    Code:
    mysql -h server.el-dom.net.ua -u root -p
    work too!

    Code:
    [email protected]:~$ mysql -h el-dom.net.ua -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7268
    Server version: 5.1.41-3ubuntu12.6 (Ubuntu)
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> \q
    Bye
    [email protected]:~$ mysql -h server.el-dom.net.ua -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7269
    Server version: 5.1.41-3ubuntu12.6 (Ubuntu)
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> \q
    Bye
    [email protected]:~$ 
    And
    Code:
    mysql -h localhost -u ispconfig -p
    and
    Code:
    mysql -h localhost -u ispconfig -p
    don't work.

    Code:
    [email protected]:~$ mysql -h localhost -u ispconfig -p
    Enter password: 
    ERROR 1045 (28000): Access denied for user 'ispconfig'@'localhost' (using password: YES)
    [email protected]:~$ mysql -h 127.0.0.1 -u ispconfig -p
    Enter password: 
    ERROR 1045 (28000): Access denied for user 'ispconfig'@'server.el-dom.net.ua' (using password: YES)
    Is it through root privileges of ispconfig user?

    May it be here?
    Code:
    MySQL root username [root]: <-- ENTER
     
    Last edited: Aug 14, 2010
  14. falko

    falko Super Moderator ISPConfig Developer

    Can you check the Host column of the ispconfig user in the mysql.user table? I guess there's a wrong hostname in it.
     
  15. theflame

    theflame New Member

    host and user columns look like md5 code. And how should it looks? Now I see seven records in this table in such way. And I get this each time after installing of Ubuntu with ISPConfig. May it be a bug?
     
    Last edited: Aug 16, 2010
  16. theflame

    theflame New Member

    This is from my phpmyadmin mysql.user
     

    Attached Files:

    • img.jpg
      img.jpg
      File size:
      38.9 KB
      Views:
      403
    Last edited: Aug 15, 2010
  17. falko

    falko Super Moderator ISPConfig Developer

    You should have hostnames in the Host column, such as localhost, server.el-dom.net.ua, or % (which is a wildcard and allows connections from all hosts). Also, there must be MySQL usernames (such as root, ispconfig, etc.) in the User column.
     
  18. theflame

    theflame New Member

    Yes, I see. But do you believe me that I have never create any records in this table? Where does it arise from? And how can I avoid it in the next installation? May it be through ISPConfig version 3.0.2.2? How can this records emerge in mysql table? What do they mean?
     
  19. falko

    falko Super Moderator ISPConfig Developer

    No, they don't come from ISPConfig because then you wouldn't be the first to report such a behaviour. I have no idea where these records come from...
     
  20. theflame

    theflame New Member

    To add some records through phpMyAdminn I want to creat extra thread )

    OK. As I see it decided something but ftp and mail don't work from box. My mail log looks like this:
    Code:
    Aug 19 14:41:49 el-dom postfix/qmgr[29554]: 636DE80FB3: to=<[email protected]>, relay=none, delay=154, delays=152/2.3/0/0, dsn=4.3.0, status=deferred (unknown mail transport error)
    Aug 19 14:41:49 el-dom postfix/qmgr[29554]: warning: connect to mysql server 127.0.0.1: Host 'el-dom.biz' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
    Aug 19 14:41:49 el-dom postfix/qmgr[29554]: fatal: mysql:/etc/postfix/mysql-virtual_relaydomains.cf(0,lock|fold_fix): table lookup problem
    Aug 19 14:41:50 el-dom postfix/master[18276]: warning: process /usr/lib/postfix/qmgr pid 29554 exit status 1
    Aug 19 14:42:41 el-dom postfix/proxymap[29460]: warning: connect to mysql server 127.0.0.1: Host 'el-dom.biz' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
    Aug 19 14:42:41 el-dom postfix/proxymap[29460]: warning: connect to mysql server 127.0.0.1: Host 'el-dom.biz' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
    Aug 19 14:42:41 el-dom postfix/trivial-rewrite[29566]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Aug 19 14:42:42 el-dom postfix/smtpd[29491]: warning: problem talking to service rewrite: Success
    Aug 19 14:42:42 el-dom postfix/master[18276]: warning: process /usr/lib/postfix/trivial-rewrite pid 29566 exit status 1
    Aug 19 14:42:42 el-dom postfix/master[18276]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling
    Aug 19 14:43:42 el-dom postfix/proxymap[29460]: warning: connect to mysql server 127.0.0.1: Host 'el-dom.biz' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
    Aug 19 14:43:42 el-dom postfix/proxymap[29460]: warning: connect to mysql server 127.0.0.1: Host 'el-dom.biz' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
    Aug 19 14:43:42 el-dom postfix/trivial-rewrite[29668]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Aug 19 14:43:43 el-dom postfix/smtpd[29491]: warning: problem talking to service rewrite: Success
    Aug 19 14:43:43 el-dom postfix/master[18276]: warning: process /usr/lib/postfix/trivial-rewrite pid 29668 exit status 1
    Aug 19 14:43:43 el-dom postfix/master[18276]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling
    Aug 19 14:44:43 el-dom postfix/proxymap[29460]: warning: connect to mysql server 127.0.0.1: Host 'el-dom.biz' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
    Aug 19 14:44:43 el-dom postfix/proxymap[29460]: warning: connect to mysql server 127.0.0.1: Host 'el-dom.biz' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
    Aug 19 14:44:43 el-dom postfix/trivial-rewrite[29674]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Aug 19 14:44:44 el-dom postfix/smtpd[29491]: warning: problem talking to service rewrite: Success
    Aug 19 14:44:44 el-dom postfix/master[18276]: warning: process /usr/lib/postfix/trivial-rewrite pid 29674 exit status 1
    Aug 19 14:44:44 el-dom postfix/master[18276]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling
    
    And FTP return the same errors in the next log:
    Aug 19 14:54:00 el-dom pure-ftpd: ([email protected]) [INFO] New connection from 192.168.0.128
    Aug 19 14:54:09 el-dom pure-ftpd: ([email protected]) [ERROR] The SQL server seems to be down [Host 'el-dom.biz' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts']
    Aug 19 14:54:09 el-dom pure-ftpd: ([email protected]) [INFO] PAM_RHOST enabled. Getting the peer address
    Aug 19 14:54:16 el-dom pure-ftpd: ([email protected]) [WARNING] Authentication failed for user [eldomftp]
    Aug 19 14:54:19 el-dom pure-ftpd: ([email protected]) [INFO] Logout.
     

Share This Page