ทำอย่างไรถึงจะ Dump ข้อมูลขนาดมหึมาใน MySQL ( > 30 MB ขึ้นไป)

ปรกติจะใช้ phpmyadmin ในการ Export ข้อมูล Backup ออกมา แต่ถ้าหากว่าฐานข้อมูลมีขนาดใหญ่มาก มันจะมีปัญหา Memory ของ Server อาจไม่พอ ดีไม่ได้อาจ Timeout ไปเลยก็ได้ แล้วจะทำอย่างไรดีหว่าก้บการ Backup ฐานข้อมูลขนาดใหญ่ใน MySQL

กรณีอย่างนี้มีวิธีแก้ไขหลายวิธีขึ้นอยู่กับผู้ดูแลมากกว่าว่าจะเลือกใช้ Solution ใด

แต่วิธีการที่แนะนำก็คือใช้คำสั่ง mysqldump ซึ่งเป็น command ที่มีติดตั้งมากับ mysql อยู่แล้ว

การ Export

• เขียน batch ไฟล์เพื่อทำการ Export ข้อมูลออกมา
  • mysqldump -u ชื่อผู้ใช้ -p รหัสผ่าน ชื่อฐานข้อมูล > ชื่อไฟล์.sql
  • เช่น mysqldump -u root -p --all -database --lock-all-table -c > db-backup.sql
  • ผลลัพธ์ที่ได้คือไฟล์ db-backup.sql
• จากนั้นใช้ Schedule Task ของ Windows ตั้งเป็น Job เพื่อให้ Backup ให้อัตโนมัติตามเวลาที่กำหนด

การ Restore

• ในกรณีที่ไฟล์มีขนาดใหญ่ไม่เกิน 50 Mb และต้องการความสะดวกสามารถใช้โปรแกรมชื่อ Bigdump ซึ่งจะช่วยในการ Import ข้อมูลขนาดใหญ่เข้าไปได้ โปรแกรมนี้เป็น PHP Script ทำงานผ่าน Browser ได้ทันที สามารถดูรายละเอียดเพิ่มได้ที่ http://www.ozerov.de/bigdump.php
  • ข้อควรระวังของการใช้ Bigdump ให้เปลี่ยนชื่อไฟล์ใหม่ให้เป็นชื่อที่จำยากๆ ก่อนนำไปใช้งานจริง มิฉะนั้นข้อมูลของคุณอาจถูกขโมยได้
  • หากไม่ได้ใช้งานแล้วลบทิ้งไปเลยก็ดี
• ในกรณีที่ไฟล์มีขนาดใหญ่เกิน 50 Mb ให้ใช้คำสั่ง mysql ในการ Restore ข้อมูลกลับไปเหมือนเดิม
  • mysql -u ชื่อผู้ใช้ -p รหัสผ่าน ชื่อฐานข้อมูล < ชื่อไฟล์.sql
  • เช่น mysql -u root -p mysql <>

ตัวอย่าง Batch สำหรับ ทำงานจริง (ควรทำตามคำแนะนำอย่างเคร่งครัดมิฉะนั้นอาจมีปัญหาเรื่อง Security)

@rem จะต้องลงโปรแกรม GZip ในเครื่อง Server ก่อน
@rem จะต้องสร้างโฟลเดอร์สำหรับ Backup ก่อน
@rem ค่าต่างๆ ที่ต้อง Config เพิ่มเติมดูในส่วน Variable
@rem ตั้ง Task Schedule ให้ Run Auto
@rem ห้ามใช้ root กับ user สำหรับ connection ฐานข้อมูล
@rem ห้ามวางไฟล์ที่ Backup แล้วไว้ที่ www หรือที่ C:\
@if not "%ECHO%"=="" echo %ECHO%
@if not "%OS%"=="Windows_NT" goto DOSEXIT
echo ===== This batch for backup mysql database on teamwork opensource server =====
cls
@rem Read server time for backup filename.
if defined TRACE %TRACE% [Sub_9 %0 %*]
for /F "tokens=1-4 delims=/ " %%i in ('date /t') do (
set DayOfWeek=%%i
set Month=%%j
set Day=%%k
set Year=%%l
)
for /F "tokens=1-4 delims=:" %%i in ('time /t') do (
set Hour=%%i
set Minute=%%j
set Second=%%k
)
set DATEBAK=%Year%%Month%%Day%

@rem Define variable for execute batch backup.
set USER=root
set PWS=password
set PATHBAK=C:\Backup\
set GZIPDIR=c:\PROGRA~1\GnuWin32\bin
set BAKFILE=%PATHBAK%db_backup_%DATEBAK%.sql

echo ===== Start Backup MySQL dump all database to file =====
mysqldump -u%USER% -p%PWS% --all-database --lock-all-table -c > %BAKFILE%

echo ===== Start zip file with GZip =====
@REM Change to workdir
CD %PATHBAK%

@REM Zip up database
%GZIPDIR%\gzip.exe %BAKFILE%

@REM Change to root path
CD\