[Solved] วิธี Import ไฟล์ .sql ขนาดใหญ่ สำหรับ MySQL

ปัญหาที่มักจะพบกันเป็นประจำ (classic problem) สำหรับการ import ข้อมูลจากไฟล์ .sql เข้า mysql server คือ เรื่องของ Execution Timeout ซึ่งมีสาเหตุหลัก 2 สาเหตุ ดังนี้

1. PHP
2. MySQL Server

PHP

ในกรณีแรก ส่วนใหญ่เรามักจะชอบอัพโหลดไฟล์ .sql ผ่านหน้าเว็บ (phpMyAdmin) เนื่องจากเป็นวิธีที่ค่อยข้างสะดวกและรวดเร็ว ถ้าเราใช้ค่า default ของ PHP config (php.ini) เราจะพบกับปัญหา Max execution time และ Maximum upload file size ค่ือมีค่าน้อยเกินไป ทำให้ไม่เพียงพอสำหรับการ execute ไฟล์ขนาดใหญ่ได้ ซึ่ง error ที่จะพบเจอ ดังรูปภาพด้านล่าง

phpMyAdmin execute error: Incorrect format parameter

วิธีแก้ไข โดยการเพิ่มค่า Config ดังนี้

; ขนาดของไฟล์มากที่สุด
; Maximum allowed size for uploaded files. 
upload_max_filesize = 40M
; ขนาดของข้อมูลที่ส่งผ่าน Method Post มากที่สุด
; Must be greater than or equal to upload_max_filesize 
post_max_size = 40M
; เวลาที่มากที่สุดสำหรับรัน script (หน่วยเป็น วินาที)
; Maximum execution time of each script, in seconds
max_execution_time = 120
; เวลาที่มากที่สุดสำหรับรัน script แต่ละ request (หน่วยเป็น วินาที)
; Maximum amount of time each script may spend parsing request data. It's a good
max_input_time = 120

ค่า Config ต่างๆ สามารถปรับเพิ่ม-ลด ได้ ตามความเหมาะสมของขนาดของไฟล์

MySQL Server

ในกรณีต่อมาคือ ปัญหาที่เกิดจากตัว mysql server เนื่องจาก ไฟล์มีขนาดใหญ่ จึงต้องใช้เวลาในการ execute นาน ซึ่งตัว mysql server เองก็มีค่า default ของ mysql config (my.cnf) มาให้ แต่ไม่เพียงพอสำหรับการใช้งาน จึงทำให้เกิด Error ขึ้น ดังรูปภาพด้านล่าง

Error: #2006 -MySQL server has gone away

หรือสำหรับใครที่ไม่ import ผ่านหน้าเว็บ (phpMyAdmin) แต่ทำผ่าน command line ก็จะเจอปัญหานี้เช่นกัน

$ mysql -u username -p database_name < file.sql
Enter password:
ERROR 2006 (HY000) at line 14861: MySQL server has gone away

วิธีแก้ไข โดยการเพิ่มค่า Config ดังนี้

# The MySQL server
[mysqld]
max_allowed_packet = 8M    # default is 1M

read_buffer_size = 512K  # default is 256K

ค่า Config ต่างๆ สามารถปรับเพิ่ม-ลด ได้ ตามความเหมาะสมของขนาดของไฟล์

***เพิ่มเติม

ถ้าในกรณีที่เราได้ทำการปรับค่าของ max_allowed_packet เพิ่มขึ้นแล้ว แต่ยังพบ error MySQL server has gone away อยู่ ให้ทำการเข้า shell mysql เพื่อไป set ค่าแบบตรงๆ ตามตัวอย่างด้านล่างครับ

$  mysql -u username -p
mysql> SET GLOBAL max_allowed_packet=1072731894
mysql> exit

วิธีดังกล่าวข้างต้น สามารถแก้ไขปัญหาได้ทั้งใน Windows OS และ macOS

(ข้อมูลอาจมีข้อผิดพลาด ถ้าจะเอาบทความนี้ไปอ้างอิงที่อื่นให้ตรวจสอบให้ดีก่อนนะครับ ขอบคุณครับ)

สำหรับวันนี้ ต้องขอลาไปก่อน สวัสดีครับ NottDev :)