Menu
📱 Lihat versi lengkap (non-AMP)
MySQL Security Database Administration

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

Editor: Hendra WIjaya
Update: 3 February 2026
Baca: 5 menit

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

Artikel Terkait

Bagikan:

Link Postingan: https://www.tirinfo.com/cara-reset-password-mysql-root-lupa-linux-windows/