一介布衣

🧭 快速导航

Sequelize 部署与运维 - 生产环境最佳实践 ​

发布时间：2024-04-10
作者：一介布衣
标签：Sequelize, 部署, 运维, 生产环境, 监控

前言 ​

今天咱们来聊聊 Sequelize 应用的部署和运维。说实话，开发环境跑得好好的应用，到了生产环境就各种问题，这是很多开发者都遇到过的痛点。

我记得第一次部署 Sequelize 应用到生产环境时，遇到了各种问题：连接池配置不当导致连接泄漏、迁移脚本执行失败、性能监控缺失等等。后来慢慢积累经验，总结出了一套完整的部署和运维方案。

今天我就把这些生产环境的最佳实践分享给大家。

生产环境配置 ​

1. 数据库连接配置 ​

javascript
// config/database.js
const config = {
  production: {
    username: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    host: process.env.DB_HOST,
    port: process.env.DB_PORT || 3306,
    dialect: 'mysql',
    
    // 连接池配置（重要！）
    pool: {
      max: 20,          // 最大连接数
      min: 5,           // 最小连接数
      acquire: 30000,   // 获取连接超时时间
      idle: 10000,      // 连接空闲时间
      evict: 1000,      // 检查空闲连接间隔
      handleDisconnects: true  // 自动处理断开连接
    },
    
    // 查询超时
    dialectOptions: {
      acquireTimeout: 60000,
      timeout: 60000,
      
      // SSL 配置（如果需要）
      ssl: process.env.DB_SSL === 'true' ? {
        require: true,
        rejectUnauthorized: false
      } : false,
      
      // 字符集配置
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci'
    },
    
    // 生产环境关闭日志
    logging: false,
    
    // 重试配置
    retry: {
      max: 3,
      match: [
        /ETIMEDOUT/,
        /EHOSTUNREACH/,
        /ECONNRESET/,
        /ECONNREFUSED/,
        /ETIMEDOUT/,
        /ESOCKETTIMEDOUT/,
        /EHOSTUNREACH/,
        /EPIPE/,
        /EAI_AGAIN/,
        /SequelizeConnectionError/,
        /SequelizeConnectionRefusedError/,
        /SequelizeHostNotFoundError/,
        /SequelizeHostNotReachableError/,
        /SequelizeInvalidConnectionError/,
        /SequelizeConnectionTimedOutError/
      ]
    }
  }
};

module.exports = config;

2. 环境变量管理 ​

bash
# .env.production
NODE_ENV=production

# 数据库配置
DB_HOST=your-db-host
DB_PORT=3306
DB_NAME=your_production_db
DB_USER=your_db_user
DB_PASS=your_secure_password
DB_SSL=true

# 应用配置
PORT=3000
JWT_SECRET=your-super-secure-jwt-secret
ENCRYPTION_KEY=your-encryption-key

# 外部服务
REDIS_URL=redis://your-redis-host:6379
EMAIL_SERVICE_API_KEY=your-email-api-key

# 监控配置
SENTRY_DSN=your-sentry-dsn
NEW_RELIC_LICENSE_KEY=your-newrelic-key

3. 安全配置 ​

javascript
// config/security.js
const helmet = require('helmet');
const rateLimit = require('express-rate-limit');

module.exports = {
  // 安全头配置
  helmet: helmet({
    contentSecurityPolicy: {
      directives: {
        defaultSrc: ["'self'"],
        styleSrc: ["'self'", "'unsafe-inline'"],
        scriptSrc: ["'self'"],
        imgSrc: ["'self'", "data:", "https:"]
      }
    }
  }),
  
  // 限流配置
  rateLimit: rateLimit({
    windowMs: 15 * 60 * 1000, // 15分钟
    max: 100, // 限制每个IP 15分钟内最多100个请求
    message: {
      error: '请求过于频繁，请稍后再试'
    }
  }),
  
  // API 限流
  apiRateLimit: rateLimit({
    windowMs: 15 * 60 * 1000,
    max: 1000,
    skip: (req) => {
      // 跳过内部服务调用
      return req.ip === '127.0.0.1' || req.ip === '::1';
    }
  })
};

数据库迁移策略 ​

1. 生产环境迁移脚本 ​

bash
#!/bin/bash
# scripts/deploy-migrate.sh

set -e  # 遇到错误立即退出

echo "开始数据库迁移..."

# 备份数据库
echo "备份数据库..."
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME > backup_$(date +%Y%m%d_%H%M%S).sql

# 检查迁移状态
echo "检查迁移状态..."
npx sequelize-cli db:migrate:status

# 执行迁移
echo "执行迁移..."
npx sequelize-cli db:migrate

# 验证迁移结果
echo "验证迁移结果..."
npx sequelize-cli db:migrate:status

echo "迁移完成！"

2. 零停机迁移策略 ​

javascript
// migrations/safe-migration-example.js
'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();
    
    try {
      // 第一步：添加新字段（允许为空）
      await queryInterface.addColumn('users', 'new_email', {
        type: Sequelize.STRING(150),
        allowNull: true
      }, { transaction });
      
      // 第二步：添加索引（在线添加）
      await queryInterface.addIndex('users', ['new_email'], {
        name: 'users_new_email_index',
        concurrently: true  // PostgreSQL 支持
      }, { transaction });
      
      await transaction.commit();
      
      console.log('迁移第一阶段完成，应用可以继续运行');
      
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  },

  async down(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();
    
    try {
      await queryInterface.removeIndex('users', 'users_new_email_index', { transaction });
      await queryInterface.removeColumn('users', 'new_email', { transaction });
      
      await transaction.commit();
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  }
};

3. 迁移回滚策略 ​

bash
#!/bin/bash
# scripts/rollback-migration.sh

MIGRATION_FILE=$1

if [ -z "$MIGRATION_FILE" ]; then
  echo "请指定要回滚到的迁移文件"
  echo "用法: ./rollback-migration.sh 20240410000000-migration-name.js"
  exit 1
fi

echo "警告：即将回滚到迁移文件 $MIGRATION_FILE"
echo "这可能会导致数据丢失，请确认操作"
read -p "确认回滚？(yes/no): " confirm

if [ "$confirm" = "yes" ]; then
  echo "创建回滚前备份..."
  mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME > rollback_backup_$(date +%Y%m%d_%H%M%S).sql
  
  echo "执行回滚..."
  npx sequelize-cli db:migrate:undo:all --to $MIGRATION_FILE
  
  echo "回滚完成"
else
  echo "回滚已取消"
fi

监控和日志 ​

1. 应用监控 ​

javascript
// middleware/monitoring.js
const prometheus = require('prom-client');

// 创建指标
const httpRequestDuration = new prometheus.Histogram({
  name: 'http_request_duration_seconds',
  help: 'HTTP请求耗时',
  labelNames: ['method', 'route', 'status_code']
});

const dbConnectionPool = new prometheus.Gauge({
  name: 'db_connection_pool_size',
  help: '数据库连接池大小',
  labelNames: ['type']
});

const dbQueryDuration = new prometheus.Histogram({
  name: 'db_query_duration_seconds',
  help: '数据库查询耗时',
  labelNames: ['operation']
});

// 监控中间件
function monitoringMiddleware(req, res, next) {
  const start = Date.now();
  
  res.on('finish', () => {
    const duration = (Date.now() - start) / 1000;
    httpRequestDuration
      .labels(req.method, req.route?.path || req.path, res.statusCode)
      .observe(duration);
  });
  
  next();
}

// 数据库监控
function setupDatabaseMonitoring(sequelize) {
  // 监控连接池
  setInterval(() => {
    const pool = sequelize.connectionManager.pool;
    dbConnectionPool.labels('total').set(pool.size);
    dbConnectionPool.labels('used').set(pool.using);
    dbConnectionPool.labels('waiting').set(pool.waiting);
  }, 10000);
  
  // 监控查询性能
  sequelize.addHook('beforeQuery', (options) => {
    options.startTime = Date.now();
  });
  
  sequelize.addHook('afterQuery', (options) => {
    const duration = (Date.now() - options.startTime) / 1000;
    const operation = options.type || 'unknown';
    dbQueryDuration.labels(operation).observe(duration);
  });
}

module.exports = {
  monitoringMiddleware,
  setupDatabaseMonitoring,
  register: prometheus.register
};

2. 结构化日志 ​

javascript
// utils/logger.js
const winston = require('winston');
const { format } = winston;

const logger = winston.createLogger({
  level: process.env.LOG_LEVEL || 'info',
  format: format.combine(
    format.timestamp(),
    format.errors({ stack: true }),
    format.json()
  ),
  defaultMeta: {
    service: 'sequelize-app',
    version: process.env.APP_VERSION || '1.0.0'
  },
  transports: [
    // 错误日志
    new winston.transports.File({
      filename: 'logs/error.log',
      level: 'error',
      maxsize: 10485760, // 10MB
      maxFiles: 5
    }),
    
    // 所有日志
    new winston.transports.File({
      filename: 'logs/combined.log',
      maxsize: 10485760,
      maxFiles: 10
    })
  ]
});

// 生产环境不输出到控制台
if (process.env.NODE_ENV !== 'production') {
  logger.add(new winston.transports.Console({
    format: format.combine(
      format.colorize(),
      format.simple()
    )
  }));
}

// 数据库操作日志
function logDatabaseOperation(operation, model, data, duration) {
  logger.info('Database operation', {
    operation,
    model,
    duration,
    recordCount: Array.isArray(data) ? data.length : 1,
    timestamp: new Date().toISOString()
  });
}

// 错误日志
function logError(error, context = {}) {
  logger.error('Application error', {
    message: error.message,
    stack: error.stack,
    ...context,
    timestamp: new Date().toISOString()
  });
}

module.exports = {
  logger,
  logDatabaseOperation,
  logError
};

3. 健康检查 ​

javascript
// routes/health.js
const express = require('express');
const router = express.Router();
const { sequelize } = require('../models');
const redis = require('../config/redis');

// 基础健康检查
router.get('/health', (req, res) => {
  res.json({
    status: 'ok',
    timestamp: new Date().toISOString(),
    uptime: process.uptime(),
    memory: process.memoryUsage(),
    version: process.env.APP_VERSION || '1.0.0'
  });
});

// 详细健康检查
router.get('/health/detailed', async (req, res) => {
  const health = {
    status: 'ok',
    timestamp: new Date().toISOString(),
    services: {}
  };

  // 检查数据库连接
  try {
    await sequelize.authenticate();
    health.services.database = {
      status: 'ok',
      responseTime: await measureResponseTime(() => sequelize.authenticate())
    };
  } catch (error) {
    health.status = 'error';
    health.services.database = {
      status: 'error',
      error: error.message
    };
  }

  // 检查 Redis 连接
  try {
    await redis.ping();
    health.services.redis = {
      status: 'ok',
      responseTime: await measureResponseTime(() => redis.ping())
    };
  } catch (error) {
    health.status = 'degraded';
    health.services.redis = {
      status: 'error',
      error: error.message
    };
  }

  const statusCode = health.status === 'ok' ? 200 : 
                    health.status === 'degraded' ? 200 : 503;
  
  res.status(statusCode).json(health);
});

async function measureResponseTime(fn) {
  const start = Date.now();
  await fn();
  return Date.now() - start;
}

module.exports = router;

性能优化 ​

1. 连接池优化 ​

javascript
// config/database-optimized.js
const config = {
  production: {
    // ... 其他配置
    
    pool: {
      // 根据服务器规格调整
      max: Math.min(20, process.env.DB_MAX_CONNECTIONS || 20),
      min: 5,
      
      // 连接获取超时
      acquire: 30000,
      
      // 空闲连接超时
      idle: 10000,
      
      // 连接检查间隔
      evict: 1000,
      
      // 自动处理断开连接
      handleDisconnects: true,
      
      // 连接验证
      validate: (client) => {
        return client && !client.connection._closing;
      }
    },
    
    // 查询优化
    dialectOptions: {
      // 查询超时
      timeout: 60000,
      
      // 连接超时
      acquireTimeout: 60000,
      
      // 启用查询缓存（MySQL）
      queryCache: true,
      
      // 批量插入优化
      multipleStatements: true
    }
  }
};

2. 查询优化监控 ​

javascript
// middleware/queryOptimization.js
const { logger } = require('../utils/logger');

function setupQueryOptimization(sequelize) {
  // 慢查询监控
  sequelize.addHook('beforeQuery', (options) => {
    options.startTime = process.hrtime.bigint();
  });

  sequelize.addHook('afterQuery', (options, result) => {
    const endTime = process.hrtime.bigint();
    const duration = Number(endTime - options.startTime) / 1000000; // 转换为毫秒

    // 记录慢查询
    if (duration > 1000) { // 超过1秒
      logger.warn('Slow query detected', {
        sql: options.sql,
        duration: `\${duration.toFixed(2)}ms`,
        type: options.type,
        model: options.model?.name
      });
    }

    // 记录查询统计
    if (process.env.NODE_ENV === 'development') {
      console.log(`Query: \${duration.toFixed(2)}ms - \${options.sql.substring(0, 100)}...`);
    }
  });

  // N+1 查询检测
  const queryCount = new Map();
  
  sequelize.addHook('beforeQuery', (options) => {
    const key = `\${options.model?.name || 'unknown'}-\${options.type}`;
    queryCount.set(key, (queryCount.get(key) || 0) + 1);
  });

  // 定期检查 N+1 查询
  setInterval(() => {
    for (const [key, count] of queryCount.entries()) {
      if (count > 10) { // 短时间内同类查询超过10次
        logger.warn('Potential N+1 query detected', {
          queryType: key,
          count
        });
      }
    }
    queryCount.clear();
  }, 60000); // 每分钟检查一次
}

module.exports = { setupQueryOptimization };

备份和恢复 ​

1. 自动备份脚本 ​

bash
#!/bin/bash
# scripts/backup.sh

# 配置
BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/backup_$DATE.sql"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
echo "开始备份数据库..."
mysqldump \
  --host=$DB_HOST \
  --user=$DB_USER \
  --password=$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --quick \
  --lock-tables=false \
  $DB_NAME > $BACKUP_FILE

# 压缩备份文件
gzip $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
  echo "备份成功: $BACKUP_FILE.gz"
  
  # 上传到云存储（可选）
  # aws s3 cp $BACKUP_FILE.gz s3://your-backup-bucket/
  
else
  echo "备份失败"
  exit 1
fi

# 清理旧备份
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "备份完成"

2. 恢复脚本 ​

bash
#!/bin/bash
# scripts/restore.sh

BACKUP_FILE=$1

if [ -z "$BACKUP_FILE" ]; then
  echo "请指定备份文件"
  echo "用法: ./restore.sh /path/to/backup.sql.gz"
  exit 1
fi

echo "警告：这将覆盖当前数据库"
read -p "确认恢复？(yes/no): " confirm

if [ "$confirm" = "yes" ]; then
  echo "开始恢复数据库..."
  
  # 解压备份文件
  if [[ $BACKUP_FILE == *.gz ]]; then
    gunzip -c $BACKUP_FILE | mysql \
      --host=$DB_HOST \
      --user=$DB_USER \
      --password=$DB_PASS \
      $DB_NAME
  else
    mysql \
      --host=$DB_HOST \
      --user=$DB_USER \
      --password=$DB_PASS \
      $DB_NAME < $BACKUP_FILE
  fi
  
  if [ $? -eq 0 ]; then
    echo "恢复成功"
  else
    echo "恢复失败"
    exit 1
  fi
else
  echo "恢复已取消"
fi

Docker 部署 ​

1. Dockerfile ​

dockerfile
# Dockerfile
FROM node:18-alpine

# 设置工作目录
WORKDIR /app

# 复制 package 文件
COPY package*.json ./

# 安装依赖
RUN npm ci --only=production && npm cache clean --force

# 复制应用代码
COPY . .

# 创建非 root 用户
RUN addgroup -g 1001 -S nodejs && \
    adduser -S nextjs -u 1001

# 设置权限
RUN chown -R nextjs:nodejs /app
USER nextjs

# 暴露端口
EXPOSE 3000

# 健康检查
HEALTHCHECK --interval=30s --timeout=3s --start-period=5s --retries=3 \
  CMD curl -f http://localhost:3000/health || exit 1

# 启动应用
CMD ["npm", "start"]

2. Docker Compose ​

yaml
# docker-compose.prod.yml
version: '3.8'

services:
  app:
    build: .
    ports:
      - "3000:3000"
    environment:
      - NODE_ENV=production
      - DB_HOST=db
      - DB_NAME=myapp
      - DB_USER=myapp_user
      - DB_PASS=secure_password
      - REDIS_URL=redis://redis:6379
    depends_on:
      - db
      - redis
    restart: unless-stopped
    volumes:
      - ./logs:/app/logs
    networks:
      - app-network

  db:
    image: mysql:8.0
    environment:
      - MYSQL_ROOT_PASSWORD=root_password
      - MYSQL_DATABASE=myapp
      - MYSQL_USER=myapp_user
      - MYSQL_PASSWORD=secure_password
    volumes:
      - db_data:/var/lib/mysql
      - ./backups:/backups
    restart: unless-stopped
    networks:
      - app-network

  redis:
    image: redis:7-alpine
    restart: unless-stopped
    networks:
      - app-network

  nginx:
    image: nginx:alpine
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ./nginx.conf:/etc/nginx/nginx.conf
      - ./ssl:/etc/nginx/ssl
    depends_on:
      - app
    restart: unless-stopped
    networks:
      - app-network

volumes:
  db_data:

networks:
  app-network:
    driver: bridge

部署自动化 ​

1. 部署脚本 ​

bash
#!/bin/bash
# scripts/deploy.sh

set -e

echo "开始部署..."

# 拉取最新代码
git pull origin main

# 安装依赖
npm ci --only=production

# 运行测试
npm test

# 备份数据库
./scripts/backup.sh

# 执行迁移
./scripts/deploy-migrate.sh

# 重启应用
pm2 reload ecosystem.config.js --env production

# 健康检查
sleep 10
curl -f http://localhost:3000/health || exit 1

echo "部署完成"

2. PM2 配置 ​

javascript
// ecosystem.config.js
module.exports = {
  apps: [{
    name: 'sequelize-app',
    script: './src/app.js',
    instances: 'max',
    exec_mode: 'cluster',
    env: {
      NODE_ENV: 'development'
    },
    env_production: {
      NODE_ENV: 'production',
      PORT: 3000
    },
    
    // 监控配置
    monitoring: true,
    pmx: true,
    
    // 日志配置
    log_file: './logs/combined.log',
    out_file: './logs/out.log',
    error_file: './logs/error.log',
    log_date_format: 'YYYY-MM-DD HH:mm:ss Z',
    
    // 重启配置
    max_restarts: 10,
    min_uptime: '10s',
    max_memory_restart: '1G',
    
    // 优雅关闭
    kill_timeout: 5000,
    listen_timeout: 3000,
    
    // 健康检查
    health_check_grace_period: 3000
  }]
};

总结 ​

今天我们深入学习了 Sequelize 应用的部署和运维：

  • ✅ 生产环境配置和安全设置
  • ✅ 数据库迁移和回滚策略
  • ✅ 监控、日志和健康检查
  • ✅ 性能优化和查询监控
  • ✅ 备份恢复和容灾方案
  • ✅ Docker 容器化部署
  • ✅ 自动化部署流程

掌握了这些知识，你就能够：

  • 构建稳定可靠的生产环境
  • 实现高效的运维管理
  • 保证应用的高可用性
  • 快速响应和解决问题

生产环境的稳定性是应用成功的关键，希望这些实践能帮助大家构建更好的系统！

相关文章推荐：

有问题欢迎留言讨论，我会及时回复大家！