How to Change MySql Root Password in Linux?

Published July 05, 2024

Problem: Changing MySQL Root Password in Linux

Updating the MySQL root password on a Linux system is important for database security. Administrators may need to change this password to follow security best practices or respond to potential security issues. The process can be complex for those new to MySQL's command-line interface or Linux system administration.

Methods to Change MySQL Root Password

Method 1: Using the MySQL Command Line

To change the MySQL root password using the command line:

  1. Start MySQL in safe mode:

    • Stop the MySQL service: sudo systemctl stop mysql
    • Start MySQL without permission checks: sudo mysqld_safe --skip-grant-tables &
  2. Reset the root password:

    • Connect to MySQL: mysql -u root
    • Select the MySQL database: USE mysql;
    • Update the root password: ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
    • Flush privileges: FLUSH PRIVILEGES;
    • Exit MySQL: EXIT;
  3. Restart the MySQL service:

    • Stop the safe mode instance: sudo killall mysqld
    • Start MySQL normally: sudo systemctl start mysql

Method 2: Modifying MySQL Configuration File

To change the root password by editing the MySQL configuration:

  1. Locate and edit the my.cnf file:

    • Find the file: sudo find /etc -name my.cnf
    • Edit the file: sudo nano /etc/mysql/my.cnf
  2. Add the skip-grant-tables option:

    • Under the [mysqld] section, add: skip-grant-tables
  3. Restart MySQL and update the password:

    • Restart MySQL: sudo systemctl restart mysql
    • Connect to MySQL: mysql -u root
    • Update the password: ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
    • Flush privileges: FLUSH PRIVILEGES;
    • Remove the skip-grant-tables option from my.cnf
    • Restart MySQL again: sudo systemctl restart mysql

Method 3: Using mysqladmin Tool

The mysqladmin tool offers a simple way to change the MySQL root password:

  1. Syntax for changing password with mysqladmin: mysqladmin -u root -p'old_password' password 'new_password'

  2. Examples of using mysqladmin command:

    • Change password when current password is known: mysqladmin -u root -p'current_password' password 'new_password'
    • Change password without current password (if not set): mysqladmin -u root password 'new_password'

Replace 'old_password', 'current_password', and 'new_password' with your actual passwords.

Verifying the New MySQL Root Password

After changing your MySQL root password, you should check if the new password works. Here's how to do it:

Logging in with the new password:

  1. Open your terminal or command prompt.
  2. Use this command to log in to MySQL: mysql -u root -p
  3. Enter your new password when asked.
  4. If you log in, you'll see the MySQL prompt.

Testing database access:

  1. Once logged in, try to access a database: SHOW DATABASES;
  2. This command should show a list of databases.
  3. Try to create a new database: CREATE DATABASE test_db;
  4. If it works, you'll see a message that confirms the database creation.
  5. You can then remove the test database: DROP DATABASE test_db;

If you can do these actions without errors, it means your new root password works and you can manage your MySQL server.

Troubleshooting Common Issues

Forgotten Root Password

If you've forgotten your MySQL root password, you can reset it by following these steps:

  1. Stop the MySQL service: sudo systemctl stop mysql

  2. Start MySQL in safe mode: sudo mysqld_safe --skip-grant-tables &

  3. Connect to MySQL without a password: mysql -u root

  4. Select the MySQL database: USE mysql;

  5. Reset the root password: ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

  6. Apply the changes: FLUSH PRIVILEGES;

  7. Exit MySQL: EXIT;

  8. Stop the safe mode instance: sudo killall mysqld

  9. Restart MySQL: sudo systemctl start mysql

Replace 'new_password' with your new password.

Permission Denied Errors

If you get permission denied errors after changing your password, try these solutions:

  1. Check your MySQL user privileges:

    • Log in to MySQL as root
    • Run: SHOW GRANTS FOR 'your_username'@'localhost';
    • Make sure you have the needed permissions
  2. Update user privileges if needed:

    • Use: GRANT ALL PRIVILEGES ON *.* TO 'your_username'@'localhost';
    • Then: FLUSH PRIVILEGES;
  3. Check the MySQL configuration file:

    • Look at /etc/mysql/my.cnf or /etc/my.cnf
    • Make sure there are no conflicting settings
  4. Restart the MySQL service: sudo systemctl restart mysql

  5. Check system file permissions:

    • Verify that MySQL data directory permissions are correct
    • Usually: chown -R mysql:mysql /var/lib/mysql

If problems continue, review MySQL error logs for more information.