MySQL连接无法正常工作 [英] MySQL connection not working

查看:70
本文介绍了MySQL连接无法正常工作的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

$con = mysqli_connect('<remote server ip address>','root','*******','CSV_DB',);
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

我有一个简单的程序可以连接到使用phpmyadmin创建的mysql服务器,但是每次尝试连接时都会收到无法连接"消息.

I have a simple program to connect to a mysql server that I created using phpmyadmin, however I get the 'could not connect' message every time I try to connect.

我知道这可能是许多不同的问题,但我确实正在寻找有关如何进行故障排除的建议.

I know that it could be a number of different issues but I'm really looking for advice on how to troubleshoot.

当我以root身份登录服务器时,可以使用终端访问服务器,一切都很好.但是,该连接使用相同的用户名/密码/db/etc,而且我似乎无法登录.而且,mysqli_error从未打印出来-不知道为什么.

When I ssh as root to the server I can access the server using the terminal and everything is fine. However this connection which uses the same username/password/db/etc and I can't seem to log in. Also, the mysqli_error never prints-not sure why.

推荐答案

下面是快速的清单,用于启用MySQL的远程连接,但请首先阅读(6).如果我错过了任何事情,请随时进行编辑.

Here is a quick quick checklist for enabling Remote Connections for MySQL but read (6) first. If I have missed anything, feel free to edit.

1)您的远程用户正在通过使用相应的user,host条目创建的帐户进行连接(请查看select user,host from mysql.user order by 1,2的输出).如果不是,请查看 CREATE USER 并/或 GRANT 命令.检查 SHOW GRANTS 的输出

1) Your remote user is connecting through an account that was created with appropriate user,host entries (look at output from select user,host from mysql.user order by 1,2). If not, look into the CREATE USER and/or GRANT commands. Examine the output from SHOW GRANTS for the user.

2)您已经完成了 flush privileges; (有人说这是不必要的,有人说这是不必要的.

2) You have done flush privileges; (Some say it is unnecessary, others say it is).

3a)通过查看

3a) Locate your mysql configuration file referenced in 3b) below by reviewing info in This Document (or for Windows, likely down the C:\ProgramData\MySQL\MySQL Server 5.NNN path). It varies by distro for Linux.

3b)您已经修改并保存了my.ini(Windows)或my.cnf(Linux),并从127.0.0.1localhost更改了bind-address,以支持0.0.0.0.您已经创建了 ,并删除了以下行:#skip-networking.它将类似于以下内容:

3b) You have modified and saved my.ini (Windows) or my.cnf (Linux) and changed bind-address away from 127.0.0.1 or localhost, in favor of 0.0.0.0. And you have created and rem'd out the following line: #skip-networking. It will look similar to this:

[mysqld]
bind-address=0.0.0.0
#skip-networking

4)重新启动mysql守护程序.这是如何随发行版而变化的.

4) Restart the mysql daemon. How one does this varies by distro.

5)防火墙问题.确保端口(默认为3306)对外界开放(实际上可能只是您的Intranet).这包括任何其他防火墙层,例如AWS EC2安全组,或类似的防火墙(如果有).

5) Firewall issues. Make sure the port, default being 3306, is open to the outside world (which may in fact just be your intranet). This includes any other layers of firewalls, such as AWS EC2 Security Groups, or similar, if any.

6)了解存在与此相关的安全风险.除非您有将mysql服务器暴露给远程连接的知识,否则不要执行此操作.

6) Understand that there is a security risk associated with this. Unless you have knowledge of exposing your mysql server to remote connections, do not perform this.

7)请使用上面1.中列出的select语句进行频繁的安全评估,包括查看这些用户的SHOW GRANTS.不要不必要地给用户通配符.相反,为用户提供完成工作所需的最小特权.

7) Please run frequent security assessments with select statement listed in 1. above including reviewing the SHOW GRANTS for those users. Do not over-grant users with wildcard unnecessarily. Rather, give users the minimal privileges for them to get their work done.

8)经常通过常规日志"和错误日志"检查失败的连接尝试,如下所述.

8) Frequently examine failed connection attempts via the General Log and the Error Log as briefly introduced below.

对于入站,您可以查看常规查询日志.

For inbound, you can look at the general query log.

select @@general_log; -- a 1 indicates it is turned on for capture
select @@general_log_file; -- the file that it logs to

因此可以将所有查询 记录到Access denied for user,以查看失败的尝试.定期执行此操作(不是每隔几年一次).我每天至少做两次.注意,您显然可以通过外部程序自动执行报告.外部环境将重击您的服务器,使其进入下图所示的状态.这是现实;为之做好准备.

So all the queries can be logged to the General Query Log if the setting is turned on. Examine the log for "connect", but especially for Access denied for user to see failed attempts. Do this regularly (not every few years). I do it at least twice a day. Note, you can obviously automate the reporting through an external program. The outside world is going to pound your server to get in like the image below. It is a reality; brace yourself for it.

请查看手册页,以获取错误日志也要注意警告级别和基于您的版本的详细程度设置.

Check out the manual page for The Error Log too, noting warning levels, and verbosity settings based on your version.

我建议您按日期创建备份副本(如此命名),并在备份后删除日志文件,以在备份后重新开始.日志文件的大小可能会迅速增大,尤其是常规日志".不要忘记您是否已打开或关闭用于记录的设置.

I would recommend that one create a backup copy by date (named as such) and delete the log files after backup to start fresh after the backup. The log files can grow huge in size rapidly, especially the General Log. Don't forget whether or not you have the setting turned on or off for logging.

您可以使用这两个日志来确定您的连接尝试是否在此处的步骤中经过了防火墙.

You can use the two logs to determine if your connection attempt made it past the firewall during the steps here.

这篇关于MySQL连接无法正常工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆