How to change MySQL password in Linux command line (SSH) Best methods

In this tutorial, we cover the following topic: 1) set and change MySQL password (root and user password) in Linux operating system, such as centOS, AlmaLinux … and recover it if you forget it.

To execute the instructions in this tutorial, you need access to the Linux command line or SSH. And you are the system administrator.  Note that the root user of the operating system is different from the root user of the MySQL service.

MySQL database management service is one of the most basic parts of a server and its proper management will have a great impact on the level of performance and security.

How to set a password for the root user of the MySQL service #

If there is no password for the root user before, you can easily set the password with the following command:

 

[html]mysqladmin -u root password NEWPASSWORD [/html]

 Instead of “NEWPASSWORD”, you have to enter the password you want. But if the password for the root user of the MySQL service already exists, you must proceed with the following command:

[html]mysqladmin -u root -p ‘OLDPASSWORD’ password NEWPASSWORD [/html]

You must enter the current password instead of “OLDPASSWORD” and the new password instead of NEWPASSWORD. It should also be noted that if you want to change the password of another MySQL user, you can replace the username with root.

Another way to set the root user password in the MySQL #

You can also do this in the mysql syntax environment. To enter the mysql command environment, you must enter the following command:

[html]mysql -u root -p PASSWORD[/html]

Instead of PASSWORD you have to enter the root password. After entering the mysql command environment, you can change the password with the following command:

[html]update user set password=PASSWORD("NEWPASSWORD") where User=’root’;[/html]

Instead of NEWPASSWORD you have to enter a new password. and then run:

[html] flush privileges;
quit[/html]

How to change the MySQL root password if you forget it! #

If you have forgotten the MySQL root password, there is no need to worry and you can set a new password by following these steps:

First, stop the MySQL service with the following command:

[html]systemctl stop mysql[/html]

Then we run the MySQL service with the following command without having to enter a password:

[html]sudo mysqld_safe –skip-grant-tables &[/html]

Fix Error & reset root password when ” /var/run/mysqld’ for UNIX socket file don’t exists” #

maybe like me when you useing the code mysqld_safe –skip-grant-tables & face the  following error:

[html] mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists.[/html]

for fix above issue , run below command :

[html] $ mkdir -p /var/run/mysqld
$ chown mysql:mysql /var/run/mysqld [/html]

then try again :

[html]sudo mysqld_safe –skip-grant-tables &[/html]

Now we can log in to mysql with the following command with the root user:

[html]mysql -u root[/html]

Set a new root password #

Run the following commands if you run MySQL 5.7.6 and later or MariaDB 10.1.20 and later:

[html] mysql > ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MY_NEW_PASSWORD’;
mysql > FLUSH PRIVILEGES;
[/html]

If  ALTER USER statement doesn’t work for you, try to modify the user table directly:

[html] mysql > UPDATE mysql.user SET authentication_string = PASSWORD(‘MY_NEW_PASSWORD’)
mysql > WHERE User = ‘root’ AND Host = ‘localhost’;
mysql > FLUSH PRIVILEGES;
[/html]

Run the following commands if you have MySQL 5.7.5 and earlier or MariaDB 10.1.20 and earlier:

[html]

mysql > SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘MY_NEW_PASSWORD’);
mysql > FLUSH PRIVILEGES;
[/html]

In both cases if all goes well, you should see the following output:

[html] Query OK, 0 rows affected (0.00 sec) [/html]

How Reset Mysql 8.x Password #

Reset MySQL 8 root password completely different. run below command :

[html] sudo service mysql stop
sudo mysqld –skip-grant-tables &
mysql -u root mysql
mysql > UPDATE mysql.user SET authentication_string=null WHERE User=’root’;
mysql > flush privileges;
mysql > ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘your_new_password_here’;
mysql > flush privileges;
mysql > exit;
[/html]

After doing the work and setting the new password, we can restart the MySQL service with the following command:

[html] sudo systemctl restart mysql
[/html]