วิธีสำรองและกู้คืนฐานข้อมูล mysql ด้วย mysqldump

บทช่วยสอนนี้อธิบายวิธีการสำรองและกู้คืนฐานข้อมูล MySQL หรือ MariaDB จากบรรทัดคำสั่งโดยใช้ยูทิลิตี mysqldump

ไฟล์สำรองข้อมูลที่สร้างขึ้นโดยยูทิลิตี mysqldump นั้นเป็นชุดคำสั่ง SQL ที่สามารถใช้เพื่อสร้างฐานข้อมูลต้นฉบับขึ้นมาใหม่ คำสั่ง mysqldump ยังสามารถสร้างไฟล์ในรูปแบบ CSV และ XML

คุณยังสามารถใช้ยูทิลิตี mysqldump เพื่อถ่ายโอนฐานข้อมูล MySQL ของคุณไปยังเซิร์ฟเวอร์ MySQL เครื่องอื่น

ไวยากรณ์คำสั่ง Mysqldump

ก่อนที่จะเข้าสู่วิธีการใช้คำสั่ง mysqldump มาเริ่มกันด้วยการทบทวนไวยากรณ์พื้นฐาน

นิพจน์ยูทิลิตี้ mysqldump ใช้แบบฟอร์มต่อไปนี้:

mysqldump > file.sql

• options - The mysqldump options file.sql - ไฟล์ dump (backup)

ในการใช้คำสั่ง mysqldump เซิร์ฟเวอร์ MySQL จะต้องสามารถเข้าถึงและใช้งานได้

สำรองฐานข้อมูล MySQL เดียว

กรณีการใช้งานบ่อยที่สุดของเครื่องมือ mysqldump คือการสำรองฐานข้อมูลเดียว

ตัวอย่างเช่นในการสร้างการสำรองข้อมูลของฐานข้อมูลชื่อ database_name โดยใช้ root ผู้ใช้และบันทึกลงในไฟล์ชื่อ database_name.sql คุณจะต้องรันคำสั่งต่อไปนี้:

mysqldump -u root -p database_name > database_name.sql

คุณจะได้รับแจ้งให้ป้อนรหัสผ่านรูท หลังจากการตรวจสอบสิทธิ์สำเร็จกระบวนการถ่ายโอนข้อมูลจะเริ่มขึ้น กระบวนการอาจใช้เวลาสักครู่ทั้งนี้ขึ้นอยู่กับขนาดฐานข้อมูล

mysqldump database_name > database_name.sql

สำรองฐานข้อมูล MySQL หลายตัว

ในการสำรองฐานข้อมูล MySQL หลายตัวด้วยคำสั่งเดียวคุณต้องใช้ตัวเลือก - ฐานข้อมูลตามด้วยรายการฐานข้อมูลที่คุณต้องการสำรองข้อมูล แต่ละชื่อฐานข้อมูลจะต้องคั่นด้วยช่องว่าง

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

คำสั่งด้านบนจะสร้างไฟล์ดัมพ์ที่มีฐานข้อมูลทั้งสอง

สำรองฐานข้อมูล MySQL ทั้งหมด

ใช้ตัวเลือก --all-databases เพื่อสำรองฐานข้อมูล MySQL ทั้งหมด:

mysqldump -u root -p --all-databases > all_databases.sql

เช่นเดียวกับตัวอย่างก่อนหน้านี้คำสั่งด้านบนจะสร้างไฟล์ดัมพ์เดียวที่มีฐานข้อมูลทั้งหมด

สำรองฐานข้อมูล MySQL ทั้งหมดเพื่อแยกไฟล์

ยูทิลิตี้ mysqldump ไม่มีตัวเลือกในการสำรองฐานข้อมูลทั้งหมดเพื่อแยกไฟล์ แต่เราทำได้อย่างง่ายดายด้วย bash FOR ลูป:

for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql"; done

คำสั่งด้านบนจะสร้างไฟล์ดัมพ์แยกต่างหากสำหรับแต่ละฐานข้อมูลโดยใช้ชื่อฐานข้อมูลเป็นชื่อไฟล์

สร้างการสำรองฐานข้อมูล MySQL ที่บีบอัด

หากขนาดฐานข้อมูลมีขนาดใหญ่มากคุณควรบีบอัดเอาต์พุต ในการทำเช่นนั้นเพียง gzip เอาต์พุตไปยังยูทิลิตี gzip และเปลี่ยนเส้นทางไปยังไฟล์ดังที่แสดงด้านล่าง:

mysqldump database_name | gzip > database_name.sql.gz

สร้างการสำรองข้อมูลด้วย Timestamp

mysqldump database_name > database_name-$(date +%Y%m%d).sql

คำสั่งด้านบนจะสร้างไฟล์ที่มีรูปแบบดังต่อไปนี้ database_name-20180617.sql

การกู้คืนดัมพ์ MySQL

คุณสามารถคืนค่าดัมพ์ MySQL โดยใช้เครื่องมือ mysql ไวยากรณ์ทั่วไปของคำสั่งมีดังนี้:

mysqld database_name < file.sql

ในกรณีส่วนใหญ่คุณจะต้องสร้างฐานข้อมูลเพื่อนำเข้า หากฐานข้อมูลมีอยู่แล้วก่อนอื่นคุณต้องลบ

ในตัวอย่างต่อไปนี้คำสั่งแรกจะสร้างฐานข้อมูลชื่อ database_name จากนั้นจะนำเข้าดัมพ์ database_name.sql ลงในนั้น:

mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql

กู้คืนฐานข้อมูล MySQL เดี่ยวจากการถ่ายโอนข้อมูล MySQL แบบเต็ม

mysql --one-database database_name < all_databases.sql

ส่งออกและนำเข้าฐานข้อมูล MySQL ในหนึ่งคำสั่ง

แทนที่จะสร้างไฟล์ดัมพ์จากฐานข้อมูลหนึ่งแล้วนำเข้าข้อมูลสำรองไปยังฐานข้อมูล MySQL อื่นคุณสามารถใช้หนึ่งซับเอกสารต่อไปนี้:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

คำสั่งข้างต้นจะ remote_database_name เอาต์พุตไปยังไคลเอนต์ mysql บนรีโมตโฮสต์และมันจะอิมพอร์ตมันไปยังฐานข้อมูลชื่อ remote_database_name ก่อนรันคำสั่งตรวจสอบให้แน่ใจว่ามีฐานข้อมูลอยู่บนเซิร์ฟเวอร์รีโมตแล้ว

สำรองข้อมูลอัตโนมัติด้วย Cron

กระบวนการทำให้การสำรองฐานข้อมูลง่ายขึ้นโดยอัตโนมัติเพียงแค่สร้างงาน cron สิ่งที่จะรันคำสั่ง mysqldump ในเวลาที่กำหนด

ในการตั้งค่าการสำรองข้อมูลอัตโนมัติของฐานข้อมูล MySQL โดยใช้ cronjob ให้ทำตามขั้นตอนด้านล่าง:

1. สร้างไฟล์ชื่อ. .my.cnf ในโฮมไดเรกทอรีผู้ใช้ของคุณ:

   sudo nano ~/.my.cnf

   คัดลอกและวางข้อความต่อไปนี้ลงในไฟล์. my.cnf

   user = dbuser password = dbpasswd

   อย่าลืมแทนที่ dbuser และ dbpasswd ด้วยผู้ใช้ฐานข้อมูลและรหัสผ่านของผู้ใช้

   จำกัด สิทธิ์ของไฟล์ข้อมูลรับรองเพื่อให้ผู้ใช้ของคุณเท่านั้นที่สามารถเข้าถึงไฟล์ได้:

   chmod 600 ~/.my.cnf

   สร้างไดเรกทอรีเพื่อจัดเก็บข้อมูลสำรอง:

   mkdir ~/db_backups

   เปิดไฟล์ crontab ผู้ใช้ของคุณ:

   crontab -e

   เพิ่มงาน cron ต่อไปนี้ที่จะสร้างการสำรองชื่อ mydb ฐานข้อมูลทุกวันเวลาตี 3:

   0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql

   อย่าลืมเปลี่ยน username ด้วย username จริงของคุณ

คุณสามารถสร้าง cronjob อีกอันเพื่อลบข้อมูลสำรองที่เก่ากว่า 30 วัน:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

แน่นอนคุณต้องปรับคำสั่งตามตำแหน่งสำรองและชื่อไฟล์ หากต้องการเรียนรู้เพิ่มเติมเกี่ยวกับคำสั่ง find ให้ตรวจสอบวิธีการค้นหาไฟล์ใน Linux โดยใช้คู่มือบรรทัดคำสั่ง

ข้อสรุป

บทช่วยสอนนี้ครอบคลุมเฉพาะพื้นฐาน แต่ก็ควรเป็นการเริ่มต้นที่ดีสำหรับผู้ที่ต้องการเรียนรู้วิธีสร้างและกู้คืนฐานข้อมูล MySQL จากบรรทัดคำสั่งโดยใช้ยูทิลิตี้ mysqldump

คุณสามารถตรวจสอบบทช่วยสอนเกี่ยวกับวิธีรีเซ็ตรหัสผ่าน root ของ MySQL ในกรณีที่คุณลืม

mysql mariadb สำรอง mysqldump