สำรองฐานข้อมูล MySQL

ใช้งาน MySQL ผ่าน xampp บน Windows มาตลอด ก่อนหน้านี้ ก็ใ่ช้ phpmyadmin สำหรับสำรองฐานข้อมูล แต่ข้อมูลเริ่มใหญ่มากขึ้นจนรู้สึกว่าการสั่ง export / import เริ่มช้ามากขึ้นเรื่อยๆ

ตอนนี้ย้ายไปใช้งาน Linux เต็มตัว เลยต้องย้ายฐานข้อมูลไปด้วย แต่ว่าขนาดไฟล์ใหญ่มาก นึกขึ้นได้ว่าสมัยตอนหัดเรียนรู้เขียนเว็บโปรแกรมมิ่งแรกๆ เคยผ่านตาว่าการสำรองข้อมูลใน MySQL มันสามารถทำงานผ่าน command line ได้ เลยไปค้นๆ วิธี แล้วจดบันทึกไว้กันลืม เพราะตอนแรกงงๆ ทำแล้ว error ตลอด กว่าจะเข้าใจว่าต้องทำยังไง

การสำรองฐานข้อมูล (Backup database)

1. เริ่มต้น ต้องเปิดการทำงานของ MySQL ก่อน จะสั่งผ่าน command line หรือจะเปิดจาก xampp control ก็แล้วแต่สะดวก

2. จากนั้นเข้าใช้งาน command โดยสั่ง [Windows] + R แล้วพิมพ์ cmd

3. เข้าไปในโฟลเดอร์ของ MySQL ด้วยคำสั่ง cd เช่น

cd d:\xampp\mysql\bin\

4. สั่งสำรองข้อมูลด้วย

mysqldump -u [username] -p[password] [database] > [filename]

เช่น

mysqldump -u root -pMyPass my_database > d:\backup\my_database.sql

เราก็จะได้ไฟล์ของฐานข้อมูลมามาล่ะ ทำงานเร็วกว่าตอนใช้ phpmyadmin มากกกกกก

สามารถเพิ่มตัวเลือกอื่นๆ ได้ เช่น
สั่งให้ลบข้อมูลเก่า (drop table) ก่อนที่จะเพิ่มข้อมูลใหม่
--add-drop-table
สั่งให้เก็บเฉพาะส่วนโครงสร้างของ table ไม่ต้องเอาส่วนข้อมูลมาด้วย
--no-data

เช่น

mysqldump -u root -pMyPass --add-drop-table --no-data my_database > d:\backup\my_database.sql

ถ้าเป็นลินุกซ์ ตอนสั่ง export ฐานข้อมูลออกมา เราสามารถสั่งให้ zip ข้อมูลเพื่อลดขนาดไฟล์ได้อีกด้วย

เช่น

/opt/lampp/bin/mysqldump -u root -pMyPass my_database | gzip > ~/backup/my_database.sql.gz

หรือ

/opt/lampp/bin/mysqldump -u root -pMyPass my_database | bzip2 > ~/backup/my_database.sql.bz2

การสั่งบันทึกข้อมูลเป็นไฟล์ จะมีเฉพาะคำสั่งสร้าง table เท่านั้น ไม่มีคำสั่งสร้าง database มาด้วย

ในตอน restore ข้อมูล ถ้าเราเอาไปใส่ในฐานข้อมูลใหม่ ที่ยังไม่เคยมี database นี้มาก่อน ก็ต้องสั่งสร้าง database ขึ้นมาก่อน ถึงจะ restore ได้

การเรียกคืนฐานข้อมูล (Restore database)

ขั้นตอนที่ 1-3 ทำเหมือนกับตอนสำรองข้อมูล

4. ถ้านี่เป็นฐานข้อมูลใหม่ (เช่น จากการย้ายเวอร์ชัน) ที่ยังไม่เคยมี database ที่เราใช้ ต้องสั่งสร้าง database ขึ้นมาก่อน (จำด้วยว่าการ encode ของรหัสอักขระในฐานข้อมูลเดิม ใช้การ encode แบบไหน) เช่น

mysql -u root

จากนั้นพิมพ์คำสั่งสร้าง database ซึ่งจะอยู่ในรูปแบบ

create database [ชื่อดาต้าเบส]

เช่น

create database my_database;
exit;

หรือจะสร้างจาก PhpMyAdmin ก็ได้

5. สั่งเรียกคืนฐานข้อมูลด้วย

mysql -u [username] -p[password] [database] < [filename]

เช่น

mysql -u root -pMyPass my_database < d:\backup\my_database.sql

ถ้าใช้ zip ไฟล์ (บนลินุกซ์) ก็เป็น

sudo gunzip < ~/backup/my_database.sql.gz |
/opt/lampp/bin/mysql -u root -pMyPass my_database

หรือ

sudo bzip2 < ~/backup/my_database.sql.bz2 | /opt/lampp/bin/mysql -u root -pMyPass my_database

* * * * *

หมายเหตุ

1. สัญลักษณ์ ~/ ในลินุกซ์ คือเป็น shortcut สำหรับใช้แทน /home/user/

2. ตอนนี้ xampp ใช้ MariaDB แทน MySQL ไปแล้ว แต่ว่าสามารถใช้คำสั่งได้เหมือนเดิมเปี๊ยบ

อย่าเผลอไปสั่ง mariadbdump หรือ mariadb restore เชียวล่ะ ฮา

3. จะเข้าใช้งาน MySQL command line บนลินุกซ์ (Ubuntu) ให้เปิด terminal และต้องใส่ path ด้านล่างไว้หน้าคำสั่ง mysqldump, mysql ด้วย

/opt/lampp/bin

เช่น

/opt/lampp/bin/mysqldump

หลังจากนั้นก็สามารถใช้คำสั่ง mysqldump, mysql ได้ตามที่เขียนไว้ด้านบน

* * * * *

[Keywords]

MySQL, Backup, Restore, MariaDB, สำรองฐานข้อมูล

.