SSD Nodes Learn Hosting plans →
คู่มือ Matt Connorโดย Matt Connor

Export และ Import ฐานข้อมูล MySQL ด้วย mysqldump ผ่าน SSH

ไฟล์ .sql ใหญ่เกินที่ phpMyAdmin รับได้ ย้ายฐานข้อมูลด้วย mysqldump และ mysql ผ่าน SSH บีบอัดด้วย gzip เก็บ procedure กับ event และตรวจว่าภาษาไทยไม่เพี้ยนด้วย CHECKSUM TABLE

export และ import ฐานข้อมูล MySQL ด้วย mysqldump ทำงานอย่างไร

การ export และ import ฐานข้อมูล MySQL ผ่าน SSH ใช้คำสั่งหลักแค่สองตัว mysqldump เขียนฐานข้อมูลออกมาเป็นไฟล์ SQL ส่วน mysql อ่านไฟล์นั้นกลับเข้าไป ทั้งสองคำสั่งไม่ผ่าน PHP เลย จึงไม่มีขีดจำกัดขนาดไฟล์แบบที่ phpMyAdmin มี ถ้าคุณกำลังย้ายเว็บ PHP หรือ WordPress ออกจาก shared hosting แล้วหน้า Import ของ phpMyAdmin ไม่ยอมรับไฟล์หรือค้างกลางทาง วิธีในคู่มือนี้ใช้ได้กับไฟล์ทุกขนาด

งานทั้งหมดมีสี่ขั้น:

  1. dump ฐานข้อมูลต้นทางด้วย mysqldump แล้วบีบอัดด้วย gzip ในคำสั่งเดียวกัน
  2. คัดลอกไฟล์ .sql.gz ไปที่ VPS ใหม่
  3. สร้างฐานข้อมูลเปล่าบนเครื่องปลายทาง
  4. คลาย gzip แล้วส่งข้อมูลเข้า mysql โดยตรง ไม่ต้องแตกไฟล์ลง disk ก่อน

ระหว่างทางเราจะตรวจสองเรื่องที่คนย้ายเว็บไทยพลาดบ่อย คือ stored procedure กับ event ติดมาด้วยหรือไม่ และภาษาไทยกลับมาครบทุก byte หรือไม่ เราจะไม่เชื่อค่าเริ่มต้นของโปรแกรม แต่จะสร้างข้อมูลทดสอบแล้วดูผลจริงบนเครื่องของคุณ คำสั่งทั้งหมดใช้แพ็กเกจ mysql-server ของ Ubuntu และทำงานเหมือนกันทั้งบน Ubuntu 24.04 และ 26.04

ทำไม phpMyAdmin ถึง import ไฟล์ใหญ่ไม่ได้

phpMyAdmin เป็นโปรแกรม PHP ไฟล์ที่คุณเลือกในหน้า Import ต้องอัปโหลดขึ้นไปใน HTTP request ครั้งเดียว และ PHP เป็นตัวตัดสินว่า request นั้นใหญ่ได้แค่ไหน ค่าที่คุมเรื่องนี้คือ upload_max_filesize และ post_max_size ในไฟล์ php.ini ตัวเลข "Max" ที่ phpMyAdmin แสดงข้างปุ่มเลือกไฟล์ก็มาจากค่าเหล่านี้ ไฟล์ที่ใหญ่กว่านั้นจึงถูกปฏิเสธก่อนที่ MySQL จะได้เห็นแม้แต่บรรทัดเดียว

ไฟล์ที่ขนาดผ่านก็ยังล้มได้ เพราะ PHP จำกัดเวลาที่ script หนึ่งตัวรันได้ด้วย (max_execution_time) การ import ทั้งหมดทำงานอยู่ภายใต้ PHP ถ้าเวลาหมดก่อน งานจะหยุดกลางไฟล์ และบางตารางถูกสร้างไปแล้วเพียงครึ่งเดียว บน shared hosting คุณมักแก้ค่าเหล่านี้เองไม่ได้ บน VPS คุณแก้ได้ แต่ไม่จำเป็นต้องแก้

mysql บน command line อ่านไฟล์จาก disk หรือจาก pipe ทีละส่วน ไม่มี PHP อยู่ตรงกลาง ขีดจำกัดทั้งสองแบบข้างบนจึงไม่เกี่ยวข้องเลย ฝั่ง export ก็เช่นกัน mysqldump เขียนข้อมูลออกมาเป็น stream ไฟล์ขนาดหลาย GB จึงใช้คำสั่งเดียวกับไฟล์ขนาดเล็ก

เตรียม VPS ปลายทาง: ติดตั้ง mysql-server

ถ้าเครื่องใหม่ยังไม่มีเว็บเซิร์ฟเวอร์และ PHP ให้ทำตาม คู่มือติดตั้ง LAMP stack บน Ubuntu 24.04 ก่อน แล้วค่อยกลับมาที่ขั้นนี้ ถ้าต้องการแค่ฐานข้อมูล ให้ติดตั้งแพ็กเกจเดียว:

sudo apt update
sudo apt install -y mysql-server
mysql --version
mysqldump --version
systemctl status mysql --no-pager

mysql --version และ mysqldump --version ควรพิมพ์เลขเวอร์ชันออกมาทั้งคู่ ส่วน systemctl status ควรแสดงบรรทัด Active: active (running) ถ้าเครื่องของคุณใช้ MariaDB แทน MySQL ให้ตรวจว่าแพ็กเกจติดตั้งคำสั่งชื่ออะไร mysqldump หรือ mariadb-dump แล้วใช้ชื่อนั้นในทุกคำสั่งข้างล่าง

root ของ MySQL ล็อกอินด้วยวิธีไหน

ก่อน dump อะไร ให้รู้ก่อนว่า root ของ MySQL บนเครื่องนี้ยืนยันตัวตนอย่างไร คำตอบนี้กำหนดว่าทุกคำสั่งข้างล่างต้องขึ้นต้นด้วย sudo หรือต้องใส่รหัสผ่าน อย่าเดา ให้ถามเซิร์ฟเวอร์:

sudo mysql -e "SELECT user, host, plugin FROM mysql.user WHERE user = 'root';"

ผลลัพธ์เป็นตารางสั้นๆ ให้ดูที่คอลัมน์ plugin:

  • ถ้าเป็น auth_socket MySQL จะไม่ถามรหัสผ่าน แต่ดูว่าผู้ใช้ Linux ที่เชื่อมต่อเข้ามาทาง Unix socket คือใคร sudo mysql จึงเข้าได้ทันที เพราะ process ทำงานในชื่อ root ของ Linux ส่วน mysql -u root -p จากผู้ใช้ธรรมดาจะได้ ERROR 1698 (28000): Access denied for user 'root'@'localhost' แม้จะพิมพ์รหัสผ่านถูกก็ตาม
  • ถ้าเป็น caching_sha2_password แปลว่า root ใช้รหัสผ่าน ให้ใช้ mysql -u root -p และ mysqldump -u root -p แทน sudo mysql และ sudo mysqldump ในทุกคำสั่งข้างล่าง
  • ถ้าคำสั่งข้างบนตอบกลับว่า ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) แปลว่า root ต้องใช้รหัสผ่าน ให้รันคำสั่งเดิมใหม่ด้วย mysql -u root -p -e แทน

คู่มือนี้เขียนตามกรณี auth_socket ข้อดีของวิธีนี้คือไม่มีรหัสผ่าน root ค้างอยู่ใน shell history หรือในไฟล์ script สำรองข้อมูล

character set ของเซิร์ฟเวอร์และของ mysqldump คืออะไร

character set คือกติกาที่ใช้แปลงตัวอักษรเป็น byte ภาษาไทยจะรอดหรือไม่ ขึ้นกับว่าทุกจุดในเส้นทางใช้กติกาที่รองรับภาษาไทย ตรวจฝั่งเซิร์ฟเวอร์ก่อน:

sudo mysql -e "SHOW VARIABLES LIKE 'character_set%';"

อ่านผลลัพธ์ทีละบรรทัด:

  • character_set_server คือชุดตัวอักษรที่ฐานข้อมูลใหม่จะได้รับ เมื่อ CREATE DATABASE ไม่ได้ระบุเอง
  • character_set_client, character_set_connection และ character_set_results เป็นค่าของ session ที่คุณเปิดอยู่ตอนนี้ ไม่ใช่ค่าของเซิร์ฟเวอร์ทั้งเครื่อง
  • character_set_database เป็นค่าของฐานข้อมูลที่เลือกอยู่ เมื่อยังไม่ได้เลือกฐานข้อมูลใด ค่านี้จะเท่ากับ character_set_server
  • character_set_filesystem และ character_set_system ใช้กับชื่อไฟล์และชื่อ object ไม่เกี่ยวกับข้อมูลในตาราง

ชุดตัวอักษรที่ภาษาไทยต้องการคือ utf8mb4 ถ้า character_set_server บนเครื่องของคุณเป็นค่าอื่น ไม่ต้องแก้ config ของเซิร์ฟเวอร์ ให้ระบุ CHARACTER SET utf8mb4 ทุกครั้งที่สร้างฐานข้อมูล ซึ่งทุกคำสั่งในคู่มือนี้ทำอยู่แล้ว

mysqldump เลือกชุดตัวอักษรของตัวเองตอนคุยกับเซิร์ฟเวอร์ ค่านี้แสดงอยู่ในส่วนท้ายของ --help:

mysqldump --help | grep '^default-character-set'

ค่าที่พิมพ์ออกมาคือชุดตัวอักษรที่ข้อมูลใน dump จะถูกเขียน mysqldump ประกาศค่านี้ไว้ที่หัวไฟล์ด้วยคำสั่ง SET NAMES เพื่อให้ฝั่ง import อ่านไฟล์ด้วยชุดเดียวกัน ถ้าค่านี้เป็น utf8mb4 ข้อมูลภาษาไทยในคอลัมน์ utf8mb4 จะผ่านไปโดยไม่ถูกแปลง เราจะเปิดดูบรรทัด SET NAMES ในไฟล์จริงในขั้นถัดไป

สร้างฐานข้อมูลทดลองที่มีภาษาไทย procedure และ event

ก่อนย้ายข้อมูลจริง ให้ซ้อมกับฐานข้อมูลเล็กๆ ที่คุณรู้เนื้อหาทุกแถว ถ้าการซ้อมผ่าน คุณก็รู้ว่าเครื่องมือและการตั้งค่าบนเครื่องนี้ถูกต้อง เปิด prompt ของ MySQL:

sudo mysql

แล้วพิมพ์คำสั่งเหล่านี้ที่ prompt mysql>:

CREATE DATABASE shopdb CHARACTER SET utf8mb4;
USE shopdb;
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO products (name, price) VALUES
  ('ข้าวหอมมะลิ 5 กิโลกรัม', 245.00),
  ('น้ำปลาแท้ 700 มิลลิลิตร', 39.50),
  ('ทุเรียนหมอนทอง', 180.00),
  ('ผ้าไหมมัดหมี่', 1250.00);
CREATE TABLE sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;
CREATE PROCEDURE count_products() SELECT COUNT(*) AS total FROM products;
CREATE EVENT purge_sessions ON SCHEDULE EVERY 1 DAY
  DO DELETE FROM sessions WHERE created_at < NOW() - INTERVAL 7 DAY;

procedure count_products และ event purge_sessions เป็นตัวแทนของสิ่งที่เว็บจริงมักมี เช่น รายงานยอดขายที่เขียนเป็น procedure หรืองานลบ session เก่าทุกคืน ทั้งสองอย่างไม่ใช่ข้อมูลในตาราง จึงเป็นสิ่งแรกที่หายไปโดยไม่มีใครเห็นตอนย้ายเครื่อง

ตรวจว่าภาษาไทยถูกเก็บเป็น UTF-8 จริง ไม่ใช่แค่แสดงผลถูกบนจอ แล้วจด checksum ไว้:

SELECT name, HEX(name) FROM products WHERE id = 1;
CHECKSUM TABLE products;
exit

ใน UTF-8 อักษรไทยทุกตัวใช้ 3 byte ที่ขึ้นต้นด้วย E0B8 หรือ E0B9 ตัว "ข" คือ E0B882 ดังนั้นคอลัมน์ HEX(name) ของแถวแรกควรขึ้นต้นด้วย E0B882 ถ้าไม่ใช่ ให้หยุดตรงนี้และตรวจว่า terminal หรือ SSH client ของคุณตั้งเป็น UTF-8 เพราะข้อมูลทดสอบไม่ได้เป็นภาษาไทยแบบ UTF-8 ตั้งแต่ต้น การทดสอบต่อจากนี้จึงไม่มีความหมาย

CHECKSUM TABLE คำนวณตัวเลขหนึ่งค่าจากข้อมูลทุกแถวในตาราง ถ้าตัวเลขของสองตารางต่างกัน แทบแน่นอนว่าข้อมูลต่างกันที่ใดที่หนึ่ง แต่คู่มือของ MySQL ระบุว่าฟังก์ชัน hash ที่ใช้ไม่รับประกันว่าจะไม่ชนกัน ตารางที่ข้อมูลไม่เหมือนกันจึงมีโอกาสเล็กน้อยที่จะได้ตัวเลขเดียวกัน ให้ถือว่า checksum เป็นหลักฐานที่ดี ไม่ใช่ข้อพิสูจน์ จดตัวเลขในคอลัมน์ Checksum ไว้ เราจะเทียบกับค่าหลัง restore

export ด้วย mysqldump: --single-transaction และ gzip

นี่คือคำสั่ง export ที่ใช้กับฐานข้อมูลจริงได้เลย:

set -o pipefail
sudo mysqldump --single-transaction --routines --events shopdb | gzip > ~/shopdb.sql.gz
echo $?
ls -lh ~/shopdb.sql.gz

echo $? ควรพิมพ์ 0 ไฟล์ ~/shopdb.sql.gz เป็นของผู้ใช้ของคุณ ไม่ใช่ของ root เพราะการ redirect ด้วย > ทำโดย shell ของคุณ ไม่ได้ทำผ่าน sudo

--single-transaction ทำให้ mysqldump เปิด transaction ในระดับ REPEATABLE READ ก่อนเริ่มอ่าน ทุกตารางจึงถูกอ่านจากข้อมูล ณ เวลาเดียวกัน และไม่มีการล็อกตาราง เว็บยังรับคำสั่งซื้อและเขียนข้อมูลได้ตามปกติระหว่าง dump ถ้าไม่ใช้ option นี้ ข้อมูลใน dump ก็ยังสอดคล้องกัน แต่ต้องแลกด้วยการล็อก mysqldump เปิด --opt เป็นค่าเริ่มต้น และ --opt รวม --lock-tables ไว้ด้วย ทุกตารางในฐานข้อมูลที่ dump จะถูกล็อกแบบ READ LOCAL ก่อนเริ่มอ่าน ซึ่งสำหรับตาราง InnoDB มีผลเท่ากับ READ ตาราง orders และ order_items จึงถูกอ่านจากข้อมูลชุดเดียวกัน แต่เว็บเขียนข้อมูลลงตารางเหล่านั้นไม่ได้จนกว่า dump จะเสร็จ ลูกค้าที่กดสั่งซื้อระหว่างนั้นจะค้างรอ นี่คือราคาจริงของการไม่ใส่ --single-transaction dump ที่มีรายการสินค้าซึ่งไม่มีคำสั่งซื้อเป็นเจ้าของจะเกิดเมื่อปิดการล็อกด้วย --skip-lock-tables ด้วย หรือเมื่อ dump หลายฐานข้อมูลในครั้งเดียว เพราะ --lock-tables ล็อกทีละฐานข้อมูล ตารางที่อยู่ต่างฐานข้อมูลจึงอาจถูกอ่านจากคนละเวลา

ข้อจำกัดคือ snapshot นี้ใช้ได้กับตาราง InnoDB เท่านั้น เพราะ InnoDB รองรับ transaction ส่วน MyISAM ไม่รองรับ เว็บ WordPress รุ่นเก่ามักยังมีตาราง MyISAM เหลืออยู่ ตรวจได้ด้วยคำสั่งนี้:

sudo mysql -e "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'shopdb';"

ถ้ามีตารางที่ ENGINE เป็น MyISAM ตารางนั้นอาจเปลี่ยนระหว่าง dump ให้ dump ตอนที่เว็บไม่มีการเขียนข้อมูล เช่น เปิดโหมดปิดปรับปรุง (maintenance mode) ไว้ก่อน อีกข้อหนึ่ง ระหว่าง dump อย่ารันคำสั่งที่เปลี่ยนโครงสร้างตาราง เช่น ALTER TABLE หรือ DROP TABLE บนฐานข้อมูลเดียวกัน เพราะคู่มือของ MySQL ระบุว่าคำสั่งกลุ่มนี้ทำให้ข้อมูลจาก --single-transaction ไม่สอดคล้องกันได้

set -o pipefail สำคัญกว่าที่เห็น ใน pipe ค่า exit ของทั้งบรรทัดคือค่าของคำสั่งสุดท้าย ซึ่งคือ gzip ถ้า mysqldump ล้มกลางทาง gzip ก็ยังปิดไฟล์ได้ตามปกติและรายงานว่าสำเร็จ คุณจะได้ไฟล์ .sql.gz ที่ดูปกติแต่ข้างในไม่ครบ ลองดูความต่างใน shell ใหม่:

false | gzip > /tmp/pipe-test.gz; echo $?
set -o pipefail
false | gzip > /tmp/pipe-test.gz; echo $?

บรรทัดแรกพิมพ์ 0 แม้ false จะล้มเหลว บรรทัดที่สามพิมพ์ 1 เพราะ pipefail ทำให้ pipe รายงานความล้มเหลวของคำสั่งใดก็ได้ในสาย

ตรวจไฟล์อีกสองจุด:

zcat ~/shopdb.sql.gz | head -n 30 | grep 'SET NAMES'
zcat ~/shopdb.sql.gz | tail -n 1

บรรทัด SET NAMES ควรระบุชุดตัวอักษรเดียวกับที่ mysqldump --help แสดง ส่วนบรรทัดสุดท้ายของไฟล์ควรขึ้นต้นด้วย -- Dump completed on ตามด้วยวันที่ mysqldump เขียนบรรทัดนี้หลังเขียนข้อมูลครบแล้วเท่านั้น ไฟล์ที่ไม่มีบรรทัดนี้คือไฟล์ที่ถูกตัดกลางทาง

dump ธรรมดาเก็บ stored procedure และ event หรือไม่

ค่าเริ่มต้นของ option เหล่านี้ไม่ใช่สิ่งที่คุณต้องจำ ให้ทดสอบบนเครื่องของคุณเอง dump ฐานข้อมูลเดียวกันสองครั้ง ครั้งแรกไม่ใส่ option ครั้งที่สองใส่ --routines และ --events แล้วนับว่าชื่อ procedure และชื่อ event ปรากฏในแต่ละไฟล์กี่ครั้ง:

sudo mysqldump --single-transaction shopdb > ~/plain.sql
sudo mysqldump --single-transaction --routines --events shopdb > ~/full.sql
grep -c 'count_products' ~/plain.sql ~/full.sql
grep -c 'purge_sessions' ~/plain.sql ~/full.sql

grep -c พิมพ์ชื่อไฟล์ตามด้วยจำนวนบรรทัดที่พบ ชื่อทั้งสองไม่ซ้ำกับชื่อตารางใดเลย ไฟล์ที่ได้ 0 จึงเป็นไฟล์ที่ไม่มี object นั้นอยู่ ถ้า plain.sql ได้ 0 ทั้งสองบรรทัดบนเครื่องของคุณ แปลว่า dump ที่ไม่ระบุ option จะทำให้ procedure และ event หายไป ไม่มี error ตอน dump และไม่มี error ตอน import ปัญหาจะปรากฏทีหลัง เมื่อโค้ดของเว็บเรียก procedure แล้วได้ ERROR 1305 (42000): PROCEDURE shopdb.count_products does not exist หรือเมื่อตาราง sessions โตขึ้นทุกวันเพราะไม่มี event คอยลบ

--routines ทำให้ dump มีคำสั่ง CREATE PROCEDURE และ CREATE FUNCTION ส่วน --events ทำให้มีคำสั่ง CREATE EVENT ใส่ทั้งสอง option ทุกครั้ง ฐานข้อมูลที่ไม่มี object เหล่านี้ก็ไม่เสียอะไร และคำสั่งของคุณไม่ต้องพึ่งค่าเริ่มต้นของเวอร์ชันใดเวอร์ชันหนึ่ง trigger ตรวจได้แบบเดียวกัน ใช้ SHOW TRIGGERS FROM shopdb; หาชื่อ trigger แล้วใช้ grep นับในไฟล์ dump

ลบไฟล์ทดสอบเมื่อดูผลเสร็จ:

rm ~/plain.sql ~/full.sql

import: สร้างฐานข้อมูลปลายทางก่อน แล้วให้ mysql อ่านไฟล์

dump ของฐานข้อมูลเดียวแบบที่เราสร้างไม่มีคำสั่ง CREATE DATABASE และไม่มี USE ตรวจได้ด้วย zcat ~/shopdb.sql.gz | grep -c 'CREATE DATABASE' ซึ่งควรได้ 0 ผลคือคุณต้องสร้างฐานข้อมูลปลายทางเอง และต้องบอกชื่อฐานข้อมูลให้ mysql ตอน import ข้อดีคือคุณเลือกชื่อปลายทางได้เอง และกำหนด character set ของฐานข้อมูลใหม่ได้เอง

ซ้อมกู้คืนจริงบนเครื่องเดียวกัน ลบฐานข้อมูลทิ้ง สร้างใหม่ แล้ว import:

sudo mysql -e "DROP DATABASE shopdb;"
sudo mysql -e "CREATE DATABASE shopdb CHARACTER SET utf8mb4;"
gunzip < ~/shopdb.sql.gz | sudo mysql shopdb
echo $?

gunzip คลายไฟล์และส่งข้อมูลออกทาง pipe โดยตรง ไฟล์ .sql ขนาดเต็มจึงไม่ต้องอยู่บน disk เลย ซึ่งช่วยได้มากเมื่อพื้นที่บน VPS เหลือน้อย ถ้าไม่ใส่ชื่อฐานข้อมูลท้ายคำสั่ง mysql คุณจะได้ ERROR 1046 (3D000) at line ...: No database selected และถ้าลืมสร้างฐานข้อมูลก่อน คุณจะได้ ERROR 1049 (42000): Unknown database 'shopdb'

ไฟล์ใหญ่ใช้เวลา import หลายนาทีโดยไม่มีอะไรแสดงบนจอ ถ้าอยากเห็นความคืบหน้า ให้ใช้ pv ซึ่งแสดงว่าอ่านไฟล์ไปแล้วกี่เปอร์เซ็นต์:

sudo apt install -y pv
pv ~/shopdb.sql.gz | gunzip | sudo mysql shopdb

ภาษาไทยกลับมาครบหรือไม่: เทียบ CHECKSUM TABLE

sudo mysql shopdb -e "CHECKSUM TABLE products; SELECT name, HEX(name) FROM products WHERE id = 1;"
sudo mysql shopdb -e "CALL count_products(); SHOW EVENTS FROM shopdb;"

ตัวเลขในคอลัมน์ Checksum ควรเท่ากับที่คุณจดไว้ก่อน dump ทุกหลัก ถ้าต่างกัน แทบแน่นอนว่ามีบางแถวเปลี่ยนไป ถ้าเท่ากัน เป็นหลักฐานที่ดีมากว่าทุกแถวกลับมาเหมือนเดิม รวมถึงทุก byte ของภาษาไทย แม้จะไม่ใช่ข้อพิสูจน์เด็ดขาด เพราะ hash ที่ CHECKSUM TABLE ใช้มีโอกาสชนกันได้เล็กน้อย HEX(name) ควรขึ้นต้นด้วย E0B882 เหมือนเดิม CALL count_products() ควรได้ total เป็น 4 เพราะเราใส่สินค้าไว้สี่แถว และ SHOW EVENTS ควรแสดง purge_sessions หนึ่งแถว

ข้อควรรู้หนึ่งข้อ: ค่าของ CHECKSUM TABLE ขึ้นกับรูปแบบการเก็บแถว (row format) ของตารางด้วย การเทียบบนเครื่องเดียวกันแบบในการซ้อมนี้จึงเชื่อถือได้ แต่เมื่อย้ายข้ามเครื่องที่ MySQL ต่างเวอร์ชันกัน ค่าอาจต่างกันได้แม้ข้อมูลจะเหมือนกัน ในกรณีนั้นให้เทียบ SELECT COUNT(*) ของทุกตาราง และสุ่มเทียบแถวที่มีภาษาไทยด้วย HEX() แทน

ย้ายเว็บจริงจาก shared hosting มา VPS

เมื่อการซ้อมผ่านแล้ว ให้ใช้คำสั่งชุดเดียวกันกับข้อมูลจริง ภาพรวมของการย้ายทั้งเว็บ รวมถึงโดเมนและอีเมล อยู่ใน คู่มือย้ายจาก shared hosting มา VPS หัวข้อนี้เน้นเฉพาะฐานข้อมูล

กรณีที่ host เก่ามี SSH ให้ล็อกอินเข้า host เก่าแล้วรัน mysqldump ด้วยผู้ใช้ฐานข้อมูลที่เว็บใช้ ค่า DB_USER และ DB_NAME อยู่ในไฟล์ wp-config.php ของ WordPress:

set -o pipefail
mysqldump --single-transaction --no-tablespaces --routines --events -u cpuser_shop -p cpuser_shopdb | gzip > ~/shopdb.sql.gz

--no-tablespaces จำเป็นบน shared hosting เพราะ mysqldump ของ MySQL 8.0.21 ขึ้นไปพยายามอ่านข้อมูล tablespace ซึ่งต้องใช้สิทธิ์ PROCESS และผู้ใช้บน shared hosting แทบไม่เคยได้สิทธิ์นี้ ถ้าไม่ใส่ คุณจะเห็น mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces ฐานข้อมูลของเว็บทั่วไปไม่ได้ใช้ tablespace แบบกำหนดเอง จึงไม่มีอะไรหายเมื่อข้ามส่วนนี้

จากนั้นดึงไฟล์มาที่ VPS โดยรันคำสั่งนี้บน VPS:

scp olduser@old-host.example.com:shopdb.sql.gz ~/

ไฟล์ของเว็บเอง เช่น wp-content/uploads ย้ายได้โดยไม่ต้องสร้างไฟล์ archive ชั่วคราว ดูวิธีใน การส่งทั้งโฟลเดอร์ผ่าน SSH ด้วย tar

กรณีที่ host เก่าไม่มี SSH ให้ใช้หน้า Export ของ phpMyAdmin ฝั่ง export ไม่ติดขีดจำกัดการอัปโหลด เพราะเป็นการดาวน์โหลดไฟล์ลงเครื่องของคุณ เลือกรูปแบบ SQL ใช้โหมด Custom เลือกบีบอัดแบบ gzip ถ้ามีตัวเลือก และตรวจว่าเลือกส่งออก procedure และ event ด้วยถ้าฐานข้อมูลมี จากนั้นอัปโหลดไฟล์จากคอมพิวเตอร์ของคุณขึ้น VPS ด้วย scp ซึ่งมีมาให้ใน Windows 10 ขึ้นไปและใน macOS อยู่แล้ว:

scp shopdb.sql.gz youruser@203.0.113.10:~/

บน VPS สร้างฐานข้อมูลและผู้ใช้สำหรับเว็บ แล้ว import ด้วย root:

sudo mysql -e "CREATE DATABASE shopdb CHARACTER SET utf8mb4;"
sudo mysql -e "CREATE USER 'shopuser'@'localhost' IDENTIFIED BY 'ใส่รหัสผ่านยาวและสุ่ม';"
sudo mysql -e "GRANT ALL PRIVILEGES ON shopdb.* TO 'shopuser'@'localhost';"
gunzip < ~/shopdb.sql.gz | sudo mysql shopdb

แก้ DB_NAME, DB_USER และ DB_PASSWORD ใน wp-config.php ให้ตรงกับค่าใหม่ และตั้ง DB_HOST เป็น localhost ทุกตารางยังใช้ prefix เดิม เช่น wp_ จึงไม่ต้องแก้ $table_prefix

procedure ที่มี DEFINER ของ host เก่า ทุก procedure และ event ใน dump มีส่วน DEFINER= ที่ระบุผู้ใช้ผู้สร้าง เช่น 'cpuser_shop'@'localhost' ผู้ใช้นี้ไม่มีอยู่บน VPS ใหม่ เมื่อเว็บเรียก procedure นั้น MySQL จะตอบ ERROR 1449 (HY000): The user specified as a definer ('cpuser_shop'@'localhost') does not exist เพราะ procedure ทำงานด้วยสิทธิ์ของ definer และ MySQL หาผู้ใช้นั้นไม่เจอ ทางแก้ที่ง่ายที่สุดคือสร้างผู้ใช้ชื่อนั้นบน VPS และให้สิทธิ์บน shopdb หรือสร้าง procedure ใหม่ด้วยผู้ใช้ shopuser ดูรายชื่อ definer ทั้งหมดก่อน import ได้ด้วย zcat ~/shopdb.sql.gz | grep -o 'DEFINER=[^ ]*' | sort -u

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

ทำไมเว็บไทยเก่าที่ใช้ tis620 หรือ latin1 กลับมาเป็นเครื่องหมายคำถาม

เว็บไทยที่เขียนก่อน UTF-8 จะเป็นมาตรฐาน มักมีตารางที่ใช้ tis620 หรือ latin1 เมื่อย้ายแล้วหน้าเว็บแสดง ???? แทนภาษาไทย ปัญหานี้เกิดตอนแปลงชุดตัวอักษร ไม่ได้เกิดตอนคัดลอกไฟล์

MySQL เก็บชุดตัวอักษรแยกตามคอลัมน์ ตอน dump เซิร์ฟเวอร์แปลงค่าทุกค่าจากชุดของคอลัมน์ไปเป็นชุดของ connection ซึ่งคือชุดที่ mysqldump ใช้และเขียนไว้ใน SET NAMES ตอน import ก็แปลงอีกครั้งจากชุดนั้นไปเป็นชุดของคอลัมน์ปลายทาง ถ้าตัวอักษรใดไม่มีอยู่ในชุดปลายทาง MySQL จะใส่ ? แทน และตัวอักษรเดิมหายไปจริง ? ที่เห็นคือ byte 3F ที่เก็บอยู่ในตาราง ไม่ใช่ปัญหาการแสดงผลของ browser

คอลัมน์ tis620 ที่เก็บภาษาไทยจริง แปลงเป็น utf8mb4 ได้ครบทุกตัว เพราะทุกตัวอักษรใน TIS-620 มีอยู่ใน Unicode การ dump ด้วยชุด utf8mb4 จึงปลอดภัย ปัญหาเกิดเมื่อชุดระหว่างทางหรือชุดปลายทางไม่มีภาษาไทย เช่น dump ด้วย --default-character-set=latin1 หรือ import เข้าตารางที่ประกาศคอลัมน์เป็น latin1 ในกรณีหลัง MySQL อาจปฏิเสธด้วย Incorrect string value หรือเก็บเป็น ? ขึ้นกับ sql_mode ของเซิร์ฟเวอร์

คอลัมน์ latin1 ที่จริงๆ เก็บ byte ของ UTF-8 พบบ่อยในเว็บ PHP รุ่นเก่า โค้ด PHP ส่งข้อความ UTF-8 เข้าไปผ่าน connection ที่ตั้งเป็น latin1 MySQL จึงเก็บ byte ไว้ตามเดิมโดยไม่แปลง เว็บเก่าแสดงผลถูก เพราะ byte เดิมถูกส่งกลับออกมาตรงๆ แต่ MySQL เชื่อว่า byte เหล่านั้นเป็นตัวอักษร latin1 เมื่อ dump ด้วย utf8mb4 มันจึงแปลง byte ทีละตัวเป็นตัวอักษรยุโรป ผลที่ได้คือข้อความที่อ่านไม่ออก หรือมี ? ปนอยู่

--default-character-set กำหนดชุดตัวอักษรของ connection ที่ mysqldump ใช้ ซึ่งก็คือชุดที่ข้อมูลถูกแปลงไปและชุดที่ถูกเขียนใน SET NAMES เมื่อชุดของ connection ตรงกับชุดของคอลัมน์ จะไม่มีการแปลง byte จึงออกมาตามที่เก็บอยู่ ในกรณี latin1 ที่เก็บ UTF-8 จึงมีคนใช้ --default-character-set=latin1 เพื่อดึง byte ดิบออกมา แล้วแปลงคอลัมน์เป็น utf8mb4 บนเครื่องใหม่ งานแบบนี้ต้องทำบนสำเนาก่อน และตรวจผลในหน้าเว็บจริงทุกครั้ง

ก่อนเลือกวิธี ให้ดูว่าตารางของคุณเป็นกรณีไหน SHOW CREATE TABLE ชื่อตาราง; บอกชุดตัวอักษรที่ประกาศไว้ ส่วน SELECT HEX(ชื่อคอลัมน์) FROM ชื่อตาราง LIMIT 5; บอกว่าข้างในเป็น byte อะไร ถ้าคอลัมน์ประกาศเป็น latin1 แต่ HEX ขึ้นต้นด้วย E0B8 หรือ E0B9 เป็นชุดละ 3 byte นั่นคือ UTF-8 ที่ซ่อนอยู่ใน latin1 ถ้าอักษรไทยแต่ละตัวเป็น byte เดียวในช่วง A1 ถึง FB นั่นคือ TIS-620

สำรองข้อมูล: ให้ restic เก็บไฟล์ .sql.gz ไม่ใช่ /var/lib/mysql

ไฟล์ dump ที่คุณเพิ่งสร้างเป็นวิธีสำรองฐานข้อมูลที่เหมาะที่สุดสำหรับเว็บขนาดนี้ด้วย คำถามที่พบบ่อยคือ ทำไมไม่สำรองโฟลเดอร์ /var/lib/mysql ไปเลย คำตอบคือ InnoDB เขียนหน้าข้อมูลและ redo log ลงไฟล์คนละเวลากัน ขณะที่โปรแกรมสำรองข้อมูลใช้เวลาหลายวินาทีถึงหลายนาทีในการอ่านไฟล์ทั้งโฟลเดอร์ สำเนาที่ได้จึงเป็นไฟล์ที่ถูกอ่านในเวลาต่างกัน และอาจไม่ตรงกันเอง เมื่อนำกลับมาใช้ MySQL อาจต้องกู้แบบ crash recovery หรืออาจเปิดไม่ขึ้นเลย นอกจากนี้ไฟล์ใน data directory ใช้ได้กับ MySQL เวอร์ชันใกล้เคียงกันเท่านั้น

ไฟล์ .sql.gz ต่างออกไป มันมาจากข้อมูล ณ เวลาเดียวที่สอดคล้องกันด้วย --single-transaction เป็นข้อความ SQL ที่เปิดอ่านได้ import เข้า MySQL เวอร์ชันอื่นได้ และบีบอัดได้ดีมาก เขียน script สั้นๆ สำหรับ dump ทุกคืน:

sudo install -d -m 700 /var/backups/mysql
sudo tee /usr/local/bin/dump-shopdb.sh > /dev/null <<'EOF'
#!/bin/bash
set -euo pipefail
out=/var/backups/mysql/shopdb-$(date +%F).sql.gz
mysqldump --single-transaction --routines --events shopdb | gzip > "$out"
zcat "$out" | tail -n 1 | grep -q '^-- Dump completed'
find /var/backups/mysql -name 'shopdb-*.sql.gz' -mtime +14 -delete
EOF
sudo chmod 700 /usr/local/bin/dump-shopdb.sh
sudo /usr/local/bin/dump-shopdb.sh && ls -lh /var/backups/mysql

script นี้รันในชื่อ root ของ Linux จึงใช้การยืนยันตัวตนแบบ auth_socket ที่ตรวจไว้ตอนต้นได้โดยไม่ต้องเก็บรหัสผ่าน บรรทัด grep -q ทำให้ script จบด้วย error ถ้าไฟล์ไม่มีบรรทัด -- Dump completed ซึ่งหมายความว่า cron จะรายงานความล้มเหลวแทนที่จะเงียบ ตั้งให้รันทุกคืนด้วย sudo crontab -e แล้วเพิ่มบรรทัด 15 2 * * * /usr/local/bin/dump-shopdb.sh

จากนั้นให้ restic สำรองโฟลเดอร์ /var/backups/mysql ไม่ใช่ /var/lib/mysql ลำดับคือ dump ก่อน แล้ว restic อ่านไฟล์ที่ dump เสร็จแล้ว การสร้าง repository และการตั้งเวลาอยู่ใน คู่มือสำรองข้อมูล VPS ด้วย restic ส่วน snapshot ของ VPS จากแผงควบคุมของผู้ให้บริการใช้แทน dump นี้ไม่ได้ เหตุผลอยู่ใน ความต่างระหว่าง snapshot ของ VPS กับการสำรองข้อมูลจริง สุดท้าย ไฟล์สำรองที่ไม่เคยถูก restore ยังไม่ใช่ไฟล์สำรองที่พิสูจน์แล้ว ให้ทำขั้นตอนลบ สร้างใหม่ import และเทียบ checksum ข้างบนกับไฟล์จริงบนเครื่องทดสอบเป็นระยะ

FAQ

phpMyAdmin import ไฟล์ .sql ขนาดใหญ่ไม่ได้ ต้องทำอย่างไร

ขีดจำกัดขนาดไฟล์ในหน้า Import มาจากค่า upload_max_filesize และ post_max_size ของ PHP ไม่ได้มาจาก MySQL ให้ข้าม PHP ไปเลย อัปโหลดไฟล์ขึ้น VPS ด้วย scp สร้างฐานข้อมูลด้วย sudo mysql -e "CREATE DATABASE shopdb CHARACTER SET utf8mb4;" แล้ว import ด้วย gunzip < shopdb.sql.gz | sudo mysql shopdb คำสั่ง mysql อ่านไฟล์เป็น stream จึงรับไฟล์ได้ทุกขนาด

mysqldump เก็บ stored procedure และ event ด้วยหรือไม่

อย่าพึ่งค่าเริ่มต้น ให้ใส่ --routines และ --events ทุกครั้ง แล้วตรวจด้วย grep -c ตามด้วยชื่อ procedure และชื่อไฟล์ dump ถ้า dump ไม่มี procedure การ import จะไม่แสดง error ใดเลย ปัญหาจะปรากฏทีหลังเมื่อเว็บเรียก procedure แล้วได้ ERROR 1305 (42000): PROCEDURE ... does not exist

ทำไมภาษาไทยกลายเป็นเครื่องหมายคำถามหลัง import

MySQL แปลงข้อมูลจากชุดตัวอักษรของคอลัมน์ไปเป็นชุดของ connection ตอน dump และแปลงอีกครั้งตอน import ถ้าชุดใดชุดหนึ่งในเส้นทางไม่มีอักษรไทย เช่น latin1 ตัวอักษรนั้นจะถูกแทนด้วย ? อย่างถาวร ตรวจชุดของคอลัมน์ด้วย SHOW CREATE TABLE ตรวจ byte จริงด้วย SELECT HEX(ชื่อคอลัมน์) แล้วตั้ง --default-character-set ให้ตรงกับข้อมูลที่เก็บอยู่จริง

ต้องใส่ --single-transaction ทุกครั้งหรือไม่

สำหรับตาราง InnoDB ควรใส่ทุกครั้ง option นี้ทำให้ทุกตารางถูกอ่านจากข้อมูล ณ เวลาเดียวกันโดยไม่ล็อกตาราง เว็บจึงทำงานต่อได้ระหว่าง dump ถ้าไม่ใส่ mysqldump จะล็อกตารางทั้งฐานข้อมูลไว้ตลอดการ dump ตามค่าเริ่มต้น --lock-tables และเว็บจะเขียนข้อมูลไม่ได้จนกว่าจะเสร็จ แต่ option นี้ไม่คุ้มครองตาราง MyISAM ตรวจ engine ของทุกตารางจาก information_schema.TABLES ก่อน ถ้ามี MyISAM ให้ dump ตอนที่เว็บไม่มีการเขียนข้อมูล

ทำไม mysql -u root -p เข้าไม่ได้ทั้งที่รหัสผ่านถูก

ถ้าได้ ERROR 1698 (28000): Access denied for user 'root'@'localhost' ให้ตรวจด้วย sudo mysql -e "SELECT user, host, plugin FROM mysql.user WHERE user = 'root';" ถ้าคอลัมน์ plugin เป็น auth_socket แปลว่า root ของ MySQL ไม่ได้ใช้รหัสผ่าน แต่ตรวจผู้ใช้ Linux ที่เชื่อมต่อเข้ามา ให้ใช้ sudo mysql และ sudo mysqldump แทน

#mysql#mysqldump#backups#migration#phpmyadmin