本地Strapi连接MySQL报错connect ECONNREFUSED ::1:3306求解决

执行strapi develop命令后出现MySQL连接拒绝错误，具体错误输出如下：

$ strapi develop
[2024-05-13 15:43:43.535] warn: @strapi/plugin-documentation has deprecated registerDoc, use strapi.plugin('documentation').service('override').registerOverride() instead
[2024-05-13 15:43:43.608] info: Using Local Image Sharp plugin
[2024-05-13 15:43:43.609] info: - Source directory: /Users/mac/Dev_jobs/BONUX/backend/public
[2024-05-13 15:43:43.609] info: - Cache directory: /Users/mac/Dev_jobs/BONUX/backend/.image-cache
[2024-05-13 15:43:43.869] debug: ⛔️ Server wasn't able to start properly.
[2024-05-13 15:43:43.870] error: connect ECONNREFUSED ::1:3306
Error: connect ECONNREFUSED ::1:3306
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1555:16)
    --------------------
    at Protocol._enqueue (/Users/mac/Dev_jobs/BONUX/backend/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Protocol.handshake (/Users/mac/Dev_jobs/BONUX/backend/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at Connection.connect (/Users/mac/Dev_jobs/BONUX/backend/node_modules/mysql/lib/Connection.js:116:18)
    at /Users/mac/Dev_jobs/BONUX/backend/node_modules/knex/lib/dialects/mysql/index.js:66:18
    at new Promise (<anonymous>)
    at Client_MySQL.acquireRawConnection (/Users/mac/Dev_jobs/BONUX/backend/node_modules/knex/lib/dialects/mysql/index.js:61:12)
    at create (/Users/mac/Dev_jobs/BONUX/backend/node_modules/knex/lib/client.js:252:39)
error Command failed with exit code 1.

已通过命令/usr/local/bin/mysql.server status确认MySQL服务正常运行，返回SUCCESS! MySQL running。

当前database.js配置文件内容如下：

module.exports = ({ env }) => ({
  connection: {
    client: "mysql",
    connection: {
      host: env("DATABASE_HOST"),
      port: env.int("DATABASE_PORT"),
      database: env("DATABASE_NAME"),
      user: env("DATABASE_USERNAME"),
      password: env("DATABASE_PASSWORD"),
      ssl: env.bool("DATABASE_SSL", false),
    },
  },
});

可从以下几个方向排查问题：

• 验证环境变量：检查项目根目录的.env文件，确认DATABASE_HOST、DATABASE_PORT等参数是否正确。如果DATABASE_HOST设为::1（IPv6地址）但MySQL未开启IPv6监听，尝试改为127.0.0.1（IPv4本地地址）
• 检查MySQL端口监听：执行lsof -i :3306或netstat -an | grep 3306，确认3306端口是否被MySQL进程监听，以及监听的是IPv4还是IPv6地址
• 手动测试数据库连接：用MySQL客户端直接测试连接，执行mysql -h [你的DATABASE_HOST值] -P [你的DATABASE_PORT值] -u [你的DATABASE_USERNAME值] -p，输入密码后看是否能成功连接到目标数据库
• 确认用户连接权限：登录MySQL执行SELECT user, host FROM mysql.user;，查看配置的数据库用户对应的host字段是否允许从本地（localhost、127.0.0.1或::1）连接。如果权限不足，执行GRANT ALL PRIVILEGES ON [DATABASE_NAME].* TO '[DATABASE_USERNAME]'@'localhost';，再执行FLUSH PRIVILEGES;更新权限
• 检查MySQL配置文件：打开my.cnf（或my.ini），查看bind-address配置项。如果设置为127.0.0.1，则MySQL只监听IPv4，此时需将Strapi的DATABASE_HOST改为127.0.0.1；如果需要支持IPv6，可将bind-address改为::

内容的提问来源于stack exchange，提问作者Rom-888