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

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.

 

Maybe. But where will ispconfig ask this? As I see here http://www.howtoforge.com/perfect-server-ubuntu-10.04-lucid-lynx-ispconfig-3-p5 in ISPConfig 3 there is no question during installation.

 

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.

 

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.

 

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)

 

It was not useful?

 

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

Are there any errors in your mail log?

 

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.

 

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

 

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

Code:

user@server:~$ 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
user@server:~$ 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
user@server:~$ 

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:

user@server:~$ 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
user@server:~$ 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
user@server:~$ 

And

Code:

mysql -h localhost -u ispconfig -p

and

Code:

mysql -h localhost -u ispconfig -p

don't work.

Code:

user@server:~$ mysql -h localhost -u ispconfig -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'ispconfig'@'localhost' (using password: YES)
user@server:~$ 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

 

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

 

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?

 

This is from my phpmyadmin mysql.user

 

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.

 

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?

 

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...

 

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.