รีเซ็ตรหัสผ่าน 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 + MySQL 8	/etc/mysql/conf.d/zz-reset.cnf	mysql
Ubuntu + MariaDB	/etc/mysql/conf.d/zz-reset.cnf	mariadb
AlmaLinux/Rocky + MariaDB	/etc/my.cnf.d/zz-reset.cnf	mariadb
AlmaLinux/Rocky + MySQL 8 (AppStream)	/etc/my.cnf.d/zz-reset.cnf	mysqld

ตัวอย่างบน 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

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

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

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

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

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

mysql -u root -p