ดึงฐานข้อมูล production ลงเครื่อง dev ใน Laravel ด้วยคำสั่งเดียว ทำยังไง

ปกติเราก๊อปฐานข้อมูล production ลงเครื่องกันด้วยมือ#

เวลาต้องไล่บั๊กที่เกิดกับข้อมูลจริง ทางที่ใช้กันมาตลอดคือ export จาก phpMyAdmin หรือ ssh เข้าไปรัน mysqldump แล้ว scp ไฟล์ลงมา import ในเครื่อง ทำไม่กี่ครั้งก็พอไหว แต่พอทำบ่อยจะเจอเรื่องเดิม ๆ

• import ไม่ผ่าน เพราะไฟล์มี DEFINER= ที่ต้องใช้สิทธิ์ SUPER หรือ pg_dump รุ่นใหม่ใส่ \restrict ที่ psql ในเครื่องไม่รู้จัก
• ตาราง sessions, jobs หรือ telescope_entries ใหญ่กว่าข้อมูลจริงหลายเท่า ไฟล์เลยใหญ่โดยไม่จำเป็น
• ฐานข้อมูล production ปิด port ไว้ ต้องเข้าทาง ssh ทุกครั้ง
• แต่ละโปรเจกต์มีขั้นตอนของตัวเอง จดไว้ในโน้ตบ้าง ในหัวบ้าง

แพ็กเกจต่อยอดจาก spatie/laravel-db-snapshots#

spatie/laravel-db-snapshots มีคำสั่ง snapshot:create กับ snapshot:load ให้ dump และโหลดฐานข้อมูลอยู่แล้ว ส่วนที่ขาดคือขั้นตอนระหว่างเซิร์ฟเวอร์กับเครื่องเรา แพ็กเกจนี้เติมให้สามส่วน

• API ภายใน บนเซิร์ฟเวอร์ ให้เครื่องเราสั่ง dump และดาวน์โหลด snapshot ผ่าน HTTPS ได้ โดยมี token กั้น
• ตัวทำความสะอาดไฟล์ dump (snapshot:sed) ที่ตัดคำสั่งที่เครื่องเรารันไม่ได้ออก แยกกฎตาม MySQL/MariaDB และ PostgreSQL
• snapshot:sync ที่ร้อยทุกขั้นเข้าด้วยกัน

ผมใช้แพ็กเกจนี้กับโปรเจกต์ที่ดูแลอยู่ แพ็กเกจรองรับ MySQL/MariaDB และ PostgreSQL บน PHP 8.4 และ Laravel 12 หรือ 13

ติดตั้งทั้งบนเซิร์ฟเวอร์และในเครื่องเรา#

แพ็กเกจต้องติดตั้งทั้งสองฝั่ง เพราะฝั่งเซิร์ฟเวอร์เป็นคนส่ง ฝั่งเครื่องเราเป็นคนรับ

composer require phattarachai/db-snapshot-sync-laravel
php artisan db-snapshot-sync:install

db-snapshot-sync:install publish config สร้าง token ใส่ใน .env และพิมพ์ config ที่ต้องเพิ่มใน config/db-snapshots.php, config/filesystems.php และ scheduler ออกมาให้วาง ทั้งสองฝั่งต้องมี disk ชื่อ snapshots

บนเซิร์ฟเวอร์เปิด API ไว้ แล้วใช้ token ตัวเดียวกันทั้งสองฝั่ง

DB_SNAPSHOT_SYNC_API=true
INTERNAL_API_TOKEN=token-ตัวเดียวกัน

DB_SNAPSHOT_SYNC_PROD_URL=https://shop.example.com
INTERNAL_API_TOKEN=token-ตัวเดียวกัน

ดึงลงเครื่องด้วย snapshot:sync#

รอบแรกลองแบบไม่โหลดก่อน ดูว่าดาวน์โหลดและทำความสะอาดไฟล์ผ่าน

php artisan snapshot:sync --no-load
php artisan snapshot:sync --fresh

--fresh สั่งให้เซิร์ฟเวอร์ dump ใหม่ตอนนั้นเลย ถ้าไม่ใส่จะดึง snapshot ล่าสุดที่มีอยู่แล้ว

flowchart LR
  A["snapshot:sync --fresh"] --> B["เซิร์ฟเวอร์ dump ใหม่"]
  B --> C["ดาวน์โหลด .sql.gz"]
  C --> D["snapshot:sed ตัดคำสั่งที่รันไม่ได้"]
  D --> E["โหลดลงฐานข้อมูลในเครื่อง"]

snapshot:sync ทำทุกขั้นต่อกันในคำสั่งเดียว

คำสั่งนี้ล้างฐานข้อมูลในเครื่องทิ้งก่อนโหลด จึงยอมรันเฉพาะ environment ที่อยู่ใน sync.allowed_environments ค่าเริ่มต้นคือ local กับ development เผลอรันบนเซิร์ฟเวอร์ก็จะไม่ทำอะไร

ไฟล์ dump จาก production มักมีคำสั่งที่เครื่องเรารันไม่ได้#

mysqldump บน production มักใส่ DEFINER= ไว้กับ view และ trigger ซึ่งต้องใช้สิทธิ์ SUPER ตอน import ส่วน pg_dump รุ่นใหม่ใส่คำสั่งอย่าง \restrict หรือ SET transaction_timeout ที่ psql รุ่นเก่ากว่าในเครื่องไม่รู้จัก รวมถึงบรรทัด owner และ grant ที่อ้างถึง user ที่เครื่องเราไม่มี

snapshot:sed อ่านหัวไฟล์ว่าเป็น dump ของ MySQL หรือ PostgreSQL แล้วตัดบรรทัดเหล่านี้ออกตามกฎของแต่ละ engine กฎอยู่ใน config ถ้าเจอบรรทัดใหม่ที่ทำให้ import พัง เพิ่มกฎเองได้ ไฟล์ PostgreSQL ประมวลผลทีละบรรทัด ไฟล์หลาย GB จึงไม่กินหน่วยความจำ

ตาราง cache, sessions และ jobs ดึงมาแค่โครงสร้าง#

ตอน dump ให้เครื่อง dev แพ็กเกจข้ามข้อมูลของตารางชั่วคราวพวก cache, sessions, jobs, pulse_* และ telescope_* แต่ยังสร้างตารางให้ครบ ไฟล์จึงเล็กลงมากโดยตารางข้อมูลจริงยังอยู่ครบ รายชื่อตารางแก้ได้ที่ dump.exclude_table_data

production เป็น MySQL แต่ในเครื่องย้ายไป PostgreSQL แล้ว#

ถ้าฐานข้อมูลบน production กับค่า default ในเครื่องเป็นคนละ engine ให้บอก connection ปลายทางไว้ใน config ของ source หรือส่ง --connection ตอนรัน

'sources' => [
    'production' => [
        'url' => env('DB_SNAPSHOT_SYNC_PROD_URL'),
        'connection' => 'mysql',
    ],
],

ก่อนโหลด แพ็กเกจเทียบ engine ในหัวไฟล์กับ driver ของ connection ปลายทางทุกครั้ง ถ้าไม่ตรงกันจะหยุดก่อนแตะฐานข้อมูล ไม่ปล่อยให้ snapshot:load --drop-tables ลบตารางไปแล้วค่อยมาพังตอนรัน SQL

PostgreSQL โหลดผ่าน psql ใน transaction เดียว#

ปลายทางที่เป็น PostgreSQL แพ็กเกจโหลดด้วย psql แทน snapshot:load ของ spatie ตัวโหลดของ spatie แยก statement ด้วย PHP และอ่าน backslash ในข้อความเป็น escape ขณะที่ pg_dump เขียน backslash เป็นตัวอักษรธรรมดา ค่าอย่าง I\'ve จึงทำให้ statement หลังจากนั้นหายไปทั้งก้อนโดยไม่มี error

psql อ่านไฟล์ dump ด้วยตัวอ่านของ PostgreSQL เอง แพ็กเกจรันใน transaction เดียวพร้อม ON_ERROR_STOP=1 และส่ง COMMIT เมื่ออ่านไฟล์จบครบเท่านั้น ถ้าไฟล์ดาวน์โหลดมาไม่ครบหรือมี statement ไหนพัง ฐานข้อมูลในเครื่องจะกลับไปเป็นแบบเดิม เครื่องเราต้องมี PostgreSQL client ติดตั้งไว้ด้วย

API บนเซิร์ฟเวอร์ปิดไว้ ต้องเปิดเอง#

API ส่ง dump ของฐานข้อมูลทั้งก้อนออกไป จึงปิดไว้เป็นค่าเริ่มต้น เปิดเมื่อตั้ง DB_SNAPSHOT_SYNC_API=true

ทุก route ตรวจ bearer token ด้วย hash_equals จำกัด 5 ครั้งต่อนาที และตอบ 404 ทั้งกลุ่มเมื่อแอปรันเป็น local

สำรองไว้นอกเครื่องด้วย snapshot:backup#

snapshot ที่ snapshot:create สร้างทุกคืนมักอยู่บนเครื่องเดียวกับฐานข้อมูล เครื่องเสียก็หายไปพร้อมกัน ส่วน snapshot:cleanup --keep=N ของ spatie ลบตามจำนวนไฟล์ วันไหน deploy หลายรอบ snapshot ก็เก็บย้อนหลังได้สั้นลง แพ็กเกจเพิ่มคำสั่งชุดนี้ให้ฝั่งเซิร์ฟเวอร์

ปลายทางเป็น disk ไหนของ Laravel ก็ได้ เช่น DigitalOcean Spaces หรือ S3, Google Drive หรือ NAS ผ่าน sftp ตั้งไว้ใน config

'backup' => [
    'disks' => ['spaces'],    // disk จาก config/filesystems.php ได้หลายตัว
    'daily_days' => 14,       // daily/ เก็บทุกไฟล์ 14 วัน
    'weekly_weeks' => 8,      // weekly/ เก็บไฟล์ล่าสุดของแต่ละสัปดาห์ 8 สัปดาห์
    'keep_min' => 3,          // ไม่ลบจนเหลือน้อยกว่า 3 ไฟล์
    'stale_after_hours' => 26,
],

ไฟล์ส่งแบบ stream ไม่โหลดทั้งไฟล์เข้าหน่วยความจำ ทุกไฟล์ตั้งเป็น private และหลังอัปโหลดจะเทียบขนาดกับต้นฉบับ ถ้าไม่ตรงจะลบทิ้งแล้วแจ้งว่าล้มเหลว ปลายทางหนึ่งพังก็ไม่กระทบปลายทางอื่น

ตั้ง scheduler ไว้ครั้งเดียว

Schedule::command('snapshot:create', ['--compress'])->dailyAt('04:00');
Schedule::command('snapshot:backup')->dailyAt('04:03')->withoutOverlapping();
Schedule::command('snapshot:prune')->dailyAt('04:05');
Schedule::command('snapshot:backup-check')->hourly();
Schedule::command('snapshot:drill')->monthlyOn(1, '05:00');

backup ขาดช่วงเมื่อไหร่ รู้ภายในชั่วโมง#

snapshot:backup-check ดูสำเนาล่าสุดของแต่ละปลายทาง ถ้าเก่ากว่า stale_after_hours หรืออ่านปลายทางไม่ได้ จะส่ง event SnapshotBackupStale เราดักไว้แล้วแจ้งเตือนช่องทางที่ทีมดูอยู่ได้เลย

use Phattarachai\DbSnapshotSyncLaravel\Events\SnapshotBackupStale;

Event::listen(SnapshotBackupStale::class, function (SnapshotBackupStale $event): void {
    Log::critical('Off-site DB backup is stale', [
        'disk' => $event->disk,
        'age_hours' => $event->ageHours(),
        'error' => $event->error,
    ]);
});

backup ที่ยังไม่เคยลอง restore ก็ยังเชื่อไม่ได้#

snapshot:drill ดาวน์โหลดสำเนาล่าสุดจากปลายทางนอกเครื่อง แล้ว restore ลงฐานข้อมูลชั่วคราวชื่อ <database>_restore_drill บนเซิร์ฟเวอร์เดียวกัน เทียบจำนวนแถวของทุกตารางกับฐานข้อมูลจริง แล้วลบฐานข้อมูลชั่วคราวทิ้ง ถ้ามีตารางที่หายไป หรือตารางที่มีข้อมูลจริงแต่ restore ออกมาว่าง คำสั่งจะล้มเหลว

drill แค่อ่านฐานข้อมูลจริง ข้อมูลไม่ออกจากเครื่อง จึงรันบน production ได้ แต่ต้องเตรียมสามอย่าง

• user ของแอปต้องมีสิทธิ์สร้างฐานข้อมูล
• ดิสก์ต้องพอสำหรับฐานข้อมูลอีกหนึ่งชุด
• รันช่วงที่คนใช้น้อย เพราะ count(*) ทุกตารางคือการอ่านทั้งตาราง

ดึงลงเครื่องได้ง่าย แต่ข้อมูลที่ได้คือข้อมูลจริงของลูกค้า#

snapshot:sed แก้แค่คำสั่ง SQL ไม่ได้ปิดบังข้อมูลส่วนบุคคล ชื่อ เบอร์โทร และอีเมลของลูกค้าจะมาอยู่ในเครื่อง dev ครบ ระบบที่เก็บข้อมูลส่วนบุคคลต้องดูแลตาม PDPA ด้วย อย่างน้อยควรเข้ารหัสดิสก์ของเครื่อง dev (FileVault บน Mac, BitLocker บน Windows) และให้เฉพาะคนที่จำเป็นได้ token ถ้าต้องการข้อมูลที่ปิดบังแล้ว ให้ดึงจาก UAT ที่ล้างข้อมูลไว้แทน production หรือเพิ่มคำสั่งของเราเองที่รันต่อจาก snapshot:sync เพื่อแทนค่าคอลัมน์ที่เป็นข้อมูลส่วนบุคคล

GitHub: phattarachai/db-snapshot-sync-laravel