1 Topic by craig (edited by craig 2016-11-04 14:33:35)

  • craig
  • Member
  • Offline
  • From: Canada
  • Registered: 2013-09-04
  • Posts: 272

Topic: Remote access to MySQL

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

I prefer to use a GUI MySQL client (HeidiSQL) to manage databases. I've done without this on my iRedMail server for about three years, but it's time to make it work.

Here is the "my.cnf" file I tried to use:

#---------------------------------------------------------------------
# This file is part of iRedMail, which is an open source mail server
# solution for Red Hat(R) Enterprise Linux, CentOS, Debian and Ubuntu.
#
# iRedMail is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# iRedMail is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with iRedMail.  If not, see <http://www.gnu.org/licenses/>.
#---------------------------------------------------------------------

#
# Sample MySQL configuration file. It should be localted at:
#
#   - RHEL/CentOS:  /etc/my.cnf
#
# Shipped within iRedMail project:
#   http://www.iredmail.org/
#

[mysqld]
#bind-address            = 127.0.0.1
bind-address            = 1.2.3.4
port                    = 3306
#default-character-set   = utf8
datadir                 = /var/lib/mysql
socket                  = /var/lib/mysql/mysql.sock
user                    = mysql

#general_log = /var/log/mysql.log

#slow_query_log
#slow_query_log_file = /var/log/mysql_slow.log

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

skip-external-locking
#skip-bdb
#skip-networking

query-cache-type    = 1
# Use up to 64Mb of RAM to store query results.
query-cache-size    = 64M
query_cache_limit   = 10M

thread_cache_size   = 8

#max_connections=1024
#max_connect_errors=150

# If you got error message like "The total number of locks exceeds the lock table size",
# please try to increase 'innodb_buffer_pool_size' to a reasonable value.
# Restarting MySQL server is required to make new value work.
# You can get current value with SQL command:
#
#   mysql> SHOW ENGINE INNODB STATUS \G
#          [...]
#          Buffer pool size   512
#          [...]
#
#innodb_buffer_pool_size=512M

#ssl-ca =
ssl-cert = /etc/pki/tls/certs/iRedMail_CA.pem
ssl-key = /etc/pki/tls/private/iRedMail.key
ssl-cipher = ALL

[client]
default-character-set=utf8

[mysql.server]
user        = mysql
basedir     = /var/lib

[mysqld_safe]
log-error   = /var/log/mysqld.log
pid-file    = /var/run/mysqld/mysqld.pid

The only change from the default iRedMail configuration file is the addition of:

bind-address            = 1.2.3.4

(Of course, 1.2.3.4 is not the actual IP address.)

And "skip-networking" is already commented out.

I thought the post at http://www.iredmail.org/forum/topic9218 … mysql.html might help, but there is no file at /etc/default/iptables and "iptables -L -n|grep 3306" returns nothing.

The problem is that I am not even able to connect to MySQL. I have already set up a remote user with the appropriate permissions through MySQL on the command line, so it's not a user issue.

The result of making the above change is that iRedAdmin-Pro stops working and outgoing mail is not delivered with a "address resolver failure" error.

Any suggestions for allowing remove access to MySQL under iRedMail?

Thanks.


Craig


Edit: Added the results of the above change to "my.cnf".

----

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

2 Reply by brix

  • brix
  • Member
  • Offline
  • Registered: 2014-01-22
  • Posts: 30

Re: Remote access to MySQL

Hello!
I think (please confirm, maybe I'm wrong) that you have to configure "bind-address = 0.0.0.0". That way, MySQL will bind to any IP on the server. Applications wants to go to 127.0.0.1, but you binded to public IP.

In CentOS iptables path is different, try to look in /etc/sysconfig/iptables and add the MySQL port there, and then restart the iptables.

And maybe because of the security, don't open MySQL port 3306 to all internet (you open door for scripts to try to login), but just your workstation (not so practical if you have dynamic IP)
Hope it helps! If you have any additional questions, just ask smile

3 Reply by craig

  • craig
  • Member
  • Offline
  • From: Canada
  • Registered: 2013-09-04
  • Posts: 272

Re: Remote access to MySQL

Hi Brix,

Thanks for your reply. Yes, I see now why access to MySQL was failing on the server after I made the configuration change. I made an incorrect assumption.

However, while binding MySQL to 0.0.0.0 keeps MySQL up, I still can't connect. As I said, there is nothing in iptables that references port 3306 or MySQL:

[00:44:22 root@host ~]# iptables -L -n|grep 3306
[00:44:28 root@host ~]# iptables -L -n|grep -i mysql
[00:44:34 root@host ~]# grep 3306 /etc/sysconfig/iptables
[00:44:49 root@host ~]# grep -i mysql /etc/sysconfig/iptables
[00:44:58 root@host ~]#

So what is preventing the connection?


Craig

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,165

Re: Remote access to MySQL

craig wrote:

I still can't connect.

What's the error message?

5 Reply by craig

  • craig
  • Member
  • Offline
  • From: Canada
  • Registered: 2013-09-04
  • Posts: 272

Re: Remote access to MySQL

ZhangHuangbin wrote:

What's the error message?

Can't connect to MySQL server on 'example.com' (10051)

As I said, it's not a user or permissions issue, it's actually not able to connect.


Craig

6 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,165

Re: Remote access to MySQL

*) Make sure mysql is running and listening on the address and port which can be accessible from external network.
*) Make sure you don't have firewall blocks the connection.

7 Reply by craig (edited by craig 2016-11-06 07:00:37)

  • craig
  • Member
  • Offline
  • From: Canada
  • Registered: 2013-09-04
  • Posts: 272

Re: Remote access to MySQL

ZhangHuangbin wrote:

*) Make sure mysql is running and listening on the address and port which can be accessible from external network.

I believe it is. Does the "my.cnf" above not show that? As I say, this is the default configuration, although I acknowledge it's the default configuration for an old version.

Does this help?:

[22:53:51 root@host ~]# netstat -a|grep 3306
[22:54:15 root@host ~]# netstat -a|grep -i mysql
tcp        0      0 *:mysql                     *:*                         LISTEN
tcp        0      0 localhost:mysql             localhost:53948             TIME_WAIT
tcp        0      0 localhost:53960             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:56996             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:53758             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53812             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:56998             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53792             ESTABLISHED
tcp        0      0 localhost:53968             localhost:mysql             TIME_WAIT
tcp        0      0 localhost:53932             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:53958             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53888             TIME_WAIT
tcp        0      0 localhost:mysql             localhost:53758             ESTABLISHED
tcp        0      0 localhost:53890             localhost:mysql             TIME_WAIT
tcp        0      0 localhost:mysql             localhost:53932             ESTABLISHED
tcp        0      0 localhost:53810             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:53764             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53958             ESTABLISHED
tcp        0      0 localhost:53812             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:53858             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53794             ESTABLISHED
tcp        0      0 localhost:53962             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53964             ESTABLISHED
tcp        0      0 localhost:53956             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:57048             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53814             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53816             ESTABLISHED
tcp        0      0 localhost:53762             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:53816             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:53814             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:53894             localhost:mysql             TIME_WAIT
tcp        0      0 localhost:56994             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:53876             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53764             ESTABLISHED
tcp        0      0 localhost:53852             localhost:mysql             TIME_WAIT
tcp        0      0 localhost:mysql             localhost:53856             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53790             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53796             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53810             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53960             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53876             ESTABLISHED
tcp        0      0 localhost:53760             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53956             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:56994             ESTABLISHED
tcp        0      0 localhost:53854             localhost:mysql             TIME_WAIT
tcp        0      0 localhost:53850             localhost:mysql             TIME_WAIT
tcp        0      0 localhost:mysql             localhost:56996             ESTABLISHED
tcp        0      0 localhost:53792             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:53794             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53760             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53962             ESTABLISHED
tcp        0      0 localhost:53796             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:56998             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:53856             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:53964             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:53790             localhost:mysql             ESTABLISHED
tcp        0      0 localhost:mysql             localhost:53762             ESTABLISHED
tcp        0      0 localhost:57048             localhost:mysql             ESTABLISHED
unix  2      [ ACC ]     STREAM     LISTENING     200008552 /var/lib/mysql/mysql.sock
[22:54:38 root@host ~]# netstat -ntpl|grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      21557/mysqld
[22:57:15 root@host ~]# netstat -ntpl|grep -i mysql
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      21557/mysqld
[22:57:20 root@host ~]#
ZhangHuangbin wrote:

*) Make sure you don't have firewall blocks the connection.

There doesn't seem to be anything blocking it on the server (see the output of "iptables" and "grep" in my last message) and there's nothing blocking it on the client end either (I use HeidiSQL with no problem on other servers in the same data centre).


Craig

8 Reply by craig

  • craig
  • Member
  • Offline
  • From: Canada
  • Registered: 2013-09-04
  • Posts: 272

Re: Remote access to MySQL

Hi Zhang,

No further feedback on this? Is this intended behaviour of a typical iRedMail installation?


Craig

9 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,165

Re: Remote access to MySQL

If MySQL is running and no firewall blocks the port (3306) on iRedMail server, you have to check firewall sites between iRedMail server and your mysql client software, and double check the server address used in your mysql client software.

10 Reply by craig

  • craig
  • Member
  • Offline
  • From: Canada
  • Registered: 2013-09-04
  • Posts: 272

Re: Remote access to MySQL

ZhangHuangbin wrote:

If MySQL is running and no firewall blocks the port (3306) on iRedMail server, you have to check firewall sites between iRedMail server and your mysql client software, and double check the server address used in your mysql client software.

I can assure you that all of those have been checked and are not the causes, as I've described in my posts above. Given the information I have, this appears to me to be a MySQL configuration issue, and it's using the default iRedMail configuration.

I can't be the only person ever to have this issue. Like I say, I connect with no problem to MySQL on other servers in the same data centre (and other data centres), except the iRedMail server.


Craig

11 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,165

Re: Remote access to MySQL

Your mysql is listening on '0.0.0.0:3306', and no firewall blocks this port, so it's supposed to be accessible from external network. The only possibility is the firewall between you and mysql server.

You didn't paste any original error message you got in your mysql client software, and you said "not able to connect". I wonder what it means.

Can you connect to server via IP/hostname? for example, test with 'telnet' program. If you cannot connect, that's a network issue.

If you can connect to sql server, but cannot login, you may need to check the granted host for the sql username you use to make sure this sql user has privilege to login. e.g.

USE mysql;
SELECT User,Host FROM user;

12 Reply by craig

  • craig
  • Member
  • Offline
  • From: Canada
  • Registered: 2013-09-04
  • Posts: 272

Re: Remote access to MySQL

Hi Zhang,

Thanks for your reply. Actually, I did post the error message I get when I try to connect. It's up there in message 5:

Can't connect to MySQL server on 'sub.example.com' (10051)

Yes, I can connect via SSH to the server with no problems. Here is the output of attempts with Telnet:

[21:31:19 user@host ~]$ ping -c 4 sub.example.com
PING sub.example.com (1.2.3.4) 56(84) bytes of data.
64 bytes from sub.example.com (1.2.3.4): icmp_seq=1 ttl=53 time=148 ms
64 bytes from sub.example.com (1.2.3.4): icmp_seq=2 ttl=53 time=151 ms
64 bytes from sub.example.com (1.2.3.4): icmp_seq=3 ttl=53 time=149 ms
64 bytes from sub.example.com (1.2.3.4): icmp_seq=4 ttl=53 time=147 ms

--- sub.example.com ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3003ms
rtt min/avg/max/mdev = 147.532/149.399/151.891/1.573 ms
[21:31:35 user@host ~]$ telnet sub.example.com
Trying 1.2.3.4...
Trying 1111:2222::3333:4444:5555:6666...
telnet: Unable to connect to remote host: Network is unreachable
[21:34:05 user@host ~]$ telnet sub.example.com 3306
Trying 1.2.3.4...
Trying 1111:2222::3333:4444:5555:6666...
telnet: Unable to connect to remote host: Network is unreachable
[21:36:59 user@host ~]$ telnet sub.example.com 587
Trying 1.2.3.4...
Connected to sub.example.com.
Escape character is '^]'.
220 sub.example.com ESMTP Postfix
^]

telnet> quit
Connection closed.
[21:37:51 user@host ~]$

Of course, the default Telnet port (23) is closed, so that fails. Port 587 works because that's open to allow mail submission. Port 3306 fails, but I can connect to other servers in the same data centre (and others), so port 3306 is not blocked at the client and it is not blocked at the data centre (Linode). That only leaves the block being on the server, but as I've already posted, it's not blocked by iptables:

[00:44:22 root@host ~]# iptables -L -n|grep 3306
[00:44:28 root@host ~]# iptables -L -n|grep -i mysql
[00:44:34 root@host ~]# grep 3306 /etc/sysconfig/iptables
[00:44:49 root@host ~]# grep -i mysql /etc/sysconfig/iptables
[00:44:58 root@host ~]#

The error message I get on trying to connect is not the same as the error message I would get if the user or the client IP address was not authorised in MySQL. I don't remember exactly what that error message says in that case, but I have seen it before in other situations. It is because I am not getting that error message that refers to the client IP address or the user I am trying to log in as that I come to the conclusion that I am not even connecting to the MySQL server. Getting an error that refers to the user or the client's IP address implies I'm connecting, but just not able to log in. I am not getting that.

ZhangHuangbin wrote:
USE mysql;
SELECT User,Host FROM user;
mysql> select user,host from user order by host;
+-------------+--------------------+
| user        | host               |
+-------------+--------------------+
| root        | 127.0.0.1          |
| remoteuser  | 55.44.33.22        |
| amavisd     | localhost          |
| iredadmin   | localhost          |
| remoteuser  | localhost          |
| policyd     | localhost          |
| root        | localhost          |
| roundcube   | localhost          |
| vmail       | localhost          |
| vmailadmin  | localhost          |
| root        | sub.example.com    |
+-------------+--------------------+
11 rows in set (0.00 sec)

I have double- and triple-checked and 55.44.33.22 is the IP address of the client, and I am trying to log in as "remoteuser". However, the client never gets a chance to try and log in because it can't connect.

I hope that provides enough additional information for troubleshooting. Thanks.


Craig

13 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,165

Re: Remote access to MySQL

craig wrote:

[21:34:05 user@host ~]$ telnet sub.example.com 3306
Trying 1.2.3.4...
Trying 1111:2222::3333:4444:5555:6666...
telnet: Unable to connect to remote host: Network is unreachable

With this command output, it's clear that there's something wrong with the network between your mysql client software and iRedMail server.

If port 3306 is accessible from your laptop (the mysql client software), you will get message LIKE this:

$ telnet u16 3306
Trying 172.16.100.144...
Connected to u16.
Escape character is '^]'.
EHost '172.16.100.1' is not allowed to connect to this MySQL serverConnection closed by foreign host.

Stop iptables and fail2ban services on your server, then try again. If it doesn't work, I'm afraid that i cannot help more in this case.

14 Reply by craig (edited by craig 2016-11-11 06:53:30)

  • craig
  • Member
  • Offline
  • From: Canada
  • Registered: 2013-09-04
  • Posts: 272

Re: Remote access to MySQL

Hi Zhang,

Thanks for your continuing help. Much appreciated.

OK, some progress. Stopping iptables does fix the problem and allow "remoteuser" in from my IP address. I also tried logging in as "remoteuser" from another VPS in the same data centre while iptables was stopped, and I got the kind of error message that I expected:

ERROR 1130 (00000): Host 'sub.otherserver.net' is not allowed to connect to this MySQL server

That's unrelated really, but I add it to make clear that the user and permissions on the iRedMail MySQL server are indeed set correctly for "remoteuser" and *my* IP address.

But given this ...:

[00:44:22 root@host ~]# iptables -L -n|grep 3306
[00:44:28 root@host ~]# iptables -L -n|grep -i mysql
[00:44:34 root@host ~]# grep 3306 /etc/sysconfig/iptables
[00:44:49 root@host ~]# grep -i mysql /etc/sysconfig/iptables
[00:44:58 root@host ~]#

... and the fact that my own (client) IP address is not blocked ...:

[22:17:52 root@host ~]# iptables -L -n|grep 55.44.33.22
[22:18:10 root@host ~]#

... what in iptables could be blocking access to MySQL?


Craig

15 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,165

Re: Remote access to MySQL

If you're using default iRedMail iptables rule, we use the policy "block all, then explicitly allow some ports". You may want to tune it to match your need. for example, add a rule in iptables to explicitly allow remote access from some IP addresses.