DatabaseDevGuide0006

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

const pool = mysql.createPool({
  host:     'localhost',
  user:     'app_user',      // root 사용 금지!
  password: process.env.DB_PASSWORD,
  database: 'shopdb',
  connectionLimit:    10,    // 최대 동시 연결 수
  waitForConnections: true,
  charset:  'utf8mb4',
  timezone: '+09:00'
});

module.exports = pool;

// ✅ Prepared Statement — SQL Injection 방지의 핵심!
const UserModel = {
  findAll: async ({ page = 1, limit = 10, search = '' } = {}) => {
    const offset = (page - 1) * limit;
    const [rows] = await pool.execute(
      `SELECT id, username, email FROM users
       WHERE username LIKE ? OR email LIKE ?
       ORDER BY created_at DESC LIMIT ? OFFSET ?`,
      [`%${search}%`, `%${search}%`, limit, offset]
    );
    return rows;
  },
  create: async ({ username, email, passwordHash, role = 'user' }) => {
    const [result] = await pool.execute(
      'INSERT INTO users (username, email, password_hash, role) VALUES (?, ?, ?, ?)',
      [username, email, passwordHash, role]
    );
    return { id: result.insertId, username, email, role };
  },
  softDelete: async (id) => {
    const [result] = await pool.execute(
      'UPDATE users SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL', [id]
    );
    return result.affectedRows > 0;
  }
};

// ── Sequelize 모델 정의 ──
const { DataTypes, Model } = require('sequelize');
class User extends Model {}
User.init({
  username: { type: DataTypes.STRING(50), allowNull: false,
    validate: { len: [2, 50] } },
  email: { type: DataTypes.STRING, allowNull: false, unique: true,
    validate: { isEmail: true } },
  role: { type: DataTypes.ENUM('user', 'admin'), defaultValue: 'user' }
}, {
  sequelize, modelName: 'User', tableName: 'users',
  timestamps: true,   // createdAt, updatedAt 자동 관리
  paranoid: true,     // deletedAt: Soft Delete 지원
  underscored: true   // camelCase → snake_case 변환
});
User.hasMany(Order, { foreignKey: 'userId', as: 'orders' });

// Sequelize CRUD
const users = await User.findAll({
  where: { isActive: true },
  include: [{ model: Order, as: 'orders', limit: 5 }],
  order: [['createdAt', 'DESC']], limit: 10
});

// ── Prisma Schema (prisma/schema.prisma) ──
model User {
  id        Int      @id @default(autoincrement())
  username  String   @db.VarChar(50)
  email     String   @unique
  role      Role     @default(USER)
  orders    Order[]
  createdAt DateTime @default(now()) @map("created_at")
  deletedAt DateTime? @map("deleted_at")
  @@map("users")
}
enum Role { USER ADMIN MODERATOR }

// ── Prisma CRUD ──
const prisma = new PrismaClient({ log: ['query', 'slow', 'error'] });

const userWithOrders = await prisma.user.findUnique({
  where: { id: userId },
  include: { orders: { where: { status: { not: 'CANCELLED' } }, take: 10 } }
});

// 페이지네이션 + 총 개수 (트랜잭션으로 한 번에)
const [users, total] = await prisma.$transaction([
  prisma.user.findMany({ where: { deletedAt: null },
    skip: (page-1)*limit, take: limit }),
  prisma.user.count({ where: { deletedAt: null } })
]);

// ✅ 실전 트랜잭션 — 주문 처리 (재고 차감 + 주문 생성 + 결제)
async function createOrder({ userId, items, paymentInfo }) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    
    for (const item of items) {
      const [stocks] = await conn.execute(
        'SELECT stock FROM products WHERE id = ? FOR UPDATE', [item.productId]);
      if (stocks[0].stock < item.quantity) throw new Error('재고 부족');
      await conn.execute('UPDATE products SET stock = stock - ? WHERE id = ?',
        [item.quantity, item.productId]);
    }
    
    const totalPrice = items.reduce((sum, i) => sum + i.price * i.quantity, 0);
    const [result] = await conn.execute(
      'INSERT INTO orders (user_id, total_price, status) VALUES (?, ?, ?)',
      [userId, totalPrice, 'PENDING']);
    
    await conn.commit();  // ✅ 성공 → COMMIT
    return { orderId: result.insertId };
  } catch (err) {
    await conn.rollback();  // ❌ 실패 → ROLLBACK
    throw err;
  } finally {
    conn.release();  // 반드시 연결 반납!
  }
}

-- ✅ 좋은 인덱스 설계
CREATE INDEX idx_users_email ON users(email);  -- 카디널리티 높은 컬럼
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);  -- 복합 인덱스
-- 커버링 인덱스 (SELECT 컬럼도 포함 → 테이블 접근 불필요)
CREATE INDEX idx_products_cover ON products(category_id, price, id, name);

-- ❌ 인덱스 무용지물
WHERE DATE(created_at) = '2026-03-27'   -- 함수로 감싸기
WHERE created_at >= '2026-03-27'        -- ✅ 이렇게 바꿔야
WHERE name LIKE '%김%'                   -- 앞 와일드카드 ❌
WHERE name LIKE '김%'                    -- ✅
WHERE user_id = '123'                    -- 타입 불일치 ❌ (INT인데 문자열)
WHERE user_id = 123                      -- ✅

for (const user of users) {
  // 매번 DB 쿼리!
  user.orders = await findOrders(user.id);
}

// Sequelize
User.findAll({ include: [Order] });
// Prisma
prisma.user.findMany({
  include: { orders: true }
});

## 전체 DB 백업 + 압축
mysqldump -u root -p --all-databases --single-transaction   | gzip > /backup/all_$(date +%Y%m%d_%H%M%S).sql.gz

## 특정 DB 백업
mysqldump -u root -p shopdb > /backup/shopdb_$(date +%Y%m%d).sql

## 복구
gunzip < /backup/shopdb_20260327.sql.gz | mysql -u root -p shopdb

## Point-in-Time Recovery (특정 시점 복구!)
# 09:00 백업, 10:30에 실수로 삭제 → 10:29:59로 복구
mysql -u root -p shopdb < /backup/shopdb_0900.sql  # 백업 복구
mysqlbinlog --start-datetime="2026-03-27 09:00:00"             --stop-datetime="2026-03-27 10:29:59"             /var/lib/mysql/binlog.000001 | mysql -u root -p shopdb

## 자동 백업 (crontab: 매일 새벽 2시)
# 0 2 * * * /home/ubuntu/backup.sh
#!/bin/bash
BACKUP_DIR="/backup/mysql"
mysqldump -u backup_user -p"${DB_PASSWORD}" --all-databases --single-transaction   | gzip > "${BACKUP_DIR}/all_$(date +%Y%m%d).sql.gz"
find ${BACKUP_DIR} -name "*.sql.gz" -mtime +7 -delete  # 7일 이상 삭제
echo "✅ 백업 완료: $(date)"

const q = `WHERE id=${userId}`;
// userId = "1 OR 1=1 --"
// → 전체 DB 유출!!

pool.execute(
  'WHERE id = ?', [userId]
);
// SQL과 데이터 분리!

-- DB 권한 관리 — 최소 권한 원칙
CREATE USER 'shopapp'@'10.0.1.%' IDENTIFIED BY 'StrongP@ssw0rd!';
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'shopapp'@'10.0.1.%';

-- 읽기 전용 계정 (보고서용)
CREATE USER 'report_user'@'10.0.2.%' IDENTIFIED BY '...';
GRANT SELECT ON shopdb.* TO 'report_user'@'10.0.2.%';

SHOW GRANTS FOR 'shopapp'@'10.0.1.%';
FLUSH PRIVILEGES;

// Read/Write 분리 구현
const writePool = mysql.createPool({ host: process.env.DB_MASTER_HOST, ... });
const readPools = ['slave1', 'slave2', 'slave3'].map(h => mysql.createPool({ host: h, ... }));
let idx = 0;
const getReadPool = () => readPools[idx++ % readPools.length];

await writePool.execute('INSERT INTO orders ...', []);  // 쓰기 → Master
const [rows] = await getReadPool().execute('SELECT ...');  // 읽기 → Slave 분산

// ── AWS RDS 연결 설정 ──
const pool = mysql.createPool({
  host:     process.env.DB_HOST,  // xxx.rds.amazonaws.com
  user:     process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  ssl: { ca: fs.readFileSync('./certs/rds-ca.pem') }  // SSL 필수!
});

// ── Zero-Downtime 마이그레이션 ──
-- ❌ ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL; (수십 분 잠금!)
-- ✅ 4단계 무중단 방식:
-- 1. nullable 컬럼 추가 (잠금 최소화)
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;
-- 2. 배치로 기존 데이터 채우기
UPDATE users SET phone = '' WHERE phone IS NULL LIMIT 10000;
-- 3. NOT NULL 제약 추가
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';

-- ── Slow Query Log 설정 ──
slow_query_log = 1          -- 활성화
long_query_time = 1         -- 1초 이상 기록
log_queries_not_using_indexes = 1
SHOW FULL PROCESSLIST;      -- 실행 중인 쿼리 확인
KILL QUERY 1234;            -- 오래된 쿼리 종료