Node.js中MySQL主从切换异常：切换至从库后登录崩溃问题排查

问题背景 #

在Node.js Web项目中配置MySQL主从架构，期望主库故障时自动切换到从库。停掉主库后，日志显示已成功连接到从库，但Web端登录时项目崩溃，报错Error: Can't add new command when connection is in closed state。

现有代码 #

let db = null;

function createDatabaseConnection() {
  if (!db) {
    db = mysql.createConnection(master);

    db.on('error', (err) => {
      console.error('Cant connect to master server:', err);
      console.log('Change to slave server.');
      db = mysql.createConnection(slave);
      reconnect(db);
    });
  }

  return db;
}

function reconnect(connection) {
  connection.connect((err) => {
    if (err) {
      console.error('Cant connect to slave server:', err);
    } else {
      console.log('Connected to slave server.');
    }
  });

  connection.on('error', (err) => {
    console.error('Cant connect to slave server:', err);
  });
}

module.exports = createDatabaseConnection();

错误日志 #

Cant connect to master server: AggregateError
    at internalConnectMultiple (node:net:1114:18)
    at afterConnectMultiple (node:net:1667:5) {
  code: 'ECONNREFUSED',
  fatal: true,
  [errors]: [
    Error: connect ECONNREFUSED ::1:3306
        at createConnectionError (node:net:1634:14)
        at afterConnectMultiple (node:net:1664:40) {
      errno: -4078,
      code: 'ECONNREFUSED',
      syscall: 'connect',
      address: '::1',
      port: 3306
    },
    Error: connect ECONNREFUSED 127.0.0.1:3306
        at createConnectionError (node:net:1634:14)
        at afterConnectMultiple (node:net:1664:40) {
      errno: -4078,
      code: 'ECONNREFUSED',
      syscall: 'connect',
      address: '127.0.0.1',
      port: 3306
    }
  ]
}
Change to slave server.
Connected to slave server.
[]
C:\Users\Admin\source\repos\HotelBooking\node_modules\mysql2\lib\connection.js:164
    const err = new Error(
                ^

Error: Can't add new command when connection is in closed state

问题原因 #

1. 主库连接报错后，虽重新赋值db为从库连接，但旧的主库连接实例可能仍有未处理的请求排队，这些请求会尝试在已关闭的连接上执行，触发报错。
2. 单连接模式下，连接断开后未完成的请求无法自动切换到新连接。
3. 模块导出的是初始创建的连接实例，切换后的新连接可能没被后续请求正确获取。

修复方案 #

方案1：改用连接池实现自动切换（推荐） #

连接池能自动管理连接状态，故障时自动重试或切换到备用库，更适合生产环境：

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

// 配置主从库列表
const dbConfigs = [master, slave];
let currentConfigIndex = 0;

async function getConnection() {
  try {
    const connection = await mysql.createConnection(dbConfigs[currentConfigIndex]);
    // 监听连接错误，自动切换到下一个配置
    connection.on('error', async (err) => {
      console.error(`连接 ${dbConfigs[currentConfigIndex].host} 失败:`, err);
      currentConfigIndex = (currentConfigIndex + 1) % dbConfigs.length;
      console.log(`切换到备用库 ${dbConfigs[currentConfigIndex].host}`);
      await connection.end();
    });
    return connection;
  } catch (err) {
    console.error(`尝试连接 ${dbConfigs[currentConfigIndex].host} 失败:`, err);
    // 当前配置失败，切换到下一个重试
    currentConfigIndex = (currentConfigIndex + 1) % dbConfigs.length;
    return getConnection();
  }
}

module.exports = getConnection;

使用示例（每次请求获取新连接）：

const getConnection = require('./db');

async function login(req, res) {
  const connection = await getConnection();
  try {
    const [rows] = await connection.execute('SELECT * FROM users WHERE username = ?', [req.body.username]);
    // 处理登录逻辑
    res.send('登录成功');
  } catch (err) {
    res.status(500).send('登录失败');
  } finally {
    await connection.end();
  }
}

方案2：修复原有单连接逻辑 #

如果坚持使用单连接，需确保后续请求都使用新连接，并清理旧连接的事件监听：

let db = null;

function createDatabaseConnection() {
  if (!db) {
    initConnection(master);
  }
  return db;
}

function initConnection(config) {
  // 清理旧连接
  if (db) {
    db.removeAllListeners('error');
    db.end();
  }

  db = mysql.createConnection(config);

  db.on('error', (err) => {
    console.error(`连接 ${config.host} 失败:`, err);
    // 主库失败切从库，从库失败可重试或告警
    const nextConfig = config === master ? slave : master;
    console.log(`切换到 ${nextConfig.host}`);
    initConnection(nextConfig);
  });

  db.connect((err) => {
    if (err) {
      console.error(`连接 ${config.host} 失败:`, err);
      initConnection(config === master ? slave : master);
    } else {
      console.log(`成功连接到 ${config.host}`);
    }
  });
}

module.exports = createDatabaseConnection();

关键注意点 #

• 主从架构中从库通常为只读，如果登录操作涉及写操作（如记录登录日志），需确保切换后仍能执行写操作，或主库恢复后自动切回。
• 生产环境建议使用ProxySQL等数据库中间件处理主从切换，降低业务代码复杂度。

内容的提问来源于stack exchange，提问作者Nguyễn Trangg