无法使用 MySQL 连接到远程主机...防火墙问题?

无法使用 MySQL 连接到远程主机...防火墙问题?

我已授予thufir通过 shell 进行远程访问的权限(正确吗?),但似乎无法实际连接。这路由器防火墙已将 SPI 关闭。我已经禁用了 IPv6 防火墙路由器, 还。

在服务器上:

linux-k7qk:~ # 
linux-k7qk:~ # cat /etc/my.cnf
# Basic ViciBox my.cnf for a quad-core 8-GB RAM or so
# Use the dedicate my-big.cnf for 16+GB RAM and 8+ Cores
#

# The following options will be passed to all MySQL clients
[client]
#password   = your_password
port        = 3306
socket      = /var/run/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3306
socket      = /var/run/mysql/mysql.sock
datadir = /var/lib/mysql
skip-external-locking
key_buffer_size = 640M
max_allowed_packet = 2M
table_open_cache = 8192
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 64M
thread_cache_size = 50
query_cache_size = 0
thread_concurrency=8
skip-name-resolve
connect_timeout=60
long_query_time=3
log_slow_queries
max_connections=768
open_files_limit=24576
max_heap_table_size=32M
expire_logs_days=3
default-storage-engine=MyISAM
table_definition_cache=8192
table_cache=8192
concurrent_insert=2
myisam_recover
myisam_repair_threads=1
tmpdir          = /tmp/


# This will disable networking
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

# The safe_mysqld script
[safe_mysqld]
log-error   = /var/log/mysql/mysqld.log
socket      = /var/run/mysql/mysql.sock

[mysqldump]
socket      = /var/run/mysql/mysql.sock
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
# user       = multi_admin
# password   = secret

# If you want to use mysqld_multi uncomment 1 or more mysqld sections
# below or add your own ones.

# WARNING
# --------
# If you uncomment mysqld1 than make absolutely sure, that database mysql,
# configured above, is not started.  This may result in corrupted data!
# [mysqld1]
# port       = 3306
# datadir    = /var/lib/mysql
# pid-file   = /var/lib/mysql/mysqld.pid
# socket     = /var/lib/mysql/mysql.sock
# user       = mysql

# [mysqld2]
# port       = 3307
# datadir    = /var/lib/mysql-databases/mysqld2
# pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld2/mysql.sock
# user       = mysql

# [mysqld3]
# port       = 3308
# datadir    = /var/lib/mysql-databases/mysqld3
# pid-file   = /var/lib/mysql-databases/mysqld3/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld3/mysql.sock
# user       = mysql

# [mysqld6]
# port       = 3309
# datadir    = /var/lib/mysql-databases/mysqld6
# pid-file   = /var/lib/mysql-databases/mysqld6/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld6/mysql.sock
# user       = mysql

linux-k7qk:~ # 
linux-k7qk:~ # 
linux-k7qk:~ # mysql -u root mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1002
Server version: 5.5.33-MariaDB-log openSUSE package

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mysql]> 
MariaDB [mysql]> GRANT ALL ON *.* TO `thufir`@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> select host, user, grant_priv from user;
+------------+--------+------------+
| host       | user   | grant_priv |
+------------+--------+------------+
| localhost  | root   | Y          |
| linux-k7qk | root   | Y          |
| 127.0.0.1  | root   | Y          |
| ::1        | root   | Y          |
| localhost  |        | N          |
| linux-k7qk |        | N          |
| %          | cron   | N          |
| localhost  | cron   | N          |
| localhost  | custom | N          |
| %          | custom | N          |
| %          | slave  | N          |
| %          | root   | N          |
| %          | thufir | N          |
+------------+--------+------------+
13 rows in set (0.00 sec)

MariaDB [mysql]> 

并从 Mac 远程连接:

Darrens-MacBook-Pro:~ thufir$ 
Darrens-MacBook-Pro:~ thufir$ ssh [email protected]
Password: 
Last login: Sun Mar 22 18:36:13 2015 from 192.168.0.28
Thank you for installing ViciBox Server v.6.0!
This software is available for free download at
http://www.vicibox.com. If you paid for this 
software you have been ripped off. Please report
any fraud or abuses of this software to 
[email protected]. Please report any bugs on 
the forum at http://www.vicidial.org

To configure the LAN settings type:
yast lan

To change the server IP in the database type:
/usr/share/astguiclient/ADMIN_update_server_ip.pl

Official paid-for ViciDial support is available at 
http://www.vicidial.com

Free community-based ViciDial Support is available
at http://www.vicidial.org/VICIDIALforum

- ViciBox Redux v.6.0.3-141118
linux-k7qk:~ # 
linux-k7qk:~ # exit
logout
Connection to 192.168.0.99 closed.
Darrens-MacBook-Pro:~ thufir$ 
Darrens-MacBook-Pro:~ thufir$ mysql -u thufir -p -h 192.168.0.99
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.99' (60)
Darrens-MacBook-Pro:~ thufir$ 

我确信关键的 ssh 条目最终会更好,但目前只想从 mac 连接到 MySQL 指定主机的服务器。

netstat我在Mac上没有,但是从服务器上:

linux-k7qk:~ # 
linux-k7qk:~ # 
linux-k7qk:~ # netstat localhost
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 192.168.0.99:ssh        192.168.0.28:50568      ESTABLISHED 
udp        0      0 localhost:9743          localhost:iax           ESTABLISHED 
udp        0      0 localhost:35582         localhost:iax           ESTABLISHED 
udp        0      0 localhost:15492         localhost:iax           ESTABLISHED 
Active UNIX domain sockets (w/o servers)
Proto RefCnt Flags       Type       State         I-Node Path
unix  2      [ ]         DGRAM                    8587   /var/lib/dhcp/dev/log
unix  2      [ ]         DGRAM                    1620   /run/systemd/journal/syslog
unix  2      [ ]         DGRAM                    1638   /run/systemd/shutdownd
unix  2      [ ]         DGRAM                    1571   @/org/freedesktop/systemd1/notify
unix  6      [ ]         DGRAM                    1653   /run/systemd/journal/socket
unix  14     [ ]         DGRAM                    1655   /dev/log
unix  2      [ ]         DGRAM                    8588   /var/lib/dhcp6/dev/log
unix  2      [ ]         DGRAM                    12252  @/org/freedesktop/systemd1/notify/13227399042924374081
unix  2      [ ]         DGRAM                    8589   /var/lib/named/dev/log
unix  3      [ ]         STREAM     CONNECTED     11039  
unix  3      [ ]         STREAM     CONNECTED     100750 
unix  2      [ ]         DGRAM                    13431  
unix  3      [ ]         STREAM     CONNECTED     11080  
unix  3      [ ]         STREAM     CONNECTED     14473  /var/run/mysql/mysql.sock
unix  3      [ ]         STREAM     CONNECTED     11082  
unix  3      [ ]         STREAM     CONNECTED     296    
unix  3      [ ]         STREAM     CONNECTED     11045  
unix  3      [ ]         STREAM     CONNECTED     11059  
unix  2      [ ]         DGRAM                    1699   
unix  3      [ ]         STREAM     CONNECTED     11061  
unix  3      [ ]         STREAM     CONNECTED     17515  
unix  3      [ ]         STREAM     CONNECTED     8605   /run/dbus/system_bus_socket
unix  3      [ ]         STREAM     CONNECTED     16532  
unix  3      [ ]         STREAM     CONNECTED     11052  
unix  3      [ ]         STREAM     CONNECTED     13429  
unix  3      [ ]         STREAM     CONNECTED     14472  
unix  3      [ ]         STREAM     CONNECTED     11056  
unix  3      [ ]         STREAM     CONNECTED     6051   /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     11058  
unix  3      [ ]         STREAM     CONNECTED     8602   
unix  3      [ ]         DGRAM                    5312   
unix  3      [ ]         STREAM     CONNECTED     11074  
unix  3      [ ]         STREAM     CONNECTED     12312  /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     11085  
unix  3      [ ]         STREAM     CONNECTED     11098  
unix  3      [ ]         STREAM     CONNECTED     11067  
unix  3      [ ]         STREAM     CONNECTED     11050  
unix  3      [ ]         STREAM     CONNECTED     11083  
unix  3      [ ]         STREAM     CONNECTED     11065  
unix  3      [ ]         STREAM     CONNECTED     11186  
unix  2      [ ]         DGRAM                    11280  
unix  2      [ ]         DGRAM                    11108  
unix  3      [ ]         STREAM     CONNECTED     11103  
unix  3      [ ]         STREAM     CONNECTED     6105   /run/dbus/system_bus_socket
unix  3      [ ]         STREAM     CONNECTED     100751 /var/run/mysql/mysql.sock
unix  3      [ ]         STREAM     CONNECTED     17516  /var/run/mysql/mysql.sock
unix  3      [ ]         STREAM     CONNECTED     12629  /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     11044  
unix  3      [ ]         DGRAM                    5313   
unix  3      [ ]         STREAM     CONNECTED     5848   
unix  3      [ ]         STREAM     CONNECTED     11100  
unix  2      [ ]         DGRAM                    18409  
unix  3      [ ]         STREAM     CONNECTED     7935   /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     11089  
unix  3      [ ]         STREAM     CONNECTED     11091  
unix  3      [ ]         STREAM     CONNECTED     11076  
unix  3      [ ]         STREAM     CONNECTED     9789   
unix  2      [ ]         DGRAM                    62341  
unix  3      [ ]         STREAM     CONNECTED     9660   
unix  2      [ ]         DGRAM                    12160  
unix  3      [ ]         STREAM     CONNECTED     11047  
unix  3      [ ]         STREAM     CONNECTED     14626  /var/run/mysql/mysql.sock
unix  3      [ ]         STREAM     CONNECTED     11079  
unix  3      [ ]         STREAM     CONNECTED     8603   
unix  2      [ ]         DGRAM                    5304   
unix  3      [ ]         STREAM     CONNECTED     14627  /var/run/mysql/mysql.sock
unix  3      [ ]         STREAM     CONNECTED     11053  
unix  2      [ ]         DGRAM                    15581  
unix  3      [ ]         STREAM     CONNECTED     11046  
unix  2      [ ]         DGRAM                    688    
unix  2      [ ]         DGRAM                    12251  
unix  3      [ ]         STREAM     CONNECTED     11062  
unix  3      [ ]         STREAM     CONNECTED     11055  
unix  3      [ ]         STREAM     CONNECTED     11071  
unix  2      [ ]         DGRAM                    82071  
unix  2      [ ]         DGRAM                    17459  
unix  3      [ ]         STREAM     CONNECTED     11073  
unix  3      [ ]         STREAM     CONNECTED     11086  
unix  3      [ ]         STREAM     CONNECTED     11097  
unix  3      [ ]         STREAM     CONNECTED     11042  
unix  3      [ ]         STREAM     CONNECTED     11068  
unix  3      [ ]         STREAM     CONNECTED     11095  
unix  3      [ ]         STREAM     CONNECTED     11070  
unix  3      [ ]         STREAM     CONNECTED     11064  
unix  3      [ ]         STREAM     CONNECTED     11049  
unix  2      [ ]         DGRAM                    11189  
unix  3      [ ]         STREAM     CONNECTED     17520  
unix  3      [ ]         STREAM     CONNECTED     12253  
unix  3      [ ]         STREAM     CONNECTED     11104  
unix  3      [ ]         STREAM     CONNECTED     8604   /run/dbus/system_bus_socket
unix  2      [ ]         DGRAM                    62349  
unix  3      [ ]         STREAM     CONNECTED     6862   /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     11101  
unix  3      [ ]         STREAM     CONNECTED     11040  
unix  3      [ ]         STREAM     CONNECTED     12314  /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     11041  
unix  3      [ ]         STREAM     CONNECTED     11092  
unix  3      [ ]         STREAM     CONNECTED     11077  
unix  3      [ ]         STREAM     CONNECTED     11687  
unix  3      [ ]         STREAM     CONNECTED     9783   
unix  3      [ ]         STREAM     CONNECTED     11094  
unix  2      [ ]         DGRAM                    9784   
unix  3      [ ]         STREAM     CONNECTED     11088  
linux-k7qk:~ # 

我认为这是一个防火墙问题:

Darrens-MacBook-Pro:~ thufir$ 
Darrens-MacBook-Pro:~ thufir$ telnet 192.168.0.99 3306
Trying 192.168.0.99...
telnet: connect to address 192.168.0.99: Operation timed out
telnet: Unable to connect to remote host
Darrens-MacBook-Pro:~ thufir$ 

我还能做些什么来缩小确切的问题范围?

-------------------------------------------------- - - - - - - - - - - - - 更新

以下是正在侦听的端口:

linux-k7qk:~ # 
linux-k7qk:~ # netstat -a | grep LISTEN
tcp        0      0 *:ssh                   *:*                     LISTEN      
tcp        0      0 *:40569                 *:*                     LISTEN      
tcp        0      0 localhost:smtp          *:*                     LISTEN      
tcp        0      0 *:4577                  *:*                     LISTEN      
tcp        0      0 *:41569                 *:*                     LISTEN      
tcp        0      0 *:42569                 *:*                     LISTEN      
tcp        0      0 *:mysql                 *:*                     LISTEN      
tcp        0      0 *:5038                  *:*                     LISTEN      
tcp        0      0 *:ssh                   *:*                     LISTEN      
tcp        0      0 localhost:smtp          *:*                     LISTEN      
tcp        0      0 *:https                 *:*                     LISTEN      
tcp        0      0 *:www-http              *:*                     LISTEN      
unix  2      [ ACC ]     STREAM     LISTENING     10152  private/verify
unix  2      [ ACC ]     STREAM     LISTENING     10155  public/flush
unix  2      [ ACC ]     STREAM     LISTENING     10158  private/proxymap
unix  2      [ ACC ]     STREAM     LISTENING     10161  private/proxywrite
unix  2      [ ACC ]     STREAM     LISTENING     10164  private/smtp
unix  2      [ ACC ]     STREAM     LISTENING     10167  private/relay
unix  2      [ ACC ]     STREAM     LISTENING     10170  public/showq
unix  2      [ ACC ]     STREAM     LISTENING     10173  private/error
unix  2      [ ACC ]     STREAM     LISTENING     10176  private/retry
unix  2      [ ACC ]     STREAM     LISTENING     10179  private/discard
unix  2      [ ACC ]     STREAM     LISTENING     10182  private/local
unix  2      [ ACC ]     STREAM     LISTENING     12810  /var/run/screens/S-root/4239.ASTVDadFILL
unix  2      [ ACC ]     STREAM     LISTENING     10185  private/virtual
unix  2      [ ACC ]     STREAM     LISTENING     10188  private/lmtp
unix  2      [ ACC ]     STREAM     LISTENING     10191  private/anvil
unix  2      [ ACC ]     STREAM     LISTENING     10194  private/scache
unix  2      [ ACC ]     STREAM     LISTENING     1555   /run/lvm/lvmetad.socket
unix  2      [ ACC ]     SEQPACKET  LISTENING     1559   /run/udev/control
unix  2      [ ACC ]     STREAM     LISTENING     1566   /run/systemd/journal/stdout
unix  2      [ ACC ]     STREAM     LISTENING     14901  /var/run/screens/S-root/4230.ASTVDadapt
unix  2      [ ACC ]     STREAM     LISTENING     14931  /var/run/screens/S-root/4255.ASTemail
unix  2      [ ACC ]     STREAM     LISTENING     12639  /var/run/screens/S-root/3543.asterisk
unix  2      [ ACC ]     STREAM     LISTENING     12656  /run/asterisk/asterisk.ctl
unix  2      [ ACC ]     STREAM     LISTENING     5747   /run/dbus/system_bus_socket
unix  2      [ ACC ]     STREAM     LISTENING     5800   /var/run/nscd/socket
unix  2      [ ACC ]     STREAM     LISTENING     13500  /var/run/mysql/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     1487   /run/systemd/private
unix  2      [ ACC ]     STREAM     LISTENING     10966  /var/run/screens/S-root/4234.ASTfastlog
unix  2      [ ACC ]     STREAM     LISTENING     10135  public/cleanup
unix  2      [ ACC ]     STREAM     LISTENING     10745  /run/user/0/systemd/private
unix  2      [ ACC ]     STREAM     LISTENING     10140  private/rewrite
unix  2      [ ACC ]     STREAM     LISTENING     10143  private/bounce
unix  2      [ ACC ]     STREAM     LISTENING     10146  private/defer
unix  2      [ ACC ]     STREAM     LISTENING     10149  private/trace
You have new mail in /var/mail/root
linux-k7qk:~ # 

相关内容