Changing the MySQL password-root

To change a MySQL user's root password, you need to connect to the server via SSH

Connect to MySQL as root user with the command ``:

mysql -u root -p  

Press Enter, specify password and press Enter again;

Execute the following commands:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');  
quit  

After that, the password will be changed to newpass.

This method can be used to change passwords for other MySQL users as well. To do this, in the above command, you need to specify the user for whom the password is changed (for example, instead of root@localhost - dovecot@localhost) and the new password.

Resetting MySQL root password

If mysql root password has been lost, you can reset it as follows:

Connect to the VPS with root privileges.

Stop mysql-server.

On Ubuntu use the command to stop the mysql-server:

service mysql stop  

Create a directory /var/run/mysqld which will be used by mysql to store and access the socket file and assign the necessary permissions:

sudo mkdir -p /var/run/mysqld  
sudo chown mysql:mysql /var/run/mysqld  

Run mysql with the following parameters:

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &amp  

Let's check that the process is running:

jobs  

At this point we can access mysql without a password.
We log in with the command:

mysql -u root  

Then reset the root password with the following query sequence:

mysql> FLUSH PRIVILEGES;  
Query OK, 0 rows affected (0.00 sec)  

mysql> USE mysql;  
Database changed  

mysql> UPDATE user SET authentication_string=PASSWORD("new_root_password") WHERE User='root';  
Query OK, 1 row affected, 1 warning (0.03 sec)  
Rows matched: 1 Changed: 1 Warnings: 1  

mysql> UPDATE user SET plugin="mysql_native_password" WHERE User='root';  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1 Changed: 1 Warnings: 0  

mysql> quit  

New_root_password, we set "1111".

Let's quit the mysqld process:

sudo pkill mysqld  

Start the mysql service:

`` sudo service mysql start

Now we can log in with the password we set:

mysql -u root --password=1111  

---

On Debian to stop the mysql server we use the command

service mysql stop  

Run mysql with the --skip-grant-tables switch in the background :

/usr/bin/mysqld_safe --skip-grant-tables --user=root &amp

Logging into mysql as root (no password will be asked for):

mysql -u root  

Change the password and apply privileges:

mysql> UPDATE mysql.user SET Password=PASSWORD('new_root_password') WHERE User='root';  
mysql> FLUSH PRIVILEGES;  
mysql> exit  

---

On CentOS 7 use command to stop mysql-server:

sudo /etc/init.d/mysqld stop  

Then we execute the command:

`` sudo mysqld_safe --skip-grant-tables &amp

The & symbol at the end of the command means that it will go into the background and we will get the terminal back.

We connect to mysql as root. Since mysql is running in safe mode, no password is needed to log in:

mysql -u root  

Select the mysql database:

use mysql;  

Set a new mysql root password with the command:

update user set password=PASSWORD("new_root_password") where User='root';  

Reboot privileges:

``flush privileges;

Disconnect from mysql:

quit;  

Stop and then restart the MySQL server with commands:
Debian/Ubuntu:

service mysql stop  
service mysql start  

CentOS 7

/etc/init.d/mysql stop
/etc/init.d/mysql start

Try to connect to mysql with the new password set.