รีเซ็ตรหัสผ่าน root ของ MySQL และ MariaDB

ลืมรหัสผ่าน root ของ MySQL หรือ MariaDB เป็นปัญหาที่พบบ่อย โดยเฉพาะเครื่องที่ติดตั้งไว้นานแล้วหรือเปลี่ยนผู้ดูแล ข่าวดีคือถ้าคุณมีสิทธิ์ root หรือ sudo บนระบบปฏิบัติการ คุณรีเซ็ตรหัสผ่านได้เสมอ และในหลายกรณีไม่ต้องรีเซ็ตเลย เพราะ root ของฐานข้อมูลบน Ubuntu เข้าได้ด้วยสิทธิ์ของระบบปฏิบัติการอยู่แล้ว

บทความนี้เริ่มจากการตรวจว่าจำเป็นต้องรีเซ็ตหรือไม่ จากนั้นแสดงวิธีรีเซ็ตด้วยโหมด skip-grant-tables ที่ใช้ได้ทั้ง MySQL 8 และ MariaDB 10.4 ขึ้นไป บน Ubuntu และ AlmaLinux/Rocky Linux

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

• สิทธิ์ root หรือ sudo บนเซิร์ฟเวอร์ที่ติดตั้งฐานข้อมูล
• ช่วงเวลาที่หยุดฐานข้อมูลได้ไม่กี่นาที เพราะต้องรีสตาร์ตบริการสองครั้ง เว็บไซต์ที่ใช้ฐานข้อมูลจะใช้งานไม่ได้ระหว่างนั้น
• ทราบว่าเครื่องใช้ MySQL หรือ MariaDB ตรวจด้วย mysql --version หรือ mariadb --version

หมายเหตุ: ถ้าเซิร์ฟเวอร์ติดตั้ง Plesk หรือ DirectAdmin ห้ามใช้วิธีในบทความนี้ Control Panel ใช้บัญชีผู้ดูแลฐานข้อมูลของตัวเอง (Plesk ใช้ผู้ใช้ admin และ DirectAdmin ใช้ da_admin) และเก็บรหัสผ่านไว้ในไฟล์ของระบบ การเปลี่ยนรหัสผ่านเองโดยตรงจะทำให้ Control Panel เชื่อมต่อฐานข้อมูลไม่ได้ ให้ใช้เครื่องมือของ Control Panel หรือติดต่อผู้ดูแลระบบ

ขั้นตอนที่ 1: ลองเข้าด้วยสิทธิ์ของระบบปฏิบัติการก่อน

sudo mysql

บน Ubuntu ทั้ง MySQL 8 และ MariaDB ตั้งค่า root ให้ยืนยันตัวตนผ่าน Socket เป็นค่าเริ่มต้น (MySQL ใช้ Plugin auth_socket ส่วน MariaDB ใช้ unix_socket) หมายความว่า root ของระบบเข้าฐานข้อมูลได้โดยไม่ต้องใส่รหัสผ่าน MariaDB 10.4 ขึ้นไปบน AlmaLinux/Rocky 9 ก็มักตั้งค่าแบบเดียวกัน ถ้าเข้าได้และเห็น Prompt mysql> หรือ MariaDB [(none)]> ให้ตรวจวิธียืนยันตัวตนของ root

MySQL 8

SELECT user, host, plugin FROM mysql.user WHERE user = 'root';

MariaDB

SELECT user, host, JSON_VALUE(priv, '$.plugin') AS plugin FROM mysql.global_priv WHERE user = 'root';

ถ้าเห็น auth_socket หรือ unix_socket แปลว่าไม่ได้ลืมรหัสผ่าน แต่ root ไม่ได้ใช้รหัสผ่านมาตั้งแต่แรก ในกรณีนี้ แนะนำให้คงไว้แบบนี้ และสร้างผู้ใช้แยกสำหรับแอปพลิเคชันหรือเครื่องมืออย่าง phpMyAdmin แทน ดู สร้างฐานข้อมูลและผู้ใช้ MySQL/MariaDB พร้อมกำหนดสิทธิ์ ถ้ายังต้องการตั้งรหัสผ่านให้ root ข้ามไปขั้นตอนที่ 4 ได้เลยโดยไม่ต้องหยุดบริการ

ถ้า sudo mysql ได้ข้อความ ERROR 1045 (28000): Access denied for user 'root'@'localhost' แปลว่า root ใช้รหัสผ่าน และต้องรีเซ็ตตามขั้นตอนถัดไป

ขั้นตอนที่ 2: เปิดโหมด skip-grant-tables ชั่วคราว

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

วิธีที่ใช้ได้ทุก Distro คือเพิ่มไฟล์คอนฟิกชั่วคราว แล้วรีสตาร์ตผ่าน systemd ตามปกติ ตำแหน่งไฟล์ขึ้นกับระบบ

ตัวอย่างบน Ubuntu

sudo tee /etc/mysql/conf.d/zz-reset.cnf > /dev/null <<'EOF'
[mysqld]
skip-grant-tables
skip-networking
EOF

sudo systemctl restart mysql

บน MariaDB ใช้ sudo systemctl restart mariadb ถ้าติดตั้ง MySQL จาก Repository ของ Oracle บน AlmaLinux/Rocky ซึ่งไม่มีโฟลเดอร์ /etc/my.cnf.d ให้เพิ่มสองบรรทัดนี้ใต้ [mysqld] ในไฟล์ /etc/my.cnf แทน และจำไว้ว่าต้องลบออกในขั้นตอนที่ 5

ขั้นตอนที่ 3: เข้าสู่ฐานข้อมูลและโหลดตารางสิทธิ์

sudo mysql

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

FLUSH PRIVILEGES;

ถ้าข้ามคำสั่งนี้ จะได้ Error The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

ขั้นตอนที่ 4: ตั้งรหัสผ่านใหม่

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

MySQL 8.0 และ 8.4

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'รหัสผ่านใหม่';

caching_sha2_password เป็นค่าเริ่มต้นของ MySQL 8 ใน MySQL 8.4 Plugin mysql_native_password ถูกปิดเป็นค่าเริ่มต้นแล้ว จึงไม่ควรใช้ หลังคำสั่งนี้ root จะเปลี่ยนจาก Socket มาใช้รหัสผ่าน และต้องเข้าด้วย mysql -u root -p

MariaDB 10.4 ขึ้นไป

แบบที่แนะนำ คือให้ root เข้าได้ทั้งผ่าน Socket และรหัสผ่าน ทำให้ sudo mysql ยังใช้ได้เหมือนเดิม

ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket OR mysql_native_password USING PASSWORD('รหัสผ่านใหม่');

ถ้าต้องการใช้รหัสผ่านอย่างเดียว

ALTER USER 'root'@'localhost' IDENTIFIED BY 'รหัสผ่านใหม่';

ผลลัพธ์ที่ถูกต้องคือ Query OK, 0 rows affected พิมพ์ exit เพื่อออก

ถ้าได้ Error ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost' แปลว่าไม่มีผู้ใช้ root ที่ host นั้น ตรวจรายชื่อด้วย SELECT user, host FROM mysql.user; แล้วใช้ host ที่มีจริง เช่น 'root'@'127.0.0.1'

ขั้นตอนที่ 5: ปิดโหมด skip-grant-tables และรีสตาร์ต

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

sudo rm /etc/mysql/conf.d/zz-reset.cnf
sudo systemctl restart mysql

บน AlmaLinux/Rocky ลบ /etc/my.cnf.d/zz-reset.cnf และใช้ชื่อบริการตามตารางในขั้นตอนที่ 2 ตรวจว่าโหมดถูกปิดแล้ว

sudo mysql -u root -p -e "SELECT @@skip_networking;"

ค่าควรเป็น 0 ถ้ายังเป็น 1 แปลว่าไฟล์ชั่วคราวยังอยู่หรือถูกตั้งไว้ในไฟล์อื่น ค้นหาด้วย sudo grep -rn "skip-grant-tables\|skip-networking" /etc/mysql /etc/my.cnf /etc/my.cnf.d 2>/dev/null

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

mysql -u root -p

ใส่รหัสผ่านใหม่ ต้องเข้าได้ และรหัสผ่านผิดต้องถูกปฏิเสธด้วย Access denied จากนั้นตรวจว่าเว็บไซต์และแอปที่ใช้ฐานข้อมูลกลับมาทำงานปกติ แอปส่วนใหญ่ไม่ได้ใช้ root จึงไม่ได้รับผลกระทบ แต่ถ้าแอปใดใช้ root อยู่ ต้องแก้รหัสผ่านในไฟล์คอนฟิกของแอปนั้นด้วย และควรถือโอกาสนี้เปลี่ยนไปใช้ผู้ใช้เฉพาะแอปแทน

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

บริการเริ่มไม่ขึ้นหลังเพิ่มไฟล์ชั่วคราว

อาจพิมพ์ชื่อ Section ผิด (ต้องเป็น [mysqld]) หรือวางไฟล์ในโฟลเดอร์ที่ไม่ถูกอ่าน ดูสาเหตุด้วย sudo journalctl -u mysql -n 50 (หรือ mariadb, mysqld) ดูแนวทางอ่าน Log เพิ่มที่ อ่าน Log ระบบบน Linux ด้วย journalctl

sudo mysql ยังถามรหัสผ่านหรือถูกปฏิเสธในขั้นตอนที่ 3

ไฟล์ชั่วคราวไม่ถูกโหลด ตรวจด้วย sudo my_print_defaults mysqld ซึ่งแสดงค่าทั้งหมดที่อ่านได้จากไฟล์คอนฟิกในกลุ่ม [mysqld] ต้องเห็น --skip-grant-tables อยู่ในรายการ ถ้าไม่เห็น ย้ายบรรทัดไปใส่ไฟล์คอนฟิกหลักใต้ [mysqld]

หลังเปลี่ยนรหัสผ่าน โปรแกรมเก่าเชื่อมต่อไม่ได้

ไคลเอนต์รุ่นเก่าบางตัวไม่รองรับ caching_sha2_password ของ MySQL 8 ให้อัปเดตไคลเอนต์หรือไลบรารีของภาษาที่ใช้ ไม่ควรลดระดับกลับไปใช้ Plugin เก่า

root เข้าจากเครื่องอื่นไม่ได้

เป็นพฤติกรรมที่ถูกต้อง root ควรเข้าได้เฉพาะจาก localhost ถ้าต้องจัดการจากเครื่องของคุณ ใช้ SSH Tunnel ตาม เชื่อมต่อฐานข้อมูลบนเซิร์ฟเวอร์ผ่าน SSH Tunnel ด้วย DBeaver หรือสร้างผู้ใช้แยกตาม เปิดให้เชื่อมต่อ MySQL/MariaDB จากภายนอกอย่างปลอดภัย

หากติดขัดระหว่างการรีเซ็ต หรือฐานข้อมูลเริ่มไม่ขึ้น ติดต่อทีมงาน THAI DATA CLOUD ได้ที่ https://thaidata.cloud/contact/