นำเข้าไฟล์ SQL ขนาดใหญ่ผ่าน Command Line

ls -lh /root/backup/shop.sql.gz
df -h /var/lib/mysql

gzip -l /root/backup/shop.sql.gz

zcat /root/backup/shop.sql.gz | head -n 40
# ถ้าเป็นไฟล์ .sql ธรรมดา
head -n 40 /root/backup/shop.sql

tmux new -s import

mysql -u shopuser -p shopdb < /root/backup/shop.sql

zcat /root/backup/shop.sql.gz | mysql -u shopuser -p shopdb

zcat /root/backup/shop.sql.gz | mysql --default-character-set=utf8mb4 -u shopuser -p shopdb

ขั้นตอนที่ 4: ดูความคืบหน้าด้วย pv

คำสั่งข้างต้นไม่บอกว่าไปถึงไหนแล้ว โปรแกรม pv ช่วยแสดงเปอร์เซ็นต์ ความเร็ว และเวลาที่เหลือโดยประมาณ

# Ubuntu
sudo apt install pv
# AlmaLinux / Rocky Linux (pv อยู่ใน EPEL)
sudo dnf install epel-release
sudo dnf install pv

จากนั้นให้ pv เป็นตัวอ่านไฟล์แทน

# ไฟล์ .sql
pv /root/backup/shop.sql | mysql -u shopuser -p shopdb

# ไฟล์ .sql.gz (เปอร์เซ็นต์คิดจากขนาดไฟล์ที่บีบอัด)
pv /root/backup/shop.sql.gz | zcat | mysql -u shopuser -p shopdb

ผลลัพธ์จะเป็นแถบความคืบหน้าในรูปแบบประมาณ 1.20GiB 0:04:12 [4.9MiB/s] [=====> ] 45% ETA 0:05:01

ขั้นตอนที่ 5: เร่งความเร็วการนำเข้า (ทางเลือก)

สำหรับไฟล์ที่ใหญ่มาก การปิดการตรวจสอบบางอย่างระหว่างนำเข้าจะช่วยลดเวลาได้ชัดเจน เพราะไม่ต้องตรวจ Foreign Key และ Unique Key ทีละแถว และไม่ต้อง Commit ทุกคำสั่ง ให้เข้า mysql client แล้วใช้คำสั่ง SOURCE

mysql --default-character-set=utf8mb4 -u shopuser -p shopdb

SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;
SOURCE /root/backup/shop.sql;
COMMIT;
SET unique_checks = 1;
SET foreign_key_checks = 1;

ค่าเหล่านี้มีผลเฉพาะ Session นี้เท่านั้น ไม่กระทบผู้ใช้อื่น คำสั่ง SOURCE ใช้ได้กับไฟล์ .sql ที่แตกแล้วเท่านั้น ข้อควรระวังคือ เมื่อปิด unique_checks และ foreign_key_checks MySQL จะไม่ตรวจความถูกต้องของข้อมูลซ้ำ จึงเหมาะกับไฟล์ Dump ที่มาจากฐานข้อมูลที่ถูกต้องอยู่แล้วเท่านั้น

ตัวเลือกอื่นที่มีผลต่อความเร็ว สรุปได้ดังนี้

วิธี	ผลที่ได้	ข้อควรระวัง
ปิด foreign_key_checks และ unique_checks	ลดงานตรวจสอบระหว่าง INSERT	ใช้กับ Dump ที่เชื่อถือได้เท่านั้น
autocommit = 0 แล้ว COMMIT ท้ายสุด	ลดจำนวนการเขียนลงดิสก์	ถ้าล้มกลางทาง ข้อมูลใน Transaction ที่ยังไม่ Commit จะถูกยกเลิก
เพิ่ม innodb_buffer_pool_size	อ่านเขียน Index ในหน่วยความจำได้มากขึ้น	ต้องมี RAM พอ ดู ปรับแต่ง MySQL/MariaDB เบื้องต้นด้วย innodb_buffer_pool_size
ใช้ Dump แบบ Extended INSERT (ค่าเริ่มต้นของ mysqldump)	หนึ่งคำสั่ง INSERT ใส่ได้หลายแถว	ต้องทำตั้งแต่ตอนสร้างไฟล์ Dump

ตรวจสอบผลลัพธ์

เมื่อนำเข้าเสร็จ ให้นับจำนวนตารางในฐานข้อมูลปลายทาง แล้วเทียบกับฐานข้อมูลต้นทาง

mysql -u shopuser -p -e "SELECT COUNT(*) AS tables FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'shopdb';"

จากนั้นสุ่มนับจำนวนแถวของตารางสำคัญ เช่น ตารางคำสั่งซื้อหรือตารางผู้ใช้ ด้วย SELECT COUNT(*) แล้วเทียบกับต้นทาง ไม่ควรใช้คอลัมน์ TABLE_ROWS ใน information_schema เป็นตัวเทียบ เพราะสำหรับ InnoDB เป็นเพียงค่าประมาณ

mysql -u shopuser -p shopdb -e "SELECT COUNT(*) FROM orders;"

สุดท้ายเปิดเว็บไซต์หรือแอปพลิเคชันที่ใช้ฐานข้อมูลนี้ แล้วตรวจว่าข้อความภาษาไทยแสดงถูกต้อง

ปัญหาที่พบบ่อย

ERROR 2006 (HY000): MySQL server has gone away หรือ Got a packet bigger than 'max_allowed_packet' bytes

เกิดเมื่อมีคำสั่ง INSERT บางคำสั่งใหญ่กว่าค่า max_allowed_packet ของเซิร์ฟเวอร์ แก้โดยเพิ่มค่าชั่วคราวก่อนนำเข้า (ต้องใช้ผู้ใช้ที่มีสิทธิ์ระดับผู้ดูแล เช่น root)

sudo mysql -e "SET GLOBAL max_allowed_packet = 1073741824;"

แล้วรันคำสั่งนำเข้าใหม่ใน Session ใหม่ พร้อมระบุค่าเดียวกันฝั่ง Client

zcat /root/backup/shop.sql.gz | mysql --max-allowed-packet=1G -u shopuser -p shopdb

ถ้าต้องการให้ค่าคงอยู่หลังรีสตาร์ต ให้เพิ่ม max_allowed_packet = 1G ในส่วน [mysqld] ของไฟล์ตั้งค่า (Ubuntu กับ MySQL คือ /etc/mysql/mysql.conf.d/mysqld.cnf, Ubuntu กับ MariaDB คือ /etc/mysql/mariadb.conf.d/50-server.cnf, AlmaLinux/Rocky คือไฟล์ใน /etc/my.cnf.d/)

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s)

มักเกิดจากบรรทัด DEFINER=`ผู้ใช้เดิม`@`host` ใน View, Trigger หรือ Stored Procedure ที่อ้างถึงผู้ใช้ที่ไม่มีบนเซิร์ฟเวอร์ใหม่ ทางแก้ที่ง่ายคือตัดส่วน DEFINER ออกระหว่างนำเข้า ซึ่งจะทำให้ Object เหล่านั้นใช้ผู้ใช้ที่กำลังนำเข้าเป็นเจ้าของแทน

zcat /root/backup/shop.sql.gz | sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' | mysql -u shopuser -p shopdb

ถ้า Error อ้างถึง @@GLOBAL.GTID_PURGED แปลว่า Dump สร้างจากเซิร์ฟเวอร์ที่เปิด GTID ไว้ ให้สร้าง Dump ใหม่ด้วยตัวเลือก --set-gtid-purged=OFF

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

Collation นี้มีเฉพาะใน MySQL 8 เมื่อนำ Dump จาก MySQL 8 ไปเข้า MariaDB จะไม่รู้จัก ให้เปลี่ยนเป็น utf8mb4_unicode_ci ระหว่างนำเข้า

zcat /root/backup/shop.sql.gz | sed 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' | mysql -u shopuser -p shopdb

ถ้าต้องการแปลงทั้งฐานข้อมูลให้เป็น utf8mb4 อย่างถูกต้อง ดู แปลงฐานข้อมูลเป็น utf8mb4 ให้รองรับภาษาไทยและอีโมจิ

ERROR 1046 (3D000): No database selected หรือดิสก์เต็มระหว่างนำเข้า

Error 1046 เกิดเมื่อไม่ได้ระบุชื่อฐานข้อมูลท้ายคำสั่งและในไฟล์ไม่มีคำสั่ง USE ให้ใส่ชื่อฐานข้อมูลเพิ่มท้ายคำสั่ง ส่วนกรณีดิสก์เต็ม (ข้อความ The table is full หรือ Error 28) ให้ตรวจพื้นที่ตามบทความ ดิสก์เต็ม: ตรวจสอบพื้นที่และหาไฟล์ขนาดใหญ่บน Linux โดยเฉพาะไฟล์ Binary Log ใน /var/lib/mysql ที่อาจโตขึ้นมากระหว่างนำเข้า

หากทำตามขั้นตอนแล้วยังนำเข้าไม่สำเร็จ หรือไม่แน่ใจว่าควรปรับค่าใดบนเซิร์ฟเวอร์ ติดต่อทีมงาน THAI DATA CLOUD ได้ที่ https://thaidata.cloud/contact/

อ่านเพิ่มเติมเกี่ยวกับ SQL

• เอกสารทางการของ MariaDB
• ตรวจสอบและซ่อมตารางด้วย mysqlcheck
• คู่มือการใช้งานทั้งหมดของ THAI DATA CLOUD