Sling Academy
Home/MySQL/MySQL 8: 2 Ways to Reset Root Password in Ubuntu

MySQL 8: 2 Ways to Reset Root Password in Ubuntu

Last updated: January 25, 2024

Introduction

Occasionally, you may find yourself in a position where you need to reset the root password for MySQL 8 on Ubuntu. This could be due to a forgotten password or inherited server management responsibilities without credential handover. Fortunately, there are multiple ways to resolve this issue. Here we’ll discuss different methods to reset the root password, each with their steps and considerations.

Method 1: Using mysqld_safe

This method involves starting MySQL in a safe mode that does not require authentication.

1. Stop the MySQL service using the command:

sudo systemctl stop mysql

2. Start mysqld_safe with the –skip-grant-tables option:

sudo mysqld_safe --skip-grant-tables &

3. Login to MySQL without a password:

mysql -u root

4. Flush privileges and set a new root password:

FLUSH PRIVILEGES;
 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

5. Exit MySQL and restart the service:

exit;
 sudo systemctl start mysql

Notes: While this method is straightforward, it involves running MySQL without any security checks, which can be risky. You should revert back to normal operation as quickly as possible to maintain server security.

Method 2: Init File Method

This method uses a SQL script executed at server startup to reset the password.

  1. Create an init file with the SQL statement to reset the password:
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';" > /var/tmp/mysql-init

2. Stop the MySQL service:

sudo systemctl stop mysql

3. Start MySQL with the init file:

sudo mysqld --init-file=/var/tmp/mysql-init

4. After MySQL starts and processes the init file, stop MySQL, and start the service normally:

sudo systemctl restart mysql

Notes: This method is more secure than the first as it does not require running MySQL in an insecure state. However, you need to ensure that the init file is protected and deleted after use to prevent unauthorized password changes.

Conclusion

Resetting the MySQL root password on Ubuntu can be achieved through various methods. Whether you decide to use mysqld_safe or an init file, both methods require caution and understanding of MySQL’s authentication process. It is essential to protect your database to prevent unauthorized access, so always remember to handle root credentials securely and remove temporary configurations promptly after use. After completing the password reset, testing your access and ensuring your databases respond as expected is recommended.

Next Article: MySQL 8: How to create a new user with root privileges

Previous Article: MySQL 8: 3 Ways to Reset Root Password in macOS

Series: MySQL Tutorials: From Basic to Advanced

MySQL

You May Also Like

  • MySQL: How to reset the AUTO_INCREMENT value of a table
  • MySQL: How to add a calculated column to SELECT query
  • MySQL: Eliminate orphan rows in one-to-many relationships
  • MySQL: Using R-Tree Indexes for Spatial Data Types
  • How to Create Custom Collations in MySQL
  • Using Hash Indexes in MySQL: A Practical Guide
  • Understanding Full-Text Indexes in MySQL
  • Partial Indexes in MySQL: A Practical Guide
  • MySQL: How to Remove FOREIGN KEY Constraints
  • Using ENUM in MySQL 8: A Practical Guide (with Examples)
  • MySQL: Creating a Fixed-Size Table by Using Triggers
  • One-to-Many Relationship in MySQL 8: A Practical Guide
  • Using Regular Expressions in MySQL 8: The Complete Guide
  • Using Loops in MySQL: A Practical Guide (with Examples)
  • How to Execute an SQL File in VS Code
  • Making use of the JSON_REMOVE() function in MySQL 8
  • MySQL 8: How to count rows in related tables
  • Replication in MySQL 8: A Comprehensive Guide
  • MySQL 8: FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() functions – Explained with examples