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 &
Login into mysql from the root user (the password will not be requested):
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.