Changing the root password for MySQL

MySQL Ubuntu Debian Centos Virtual server Dedicated server SSH

To change the root password for the MySQL user, you should have a server connection with SSH.

Connect to MySQL as the root user using the command:

mysql –u root –p

Press Enter, set the password, and press Enter again;

Run the following commands:

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

quit

After this, the password will be changed to newpass

This method is also suitable for changing the passwords of other MySQL users. To do this, in the above-mentioned command, you should state the user for whom the password is changed (for example, instead of 'root'@'localhost' — 'dovecot'@'localhost'), and the new password.

The Reset of root-password MySQL

If mysql root-password has been lost, you can reset it in the following way: Connect to VPS with the root rights.

Stop the mysql-server.

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

service mysql stop

Create a catalogue /var/run/mysqld that will be used by mysql for storage and access to the socket file, and set the necessary rights:

sudo mkdir -p /var/run/mysqld

sudo chown mysql:mysql /var/run/mysqld

Set mysql with the following parameters:

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

Check that the process is launched:

jobs

At this stage, we can get access to mysql without a password.

Enter with the command:

mysql -u root

Then reset the root password with the following sequence of requests:

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> USE mysql;

Database changed

mysql> UPDATE user SET authentication_string=PASSWORD("новый_root_пароль") 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".

Finish the process mysqld:

sudo pkill mysqld

Launch the mysql service:

sudo service mysql start

Now we can log in using the set password:

mysql -u root --password=1111

On Debian, to stop the mysql-server, use the command:

service mysql stop

Launch mysql with the key --skip-grant-tables in the background:

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

mysql -u root

Change the password and apply privileges:

mysql> UPDATE mysql.user SET Password=PASSWORD('новый_root_пароль') WHERE User='root';

mysql> FLUSH PRIVILEGES;

mysql> exit

On CentOS 7, to stop the mysql-server, use the command:

sudo /etc/init.d/mysqld stop

Then execute the command:

sudo mysqld_safe --skip-grant-tables &

Character & at the end of the command means that it will switch to background, and we will get the instrument back.

Connect to mysql with the root rights. As mysql is launched in the safe mode, the password is not necessary to log in:

mysql -u root

Choose the mysql database:

use mysql;

Set the new mysql root-password with the command:

update user set password=PASSWORD("новый_root_пароль") where User='root';

Reboot privileges:

flush privileges;

Disconnect from mysql:

quit;

Stop and then relaunch the server MySQL with the commands:

Debian/Ubuntu:

service mysql stop

service mysql start

CentOS 7

/etc/init.d/mysql stop

/etc/init.d/mysql start

Try connecting to mysql with the new set password.

Blog