如果我尝试连接到MySQL
telnet myhost.com 3306 我得到一个network not reachable错误，即使我刷新所有的iptables规则。 我可以到达其他端口，如ssh （与ssh相同的iptables规则适用）。
以下是我在netstat看到的。 这给我的印象是有更多的访问端口比iptables ：端口像10025在我的iptablesclosures。
我看到MySQL打开tcp但不是tcp6（ssh打开两个）。
这个问题从Ubuntu服务器12.04升级到14.04之后开始。 任何有关连接超时的build议？
$ netstat -tlp 3306 (No info could be read for "-p": geteuid()=1000 but you should be root.) Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 *:pop3s *:* LISTEN - tcp 0 0 localhost:10023 *:* LISTEN - tcp 0 0 localhost:10024 *:* LISTEN - tcp 0 0 localhost:10025 *:* LISTEN - tcp 0 0 *:mysql *:* LISTEN - tcp 0 0 *:pop3 *:* LISTEN - tcp 0 0 localhost:spamd *:* LISTEN - tcp 0 0 *:imap2 *:* LISTEN - tcp 0 0 *:urd *:* LISTEN - tcp 0 0 *:smtp *:* LISTEN - tcp 0 0 *:sieve *:* LISTEN - tcp 0 0 *:ssh *:* LISTEN - tcp 0 0 *:imaps *:* LISTEN - tcp6 0 0 [::]:pop3s [::]:* LISTEN - tcp6 0 0 localhost:10023 [::]:* LISTEN - tcp6 0 0 [::]:pop3 [::]:* LISTEN - tcp6 0 0 localhost:spamd [::]:* LISTEN - tcp6 0 0 [::]:imap2 [::]:* LISTEN - tcp6 0 0 [::]:http [::]:* LISTEN - tcp6 0 0 [::]:urd [::]:* LISTEN - tcp6 0 0 [::]:smtp [::]:* LISTEN - tcp6 0 0 [::]:https [::]:* LISTEN - tcp6 0 0 [::]:sieve [::]:* LISTEN - tcp6 0 0 [::]:ssh [::]:* LISTEN - tcp6 0 0 [::]:imaps [::]:* LISTEN - 150407 12:31:07 [Note] /usr/sbin/mysqld: Normal shutdown 150407 12:31:07 [Note] Event Scheduler: Purging the queue. 0 events 150407 12:31:07 InnoDB: Starting shutdown... 150407 12:31:10 InnoDB: Shutdown completed; log sequence number 574674933 150407 12:31:10 [Note] /usr/sbin/mysqld: Shutdown complete 150407 12:31:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 150407 12:31:11 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead. 150407 12:31:12 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead. 150407 12:31:12 [Note] Plugin 'FEDERATED' is disabled. 150407 12:31:12 InnoDB: The InnoDB memory heap is disabled 150407 12:31:12 InnoDB: Mutexes and rw_locks use GCC atomic builtins 150407 12:31:12 InnoDB: Compressed tables use zlib 1.2.8 150407 12:31:12 InnoDB: Using Linux native AIO 150407 12:31:12 InnoDB: Initializing buffer pool, size = 128.0M 150407 12:31:12 InnoDB: Completed initialization of buffer pool 150407 12:31:12 InnoDB: highest supported file format is Barracuda. 150407 12:31:12 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead. 150407 12:31:12 [Note] Plugin 'FEDERATED' is disabled. 150407 12:31:12 InnoDB: The InnoDB memory heap is disabled 150407 12:31:12 InnoDB: Mutexes and rw_locks use GCC atomic builtins 150407 12:31:12 InnoDB: Compressed tables use zlib 1.2.8 150407 12:31:12 InnoDB: Using Linux native AIO 150407 12:31:12 InnoDB: Initializing buffer pool, size = 128.0M 150407 12:31:12 InnoDB: Completed initialization of buffer pool InnoDB: Unable to lock ./ibdata1, error: 11 InnoDB: Check that you do not already have another mysqld process InnoDB: using the same InnoDB data or log files. 150407 12:31:12 InnoDB: Retrying to lock the first data file InnoDB: Unable to lock ./ibdata1, error: 11 InnoDB: Check that you do not already have another mysqld process InnoDB: using the same InnoDB data or log files. 150407 12:31:13 InnoDB: Waiting for the background threads to start InnoDB: Unable to lock ./ibdata1, error: 11 InnoDB: Check that you do not already have another mysqld process InnoDB: using the same InnoDB data or log files. 150407 12:31:14 InnoDB: 5.5.41 started; log sequence number 574674933 150407 12:31:14 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 150407 12:31:14 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 150407 12:31:14 [Note] Server socket created on IP: '0.0.0.0'. 150407 12:31:15 [Note] Event Scheduler: Loaded 0 events 150407 12:31:15 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.41-0ubuntu0.14.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu) InnoDB: Unable to lock ./ibdata1, error: 11 InnoDB: Check that you do not already have another mysqld process InnoDB: using the same InnoDB data or log files. ... InnoDB: Unable to lock ./ibdata1, error: 11 InnoDB: Check that you do not already have another mysqld process InnoDB: using the same InnoDB data or log files. 150407 12:32:52 InnoDB: Unable to open the first data file InnoDB: Error in opening ./ibdata1 150407 12:32:52 InnoDB: Operating system error number 11 in a file operation. InnoDB: Error number 11 means 'Resource temporarily unavailable'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html 150407 12:32:52 InnoDB: Could not open or create data files. 150407 12:32:52 InnoDB: If you tried to add new data files, and it failed here, 150407 12:32:52 InnoDB: you should now edit innodb_data_file_path in my.cnf back 150407 12:32:52 InnoDB: to what it was, and remove the new ibdata files InnoDB created 150407 12:32:52 InnoDB: in this failed attempt. InnoDB only wrote those files full of 150407 12:32:52 InnoDB: zeros, but did not yet use them in any way. But be careful: do not 150407 12:32:52 InnoDB: remove old data files which contain your precious data! 150407 12:32:52 [ERROR] Plugin 'InnoDB' init function returned error. 150407 12:32:52 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 150407 12:32:52 [ERROR] Unknown/unsupported storage engine: InnoDB 150407 12:32:52 [ERROR] Aborting 150407 12:32:52 [Note] /usr/sbin/mysqld: Shutdown complete 150407 12:32:52 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead. 150407 12:32:52 [Note] Plugin 'FEDERATED' is disabled. ......
在将bind-address更改为0.0.0.0之后，输出netstat：
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 18890/mysqld
输出挖掘：
; <<>> DiG 9.9.5-3ubuntu0.2-Ubuntu <<>> myhost.com ip r get 123.45.67.890 telnet 123.45.67.890 3306 ;; global options: +cmd ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 55636 ;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1 ;; OPT PSEUDOSECTION: ; EDNS: version: 0, flags:; udp: 4000 ;; QUESTION SECTION: ;myhost.com. IN A ;; ANSWER SECTION: myhost.com. 3600 IN A 123.45.67.890 ;; Query time: 856 msec ;; SERVER: 127.0.1.1#53(127.0.1.1) ;; WHEN: Tue Apr 07 22:55:03 CEST 2015 ;; MSG SIZE rcvd: 60 ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 35733 ;; flags: qr rd ra; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 1 ;; OPT PSEUDOSECTION: ; EDNS: version: 0, flags:; udp: 4000 ;; QUESTION SECTION: ;ip. IN A ;; AUTHORITY SECTION: . 528 IN SOA a.root-servers.net. nstld.verisign-grs.com. 2015040701 1800 900 604800 86400 ;; Query time: 159 msec ;; SERVER: 127.0.1.1#53(127.0.1.1) ;; WHEN: Tue Apr 07 22:55:03 CEST 2015 ;; MSG SIZE rcvd: 106 ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 17760 ;; flags: qr rd ra; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 1 ;; OPT PSEUDOSECTION: ; EDNS: version: 0, flags:; udp: 4000 ;; QUESTION SECTION: ;r. IN A ;; AUTHORITY SECTION: . 528 IN SOA a.root-servers.net. nstld.verisign-grs.com. 2015040701 1800 900 604800 86400 ;; Query time: 55 msec ;; SERVER: 127.0.1.1#53(127.0.1.1) ;; WHEN: Tue Apr 07 22:55:03 CEST 2015 ;; MSG SIZE rcvd: 105 ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 20236 ;; flags: qr rd ra; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 1 ;; OPT PSEUDOSECTION: ; EDNS: version: 0, flags:; udp: 4000 ;; QUESTION SECTION: ;get. IN A ;; AUTHORITY SECTION: . 527 IN SOA a.root-servers.net. nstld.verisign-grs.com. 2015040701 1800 900 604800 86400 ;; Query time: 62 msec ;; SERVER: 127.0.1.1#53(127.0.1.1) ;; WHEN: Tue Apr 07 22:55:04 CEST 2015 ;; MSG SIZE rcvd: 107 ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 29568 ;; flags: qr aa rd ra ad; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 0 ;; QUESTION SECTION: ;123.45.67.890. IN A ;; ANSWER SECTION: 123.45.67.890. 0 IN A 123.45.67.890 ;; Query time: 0 msec ;; SERVER: 127.0.1.1#53(127.0.1.1)
/etc/mysql/my.cnf：
# # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [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 [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/
路由跟踪：
1 192.168.1.1 (192.168.1.1) 4.728 ms 4.720 ms 4.707 ms 2 1.16.15.37.dynamic.jazztel.es (37.15.16.1) 26.522 ms 26.529 ms 28.352 ms 3 10.255.160.254 (10.255.160.254) 30.024 ms 30.017 ms 29.987 ms 4 41.217.106.212.static.jazztel.es (212.106.217.41) 44.086 ms 45.217.106.212.static.jazztel.es (212.106.217.45) 52.257 ms 41.217.106.212.static.jazztel.es (212.106.217.41) 42.428 ms 5 * 42.217.106.212.static.jazztel.es (212.106.217.42) 47.672 ms 52.229 ms 6 129.216.106.212.static.jazztel.es (212.106.216.129) 57.838 ms 61.308 ms * 7 142.216.106.212.static.jazztel.es (212.106.216.142) 89.549 ms 106.063 ms * 8 142.216.106.212.static.jazztel.es (212.106.216.142) 76.570 ms 195.66.225.53 (195.66.225.53) 87.575 ms 142.216.106.212.static.jazztel.es (212.106.216.142) 84.337 ms 9 195.66.225.53 (195.66.225.53) 106.011 ms 76.555 ms 105.993 ms 10 openpeering.pcextreme.nl (82.150.154.35) 84.274 ms telecity2.openpeering.nl (82.150.154.26) 87.533 ms nikhef.openpeering.nl (82.150.154.25) 105.973 ms 11 openpeering.pcextreme.nl (82.150.154.35) 87.506 ms 87.474 ms 185.27.173.130 (185.27.173.130) 79.570 ms 12 185.27.173.150 (185.27.173.150) 95.558 ms 95.510 ms 185.27.173.130 (185.27.173.130) 81.846 ms 13 185.27.173.150 (185.27.173.150) 68.465 ms * 84.567 ms 14 * * * 15 * * * 16 * * * 17 * * * 18 * * * 19 * * * 20 * * * 21 * * * 22 * * * 23 * * * 24 * * * 25 * * * 26 * * * 27 * * * 28 * * * 29 * * * 30 * * *
我将如何去使用普通的telnet到一些端口的networking不可达 ？
在客户端
名称parsing（你实际连接到了什么地方？）
nslookup myhost.com
结果是什么？ 什么IP？ IPv4还是IPv6？ （这将有助于不混淆输出。）
哦，顺便说一句你正在尝试的操作系统是什么？ 如果Linux：
traceroute myhost.com
在服务器上
服务是否在监听（如果不在监听，则无法连接）
netstat -tulpn | grep mysql
在服务器上尝试一个本地连接
telnet localhost 3306
结果是什么？
在从客户端启动的telnet期间
tshark -ta -n port 3306
你看到数据包来自客户端吗？ （确保tshark运行在正确的界面上，如果有的话）
如果没有数据包：networking上的某些东西阻塞了它们（不是服务器上的潜在iptables防火墙，我们将接下来的内容。）
如果确实看到数据包：没有networking问题，请检查iptables
iptables -vnL
它说什么？ 那么iptables -t raw -vnL ， iptables -t mangle -vnL ， iptables -t nat -vnL ？
Selinux是活跃的吗？ 还是其他一些主机保护？
我在关机期间看到locking错误。 你可能有多个mysqlds错误地运行？ ps -ef | grep mysql是什么？ ps -ef | grep mysql说？ 像关机一样麻烦启动？
请回复详细的结果。
这一直是我的问题，我无法通过互联网连接到远程MySQL服务器。 我做了一些快速的故障排除testing，例如：
根据你提供的信息，你似乎已经完成了上述，因为我可以看到它必须与行：
tcp 0 0 *:mysql *:* LISTEN -
然后我回想起我从apesa的Stackoverflow上读到的一篇文章，其中包括：
要将MySQL公开到本地主机以外的任何地方，必须在/etc/mysql/my.cnf中取消注释以下行，并将其分配给您的计算机IP地址，而不是回送
#Replace xxx with your IP Address bind-address = xxx.xxx.xxx.xxx或者，如果您不想指定IP，请添加一个
bind-address = 0.0.0.0然后停止并用新的my.cnf条目重新启动MySQL。 一旦运行到terminal并input以下命令。
lsof -i -P | grep :3306这应该回来这样的事情与你的实际IP在xxx的
mysqld 1046 mysql 10u IPv4 5203 0t0 TCP xxx.xxx.xxx.xxx:3306 (LISTEN)如果上述语句正确返回，您将可以接受远程用户。 但是，对于远程用户连接正确的priveleges，您需要在localhost和'％'中创build该用户。
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass'; CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';然后
GRANT ALL ON *.* TO 'myuser'@'localhost'; GRANT ALL ON *.* TO 'myuser'@'%';如果您没有创build与上述相同的用户，则在本地login时，您可能会inheritance基本本地主机权限并存在访问问题。 如果你想限制myuser的访问，那么你需要阅读GRANT语句的语法。这里如果你通过所有这些，仍然有问题发布一些额外的错误输出和my.cnf适当的行。
注意：如果lsof没有返回或者没有find，你可以在这里根据你的Linux发行版安装它。 你不需要使用它来工作，但是当事情没有按预期工作时，它是非常方便的。
– >通过Apesa回答： 远程连接MySQL Ubuntu
通常这解决了我的问题，因为我没有configurationmy.cnf
如果/etc/mysql/my.cnf中的bind-address被设置为127.0.0.1，请尝试将其更改为以下内容，这将允许MySQL绑定到所有可用的接口：
bind-address = 0.0.0.0
另外，尝试授予自己在MySQL本身的networking访问权限：
mysql> GRANT ALL ON 。 以your-user-name @'your-hostname'IDENTIFIED BY'your-password';
那么tcpwrapers：/etc/hosts.{allow,deny}文件呢？ 看看你的netstat输出，它看起来像MySQL正在监听所有的IPv4地址，你的iptables也是开放的。 所以它归结为IPv6问题或tcpwrappers，因为他们坐在中间的东西，不容易debugging。