Ytukyg

The official and easy way to reset the root password on an ubuntu server...

If you are on 16.04, 14.04, 12.04:

sudo dpkg-reconfigure mysql-server-5.5

If you are on 10.04:

sudo dpkg-reconfigure mysql-server-5.1

If you are not sure which mysql-server version is installed you can try:

dpkg --get-selections | grep mysql-server

Updated notes for mysql-server-5.7

Note that if you are using mysql-server-5.7 you can not use the easier dpkg-reconfigure method shown above.

If you know the password, login and run this:

UPDATE mysql.user SET authentication_string=PASSWORD('my-new-password') WHERE USER='root';

FLUSH PRIVILEGES;

Alternatively, you can use the following:

sudo mysql_secure_installation

This will ask you a series of questions about securing your installation (highly recommended), including if you want to provide a new root password.

If you do NOT know the root password, refer to this Ubuntu-centric write up on the process.

See for more info:

https://help.ubuntu.com/16.04/serverguide/mysql.html
https://help.ubuntu.com/14.04/serverguide/mysql.html

I am sharing the step by step final solution to reset a MySQL password in Linux
Ubuntu.

Reference taken from blog (dbrnd.com)

Step 1:
Stop MySQL Service.

sudo stop mysql

Step 2:
Kill all running mysqld.

sudo killall -9 mysqld

Step 3:
Starting mysqld in Safe mode.

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

Step 4:
Start mysql client

mysql -u root

Step 5:
After successful login, please execute this command to change any password.

FLUSH PRIVILEGES;

Step 6:
You can update mysql root password .

UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

Note: On MySQL 5.7, column Password is called authentication_string.

Step 7:
Please execute this command.

FLUSH PRIVILEGES;

The only method that worked for me is the one described here (I am running ubuntu 14.04). For the sake of clarity, these are the steps I followed:

1. sudo vim /etc/mysql/my.cnf


2. 
   

   Add the following lines at the end:

   
   
   

   [mysqld]
   
   
   
   skip-grant-tables

   
   



3. sudo service mysql restart




4. mysql -u root




5. use mysql




6. select * from mysql.user where user = 'root'; - Look at the top to determine whether the password column is called
   password or authentication_string




7. UPDATE mysql.user set *password_field from above* = PASSWORD('your_new_password') where user = 'root' and host = 'localhost'; - Use the proper password column from above




8. FLUSH PRIVILEGES;




9. exit




10. sudo vim /etc/mysql/my.cnf




11. Remove the lines added in step 2 if you want to keep your security standards.




12. sudo service mysql restart

For reference : https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

Change the MySQL root password.

This method exposes the password to the command-line history, these commands should be run as root.

1. 
   

   Login through mysql command line tool:

   
   
   

   mysql -uroot -poldpassword
   
   

   
   


2. 
   

   Run this command:

   
   
   

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

   
   

or

2. 
   

   Run this command, which sets a password for the current user ('root' for this case) :

   
   
   

   
   

   SET PASSWORD = PASSWORD('newpassword');

   
   

   
   

What worked for me (Ubuntu 16.04, mysql 5.7):

Stop MySQL

sudo service mysql stop

Make MySQL service directory.

sudo mkdir /var/run/mysqld

Give MySQL user permission to write to the service directory.

sudo chown mysql: /var/run/mysqld

Start MySQL manually, without permission checks or networking.

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

On another console, log in without a password.

mysql -uroot mysql

Then:

UPDATE mysql.user SET authentication_string=PASSWORD('YOURNEWPASSWORD'), plugin='mysql_native_password' WHERE User='root' AND Host='%';

EXIT;

Turn off MySQL.

sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown

Start the MySQL service normally.

sudo service mysql start

If you would like to change the MySQL root password, in a terminal enter:

sudo dpkg-reconfigure mysql-server-5.5

The MySQL daemon will be stopped, and you will be prompted to enter a new password.

I faced problems with ubuntu 18.04 and mysql 5.7, this is the solution

MYSQL-SERVER >= 5.7

sudo mysql -uroot -p

USE mysql;

UPDATE user SET authentication_string=PASSWORD('YOUR_PASSWORD') WHERE User='root';

UPDATE user SET plugin="mysql_native_password";

FLUSH PRIVILEGES;

quit;

MYSQL-SERVER < 5.7

sudo mysql -uroot -p

USE mysql;

UPDATE user SET password=PASSWORD('YOUR_PASSWORD') WHERE User='root';

UPDATE user SET plugin="mysql_native_password";

FLUSH PRIVILEGES;

quit;

This works like charm I did it for Ubuntu 16.04.
Full credit to below link as I got it from there.
[https://coderwall.com/p/j9btlg/reset-the-mysql-5-7-root-password-in-ubuntu-16-04-lts][1]

Stop MySQL

sudo service mysql stop

Make MySQL service directory.
sudo mkdir /var/run/mysqld

Give MySQL user permission to write to the service directory.

sudo chown mysql: /var/run/mysqld

Start MySQL manually, without permission checks or networking.

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

Log in without a password.

mysql -uroot mysql

Update the password for the root user.
make sure at atleast root account gets updated by the below query.
make some selection and check the existing values if you like

UPDATE mysql.user SET 

authentication_string=PASSWORD('YOURNEWPASSWORD'), 

plugin='mysql_native_password' WHERE User='root' AND Host='%';

EXIT;

Turn off MySQL.

sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown

Start the MySQL service normally.

sudo service mysql start

1. Stop MySQL
   sudo service mysql stop




2. Make MySQL service directory.
   sudo mkdir /var/run/mysqld




3. Give MySQL user permission to write to the service directory.
   sudo chown mysql: /var/run/mysqld




4. Start MySQL manually, without permission checks or networking.
   sudo mysqld_safe --skip-grant-tables --skip-networking &

5.Log in without a password.
mysql -uroot mysql

6.Update the password for the root user.

UPDATE mysql.user SET authentication_string=PASSWORD('YOURNEWPASSWORD'), plugin='mysql_native_password' WHERE User='root' AND Host='%';
EXIT;

7. Turn off MySQL.
   sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown




8. Start the MySQL service normally.
   sudo service mysql start

This solution belongs to the previous version of MySQL.
By logging in to MySQL using socket authentication, you can do it.

sudo mysql -u root

Then the following command could be run.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Details are available here .

Echoing rogerdpack's comment: if you don't know the MySQL root password and you don't care about MySQL data/settings, you can reinstall it and reset the root's password as follows:

sudo apt-get remove --purge mysql-server mysql-client mysql-common

sudo rm -rf /var/lib/mysql

sudo apt-get install -y mysql-server mysql-client 

During the installation, you can choose the root's password:

If you know your current password, you don't have to stop mysql server.
Open the ubuntu terminal.
Login to mysql using:

mysql - username -p

Then type your password.
This will take you into the mysql console.
Inside the console, type:

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

Then flush privileges using:

> flush privileges;

Then you are all done.

When you use MySQL's PASSWORD() on the system where you want to change the password, it can cause the password turn up in a MySQL log in cleartext [source]. Keeping them, their backups etc. as secure as the password sounds like nightmare to me, so I rather like to do it as follows:

1. 
   

   On your local machine, run this with your password:

   
   
   

    mysql -u someuser -p < <(echo "SELECT PASSWORD('mypass');")
   
   

   
   
   

   Note the space in front to prevent it from turning up in the bash history (for other distros than Ubuntu, this might work differently – source).

   
   


2. 
   

   On your server machine, execute the following command to change its MySQL root password (replace myhash with your password's hash as printed by the first command):

   
   
   

   mysql -u root -p < <(echo "SET PASSWORD FOR root@localhost = 'myhash';")
   
   

   
   



3. Optionally, let's be a bit paranoid: On your local machine, clear your terminal screen with clear and purge your virtual terminal scrollback, to hide the cleartext password appearing in the command above.

To update the "root" Mysql user password you must have in mind that you will need of super user permissions for that. If you have super user privilegies, try the following commands:

MySQL 5.7.6 and later

sudo su

service mysql stop

mysql -u root

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

q;

exit

mysql -u root -p MyNewPass

MySQL 5.7.5 and earlier

sudo su

service mysql stop

mysql -u root

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

q;

exit

mysql -u root -p MyNewPass

Instead of resetting the password there is a work around on the local machine if you have setup phpmyadmin to connect without giving the password or username. Check this out by starting mysql, apache etc. I have xampp installed in my local machine. So starting the xampp will start all the necessary services. Now going to http://localhost/phpmyadmin shows me all the databases. This confirms that you have saved the username and passsword in the config file of phpmyadmin which can be found in the phpmyadmin install location. If you have xampp installed the phpmyadmin folder can be found in the root folder of xampp installation. Search for the word password in the config.inc.php file. There you will find the password and username.

You can easily change the mysql password if deployed on xampp through provided phpadmin gui.

phpMyAdmin -> User Accounts -> Edit Privileges (Select the intended user) -> Change Password (Tab)

You can use this command :

UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

after that pleas use flush
FLUSH PRIVILEGES;

for mysql 5.6 this command works and you can set password through the wizard:

sudo dpkg-reconfigure mysql-server-5.6

You don't need all this. Simply log in:

mysql -u root -p

Then change the current user's password as the mysql> prompt:

mysql> set password=password('the_new_password');

mysql> flush privileges;

You can try these some steps to reset mysql 5.7 root password :

Stop Mysql Service 1st

sudo /etc/init.d/mysql stop 

Login as root without password
sudo mysqld_safe --skip-grant-tables &

After login mysql terminal you should need execute commands more:

use mysql;









UPDATE mysql.user SET authentication_string=PASSWORD('solutionclub3@*^G'), plugin='mysql_native_password' WHERE User='root';





flush privileges;





sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown

After you restart your mysql server
If you still facing error you must visit :
Reset MySQL 5.7 root password Ubuntu 16.04

This is the solution for me. I work at Ubuntu 18.04:
https://stackoverflow.com/a/46076838/2400373

But is important this change in the last step:

UPDATE mysql.user SET authentication_string=PASSWORD('YOURNEWPASSWORD'), plugin='mysql_native_password' WHERE User='root' AND Host='localhost'; 

when changing/resetting the MySQL password the following commands listed above did not help. I found that going into the terminal and using these commands is pointless. instead use the command sudo stop everything. DELETE SYSTEM 32 for windows if that helps.

To reset or change the password enter sudo dpkg-reconfigure mysql-server-X.X (X.X is mysql version you have installed i.e. 5.6, 5.7) and then you will prompt a screen where you have to set the new password and then in next step confirm the password and just wait for a moment. That's it.

I had to go this route on Ubuntu 16.04.1 LTS. It is somewhat of a mix of some of the other answers above - but none of them helped. I spent an hour or more trying all other suggestions from MySql website to everything on SO, I finally got it working with:


Note: while it showed Enter password for user root, I didnt have the original password so I just entered the same password to be used as the new password.


Note: there was no /var/log/mysqld.log only /var/log/mysql/error.log



Also note this did not work for me:

sudo dpkg-reconfigure mysql-server-5.7



Nor did:

sudo dpkg-reconfigure --force mysql-server-5.5



Make MySQL service directory.

sudo mkdir /var/run/mysqld



Give MySQL user permission to write to the service directory.

sudo chown mysql: /var/run/mysqld



Then:

1. kill the current mysqld pid


2. run mysqld with sudo /usr/sbin/mysqld &


3. 
   

   run /usr/bin/mysql_secure_installation
   
   
   Output from mysql_secure_installation

   
   
   

   
   

   root@myServer:~# /usr/bin/mysql_secure_installation

   
   
   

   Securing the MySQL server deployment.

   
   
   

   Enter password for user root:

   
   
   

   VALIDATE PASSWORD PLUGIN can be used to test passwords
   and improve security. It checks the strength of password
   and allows the users to set only those passwords which are
   secure enough. Would you like to setup VALIDATE PASSWORD plugin?

   
   
   

   Press y|Y for Yes, any other key for No: no
   Using existing password for root.
   Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

   
   
   

   New password:

   
   
   

   Re-enter new password:
   By default, a MySQL installation has an anonymous user,
   allowing anyone to log into MySQL without having to have
   a user account created for them. This is intended only for
   testing, and to make the installation go a bit smoother.
   You should remove them before moving into a production
   environment.

   
   
   

   Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
   Success.

   
   
   

   Normally, root should only be allowed to connect from
   'localhost'. This ensures that someone cannot guess at
   the root password from the network.

   
   
   

   Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
   Success.

   
   
   

   By default, MySQL comes with a database named 'test' that
   anyone can access. This is also intended only for testing,
   and should be removed before moving into a production
   environment.

   
   
   

   Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

   
   
   

   
   

   • Dropping test database...
     Success.

   
   

   • Removing privileges on test database...
     Success.

   
   

   
   

   Reloading the privilege tables will ensure that all changes
   made so far will take effect immediately.

   
   
   

   Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
   Success.

   
   
   

   All done!

   
   

   
   

As mysql documentation on the password() function says:

This function was removed in MySQL 8.0.11.

This invalidates pretty much all existing answers for mysql v8.0.11 and newer.

Per mysql documentation the new generic way to reset the root password is as follows:

The preceding sections provide password-resetting instructions
specifically for Windows and Unix and Unix-like systems.
Alternatively, on any platform, you can reset the password using the
mysql client (but this approach is less secure):

Stop the MySQL server if necessary, then restart it with the
--skip-grant-tables option. This enables anyone to connect without a password and with all privileges, and disables account-management
statements such as ALTER USER and SET PASSWORD. Because this is
insecure, if the server is started with the --skip-grant-tables
option, it enables --skip-networking automatically to prevent remote
connections.

Connect to the MySQL server using the mysql client; no password is
necessary because the server was started with --skip-grant-tables:

shell> mysql

In the mysql client, tell the server to reload the grant tables so that account-management statements work:

mysql> FLUSH PRIVILEGES;

Then change the 'root'@'localhost' account password. Replace the password with the password that you want to use.
To change the password for a root account with a different host name
part, modify the instructions to use that host name.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

You should now be able to connect to the MySQL server as root using the
new password. Stop the server and restart it normally (without the
--skip-grant-tables and --skip-networking options).