MySQL 服务器没有响应

Question

我在 Windows Server 2012 中安装了 easyphp，当我想访问此服务器上的 phpmyadmin 时，我输入:

http://192.168.75.100/modules/phpmyadmin3522x160307103707/

它之前运行过，但今天我收到此错误消息:

2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)

我在 stackoverflow 中查找了类似的问题，我所能找到的只是检查 MySQL 是否在端口 3306 上运行，正如您所看到的，它已启动并正在运行:

另一个解决方案是检查 mysqld.exe 是否正在运行，正如您所看到的，它正在运行:

我找到了另一个解决方案:

mysql default port is 3306 can you try putting it and then try

这是我的配置文件:

# The following options will be passed to all MySQL clients
[client]
#password   = your_password
port        = 3306
socket      = "${path}/mysql/mysql.sock"

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port        = 3306

#Path to installation directory. All paths are usually resolved relative to this.
basedir="${path}/mysql/"

#Path to the database root
datadir="${path}/mysql/data/"

# The default storage engine that will be used when create new tables when
default-storage-engine = INNODB

# bind-address = 192.168.75.100
socket      = "${path}/mysql/mysql.sock"
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M


# 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


# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\\mysql\\data\\
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\\mysql\\data\\
# 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 = 1M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
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

编辑:

这是错误文件:

https://www.dropbox.com/s/it9cvsutxldebyi/WIN-BQ33O4RKADD.err?dl=0

Answer 1

我查看了你的日志。

到目前为止，您的绑定(bind)地址是 localhost (127.0.0.1)

160307 15:26:18 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
160307 15:26:18 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
160307 15:26:18 [Note] Server socket created on IP: '127.0.0.1'.

那么看来您在接下来的 5 分钟内更改了它:

160307 15:31:56 [Note] Server hostname (bind-address): '192.168.75.100'; port: 3306
160307 15:31:56 [Note]   - '192.168.75.100' resolves to '192.168.75.100';
160307 15:31:56 [Note] Server socket created on IP: '192.168.75.100'.

然后又更改了bind ip，这次又报错了!

160307 15:35:33 InnoDB: 1.1.8 started; log sequence number 1597526
160307 15:35:33 [Note] Server hostname (bind-address): '192.168.75.0/24'; port: 3306
160307 15:35:33 [ERROR] Ne peut créer la connexion IP (socket): No such file or directory
160307 15:35:33 [ERROR] Can't start server: cannot resolve hostname!
160307 15:35:33 [ERROR] Aborting

您尝试启动 MySql 两次并出现该错误，然后改回绑定(bind)地址:

160307 15:37:19 [Note] Server hostname (bind-address): '192.168.75.100'; port: 3306
160307 15:37:19 [Note]   - '192.168.75.100' resolves to '192.168.75.100';
160307 15:37:19 [Note] Server socket created on IP: '192.168.75.100'.

之后你再次更改绑定(bind)地址，也许在不停止服务器的情况下再次启动它......服务器变得非常不安:

160307 15:43:49 [Note] Server hostname (bind-address): '192.168.75.10'; port: 3306
160307 15:43:49 [Note]   - '192.168.75.10' resolves to '192.168.75.10';
160307 15:43:49 [Note] Server socket created on IP: '192.168.75.10'.
160307 15:43:49 [ERROR] Can't start server: Bind on TCP/IP port: No such file or directory
160307 15:43:49 [ERROR] Do you already have another mysqld server running on port: 3306 ?
160307 15:43:49 [ERROR] Aborting

看来您已经修复了该问题，并且再次返回到之前的地址:

160307 15:44:07 [Note] Server hostname (bind-address): '192.168.75.100'; port: 3306
160307 15:44:07 [Note]   - '192.168.75.100' resolves to '192.168.75.100';
160307 15:44:07 [Note] Server socket created on IP: '192.168.75.100'.

您再次更改了地址!

160307 15:45:41 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160307 15:45:41 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
160307 15:45:41 [Note] Server socket created on IP: '0.0.0.0'.

所有这些更改都出了问题，请重新启动:

160308 20:41:18 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
160308 20:41:19  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 7481, file name .\mysql-bin.000019
160308 20:41:31  InnoDB: Waiting for the background threads to start
160308 20:41:32 InnoDB: 1.1.8 started; log sequence number 1606824
160308 20:41:32 [Note] Recovering after a crash using mysql-bin
160308 20:41:32 [Note] Starting crash recovery...
160308 20:41:32 [Note] Crash recovery finished.

从那时起，您会遇到很多崩溃、错误以及从异常情况或关闭中恢复，直到最后:

160325 11:07:37  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 5190394, file name .\mysql-bin.000035
160325 11:07:37  InnoDB: Waiting for the background threads to start
160325 11:07:38 InnoDB: 1.1.8 started; log sequence number 48295796
160325 11:07:38 [Note] Recovering after a crash using mysql-bin
160325 11:07:38 [Note] Starting crash recovery...
160325 11:07:38 [Note] Crash recovery finished.
160325 11:07:38 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160325 11:07:38 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
160325 11:07:38 [Note] Server socket created on IP: '0.0.0.0'.

看来你对待服务器不太友善。

现在重点是您授予用户的权限，如果您从

localhost (127.0.0.1)

至:

192.168.75.100

至:

any host (0.0.0.0)

您必须确保您的用户具有适当的权限，它们是用户/主机的组合。如果您想同时拥有所有这些可能性，您需要创建多个具有相同 ID/密码和不同主机(127.0.0.1、localhost、任意)的用户。

顺便说一句:您当前的绑定(bind)地址是任意 (0.0.0.0) 而不是 192.168.75.100。

问候