14.5 Database operations

บทนี้รวบรวมการดำเนินงานที่เกี่ยวข้องกับ database — เข้าถึง, สำรอง, restore, ตรวจสอบ, และสิ่งที่ต้องระวัง

ภาพรวม database

Database	Engine	ใช้โดย	schema
vtrc	MariaDB	vtrc-api (db)	ทั้งหมดของ vtrc-api
vtrc-centralize	MariaDB	vtrc-api (db2)	mirror ของ HR data จาก cu-central
HRMI	MSSQL	cu-central-api, centralize-api	HR data จริงของกาชาด

MariaDB อยู่บน on-prem (ไม่ใช่ใน docker-compose ของ vtrc-api โดยตรง — vtrc-database เป็น host ที่อ้างถึงใน env)

การเข้าถึง MariaDB

ผ่าน phpMyAdmin (port 8093)

URL: http://<vtrc-api-host>:8093/

ใช้ credentials จาก DB_USERNAME และ DB_PASSWORD ใน .env

คำเตือน: phpMyAdmin เปิดสู่ internet บน port 8093 — เป็นปัญหาด้านความปลอดภัย (Volume 9 SEC-7)

ผ่าน mysql CLI ใน container

bash

# เข้า container ของ vtrc-api แล้วใช้ mysql client
docker exec -it vtrc-api-nodejs sh

# ใน container
mysql -h vtrc-database -u root -p
Enter password: ********

mysql> USE vtrc;
mysql> SHOW TABLES;

ผ่าน mysql CLI บน host

bash

mysql -h <db-host> -u root -p vtrc

การเข้าถึง MSSQL HRMI

ผ่าน sqlcmd (บน host ที่มี MSSQL tools)

bash

sqlcmd -S <hrmi-host> -U <username> -P <password> -d HRMI

ผ่าน mssql CLI ใน container ของ centralize-api

bash

docker exec -it centralize-api sh
# ใช้ TypeORM CLI หรือ query ผ่าน NestJS REPL

MSSQL ไม่มี phpMyAdmin เทียบเท่า — ทีมมักใช้ SQL Server Management Studio จากเครื่อง Windows

การสำรองข้อมูล (backup)

Backup MariaDB

bash

# backup schema vtrc ทั้งหมด
mysqldump -h <db-host> -u root -p vtrc > /backup/vtrc-$(date +%Y%m%d-%H%M).sql

# backup schema vtrc-centralize
mysqldump -h <db-host> -u root -p vtrc-centralize > /backup/vtrc-centralize-$(date +%Y%m%d-%H%M).sql

# backup เฉพาะบางตาราง
mysqldump -h <db-host> -u root -p vtrc Users Sessions > /backup/users-sessions-$(date +%Y%m%d).sql

# backup เฉพาะ data (ไม่มี schema)
mysqldump --no-create-info -h <db-host> -u root -p vtrc > /backup/vtrc-data-$(date +%Y%m%d).sql

# backup เฉพาะ schema (ไม่มี data)
mysqldump --no-data -h <db-host> -u root -p vtrc > /backup/vtrc-schema-$(date +%Y%m%d).sql

Backup MSSQL

bash

sqlcmd -S <hrmi-host> -U <username> -P <password> -Q "BACKUP DATABASE HRMI TO DISK = '/backup/HRMI-$(date +%Y%m%d).bak'"

สคริปต์ backup อัตโนมัติ

ไม่มีในระบบ — ต้องสร้างเองผ่าน cron ของ host:

bash

cat > /etc/cron.daily/backup-vtrc-db <<EOF
#!/bin/bash
BACKUP_DIR=/backup/db
mkdir -p \$BACKUP_DIR
mysqldump -h <db-host> -u root -p<password> vtrc | gzip > \$BACKUP_DIR/vtrc-\$(date +\%Y\%m\%d).sql.gz
mysqldump -h <db-host> -u root -p<password> vtrc-centralize | gzip > \$BACKUP_DIR/vtrc-centralize-\$(date +\%Y\%m\%d).sql.gz

# เก็บเฉพาะ 30 วันล่าสุด
find \$BACKUP_DIR -mtime +30 -delete
EOF
chmod +x /etc/cron.daily/backup-vtrc-db

การ restore

Restore MariaDB

bash

# restore ทั้ง schema
mysql -h <db-host> -u root -p vtrc < /backup/vtrc-20250711-1430.sql

# restore เฉพาะบางตาราง (ต้องแก้ไขไฟล์ .sql เอง)
mysql -h <db-host> -u root -p vtrc < /backup/users-only.sql

ระวัง: restore จะ drop หรือ overwrite ข้อมูลที่มีอยู่ — ควรทดสอบใน staging ก่อน

Restore MSSQL

bash

sqlcmd -S <hrmi-host> -U <username> -P <password> -Q "RESTORE DATABASE HRMI FROM DISK = '/backup/HRMI-20250711.bak'"

การตรวจสอบสถานะ database

ดูจำนวน connection ที่เปิดอยู่

sql

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

ดู query ที่กำลังรัน

sql

SHOW PROCESSLIST;

คอลัมน์ Time สูงแสดงว่า query ค้างนาน — อาจเป็นตัวเลือกที่ต้อง kill:

sql

KILL <process_id>;

ดูขนาดตาราง

sql

SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
    table_rows
FROM information_schema.tables
WHERE table_schema = 'vtrc'
ORDER BY size_mb DESC
LIMIT 20;

ดู index ที่ไม่ได้ใช้

sql

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'vtrc';

การทำ schema change

สิ่งที่ต้องระวัง — syncModelToTable

vtrc-api/api/src/index.js:104 เรียก syncModelToTable() ทุกครั้งที่ service start ซึ่งจะ sync schema ตาม Sequelize model ใน code

หมายความว่า:

ถ้าแก้ model ใน code แล้ว deploy, schema จะถูกแก้ไขอัตโนมัติทันที
ถ้ามี column ใหม่ใน code, syncModelToTable จะเพิ่ม column นั้น
แต่ syncModelToTable จะไม่ลบ column ที่มีอยู่ใน db แต่ไม่อยู่ใน code (safe mode)

การเปลี่ยน column type

เปลี่ยน type ใน code แล้ว deploy อาจทำให้ข้อมูลหาย — syncModelToTable อาจ drop และ recreate column

แนวทางปลอดภัย:

สำรองข้อมูลก่อน
ทำ schema change ด้วย SQL manual ใน db
deploy code ทีหลัง

การเพิ่ม index

แนะนำให้เพิ่ม index manual:

sql

ALTER TABLE vtrc.Sessions ADD INDEX idx_userId (userId);

แล้วแก้ model ใน code เพื่อให้ตรง

การดูแล connection pool

vtrc-api ตั้ง pool 100 ต่อ replica (vtrc-api/api/src/connector.js:22):

javascript

pool: { max: 100, min: 0, acquire: 60000, idle: 10000 }

ตรวจสอบ pool ที่เต็ม

ถ้า log แสดง SequelizeConnectionAcquireTimeoutError:

ดู SHOW PROCESSLIST ว่ามี query ค้างนาน
ลด pool size ในแต่ละ replica ลง (จาก 100 → 50)
เพิ่ม max_connections ของ MariaDB ถ้าจำเป็น

ตั้งค่า MariaDB max_connections

sql

SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 500;

เพื่อถาวร แก้ใน /etc/mysql/my.cnf:

[mysqld]
max_connections = 500

การ clean ข้อมูลเก่า

ลบ Session ที่หมดอายุ

sql

-- ดูก่อน
SELECT COUNT(*) FROM vtrc.Sessions WHERE updatedAt < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- ลบทีละน้อย (ป้องกัน lock)
DELETE FROM vtrc.Sessions WHERE updatedAt < DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 1000;

ลบ NotificationLog เก่า

sql

DELETE FROM vtrc.NotificationLogs WHERE createdAt < DATE_SUB(NOW(), INTERVAL 90 DAY) LIMIT 1000;

ลบ PDF payslip ที่หมดอายุ

bash

# ไฟล์ PDF ที่เก่ากว่า 30 วัน
find /home/dev/vtrc-api/api-storage/prs/ -mtime +30 -delete

การ import/export ข้อมูล

Export เป็น CSV

sql

SELECT * FROM vtrc.Users
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Import จาก CSV

sql

LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE vtrc.Users
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

การ debug query ช้า

เปิด slow query log

sql

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- log query ที่ช้ากว่า 2 วินาที
SHOW VARIABLES LIKE 'slow_query_log_file';

ดู log:

bash

tail -f /var/log/mysql/slow.log

ใช้ EXPLAIN

sql

EXPLAIN SELECT * FROM vtrc.Sessions WHERE userSession LIKE '%abc%';

คอลัมน์ type: ALL แสดงว่าใช้ full table scan — ควรเพิ่ม index

สิ่งที่ต้องระวัง — cross-schema write

vtrc-api/api/src/connector.js ใช้ db (schema vtrc) และ db2 (schema vtrc-centralize) แยกกัน — การเขียนข้อมูลที่กระจายทั้งสอง schema ไม่มี transaction boundary

ถ้าต้อง rollback หลัง write ผิดพลาด ต้อง rollback ทั้งสอง schema แยกกัน

รายละเอียด Volume 8 บทที่ 11

แนะนำ

ทำ backup อัตโนมัติ ทุกวัน และทดสอบ restore เดือนละครั้ง
เพิ่ม monitoring ด้วย mtop หรือ pmmm เพื่อดู slow query
วางแผน migration strategy จาก syncModelToTable ไปใช้ Sequelize CLI migration (รายละเอียด Volume 15)
จำกัดการเข้าถึง production db — ใช้ read-only account สำหรับการสอบถามทั่วไป
ปิด phpMyAdmin ใน production หรือจำกัด IP เฉพาะ

14.5 Database operations ​

ภาพรวม database ​

การเข้าถึง MariaDB ​

ผ่าน phpMyAdmin (port 8093) ​

ผ่าน mysql CLI ใน container ​

ผ่าน mysql CLI บน host ​

การเข้าถึง MSSQL HRMI ​

ผ่าน sqlcmd (บน host ที่มี MSSQL tools) ​

ผ่าน mssql CLI ใน container ของ centralize-api ​

การสำรองข้อมูล (backup) ​

Backup MariaDB ​

Backup MSSQL ​

สคริปต์ backup อัตโนมัติ ​

การ restore ​

Restore MariaDB ​

Restore MSSQL ​

การตรวจสอบสถานะ database ​

ดูจำนวน connection ที่เปิดอยู่ ​

ดู query ที่กำลังรัน ​

ดูขนาดตาราง ​

ดู index ที่ไม่ได้ใช้ ​

การทำ schema change ​

สิ่งที่ต้องระวัง — syncModelToTable ​

การเปลี่ยน column type ​

การเพิ่ม index ​

การดูแล connection pool ​

ตรวจสอบ pool ที่เต็ม ​

ตั้งค่า MariaDB max_connections ​

การ clean ข้อมูลเก่า ​

ลบ Session ที่หมดอายุ ​

ลบ NotificationLog เก่า ​

ลบ PDF payslip ที่หมดอายุ ​

การ import/export ข้อมูล ​

Export เป็น CSV ​

Import จาก CSV ​

การ debug query ช้า ​

เปิด slow query log ​

ใช้ EXPLAIN ​

สิ่งที่ต้องระวัง — cross-schema write ​

แนะนำ ​

14.5 Database operations

ภาพรวม database

การเข้าถึง MariaDB

ผ่าน phpMyAdmin (port 8093)

ผ่าน mysql CLI ใน container

ผ่าน mysql CLI บน host

การเข้าถึง MSSQL HRMI

ผ่าน sqlcmd (บน host ที่มี MSSQL tools)

ผ่าน mssql CLI ใน container ของ centralize-api

การสำรองข้อมูล (backup)

Backup MariaDB

Backup MSSQL

สคริปต์ backup อัตโนมัติ

การ restore

Restore MariaDB

Restore MSSQL

การตรวจสอบสถานะ database

ดูจำนวน connection ที่เปิดอยู่

ดู query ที่กำลังรัน

ดูขนาดตาราง

ดู index ที่ไม่ได้ใช้

การทำ schema change

สิ่งที่ต้องระวัง — syncModelToTable

การเปลี่ยน column type

การเพิ่ม index

การดูแล connection pool

ตรวจสอบ pool ที่เต็ม

ตั้งค่า MariaDB max_connections

การ clean ข้อมูลเก่า

ลบ Session ที่หมดอายุ

ลบ NotificationLog เก่า

ลบ PDF payslip ที่หมดอายุ

การ import/export ข้อมูล

Export เป็น CSV

Import จาก CSV

การ debug query ช้า

เปิด slow query log

ใช้ EXPLAIN

สิ่งที่ต้องระวัง — cross-schema write

แนะนำ