Troubleshooting "Invalid Password" Errors When Connecting to MySQL or MariaDB

• This message typically appears when you attempt to connect to a MySQL or MariaDB server using the mysql command-line tool and the root user, but the password you provide is incorrect.
• However, in some cases, it might also show up even if you entered the correct password, due to specific authentication methods.

Understanding the Message:

• Literal "invalid" Password: In most scenarios, "invalid" doesn't literally mean the password itself is invalid (e.g., containing invalid characters). It usually indicates that password authentication is disabled for the root user using the conventional MySQL/MariaDB password hashing method.
• Authentication Methods: MySQL and MariaDB offer multiple authentication methods for users to connect. By default (since MariaDB 10.4.3), the unix_socket authentication method is often used for local connections (connecting from the same machine as the database server). This method leverages the operating system's user accounts for authentication and doesn't require a separate password for the database user.

Why You Might See This Message:

• Incorrect Password: If you're using password authentication (not unix_socket) and entered the wrong password, you'll see this message.
• unix_socket Authentication: If you're connecting locally and unix_socket authentication is enabled, you might still see this message even with the correct password, because the password isn't being checked in this method.

Resolving the Issue:

1. Verify Authentication Method:

   • Use the following command to check the authentication plugin for the root user:

     mysql -u root -p -h localhost -e "SELECT user, plugin FROM user;"
     

   • If it shows unix_socket, password authentication is likely disabled.

2. Solutions (Depending on Authentication Method):

   • unix_socket Authentication:

Additional Considerations:

• Security: Using password authentication is generally recommended for improved security, especially on publicly accessible servers.
• Alternatives: If you're concerned about managing passwords, consider using a tool like mysql_config_editor to create a secure connection file with your credentials.

mysql -u root -p -h localhost -e "SELECT user, plugin FROM user;"

This command connects to the MySQL/MariaDB server using the root user (replace with your actual username if needed), prompts for the password (-p), specifies the hostname (-h localhost), and executes the SQL query to retrieve the user and plugin information for all users. Look for the entry where user is root. The value in the plugin column will indicate the authentication method being used:

• unix_socket: This indicates unix_socket authentication is in use.
• Other values (e.g., caching_sha2_password): This suggests password authentication is being used.

Setting Password for root User (Password Authentication):

Part A: Stopping and Starting Server in Safe Mode (One-time setup):

# Stop the MySQL/MariaDB server (replace with the appropriate command for your system)
sudo service mysql stop

# Start the server in safe mode (skipping grant tables)
sudo mysqld_safe --skip-grant-tables

Part B: Connecting and Setting Password:

# Connect to the server without a password
mysql -u root

# Set a new password for the root user
USE mysql;
UPDATE user SET password = PASSWORD('your_new_password') WHERE User = 'root';
FLUSH PRIVILEGES;

# Exit the MySQL prompt
exit;

# Restart the server normally (replace with the appropriate command for your system)
sudo service mysql start

Explanation:

• The first part stops the MySQL/MariaDB server and then starts it in safe mode using mysqld_safe with the --skip-grant-tables option. This allows us to connect without needing a password temporarily.
• The second part connects to the server in safe mode (no password required) and then uses SQL commands to:
  • Switch to the mysql database (where user information is stored).
  • Update the password field for the root user using the PASSWORD() function to hash the new password securely.
  • Flush privileges to make the changes take effect.
  • Exit the MySQL prompt.
• Finally, we restart the server normally using the appropriate command for your system. Now you should be able to connect using the root user and the newly set password.

• This approach involves creating a secure connection file containing your credentials, which can be a good alternative to storing the password directly in the ~/.my.cnf file.
• Steps:
  1. Run mysql_config_editor create on the command line.
  2. Follow the prompts to specify the host, username, password, and any other desired options.
  3. This will create a secure file (usually in ~/.mysql_secure_connection) that the mysql client can use to connect without prompting for the password each time.

User with Specific Privileges (Reduced Access):

• Instead of using the root user for everyday tasks, create a user with specific permissions for the actions you need to perform. This enhances security by limiting potential damage if unauthorized access occurs.
• Steps:
  1. Connect to the server as root (assuming you have the password or can use another method).
  2. Use SQL commands to create the user, grant appropriate privileges (e.g., SELECT, INSERT, UPDATE, etc.), and set a secure password for the new user.
  3. Connect to the server using the newly created user and its password for your daily tasks.

Public Key Authentication (Enhanced Security - Server Configuration Required):

• This method leverages public key cryptography for authentication. You generate a key pair (public and private keys) on your client machine. The public key is then registered on the MySQL/MariaDB server. The client uses the private key to connect securely without requiring a password.
• Note: Setting up public key authentication involves server configuration steps beyond the scope of this explanation. Refer to your MySQL/MariaDB documentation for detailed instructions.

Choosing the best alternate method depends on your specific needs and security requirements.

Security Considerations:

• Using mysql_config_editor offers a more secure way to manage credentials compared to storing them directly in plain text.
• Creating a user with specific privileges reduces the attack surface by granting only necessary permissions.
• Public key authentication provides the most robust security but requires additional server-side configuration.