0

I need to configure mysql so I can connect to it remotely. Here is part of /etc/mysql/my.cnf file:

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# 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
bind-address        = 0.0.0.0
#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

I also tried to comment out all bind-address directives but it didn't solve the problem.

Output of netstat -tlnep command:

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       User       Inode       PID/Program name
tcp        0      0 0.0.0.0:21              0.0.0.0:*               LISTEN      0          3159847     6038/vsftpd
tcp        0      0 0.0.0.0:25              0.0.0.0:*               LISTEN      0          10112       1372/master
tcp        0      0 0.0.0.0:443             0.0.0.0:*               LISTEN      0          15680697    53108/nginx: worker
tcp        0      0 0.0.0.0:2277            0.0.0.0:*               LISTEN      0          11247       1109/sshd
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      105        23269036    57272/mysqld
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      0          15680696    53108/nginx: worker
tcp6       0      0 :::25                   :::*                    LISTEN      0          10113       1372/master
tcp6       0      0 :::443                  :::*                    LISTEN      0          15680698    53108/nginx: worker
tcp6       0      0 :::2277                 :::*                    LISTEN      0          11249       1109/sshd

Now I can connect to mysql from localhost, but can't connect from remote host. When connecting from remote host I get an error: ERROR 2003 (HY000): Can't connect to MySQL server on '<my server ip address>' (110)

Telnet also fails connecting to server ip on port 3306.

Some system info:

uname -a
Linux webdevgranat 3.13.0-24-generic #47-Ubuntu SMP Fri May 2 23:30:00 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

iptables -S
-P INPUT ACCEPT
-P FORWARD ACCEPT
-P OUTPUT ACCEPT


mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.5.54-0ubuntu0.14.04.1 |
+-------------------------+
1 row in set (0.00 sec)

Also when I try scan for open ports (for example, here), I get 3306 port marked not closed but filtered.

enter image description here

ufw firewall is disabled.

  • Are you able to connect from a computer on the same subnet as the mysql server? – L.Ray Apr 23 '18 at 12:41
  • @l-ray unfortunately, I can't check this 'cause I have no computer that's on the same local network with server – hungl Apr 23 '18 at 13:27
  • Are you using a residential internet service? Many ISPs block specific ports for residential customers. Do you have/can you install tcpdump for troubleshooting? – L.Ray Apr 23 '18 at 17:20
0
  1. If your MySQL server will listen on "0.0.0.0:3306" it is OK.
  2. You must have allowed connections to you database server on it port in firewall.
  3. You must have granted correct privileges for the database user on MySQL server.

How you have connected this machine with MySQL to the Internet? It is a VPS or other server with public IP?

  • It's a dedicated server with public IP. I have granted privileges to mysql user to connect not only from localhost. How should I allow connections to my database in firewall? With iptables? – hungl Apr 23 '18 at 9:15
  • 1
    Yes, you can do it using iptables on this machine but you have must in mind, If this dedicated server stay behind firewall of your hosting provider / DC you probably must use their panel to open required ports. – mariaczi Apr 23 '18 at 9:19
  • Questions should be asked in comments, not in your answer. – Hauke Laging Apr 23 '18 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.