lnmp架构 mysql数据库Cannot assign requested address报错解决-智慧文博士

数据库连接错误，具体是网络连接问题 导致的。错误信息 "Cannot assign requested address" 表示系统无法分配本地地址来建立数据库连接。

报错截取如下：

{
"code": 10501,
"message": "SQLSTATE[HY000] [2002] Cannot assign requested address",
"traces": [
{
"name": "think\\db\\exception\\PDOException",
"file": "\/www\/wwwroot\/zjjgzyxy\/vendor\/topthink\/think-orm\/src\/db\/PDOConnection.php",
"line": 797,
"code": 10501,
"message": "SQLSTATE[HY000] [2002] Cannot assign requested address",
"trace": [
{
"file": "\/www\/wwwroot\/zjjgzyxy\/vendor\/topthink\/think-orm\/src\/db\/connector\/Mysql.php",
"line": 66,
"function": "getPDOStatement",
"class": "think\\db\\PDOConnection",
"type": "->"
},

}

这个错误通常在高并发场景下出现。首先排查php及mysql配置文件。优化数据库连接管理，增加复用，减少频繁创建新连接。

具体操作步骤：

编辑 /usr/local/php/etc/php-fpm.conf 或 www.conf

PHP-FPM配置

1.连接池设置

pm = dynamic
pm.max_children = 50
pm.start_servers = 5
pm.min_spare_servers = 5
pm.max_spare_servers = 35
pm.max_requests = 1000 # 进程处理1000个请求后重启，避免内存泄漏

2.进程管理
ps aux | grep php-fpm #查看当前FPM进程状态
service php-fpm restart #重启FPM

Nginx配置

1. 连接数限制

编辑 /usr/local/nginx/conf/nginx.conf
worker_processes auto; # 自动设置工作进程数
worker_connections 10240; # 每个工作进程最大连接数
multi_accept on;
use epoll;

查看当前连接状态
netstat -an | grep :80 | wc -l

2.反向代理超时

在server或location块中
location ~ \.php$ {
fastcgi_connect_timeout 300;
fastcgi_send_timeout 300;
fastcgi_read_timeout 300;
fastcgi_buffer_size 64k;
fastcgi_buffers 4 64k;
}

MySQL配置

1. 连接数设置

编辑 /etc/my.cnf

[mysqld]
max_connections = 1000
max_connect_errors = 1000
wait_timeout = 600
interactive_timeout = 600
mysql -e "show status like 'Threads_connected';" 查看当前连接数
mysql -e "show variables like 'max_connections';" 查看最大连接数

2. 连接池优化

查看当前连接状态
mysql -e "show processlist;"
mysql -e "show status like '%connection%';"

重启命令：

重启PHP-FPM：service php-fpm restart
重启Nginx：service nginx restart
重启MySQL：service mysql restart

检查服务器资源：
free -h 查看可用内存
mysql -e "SHOW STATUS LIKE 'Threads_connected';" 查看当前连接数

还是无法解决，则查看服务器文件

编辑/etc/sysctl.conf文件：

打开文件
nano /etc/sysctl.conf

添加或修改以下参数：
net.ipv4.tcp_max_syn_backlog = 16384
net.ipv4.tcp_synack_retries = 1
net.ipv4.tcp_fin_timeout = 5 # 从10改为5秒
net.ipv4.tcp_keepalive_time = 1200
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl = 5
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.send_redirects = 0
net.ipv4.conf.all.accept_redirects = 0
vm.swappiness = 5
net.core.rmem_max = 2048000
net.core.wmem_max = 2048000
net.ipv4.ip_local_port_range=10000 65000 # 从40000-60000改为10000-65000
net.ipv4.ip_local_reserved_ports=48200-48253
net.ipv4.neigh.default.gc_thresh1 = 10240
net.ipv4.neigh.default.gc_thresh2 = 20480
net.ipv4.neigh.default.gc_thresh3 = 40960
vm.max_map_count=262144

# 添加的参数 ↓
net.ipv4.tcp_tw_reuse = 1 # 开启TIME_WAIT套接字重用
net.ipv4.tcp_tw_recycle = 1 # 开启TIME_WAIT套接字快速回收
net.ipv4.tcp_max_tw_buckets = 20000 # 限制TIME_WAIT最大数量
net.core.somaxconn = 65535 # 增加监听队列长度
net.ipv4.tcp_syncookies = 1 # 开启SYN cookies
net.ipv4.tcp_abort_on_overflow = 0 # 不拒绝新的连接请求

立即生效命令（终端执行）
sysctl -p

sysctl -p 如果不行，尝试
/sbin/sysctl -p

监控效果：观察"无法分配请求的地址"错误是否消失、持续监控TIME_WAIT连接数