Salin dan Bagikan

Cara Reset Password MySQL Root yang Lupa: 3 Metode untuk Linux dan Windows

Lupa password root MySQL adalah situasi yang stressful tapi common. Setelah membantu banyak administrator dalam situasi ini, saya akan berbagi 3 metode yang terbukti reliable untuk reset password root di berbagai scenario.

Metode 1: Skip-Grant-Tables (Paling Mudah)

Untuk Linux (Ubuntu/Debian/CentOS)

Step 1: Stop MySQL

# Stop MySQL service
sudo systemctl stop mysql

# Atau untuk CentOS/RHEL
sudo systemctl stop mysqld

# Verifikasi
sudo systemctl status mysql

Step 2: Start dengan Skip-Grant-Tables

# Option A: Command line
sudo mysqld_safe --skip-grant-tables --skip-networking &

# Option B: Edit my.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Tambahkan:

[mysqld]
skip-grant-tables
skip-networking

# Restart MySQL
sudo systemctl start mysql

Step 3: Reset Password

# Connect tanpa password
mysql -u root

# Reset password
mysql> FLUSH PRIVILEGES;

# MySQL 5.7.6+ dan 8.0+
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewSecurePassword123!';

# MySQL 5.7.5 dan sebelumnya
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewSecurePassword123!');

# Atau menggunakan UPDATE (legacy method)
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('NewSecurePassword123!') WHERE User = 'root';

# Alternative untuk MySQL 8.0+
mysql> UPDATE mysql.user SET authentication_string = 'NewSecurePassword123!' WHERE User = 'root';

mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Step 4: Restart Normal Mode

# Stop MySQL
sudo systemctl stop mysql
sudo pkill mysqld

# Remove skip-grant-tables dari my.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Hapus atau comment out:
# skip-grant-tables
# skip-networking

# Start normal
sudo systemctl start mysql

# Test dengan password baru
mysql -u root -p
# Masukkan: NewSecurePassword123!

Untuk Windows

Step 1: Stop MySQL Service

# Buka Command Prompt sebagai Administrator
net stop MySQL80

# Atau via Services
services.msc → Cari MySQL → Stop

Step 2: Start dengan Skip-Grant-Tables

# Buka Command Prompt baru sebagai Administrator
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

# Start dengan skip-grant-tables
mysqld --console --skip-grant-tables --shared-memory

# Biarkan window ini terbuka, buka Command Prompt baru

Step 3: Reset Password

# Di Command Prompt kedua
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

mysql -u root

# Di MySQL prompt
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewSecurePassword123!';
EXIT;

Step 4: Restart Normal

# Tutup Command Prompt pertama (tekan Ctrl+C)
net start MySQL80

# Test
mysql -u root -p

Metode 2: Init-File (Lebih Secure)

Untuk Linux

Step 1: Buat Init SQL File

# Buat file untuk reset password
cat > /tmp/reset_password.sql << 'EOF'
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewSecurePassword123!';
FLUSH PRIVILEGES;
EOF

Step 2: Stop MySQL

sudo systemctl stop mysql

Step 3: Start dengan Init File

# Option A: Command line
sudo mysqld --init-file=/tmp/reset_password.sql &

# Option B: Edit my.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Tambahkan:

[mysqld]
init-file=/tmp/reset_password.sql

# Start MySQL
sudo systemctl start mysql

# Tunggu sebentar, lalu stop dan hapus init-file
sudo systemctl stop mysql

# Hapus init-file dari konfigurasi
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Hapus: init-file=/tmp/reset_password.sql

# Hapus SQL file
sudo rm /tmp/reset_password.sql

# Start normal
sudo systemctl start mysql

Step 4: Verifikasi

mysql -u root -p
# Masukkan password baru

Untuk Windows

Step 1: Buat Init SQL File

# Buat file
echo ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewSecurePassword123!'; > C:\reset_password.sql
echo FLUSH PRIVILEGES; >> C:\reset_password.sql

Step 2: Stop MySQL

net stop MySQL80

Step 3: Start dengan Init File

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
mysqld --init-file=C:\reset_password.sql --console

# Tunggu sampai MySQL start, lalu Ctrl+C

Step 4: Restart Normal dan Cleanup

net start MySQL80
del C:\reset_password.sql

# Test
mysql -u root -p

Metode 3: MySQL 8.0 Auth Socket (Linux)

Untuk MySQL 8.0 dengan auth_socket plugin:

Step 1: Login via System User

# Jika MySQL dikonfigurasi dengan auth_socket
sudo mysql

# Atau
sudo mysql -u root

# Ini works karena MySQL authenticates OS user yang sudo

Step 2: Reset Password dan Change Auth Plugin

-- Ganti ke mysql_native_password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NewSecurePassword123!';
FLUSH PRIVILEGES;
EXIT;

Step 3: Test

# Test dengan password
mysql -u root -p

Troubleshooting Reset Issues

1. “ERROR 1290 (HY000): The MySQL server is running with the –skip-grant-tables option”

Solusi:

-- Ini normal behavior, FLUSH PRIVILEGES untuk enable normal operations
FLUSH PRIVILEGES;
-- Lalu jalankan ALTER USER

2. “ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@’localhost’”

Solusi:

-- User mungkin tidak exist, cek dulu
SELECT User, Host FROM mysql.user;

-- Jika root@localhost tidak ada, create
CREATE USER 'root'@'localhost' IDENTIFIED BY 'NewSecurePassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

3. “ERROR 1819 (HY000): Your password does not satisfy the current policy requirements”

Solusi:

-- Check current policy
SHOW VARIABLES LIKE 'validate_password%';

-- Temporary disable policy
SET GLOBAL validate_password.policy = LOW;
SET GLOBAL validate_password.length = 6;

-- Atau buat password yang kuat
ALTER USER 'root'@'localhost' IDENTIFIED BY 'VeryStrongPass123!@#';

4. Permission Denied saat Edit my.cnf

Solusi:

# Gunakan sudo
sudo nano /etc/mysql/my.cnf

# Atau
sudo vim /etc/mysql/my.cnf

# Set permission jika perlu
sudo chown mysql:mysql /etc/mysql/my.cnf

Security Considerations Setelah Reset

1. Change Password Lagi dengan Password yang Lebih Kuat

-- Login dengan temporary password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'SuperSecurePassword!2024#';

2. Remove Anonymous Users

DELETE FROM mysql.user WHERE User = '';
FLUSH PRIVILEGES;

3. Secure Installation

sudo mysql_secure_installation

# Atau manual:

-- Remove test database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

-- Disable remote root
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Reload privileges
FLUSH PRIVILEGES;

4. Enable Password Policy

-- Install plugin jika belum
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Set strong policy
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

Prevention: Password Management

1. Use Password Manager

# Store di encrypted file
# Gunakan tools seperti pass, 1Password CLI, atau KeePass

2. Setup Multiple Admin Users

-- Buat backup admin users
CREATE USER 'admin_backup'@'localhost' IDENTIFIED BY 'DifferentSecurePass!';
GRANT ALL PRIVILEGES ON *.* TO 'admin_backup'@'localhost' WITH GRANT OPTION;

-- Atau
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'DBAPassword!';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;

3. Document Password (Securely)

# Simpan di encrypted vault
# Atau physical safe untuk recovery password

Automated Reset Script

#!/bin/bash
# reset_mysql_root.sh

NEW_PASSWORD="NewSecurePassword123!"
LOG_FILE="/var/log/mysql_reset.log"

echo "[$(date)] Starting MySQL root password reset" >> $LOG_FILE

# Stop MySQL
sudo systemctl stop mysql >> $LOG_FILE 2>&1

# Create init file
cat > /tmp/mysql_reset.sql << EOF
ALTER USER 'root'@'localhost' IDENTIFIED BY '${NEW_PASSWORD}';
FLUSH PRIVILEGES;
EOF

# Start MySQL dengan init file
sudo mysqld --init-file=/tmp/mysql_reset.sql --daemonize >> $LOG_FILE 2>&1

sleep 5

# Cleanup
sudo rm /tmp/mysql_reset.sql
sudo systemctl restart mysql >> $LOG_FILE 2>&1

# Verify
if mysql -u root -p${NEW_PASSWORD} -e "SELECT 1;" > /dev/null 2>&1; then
    echo "[$(date)] Password reset successful" >> $LOG_FILE
    echo "New password: ${NEW_PASSWORD}"
else
    echo "[$(date)] Password reset failed" >> $LOG_FILE
    exit 1
fi

Kesimpulan

Reset password root MySQL adalah skill yang harus dikuasai setiap DBA:

Metode 1 (Skip-Grant-Tables): Paling mudah, untuk emergency
Metode 2 (Init-File): Lebih secure, recommended
Metode 3 (Auth Socket): Spesifik untuk MySQL 8.0 dengan auth_socket

Selalu prioritize security setelah reset dengan:

Strong password
Remove anonymous users
Secure installation
Multiple admin accounts

Cara Reset Password MySQL Root yang Lupa: 3 Metode untuk Linux dan Windows

Cara Reset Password MySQL Root yang Lupa: 3 Metode untuk Linux dan Windows

Metode 1: Skip-Grant-Tables (Paling Mudah)

Untuk Linux (Ubuntu/Debian/CentOS)

Untuk Windows

Metode 2: Init-File (Lebih Secure)

Untuk Linux

Untuk Windows

Metode 3: MySQL 8.0 Auth Socket (Linux)

Troubleshooting Reset Issues

1. “ERROR 1290 (HY000): The MySQL server is running with the –skip-grant-tables option”

2. “ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@’localhost’”

3. “ERROR 1819 (HY000): Your password does not satisfy the current policy requirements”

4. Permission Denied saat Edit my.cnf

Security Considerations Setelah Reset

1. Change Password Lagi dengan Password yang Lebih Kuat

2. Remove Anonymous Users

3. Secure Installation

4. Enable Password Policy

Prevention: Password Management

1. Use Password Manager

2. Setup Multiple Admin Users

3. Document Password (Securely)

Automated Reset Script

Kesimpulan

Artikel Terkait