Node.js(Express)+mysql2数据库断开后无法自动重连问题求助

在使用Node.js（Express.js）搭配mysql2开发时，当MySQL服务器关闭或重启，应用会因未处理的数据库连接错误崩溃，且无法自动重连。需要实现每5分钟自动尝试重连的功能，避免手动重启服务。

原代码问题 #

const mysql = require('mysql2');

let db;

const dbConfig = {
  host: HOST_DB,
  user: USER_DB,
  password: PASSWORD_DB,
  database: DATABASE_NAME,
  port: PORT_DB,
};

function handleDisconnect() {

  db = mysql.createConnection(dbConfig);

  db.connect(error => {
    if (error) {
      console.error('Error connecting to the database:', error);
      setTimeout(handleDisconnect, 300000);
    }
  });

  db.on('error', error => {
    console.error('Database error:', error);
    if (error.code === 'PROTOCOL_CONNECTION_LOST') {
      console.log('Attempting to reconnect to the database...');
      handleDisconnect();
    } else {
      throw error;
    }
  });

}
handleDisconnect()

// 问题核心：该行覆盖了handleDisconnect中创建的带错误监听的连接，新连接未绑定错误处理逻辑
db = mysql.createConnection(dbConfig);

报错信息 #

node:events:497
      throw er; // Unhandled 'error' event
      ^

Error: Connection lost: The server closed the connection.
    at Socket.<anonymous> (C:\Users\admin\Desktop\otp\server\node_modules\mysql2\lib\connection.js:117:31)
    at Socket.emit (node:events:519:28)
    at TCP.<anonymous> (node:net:338:12)
Emitted 'error' event on Connection instance at:
    at Connection._notifyError (C:\Users\admin\Desktop\otp\server\node_modules\mysql2\lib\connection.js:252:12)
    at Socket.<anonymous> (C:\Users\admin\Desktop\otp\server\node_modules\mysql2\lib\connection.js:123:12)
    at Socket.emit (node:events:519:28)
    at TCP.<anonymous> (node:net:338:12) {
  fatal: true,
  code: 'PROTOCOL_CONNECTION_LOST'
}

Node.js v20.15.0
[nodemon] app crashed - waiting for file changes before starting...

修正后的代码 #

const mysql = require('mysql2');

let db;

const dbConfig = {
  host: HOST_DB,
  user: USER_DB,
  password: PASSWORD_DB,
  database: DATABASE_NAME,
  port: PORT_DB,
};

function handleDisconnect() {
  // 创建新连接并绑定到全局db变量
  db = mysql.createConnection(dbConfig);

  db.connect(error => {
    if (error) {
      console.error('数据库连接失败:', error);
      // 5分钟后自动重试连接
      setTimeout(handleDisconnect, 300000);
    } else {
      console.log('成功连接到数据库');
    }
  });

  db.on('error', error => {
    console.error('数据库错误:', error);
    // 处理连接丢失及所有致命错误，触发重连
    if (error.code === 'PROTOCOL_CONNECTION_LOST' || error.fatal) {
      console.log('尝试重新连接数据库...');
      handleDisconnect();
    }
    // 非致命错误不抛出，避免应用崩溃
  });
}

// 初始化数据库连接
handleDisconnect();

关键修复点 #

• 移除最后一行多余的连接创建代码，确保全局使用的是绑定了错误监听的连接实例
• 扩展错误处理范围，除PROTOCOL_CONNECTION_LOST外，所有致命错误都触发重连逻辑
• 移除throw error语句，避免未处理错误导致应用崩溃
• 增加连接成功日志，便于排查连接状态

额外建议 #

如果服务并发较高，推荐使用mysql2的连接池替代单连接，连接池会自动管理连接的创建与回收，内置重连机制，更适合生产环境：

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  ...dbConfig,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 使用连接池执行查询的封装函数
async function executeQuery(sql, params) {
  let connection;
  try {
    connection = await pool.getConnection();
    const [rows] = await connection.query(sql, params);
    return rows;
  } catch (error) {
    console.error('查询执行失败:', error);
    throw error;
  } finally {
    if (connection) connection.release();
  }
}

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