วิธีแก้ปัญหาในกรณีที่ลืมรหัส root ของ MySQL8 ใน EC2

POP จากบริษัท Classmethod (Thailand) ครับ

ครั้งนี้ผมจะมาแนะนำวิธีแก้ปัญหาในกรณีที่ลืมรหัส root ของ MySQL8 ที่ใช้งานใน EC2 ด้วยการ Run Command ครับ

ปัญหา

ในขณะที่เราใช้งาน MySQL 8 ใน EC2 บางครั้งเราอาจจะลืมรหัส root ที่ต้องใช้ Login เพื่อเข้าไปจัดการ Database
เมื่อเราไม่สามารถ Login เข้าไปยัง MySQL 8 ได้ก็จะทำให้ไม่สามารถจัดการ Database ได้นั่นเอง
แต่ไม่ต้องกังวลไปครับ ทุกปัญหามีทางออก สำหรับผู้ใช้งานที่เจอปัญหานี้ ให้ทำการแก้ไขตามขั้นตอนในบทความนี้ได้เลยครับ

สิ่งที่ต้องมี

• EC2 Instance ที่มีการติดตั้ง MySQL 8 ไว้แล้ว

สำหรับผู้ใช้งานที่ต้องการติดตั้ง MySQL 8 เพื่อทำการทดสอบ ดูตัวอย่างได้ที่ลิงก์บทความด้านล่างนี้

สิ่งที่ควรรู้

สิ่งที่จะทำต่อไปนี้คือการ Run Command ทั้งหมด

ก่อนที่จะเริ่มทำผู้อ่านต้องเข้าใจก่อนว่า MySQL สามารถตั้งค่าตัวแปรสภาพแวดล้อมให้เป็น Safe Mode ได้
และสามารถลบการตั้งค่าตัวแปรสภาพแวดล้อมให้กลับมาเป็นสภาพแวดล้อมปกติได้

พูดง่ายๆคือ [การทำให้ MySQL เป็นสภาพแวดล้อม Safe Mode] และ [การทำให้ MySQL กลับเป็นสภาพแวดล้อมปกติ] จะ Run Command ใน Server EC2
! แต่ [การลบ Password] และ [การเปลี่ยน Password] จะต้อง Login เข้าไปใน MySQL และ Run Command

หมายเหตุ: การตั้งค่าเกี่ยวกับตัวแปรสภาพแวดล้อม Safe Mode นี้เป็นของ MySQL เท่านั้น ไม่มีความเกี่ยวข้องกับระบบ Server EC2

ผมจะแบ่งขั้นตอนการทำตามหัวข้อให้เข้าใจง่ายๆดังนี้

• Run Command ในสภาพแวดล้อม Safe Mode ของ MySQL
• Run Command ในสภาพแวดล้อมของ MySQL ปกติ

ถ้าพร้อมแล้วไปเริ่มกันเลยครับ

Run Command ในสภาพแวดล้อม Safe Mode ของ MySQL

ให้ทำการ Run Command ในสภาพแวดล้อม Safe Mode นี้

เปิดการใช้งาน Safe Mode

Stop Service MySQL

systemctl stop mysqld.service

ตั้งค่าตัวแปรสภาพแวดล้อม Safe Mode

systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

Start Safe Mode

systemctl start mysqld.service

ตอนนี้ MySQL เป็นสภาพแวดล้อม Safe Mode แล้วครับ ต่อไปให้ Login เข้าไปที่ MySQL ได้เลย

Login เข้าไปที่ MySQL

ให้ทำการ Login เข้าไปที่ MySQL ด้วย root และทำการ Run Command ต่อไปนี้

Login ด้วย root

mysql -u root

Output (example)

TERMINAL (VSCode)

[root@ip-172-31-29-138 ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.30 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

จากนี้ไปคือการ Run Command ใน Safe Mode ของ MySQL

เลือก Database mysql

use mysql

Output (example)

TERMINAL (VSCode)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

ตรวจสอบ User ที่มีสิทธิ์ root

SELECT user, host, authentication_string FROM user;

Output (example)

TERMINAL (VSCode)

mysql> SELECT user, host, authentication_string FROM user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | $A$005$*************************************************************** |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql>

ลบ Password root ใน Safe Mode ของ MySQL

UPDATE user SET authentication_string=null WHERE User='root';

Output (example)

TERMINAL (VSCode)

mysql> UPDATE user SET authentication_string=null WHERE User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

ตรวจสอบ User ที่มีสิทธิ์ root อีกครั้ง

SELECT user, host, authentication_string FROM user;

Output (example)
จะเห็นว่า root มีสถานะเป็น NULL

TERMINAL (VSCode)

mysql> SELECT user, host, authentication_string FROM user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | NULL                                                                   |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql>

สั่งให้ MySQL update ข้อมูลใหม่ที่เราเพิ่ง update เข้าไป

FLUSH PRIVILEGES;

ออกจาก Safe Mode ของ MySQL

exit

ตอนนี้เราก็ได้ลบ Password เสร็จเรียบร้อยแล้วครับ ทีนี้ให้เริ่มทำตามขั้นตอนถัดไปได้เลย

Run Command ในสภาพแวดล้อมของ MySQL ปกติ

ขั้นตอนนี้จะทำการลบตัวแปรสภาพแวดล้อม Safe Mode ของ MySQL และตั้งค่า Password ใหม่ใน MySQL ปกติ

ลบตัวแปรสภาพแวดล้อม Safe Mode ของ MySQL

Stop Service MySQL

systemctl stop mysqld.service

ลบ Safe Mode เพื่อให้ MySQL กลับมาเป็นตัวแปรสภาพแวดล้อมเดิม

systemctl unset-environment MYSQLD_OPTS

Start Service

systemctl start mysqld

ตรวจสอบ Service ว่าทำงานอยู่หรือไม่

systemctl status mysqld

Output (example)
ถ้า Service กำลังทำงานอยู่ จะแสดงหน้าจอแบบนี้

TERMINAL (VSCode)

[root@ip-172-31-29-138 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2022-08-26 09:41:57 UTC; 9min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7487 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 7510 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─7510 /usr/sbin/mysqld

Aug 26 09:41:56 ip-172-31-29-138.ap-southeast-1.compute.internal systemd[1]: Starting MySQL Server...
Aug 26 09:41:57 ip-172-31-29-138.ap-southeast-1.compute.internal systemd[1]: Started MySQL Server.
[root@ip-172-31-29-138 ~]#

ตอนนี้ MySQL กลับมาเป็นสภาพแวดล้อมปกติเรียบร้อยแล้ว ต่อไปให้ตั้งค่า Password ใหม่ใน MySQL

Login เข้าไปที่ MySQL

mysql -u root

ตั้งค่า Password ใหม่

เปลี่ยน[New Password]ให้เป็นของคุณ เช่น'P@ssW0rd'เป็นต้น

ALTER USER 'root'@'localhost' identified BY '[New Password]';

Output (example)

TERMINAL (VSCode)

mysql> ALTER USER 'root'@'localhost' identified BY 'P@ssW0rd';
Query OK, 0 rows affected (0.01 sec)

mysql>

สั่งให้ MySQL update ข้อมูลใหม่ที่เราเพิ่ง update เข้าไป

FLUSH PRIVILEGES;

ออกจาก MySQL

exit

สรุป

การสาธิตนี้ผมได้ทำการตั้งค่าตัวแปรสภาพแวดล้อมให้เป็น Safe Mode ก่อน แล้วเข้าไปยัง MySQL เพื่อลบ Password root จากนั้นลบการตั้งค่าตัวแปรสภาพแวดล้อม Safe Mode เพื่อให้กลับมาเป็นสภาพแวดล้อมปกติ แล้วทำการตั้งค่า Password ใหม่

ผมหวังว่าบทความนี้จะเป็นประโยชน์ให้กับผู้อ่านได้นะครับ

POP จากบริษัท Classmethod (Thailand) ครับ !

Link อ้างอิง

• 【Update】วิธีติดตั้ง Amazon Linux 2 บน EC2 และเชื่อมต่อเซิร์ฟเวอร์ด้วยโปรแกรม PuTTY
• การติดตั้ง MySQL 8 และสร้าง Database ใน Amazon Linux 2