执行MariaDB密码更新命令后遇权限拒绝，数据库疑似丢失求助

Hey there, let's work through this frustrating permission mess you're facing. The problem likely stems from two common missteps when updating the root password in MariaDB: either you didn't refresh privileges after running the update command, or you used an outdated column name for the password (newer MariaDB versions changed this field). Here's how to fix it step by step:

Step 1: Stop MariaDB and Start in Privilege-Skip Mode #

First, we need to bypass permission checks to regain access to the server:

• Stop your MariaDB service (command varies by OS):
  • Systemd-based systems (Ubuntu/Debian/RHEL 8+): sudo systemctl stop mariadb
  • Init.d systems: sudo /etc/init.d/mariadb stop
• Start MariaDB with flags to skip privilege checks and remote connections (for safety during repair):

  sudo mysqld_safe --skip-grant-tables --skip-networking &
  

Step 2: Reset Root Password and Repair Privileges #

Now you can log in without a password:

• Run mysql -u root to enter the MariaDB shell.
• Switch to the core mysql database where all permission data is stored:

  USE mysql;
  

• Set the root password correctly, matching your MariaDB version:
  • MariaDB 10.3 or older: Use the legacy password column

    UPDATE user SET password = PASSWORD('your-new-secure-password') WHERE user = 'root';
    

  • MariaDB 10.4+: Use the updated authentication_string column

    UPDATE user SET authentication_string = PASSWORD('your-new-secure-password') WHERE user = 'root';
    

• Ensure the root user has a valid host entry (stick to localhost for local access first):

  UPDATE user SET host = 'localhost' WHERE user = 'root' AND host = '127.0.0.1';
  

• Critical step: Flush privileges to apply changes immediately (this fixes the cached permission state that caused your denial errors):

  FLUSH PRIVILEGES;
  

• Exit the shell: EXIT;

Step 3: Restart MariaDB Normally #

• Stop the safe-mode instance: sudo mysqladmin -u root shutdown
• Start MariaDB normally:
  • Systemd: sudo systemctl start mariadb
  • Init.d: sudo /etc/init.d/mariadb start
• Test your new password: mysql -u root -p (enter your new password when prompted)

Why This Happened #

Your original issue popped up because:

1. You ran the UPDATE command but didn't run FLUSH PRIVILEGES;, leaving the server's permission cache in an inconsistent state.
2. If you're on a newer MariaDB version, using the outdated password column instead of authentication_string corrupted the user table, leading to full permission denial.

内容的提问来源于stack exchange，提问作者anish samant