Resetting the MySQL root password on a Linux server can be a critical task when access to the MySQL server is lost or when setting up a new server. This process involves several steps, including stopping the MySQL service, starting MySQL in a safe mode, and updating the root password. This guide will walk you through each step in detail to ensure you can reset your MySQL root password successfully.

In this tutorial you will learn:

• How to stop the MySQL service
• How to start MySQL in safe mode
• How to update the MySQL root password
• How to restart the MySQL service

👁 How to reset MySQL root password on your Linux server

Software Requirements and Linux Command Line Conventions

Category	Requirements, Conventions or Software Version Used
System	Linux server
Software	MySQL
Other	Root or sudo access to the server
Conventions	# – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command $ – requires given linux commands to be executed as a regular non-privileged user

How to Reset MySQL Root Password on Your Linux Server

When you lose access to your MySQL root account, resetting the root password is essential. This guide provides a step-by-step approach to reset your MySQL root password on a Linux server.

1. Stop the MySQL Service: The first step is to stop the MySQL service to prevent any new connections.

   $ sudo systemctl stop mysql

   Stopping the MySQL service ensures that no other users can connect to the database while we are resetting the root password.

2. Create and Set Permissions for the MySQL Directory: Next, you need to create the necessary directory and set the correct permissions. Using the chown command, we’ll set proper ownership.

   $ sudo mkdir -p /var/run/mysqld
   $ sudo chown mysql:mysql /var/run/mysqld
   $ sudo chmod -R 755 /var/run/mysqld
   $ sudo rm -f /var/run/mysqld/mysqld.sock.lock
   $ sudo rm -f /var/run/mysqld/mysqlx.sock.lock

   These commands create the directory, set ownership, permissions, and remove any existing socket lock files to avoid conflicts.

3. Start MySQL in Safe Mode: Now, start MySQL in safe mode with the grant tables disabled.

   $ sudo mysqld_safe --skip-grant-tables &

   Starting MySQL in safe mode allows you to access the database without a password, which is necessary for resetting the root password.

4. Log in to MySQL: Log in to MySQL as the root user without a password.

   mysql -u root

   This command logs you into the MySQL shell where you can execute SQL commands to reset the root password.

5. Select the MySQL Database: Use the MySQL database where user information is stored.

   mysql> USE mysql;

   Selecting the MySQL database allows you to modify user information, including resetting the root password.

6. Flush Privileges: Refresh the MySQL privileges to ensure any changes take effect.

   mysql> FLUSH PRIVILEGES;

   Flushing privileges reloads the grant tables, ensuring any changes made are recognized by the server.

   ---

   ---

7. Reset the Root Password: Update the root user password.

   mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

   This command changes the root password to ‘new_password’. Replace ‘new_password’ with your desired password.

8. Exit MySQL and Restart the Service: Exit the MySQL shell.

   mysql> exit

   👁 Resetting the MySQL password.
   

9. Kill the Safe Mode Process: Terminate the MySQL safe mode process.

   $ sudo killall mysqld

   Killing the safe mode process ensures that MySQL runs normally without skipping grant tables.

10. Start MySQL Service Again: Finally, start the MySQL service normally.

    $ sudo systemctl start mysql

    Starting the MySQL service again in normal mode allows you to use the server with the new root password.

11. Verify the New Password: Verify that the new root password works by logging into MySQL.

    $ mysql -p

    When prompted, enter the new password to confirm that the password reset was successful.

    👁 Using the new password to login as root to the MySQL server
    

Conclusion

Resetting the MySQL root password on a Linux server is a straightforward process if followed carefully. By stopping the MySQL service, starting it in safe mode, and updating the root password, you can regain access to your MySQL server. Ensure that you follow each step correctly to avoid any issues.

Troubleshooting Appendix

If you encounter issues during the reset process, refer to the log messages and solutions below.

Relevant Log Messages:

2024-06-26T09:13:42.950465Z 0 [ERROR] [MY-011292] [Server] Plugin mysqlx reported: 'Preparation of I/O interfaces failed, X Protocol won't be accessible'
2024-06-26T09:13:42.950494Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of socket: '/var/run/mysqld/mysqlx.sock' failed, can't create lock file /var/run/mysqld/mysqlx.sock.lock'
2024-06-26T09:13:43.032408Z 0 [ERROR] [MY-010273] [Server] Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock.
2024-06-26T09:13:43.032420Z 0 [ERROR] [MY-010268] [Server] Unable to setup unix socket lock file.
2024-06-26T09:13:43.032425Z 0 [ERROR] [MY-010119] [Server] Aborting

Common Issues and Solutions:

1. Plugin mysqlx Errors:
– Errors:
– `Preparation of I/O interfaces failed, X Protocol won’t be accessible`
– `Setup of socket: ‘/var/run/mysqld/mysqlx.sock’ failed, can’t create lock file /var/run/mysqld/mysqlx.sock.lock`
– Solution: Ensure MySQL installation is complete. Address socket issues with the following commands:

$ sudo mkdir -p /var/run/mysqld
$ sudo chown mysql:mysql /var/run/mysqld
$ sudo chmod -R 755 /var/run/mysqld
$ sudo rm -f /var/run/mysqld/mysqld.sock.lock
$ sudo rm -f /var/run/mysqld/mysqlx.sock.lock

2. Unix Socket Lock File Errors:
Errors:
– `Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock.`
– `Unable to setup unix socket lock file.`
Solution: Ensure there are no permission issues or existing lock files. Use the commands above to fix.

3. Server Aborting:
Error: `Aborting`
Solution: Follow the steps to set permissions and remove existing lock files as shown above.

4. Existing MySQL Process:
Error:

$ sudo mysqld_safe --skip-grant-tables &
[2] 6050
2024-06-26T10:02:00.665573Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2024-06-26T10:02:00.683616Z mysqld_safe A mysqld process already exists
[1]+ Exit 1 sudo mysqld_safe --skip-grant-tables

Solution: Ensure that the MySQL server is stopped before attempting to start in safe mode. Use:

sudo systemctl stop mysql

5. Error While Setting New Password:
Error:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Solution: Ensure to run:

FLUSH PRIVILEGES;

before setting a new password.

Check the MySQL error log at `/var/log/mysql/error.log` for more detailed information if problems persist.

FAQ for Resetting MySQL Root Password on Linux Server

1. Q: Why do I need to stop the MySQL service?

   A: Stopping the MySQL service ensures no new connections are made while resetting the root password, providing a stable environment for making changes.

2. Q: What does ‘mysqld_safe –skip-grant-tables &’ do?

   A: This command starts MySQL in safe mode with grant tables disabled, allowing you to connect without a password and reset the root password.

3. Q: Why do I need to create and set permissions for the MySQL directory?

   A: Creating and setting permissions for the directory ensures MySQL can start properly without encountering permission issues or socket file conflicts.

4. Q: What is the purpose of the ‘FLUSH PRIVILEGES’ command?

   A: ‘FLUSH PRIVILEGES’ reloads the grant tables in MySQL, ensuring any changes to user privileges take effect immediately.

5. Q: How do I verify that the new root password works?

   A: You can verify the new password by logging into MySQL using the command ‘mysql -p’ and entering the new password when prompted.

6. Q: Why do I need to restart the MySQL service after resetting the password?

   A: Restarting the MySQL service applies the changes made to the root password and ensures the server runs with updated configurations.

7. Q: What happens if I don’t kill the mysqld_safe process?

   A: If the mysqld_safe process is not killed, MySQL may continue running in safe mode, which can pose a security risk and prevent normal operation.

8. Q: What does the ‘sudo mysql’ command do?

   A: When you run ‘sudo mysql’, you are effectively running the MySQL client as the root user of the operating system, not as the root user of the MySQL database. This allows the OS root user to access the MySQL server without a password if configured to do so.

9. Q: What is Unix socket authentication?

   A: Unix socket authentication allows MySQL to authenticate users based on their Unix (Linux) user account, providing passwordless access for the OS root user if configured properly.

10. Q: Why do I get an access denied error when using ‘mysql -uroot -ppassword’?

    A: This error occurs because MySQL is attempting to authenticate using a password, but the root user may be configured to use auth_socket plugin, which does not require a password.

11. Q: How do I switch from auth_socket to password authentication for the MySQL root user?

    A: You can switch by updating the root user’s authentication method in MySQL using the command ‘ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘new_password’;’.