Laravel+Docker执行迁移填充时出现SQLSTATE[HY000] [2002]连接拒绝错误

问题描述 #

在Docker环境中运行Laravel执行php artisan migrate:fresh --seed时，所有迁移和短时间数据填充可正常完成，但调用IBGE接口批量插入城市数据的CitySeeder因耗时较长，会抛出固定错误：

SQLSTATE[HY000] [2002] Connection refused (SQL: insert into `cities` (`state_id`, `name`, `iso`) values (16, Itajuípe, 2915502))

核心原因 #

1. MariaDB连接超时：默认配置下，MariaDB会自动关闭长时间闲置的连接，Seeder在请求IBGE接口期间，数据库连接处于闲置状态，超过超时阈值后被主动断开，后续插入操作复用了失效连接。
2. Laravel连接复用机制：Laravel的数据库连接池未自动处理失效连接，闲置过久的连接不会主动重连。

解决方案 #

方案1：延长MariaDB连接超时时间 #

修改docker-compose.yml中mysql服务的环境变量，增加超时相关配置：

mysql:
    container_name: mysql
    image: mariadb:latest
    restart: always
    environment: 
        MYSQL_ROOT_PASSWORD: ${DB_PASSWORD}
        MYSQL_DATABASE: ${DB_DATABASE}
        MYSQL_USER: ${DB_USERNAME}
        MYSQL_PASSWORD: ${DB_PASSWORD}
        # 延长连接超时参数
        MYSQL_WAIT_TIMEOUT: 3600
        MYSQL_INTERACTIVE_TIMEOUT: 3600
        MYSQL_CONNECT_TIMEOUT: 3600
    volumes: 
        - ./.docker/mariadb:/var/lib/mysql
    ports: 
        - "3306:3306"
    networks: 
        - backend

修改后重启mysql容器：

docker-compose down && docker-compose up -d mysql

方案2：Seeder中手动重连数据库 #

在请求IBGE接口前，手动检查并重建数据库连接，避免复用失效连接：

namespace Database\Seeders;

use App\Models\City;
use App\Models\State;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Facades\DB;

class CitySeeder extends Seeder
{
    public function run()
    {
        $states = State::orderBy('name')->get();
        
        foreach($states as $state){
            // 检查连接有效性，失效则自动重连
            DB::connection()->getPdo();
            
            $apiCities = Http::get('https://servicodados.ibge.gov.br/api/v1/localidades/estados/'.$state->initials.'/municipios');

            foreach($apiCities->json() as $city){
                City::create([
                    'state_id'=>$state->id,
                    'name'=>$city['nome'],
                    'iso'=>$city['id'],
                ]);
            }
        }
    }
}

方案3：批量插入优化性能 #

将每个州的城市数据批量收集后一次性插入，减少数据库交互次数，同时避免连接闲置：

namespace Database\Seeders;

use App\Models\City;
use App\Models\State;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\Http;

class CitySeeder extends Seeder
{
    public function run()
    {
        $states = State::orderBy('name')->get();
        
        foreach($states as $state){
            $apiCities = Http::get('https://servicodados.ibge.gov.br/api/v1/localidades/estados/'.$state->initials.'/municipios');
            
            $citiesData = [];
            foreach($apiCities->json() as $city){
                $citiesData[] = [
                    'state_id'=>$state->id,
                    'name'=>$city['nome'],
                    'iso'=>$city['id'],
                    'created_at' => now(),
                    'updated_at' => now()
                ];
            }
            
            // 批量插入，减少数据库请求次数
            City::insert($citiesData);
        }
    }
}

额外检查项 #

确认Laravel .env 文件中数据库主机配置为Docker服务名mysql，而非localhost或127.0.0.1：

DB_HOST=mysql
DB_PORT=3306

内容的提问来源于stack exchange，提问作者Fabricio Gwadera