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

การนำเข้าไฟล์ SQL ผ่านหน้าเว็บอย่าง phpMyAdmin มักติดข้อจำกัดเรื่องขนาดไฟล์อัปโหลดและเวลาประมวลผลของ PHP เมื่อไฟล์ Dump มีขนาดหลายร้อย MB หรือหลาย GB วิธีที่เสถียรกว่ามากคือนำเข้าผ่าน Command Line ด้วยโปรแกรม mysql โดยตรงบนเซิร์ฟเวอร์ ซึ่งไม่มีข้อจำกัดเรื่อง Upload Limit และทำงานได้เร็วกว่า

บทความนี้อธิบายการนำเข้าไฟล์ .sql และ .sql.gz ขนาดใหญ่เข้า MySQL 8 หรือ MariaDB 10.x พร้อมวิธีดูความคืบหน้า วิธีเร่งความเร็ว และวิธีแก้ Error ที่เจอบ่อยเมื่อย้ายฐานข้อมูลข้ามเวอร์ชัน

สิ่งที่ต้องเตรียม

• เข้าเซิร์ฟเวอร์ผ่าน SSH ได้ และมีสิทธิ์ sudo หรือรู้รหัสผ่านผู้ใช้ฐานข้อมูลที่มีสิทธิ์ในฐานข้อมูลปลายทาง
• ไฟล์ SQL อยู่บนเซิร์ฟเวอร์แล้ว หากยังอยู่บนเครื่องของคุณ ให้อัปโหลดขึ้นไปก่อนตามบทความ โอนไฟล์ระหว่างเครื่องด้วย scp และ sftp
• ฐานข้อมูลปลายทางสร้างไว้แล้ว หากยังไม่มี ดู สร้างฐานข้อมูลและผู้ใช้ MySQL/MariaDB พร้อมกำหนดสิทธิ์ (ผู้ใช้ Plesk ดู สร้างฐานข้อมูลและผู้ใช้ฐานข้อมูลบน Plesk)
• พื้นที่ดิสก์ว่างเพียงพอ โดยทั่วไปควรว่างอย่างน้อย 2-3 เท่าของขนาดไฟล์ SQL ที่ยังไม่บีบอัด เพราะข้อมูลในตารางและ Index รวมถึง Binary Log จะใช้พื้นที่เพิ่ม

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

ขั้นตอนที่ 1: ตรวจสอบไฟล์และพื้นที่ดิสก์

ดูขนาดไฟล์และพื้นที่ว่างของพาร์ติชันที่เก็บข้อมูล MySQL (ปกติคือ /var/lib/mysql)

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

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

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

จากนั้นเปิดดูส่วนหัวของไฟล์ เพื่อดูว่า Dump มาจากเวอร์ชันใด มีคำสั่ง CREATE DATABASE หรือ USE อยู่หรือไม่ และใช้ Collation อะไร

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

ถ้าในไฟล์มีบรรทัด USE `ชื่อฐานข้อมูลเดิม`; ข้อมูลจะถูกนำเข้าไปยังฐานข้อมูลชื่อเดิม ไม่ใช่ฐานข้อมูลที่คุณระบุในคำสั่ง ต้องระวังเป็นพิเศษถ้าชื่อฐานข้อมูลบนเซิร์ฟเวอร์ใหม่ต่างจากเดิม

ขั้นตอนที่ 2: เปิด Session ที่ไม่หลุดเมื่อ SSH ขาด

การนำเข้าไฟล์ขนาดหลาย GB อาจใช้เวลาเป็นชั่วโมง ถ้า SSH หลุดกลางทาง คำสั่งจะหยุดและฐานข้อมูลจะเหลือข้อมูลครึ่ง ๆ กลาง ๆ ให้รันคำสั่งภายใน tmux ก่อนเสมอ (รายละเอียดใน ใช้ tmux ให้คำสั่งทำงานต่อแม้ SSH หลุด)

tmux new -s import

ขั้นตอนที่ 3: นำเข้าไฟล์ด้วยคำสั่ง mysql

รูปแบบพื้นฐานสำหรับไฟล์ .sql คือ

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

ระบบจะถามรหัสผ่าน เมื่อพิมพ์แล้วจะไม่มีข้อความใดแสดงจนกว่าจะเสร็จ ถ้ากลับมาที่ Prompt โดยไม่มีข้อความ ERROR แปลว่านำเข้าสำเร็จ

สำหรับไฟล์ที่บีบอัดด้วย gzip ไม่จำเป็นต้องแตกไฟล์ลงดิสก์ก่อน ให้ส่งข้อมูลผ่าน Pipe แทน ซึ่งประหยัดพื้นที่ได้มาก

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

ควรระบุ Character Set ของการเชื่อมต่อให้ตรงกับไฟล์ด้วย เพื่อป้องกันภาษาไทยกลายเป็นตัวอักษรเพี้ยน ไฟล์ Dump จาก mysqldump รุ่นใหม่มักตั้งค่านี้ไว้ในไฟล์อยู่แล้ว แต่การระบุซ้ำไม่มีผลเสีย

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

บน MariaDB 11 ขึ้นไป ชื่อคำสั่งหลักคือ mariadb ถ้าเซิร์ฟเวอร์ไม่มีคำสั่ง mysql ให้เปลี่ยนเป็น mariadb ได้ทันที ตัวเลือกเหมือนกันทุกอย่าง

ขั้นตอนที่ 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/