Solution to the problem that php cannot connect to MySQL

Solution to the problem that php cannot connect to MySQL_PHP tutorial

WBOY

Release： 2016-07-13 10:46:53

Original

1365 people have browsed it

php连接不上mysql的原因有很多种常用的可能是函数没开启或mysql数据库配置有问题，下面我来给大家介绍php连接不上MySQL一些问题的分析与解决方法。

现象1

在PHP error log里发现：
PHP Warning: mysqli::mysqli(): (HY000/2003): Can’t connect to MySQL server on ‘XXX.XXX.XXX.XXX’ (99) in /u1/www/XXXX.php on line 10
PHP Warning: mysqli::close(): Couldn’t fetch mysqli in /u1/www/XXXX.php on line 11
推断：只有在高并发的环境下出现

诊断分析：

通过MySQL数据库上抓包，没发现异常。又把目标转到php 服务器上。

BTW:
linux开着selinux连接MySQL在测试中基本上属于1ms+,禁掉selinux后在0.96左右。selinux还是要禁掉的。
既然又怀疑是PHP的问题就写一个程序测试(禁掉selinux后)：
cat tconn.php

代码如下	复制代码
function microtime_float() { list($usec, $sec) = explode(" ", microtime()); return ((float)$usec + (float)$sec); } $time_start = microtime_float(); for ( $i=0; $i<30000; $i++){ $dbh=new mysqli("XXX.XXX.XXX.XXX", "wubx", "wubxwubx", "userdb", 3308); $dbh->close(); } $time_end= microtime_float(); $time_use= ($time_end - $time_start)/30000; print "$time_usen"; #php tconn.php 0.00090954260031382

再次运行就开始大量的报错。

PHP Warning: mysqli::mysqli(): (HY000/2003): Can't connect to MySQL server on 'XXX.XXX.XXX.XXX' (99) in /u1/www/XXXX.php on line 10
PHP Warning: mysqli::close(): Couldn't fetch mysqli in /u1/www/XXXX.php on line 11
中止该程序后，通过

#strace php tconn.php 运行

得到：

connect(3, {sa_family=AF_INET, sin_port=htons(3308), sin_addr=inet_addr("XXX.XXX.XXX.XXX")}, 16) = -1 EADDRNOTAVAIL (Cannot assign requested address)
shutdown(3, 2 /* send and receive */) = -1 ENOTCONN (Transport endpoint is not connected)
看到这个大概明白是本地的网络可能注册不上了，也难怪在MySQL抓包看也正常。

看样子是本地tcp不能重用造成的。改一下在测试

代码如下	复制代码
sysctl -w net.ipv4.tcp_tw_reuse=1;

在次测试问题不存在了。在这个上面碰了一下后顺便改一下/etc/sysctl.conf添加：

代码如下	复制代码
net.ipv4.tcp_max_syn_backlog = 819200 net.core.netdev_max_backlog = 400000 net.core.somaxconn = 4096 net.ipv4.tcp_tw_reuse=1 net.ipv4.tcp_tw_recycle=0 #sysctl -p

Problem Solved

Phenomena 2

MYSQL. Test connection mysql prompts 'Fatal error: Call to undefined function mysql_connect()" The environment j is: windows xp sp2 en, apache2.2, mysql5.1rc.php5.28.

This prompt, could it be that php is not loaded into the library file connected to mysql? After starting the apache server, I tried to delete 'php5ts.dll' and 'libmysql.dll'. The prompt cannot be deleted. It means there is a program When using these two library files, it shows that they are loaded. (Of course, there are many ways to test. For example, you can use some software to view all the library files loaded by the program service. It is also possible. However, ap also refers to the php.ini settings. If there is a problem, then I will not look at anything else. I will focus on the php.ini configuration.

Without superstition that php.ini is correct, I finally discovered that this line is missing from .php.ini.

PHPIniDir "your php directory"

#(for example: PHPIniDir "c:/php")

Restart the apache server, and then use the methods commonly used on the Internet

The following is the quoted content:

The IP address to bind to. Only one address can be selected. If this option is specified multiple times, the last address given is used. If no address or 0.0.0.0 is specified, the server listens on all interfaces. 🎜>

The code is as follows

代码如下	复制代码
$link=mysql_connect('localhost','用户名','password'); if(!$link) echo "失败!"; else echo "成功!"; mysql_close(); ?>

Copy code

$link=mysql_connect('localhost','username','password');

if(!$link) echo "Failed!";
else echo "Success!"; mysql_close(); ?>

Just test it. That’s it

Summarize these issues

1. First, check for network problems and firewall problems

This is necessary. If you can't even connect to the MySQL server, what else can you access? How to check? Ping 192.168.0.11 If the ping works, check whether port 3306 is blocked by the firewall. Ping 192.168.0.11:3306 or simply turn off the firewall and service iptables stop (Red hat ) or ufw disable(ubuntu) If there is no problem with this step, start the next step:

2. Check whether there is access permission

Speaking of access rights, MySQL will specify a host when allocating users. For example, if my host is specified as 192.168.0.5, then this account can only be accessed by this machine. Other machines using this account will not be able to access. It says there is no permission. Specifying host as % means that all machines are allowed to access. Generally speaking, for security reasons, following the principle of least privilege, we won’t talk much about permissions. If you don’t know how, you can check the manual yourself. If you confirm that there is no problem with the permissions, proceed to the next step:

3. To check the configuration of MySQL

Check the configuration file of mysql. The configuration file of MySQL under Linux is called my.cnf and under windows is called my.ini. Check this configuration item: –bind-address=IP

Quote from the manual:

The bound IP can only be bound to one IP. If multiple IPs are bound, the last bound one shall prevail. If there is no binding or binding 0.0.0.0, the server listens for all clients. I have suffered miserably from this thing. Once I spent an afternoon trying to figure it out and couldn't get it right. I checked the network connection and the permissions were OK, but the client just couldn't connect. After looking at the manual, I figured it out. So if you have any questions, you should read the manual more