1 Topic by pasaisea

  • pasaisea
  • Member
  • Offline
  • Registered: 2015-12-15
  • Posts: 28

Topic: Error MySQL Too Many Connections from vmail and vmailadmin

==== Required information ====
- iRedMail version (check /etc/iredmail-release):
- Linux/BSD distribution name and version: Ubuntu
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro?
- Related log if you're reporting an issue:
====

Hello, my Webmin Database Connections have been reporting active connections from MySQL user vmail and vmail admin and I am new to running my own webmail server but do you think that my server is experiencing DDoS from outside connections trying to port scan? Or is it normal for iredmail. Please check my attachment below. Thanks before

ID    Username    Client host    Database    Mode    Connected    Running query
6045    vmail    localhost:45028    vmail    Sleep    00:00:06   
6078    vmail    localhost:45156    vmail    Sleep    00:00:06   
6155    vmail    localhost:45336    vmail    Sleep    00:24:47   
6174    vmail    localhost:45382    vmail    Sleep    00:23:41   
6284    vmail    localhost:45742    vmail    Sleep    00:18:53   
6289    vmail    localhost:45774    vmail    Sleep    00:18:06   
6292    vmail    localhost:45786    vmail    Sleep    00:17:50   
6385    vmail    localhost:46104    vmail    Sleep    00:14:49   
6386    vmail    localhost:46106    vmail    Sleep    00:14:49   
6389    vmail    localhost:46124    vmail    Sleep    00:14:26   
6391    vmail    localhost:46130    vmail    Sleep    00:14:25   
6392    vmail    localhost:46132    vmail    Sleep    00:14:25   
6426    vmailadmin    localhost:46392    vmail    Sleep    00:00:06   
6475    vmailadmin    localhost:46678    vmail    Sleep    00:00:08   
6481    vmail    localhost:46746    vmail    Sleep    00:00:46   
6482    vmail    localhost:46748    vmail    Sleep    00:00:46   
6483    vmail    localhost:46750    vmail    Sleep    00:00:46   
6484    vmail    localhost:46752    vmail    Sleep    00:00:46   
6485    vmail    localhost:46754    vmail    Sleep    00:00:46   
6486    vmail    localhost:46756    vmail    Sleep    00:00:46   
6487    vmail    localhost:46758    vmail    Sleep    00:00:46   
6488    vmail    localhost:46760    vmail    Sleep    00:00:46   
6489    vmail    localhost:46762    vmail    Sleep    00:00:46   
6490    vmail    localhost:46764    vmail    Sleep    00:00:46   
6491    vmail    localhost:46766    vmail    Sleep    00:00:46   
6492    vmail    localhost:46768    vmail    Sleep    00:00:46   
6495    vmail    localhost:46784    vmail    Sleep    00:00:08

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,787

Re: Error MySQL Too Many Connections from vmail and vmailadmin

*) Please check Apache log files, do you have many http connections to any web application?
*) Show us output of command: "postconf -n" please. If Postfix uses proxy map ("proxy:mysql:/etc/postfix/mysql/xxx.cf") for sql queries, it won't open new sql connections for new smtp requests, so it's not a problem at all. but we need to make sure you have proxy map enabled for all sql lookups.

3 Reply by pasaisea (edited by pasaisea 2016-04-13 12:22:25)

  • pasaisea
  • Member
  • Offline
  • Registered: 2015-12-15
  • Posts: 28

Re: Error MySQL Too Many Connections from vmail and vmailadmin

Hi, thank you very much for the response Zang.

My Apache Access.Log has logged many from webmail applications. I have installed roundcube, rainloop and afterlogic as the three choice of webmail interface for the iredmail. And my users accessed it everyday every hour

My Postconf -n is like this.

alias_database = hash:/etc/postfix/aliases
alias_maps = hash:/etc/postfix/aliases
allow_min_user = no
allow_percent_hack = no
append_dot_mydomain = no
biff = no
bounce_queue_lifetime = 4h
broken_sasl_auth_clients = yes
config_directory = /etc/postfix
disable_vrfy_command = yes
dovecot_destination_recipient_limit = 1
enable_original_recipient = no
home_mailbox = Maildir/
inet_protocols = ipv4
lmtp_tls_mandatory_protocols = !SSLv2 !SSLv3
lmtp_tls_protocols = !SSLv2 !SSLv3
mailbox_command = /usr/lib/dovecot/deliver
mailbox_size_limit = 0
maximal_backoff_time = 4000s
maximal_queue_lifetime = 4h
message_size_limit = 104857600
minimal_backoff_time = 300s
mydestination = localhost, localhost.localdomain, mywebsite.co.id
mydomain = mywebsite.co.id
myhostname = mywebsite.co.id
mynetworks = 127.0.0.0/8, 203.128.78.41
mynetworks_style = host
myorigin = mywebsite.co.id
proxy_read_maps = $canonical_maps $lmtp_generic_maps $local_recipient_maps $mydestination $mynetworks $recipient_bcc_maps $recipient_canonical_maps $relay_domains $relay_recipient_maps $relocated_maps $sender_bcc_maps $sender_canonical_maps $smtp_generic_maps $smtpd_sender_login_maps $transport_maps $virtual_alias_domains $virtual_alias_maps $virtual_mailbox_domains $virtual_mailbox_maps $smtpd_sender_restrictions
queue_run_delay = 300s
readme_directory = no
recipient_bcc_maps = proxy:mysql:/etc/postfix/mysql/recipient_bcc_maps_user.cf, proxy:mysql:/etc/postfix/mysql/recipient_bcc_maps_domain.cf
recipient_delimiter = +
relay_domains = $mydestination, proxy:mysql:/etc/postfix/mysql/relay_domains.cf
sender_bcc_maps = proxy:mysql:/etc/postfix/mysql/sender_bcc_maps_user.cf, proxy:mysql:/etc/postfix/mysql/sender_bcc_maps_domain.cf
smtp-amavis_destination_recipient_limit = 1
smtp_data_init_timeout = 240s
smtp_data_xfer_timeout = 600s
smtp_tls_CAfile = $smtpd_tls_CAfile
smtp_tls_loglevel = 0
smtp_tls_mandatory_protocols = !SSLv2 !SSLv3
smtp_tls_note_starttls_offer = yes
smtp_tls_protocols = !SSLv2 !SSLv3
smtp_tls_security_level = may
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
smtpd_data_restrictions = reject_unauth_pipelining
smtpd_helo_required = yes
smtpd_helo_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_non_fqdn_helo_hostname, reject_invalid_helo_hostname, check_helo_access pcre:/etc/postfix/helo_access.pcre
smtpd_reject_unlisted_recipient = yes
smtpd_reject_unlisted_sender = yes
smtpd_relay_restrictions = permit_mynetworks permit_sasl_authenticated defer_unauth_destination
smtpd_sasl_auth_enable = yes
smtpd_sasl_local_domain = $myhostname
smtpd_sasl_path = private/dovecot-auth
smtpd_sasl_security_options = noanonymous
smtpd_sasl_type = dovecot
smtpd_sender_login_maps = proxy:mysql:/etc/postfix/mysql/sender_login_maps.cf
smtpd_sender_restrictions = reject_unknown_sender_domain, reject_non_fqdn_sender, reject_unlisted_sender, permit_mynetworks, permit_sasl_authenticated
smtpd_tls_CAfile = /etc/sertifikat/1_root_bundle.crt
smtpd_tls_cert_file = /etc/sertifikat/2_www.mywebsite.co.id.crt
smtpd_tls_dh1024_param_file = /etc/ssl/dhparams.pem
smtpd_tls_key_file = /etc/sertifikat/3_www.mywebsite.co.id.key
smtpd_tls_loglevel = 0
smtpd_tls_mandatory_exclude_ciphers = aNULL, eNULL, EXPORT, DES, RC4, MD5, PSK, aECDH, EDH-DSS-DES-CBC3-SHA, EDH-RSA-DES-CDC3-SHA, KRB5-DE5, CBC3-SHA
smtpd_tls_mandatory_protocols = !SSLv2 !SSLv3
smtpd_tls_protocols = !SSLv2 !SSLv3
smtpd_tls_security_level = may
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtpd_tls_session_cache_timeout = 3600s
smtpd_use_tls = no
swap_bangpath = no
tls_random_source = dev:/dev/urandom
transport_maps = proxy:mysql:/etc/postfix/mysql/transport_maps_user.cf, proxy:mysql:/etc/postfix/mysql/transport_maps_domain.cf
virtual_alias_domains =
virtual_alias_maps = proxy:mysql:/etc/postfix/mysql/virtual_alias_maps.cf, proxy:mysql:/etc/postfix/mysql/domain_alias_maps.cf, proxy:mysql:/etc/postfix/mysql/catchall_maps.cf, proxy:mysql:/etc/postfix/mysql/domain_alias_catchall_maps.cf
virtual_gid_maps = static:2000
virtual_mailbox_base = /var/vmail
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql/virtual_mailbox_domains.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql/virtual_mailbox_maps.cf
virtual_minimum_uid = 2000
virtual_transport = dovecot
virtual_uid_maps = static:2000


I don't understand I'm still new, this is my first time running webmail server before. So, do you think this is normal? the mail server is safe then from DDoS attack?

What are your opinion about the Database Connections ID's? It keeps generating and won't stop. few hours ago it was still about 5000-ish and now about 6700-ish. How to stop this?

Thanks in advance

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,787

Re: Error MySQL Too Many Connections from vmail and vmailadmin

*) How many concurrent SQL connections?
*) What's the output of SQL command below (run as mysql root user):

sql> show processlist;

*) Also, is it possible to disable some web applications to make sure it's not caused by the web application?

Just curious, why so many webmails?

5 Reply by pasaisea (edited by pasaisea 2016-04-13 12:39:28)

  • pasaisea
  • Member
  • Offline
  • Registered: 2015-12-15
  • Posts: 28

Re: Error MySQL Too Many Connections from vmail and vmailadmin

Thanks for the reply Zhang, Right now it was like this:
+------+------------+-----------------+-------+---------+------+-------+------------------+
| Id   | User       | Host            | db    | Command | Time | State | Info             |
+------+------------+-----------------+-------+---------+------+-------+------------------+
| 6045 | vmail      | localhost:45028 | vmail | Sleep   |    6 |       | NULL             |
| 6078 | vmail      | localhost:45156 | vmail | Sleep   |    6 |       | NULL             |
| 7143 | vmail      | localhost:49002 | vmail | Sleep   | 1309 |       | NULL             |
| 7280 | vmailadmin | localhost:49462 | vmail | Sleep   |   25 |       | NULL             |
| 7320 | vmail      | localhost:49554 | vmail | Sleep   |  704 |       | NULL             |
| 7345 | vmail      | localhost:49604 | vmail | Sleep   |  703 |       | NULL             |
| 7393 | vmail      | localhost:49850 | vmail | Sleep   |  514 |       | NULL             |
| 7400 | vmail      | localhost:49866 | vmail | Sleep   |  512 |       | NULL             |
| 7402 | vmail      | localhost:49868 | vmail | Sleep   |  511 |       | NULL             |
| 7416 | vmail      | localhost:49898 | vmail | Sleep   |  510 |       | NULL             |
| 7419 | vmail      | localhost:49904 | vmail | Sleep   |  509 |       | NULL             |
| 7422 | vmail      | localhost:49910 | vmail | Sleep   |  509 |       | NULL             |
| 7446 | vmailadmin | localhost:50034 | vmail | Sleep   |   13 |       | NULL             |
| 7474 | vmail      | localhost:50122 | vmail | Sleep   |   57 |       | NULL             |
| 7477 | vmail      | localhost:50134 | vmail | Sleep   |   25 |       | NULL             |
| 7478 | vmail      | localhost:50136 | vmail | Sleep   |   26 |       | NULL             |
| 7479 | vmail      | localhost:50138 | vmail | Sleep   |   25 |       | NULL             |
| 7480 | vmail      | localhost:50140 | vmail | Sleep   |   25 |       | NULL             |
| 7481 | vmail      | localhost:50142 | vmail | Sleep   |   26 |       | NULL             |
| 7482 | vmail      | localhost:50144 | vmail | Sleep   |   26 |       | NULL             |
| 7483 | vmail      | localhost:50146 | vmail | Sleep   |   26 |       | NULL             |
| 7484 | vmail      | localhost:50148 | vmail | Sleep   |   26 |       | NULL             |
| 7485 | vmail      | localhost:50150 | vmail | Sleep   |   26 |       | NULL             |
| 7486 | vmail      | localhost:50152 | vmail | Sleep   |   26 |       | NULL             |
| 7487 | vmail      | localhost:50154 | vmail | Sleep   |   26 |       | NULL             |
| 7488 | vmail      | localhost:50156 | vmail | Sleep   |   26 |       | NULL             |
| 7494 | root       | localhost       | NULL  | Query   |    0 | init  | show processlist |
+------+------------+-----------------+-------+---------+------+-------+------------------+
27 rows in set (0,00 sec)

Update: Only 27 active connections.

Before I got hundreds active connections in the sql> show processlist; My Server CPU Load became 100% and can't send or receive mails. Like this server postfix/proxymap[9378]: warning: connect to mysql server 127.0.0.1: Too many connections

Hence, I did netstat -tan on my ubuntu terminal and it shows me 2 suspicious IP from another country keeps trying to connect. I then blocked those 2 IP's and my server cpu load dropped from 100% to 3% The sql started working fine and can send /receive email again.

But I still can't figured out what is this vmail and vmailadmin on sql> show processlist; keeps generating new ID's and the host is localhost:45028 , localhost:45156 , localhost:48888 are these normal? I don't have those ports. I only have port 25, port 587, port 443, port 80 and port 3360 for web mail server.

---

Okay, about the question so many webmails is to add a nice diversity. I know that Roundcube is superb, but I would like to give users to choose Rainloop for the nice webmail interface and Afterlogic which is responsive on mobile devices.

6 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,787

Re: Error MySQL Too Many Connections from vmail and vmailadmin

pasaisea wrote:

the host is localhost:45028 , localhost:45156 , localhost:48888 are these normal?

The digital numbers means local network port (sql client).

Current situation is, you need to figure out which web application is used by spammer/hacker to generate so many sql connections. If you don't know which one, try to disable it temporarily when this issue is happening. If disabling this application significantly reduces the sql connections (or no more new sql connections), then you can know it's caused by this application.

7 Reply by pasaisea

  • pasaisea
  • Member
  • Offline
  • Registered: 2015-12-15
  • Posts: 28

Re: Error MySQL Too Many Connections from vmail and vmailadmin

I still can't find the source of problem that make my server keeps generating new SQL Connections sad

Will changing password for user vmail and database vmail will get rid of this issue? What will happened if I changed the user vmail password? Will my user mails stopped working?

8 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,787

Re: Error MySQL Too Many Connections from vmail and vmailadmin

pasaisea wrote:

I still can't find the source of problem that make my server keeps generating new SQL Connections sad

What did you do to try go figure it out?

pasaisea wrote:

Will changing password for user vmail and database vmail will get rid of this issue? What will happened if I changed the user vmail password? Will my user mails stopped working?

Don't change their passwords unless you already know it's the root cause. vmail/vmailadmin passwords are used in many config files, so if you change the sql password WITHOUT updating related config files, your mail services will stop, including SMTP, POP3/IMAP.