How To Reset Or Change The MySQL Root Password On Ubuntu?

Published August 24, 2024

Problem: MySQL Root Password Reset on Ubuntu

Resetting the MySQL root password on Ubuntu is sometimes needed for security or when you forget the current password. This process requires specific steps and commands to maintain database access and functionality.

Preparing for MySQL Root Password Reset

Checking MySQL Service Status

Before resetting the MySQL root password, check the status of the MySQL service. This step helps you know if MySQL is running and if you need to stop it before the password reset.

To verify if MySQL is running, use this command in the terminal:

sudo systemctl status mysql

This command will show the current status of the MySQL service. If MySQL is running, you'll see output indicating "active (running)".

You can also use this command to check MySQL status:

sudo service mysql status

This command gives similar information about the MySQL service status.

Tip: Troubleshooting MySQL Service

If the MySQL service is not running, you can start it using the command:

sudo systemctl start mysql

If you encounter issues starting the service, check the MySQL error logs for more information:

sudo tail -f /var/log/mysql/error.log

Backing Up MySQL Data

Backing up your MySQL data is important before changing the root password. This step protects your data if something goes wrong during the password reset.

To back up all MySQL databases, use the mysqldump command:

sudo mysqldump --all-databases > mysql_backup.sql

This command creates a SQL file with all your database structures and data.

For individual database backups, use:

sudo mysqldump database_name > database_backup.sql

Replace "database_name" with the name of the database you want to back up.

You can also use the mysqlpump utility to back up MySQL databases:

sudo mysqlpump --all-databases > mysql_backup.sql

This tool can be faster for large databases due to its parallel processing capabilities.

Example: Compressing MySQL Backups

To save disk space, you can compress your MySQL backup file using gzip:

sudo mysqldump --all-databases | gzip > mysql_backup.sql.gz

To restore from this compressed backup, use:

gunzip < mysql_backup.sql.gz | mysql -u root -p

By checking the MySQL service status and backing up your data, you're ready to reset the MySQL root password safely.

Methods to Reset MySQL Root Password

Method 1: Using mysqld_safe

To reset the MySQL root password using mysqld_safe:

Stop the MySQL service:
```
sudo systemctl stop mysql
```

Start MySQL without password validation:

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

Connect to MySQL as root:
```
mysql -u root
```

Reset the password in safe mode:

USE mysql;
UPDATE user SET authentication_string=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;
EXIT;

Stop and restart MySQL:

sudo killall mysqld
sudo systemctl start mysql

Tip: Backup Before Resetting

Before attempting to reset your MySQL root password, create a backup of your database. This precaution helps prevent data loss in case of errors during the reset process.

Method 2: Using ALTER USER Command

To reset the MySQL root password using the ALTER USER command:

Log into MySQL as root:
```
mysql -u root -p
```

Use the ALTER USER command to change the password:

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

Apply the changes:
```
FLUSH PRIVILEGES;
```

Method 3: Editing my.cnf File

To reset the MySQL root password by editing the my.cnf file:

Find the my.cnf file:
```
sudo nano /etc/mysql/my.cnf
```
Add this line under the [mysqld] section:
```
skip-grant-tables
```
Save the file and restart MySQL:
```
sudo systemctl restart mysql
```
Connect to MySQL without a password:
```
mysql -u root
```

Set the new password:

USE mysql;
UPDATE user SET authentication_string=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;
EXIT;

Remove the skip-grant-tables line from my.cnf and restart MySQL:
```
sudo systemctl restart mysql
```

These methods help you reset the MySQL root password on Ubuntu. Pick the method that suits your needs and MySQL admin skills.

Verifying the New MySQL Root Password

Testing MySQL Login

After resetting the MySQL root password, test that the new password works. Use this command in the terminal:

mysql -u root -p

Enter the new password when prompted. If successful, you'll see the MySQL prompt.

If you have login issues, try these steps:

Check the password for typing errors.
Check if the MySQL service is running:
```
sudo systemctl status mysql
```
Look at MySQL error logs for issues:
```
sudo tail -f /var/log/mysql/error.log
```

Tip: Use a Password Manager

Store your MySQL root password in a secure password manager. This helps avoid typing errors and ensures you always have access to the correct password.

Checking User Privileges

After logging in, verify that the root user has the correct permissions. Use this SQL command:

SHOW GRANTS FOR 'root'@'localhost';

This shows all privileges for the root user. The output should show that the root user has full access to all databases.

Check that these privileges are present:

ALL PRIVILEGES
GRANT OPTION

If any are missing, grant them with this command:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

This gives the root user full access to all databases and the ability to grant privileges to other users.

Tip: Regular Privilege Audits

Check and update user privileges often to keep your database secure. Remove unneeded privileges and keep only those needed for each user's role.

By testing the MySQL login and checking user privileges, you can confirm that the root password reset worked and that the root user has the needed access rights to manage the MySQL server.

	English
	Deutsch
	Français