MySQL Database

backup mysql database ด้วย คำสั่ง mysqldump

พอดีไปเจอข้อมูลจากเว็บ http://www.narisa.com/blog/patrickz/index.php?showentry=467
น่าสนใจมากครับเลยขอ ทำ mirror ใว้อ่านครับ

สำหรับ database เล็กๆ ใช้ phpmyadmin ก็สะดวกดี แต่ถ้า database ขนาดใหญ่ หลายล้าน records

ล่ะ ก็คงไม่สะดวก ยิ่ง Server config ให้ PHP เป็น safe mode ก็จะมีปัญหากับ time-out ซึ่งทำให้ใช้ phpMyAdmin ไม่ได้

เขียน PHP scripts ให้ backup database โดยการ generate เป็น ไฟล์ SQL เก็บเอาไว้
มันก็ใช้งานได้ดีอยู่.. แต่มันทำงานได้ช้ามาก (เกือบ 1 ชั่วโมง สำหรับ database ขนาด 300 MB)

สำหรับ database ขนาดใหญ่แล้ว ผมคิดว่า mysqldump นั้นมีประสิทธิภาพมากที่สุดแล้ว
ทั้งๆที่มากับ mysql อยู่แล้ว ทำไมจึงมองข้ามก็ไม่รู้ วันนี้ก็เลยมาลองใช้สิ่งที่ MySQL จัดเตรียมมาให้แล้ว

การ backup
คำสั่งสำหรับ backup ฐานข้อมูล ของ mysql คือ mysqldump รูปแบบการใช้งานมีดังต่อไปนี้

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases db_name1 [db_name2 db_name3...]
shell> mysqldump [options] --all-databases

สำหรับรายละเอียดของ options ต่างๆ ดูที่ http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

ตัวอย่างการใช้งาน

dump database
#mysqldump -u root -p mydatabase > db.sql

dump เฉพาะ tables customer
#mysqldump -u root -p mydatabase -tables customer > db.sql

หรับบน Linux จะมี mysqlhotcopy ให้ใช้ด้วย พัฒนาโดยคุณ Tim Bunce เขียนด้วย PERL เพราะท่านเล่น

lock และ flush จากนั้นก็ copy file มาซะงั้น

อันนี้เป็นตัวอย่างที่ผมใ้ช้อยู่
mysqldump -u root -pMyPassword --all-database --lock-all-table | bzip2 -c>

db-backup_$(date +%Y%m%d_%H%M%S).sql.bz2

จะได้ db-backup_20071926_070420.sql.bz2

การ restore

ง่ายๆ และรวดเร็วดี อันนี้ในกรณี restore ทั้ง database
#mysql -u root -p database < db.sql

แต่ถ้าต้องการเลือก database ใช้ mysqlimport

#mysql -u root -pMyPassword MyDatabase < db.sql