ChatGPT解决这个技术问题 Extra ChatGPT

MySQL: How to reset or change the MySQL root password?

How do I change the MySQL root password and username in ubuntu server? Do I need to stop the mysql service before setting any changes?

I have a phpmyadmin setup as well, will phpmyadmin get updated automatically?

If you don't need the data you can "reset" the password by removing mysql in its entirety: stackoverflow.com/questions/10853004/… then installing it again (it will prompt you for the "new" root password) FWIW :)
Do you know the MySQL root password?
in mysql-server-5.7 these methods dont work. use 'sudo' without password as mentioned in askubuntu.com/a/848504
Tip: sort by "oldest" and then look at the newest answers. The most upvoted answers don't work in newer versions of MySQL.

M
Mike

Set / change / reset the MySQL root password on Ubuntu Linux. Enter the following lines in your terminal.

Stop the MySQL Server: sudo /etc/init.d/mysql stop Start the mysqld configuration: sudo mysqld --skip-grant-tables &

In some cases, you've to create the /var/run/mysqld first:

    sudo mkdir -v /var/run/mysqld && sudo chown mysql /var/run/mysqld

Login to MySQL as root: mysql -u root mysql Replace YOURNEWPASSWORD with your new password:

For MySQL < 8.0

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

Note: on some versions, if password column doesn't exist, you may want to try: UPDATE user SET authentication_string=password('YOURNEWPASSWORD') WHERE user='root';

Note: This method is not regarded as the most secure way of resetting the password, however, it works.

For MySQL >= 8.0

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

References:

Set / Change / Reset the MySQL root password on Ubuntu Linux How to Reset the Root Password (v5.6) How to Reset the Root Password (v8.0)


Of course, after this point you'll need to kill the temporary, password-less server process that you started in step 2. maybe use sudo killall -9 mysqld? and then sudo service mysql start to restart the normal daemon...
The method in this answer is only needed for resetting a MySQL root password that you don't know [source]. If you know it, the SET PASSWORD MySQL instruction is for you.
i followed till 3rd step and i get Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when i login thru root
in some cases, you also need to run mkdir /var/run/mysqld and chown mysql: /var/run/mysqld between steps 1 and 2
step 3 did not work for me with Ubuntu 18.04. I just logged in using sudo mysql
R
Ravindra Gullapalli

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:

sudo vim /etc/mysql/my.cnf Add the following lines at the end: [mysqld] skip-grant-tables sudo service mysql restart mysql -u root use mysql select * from mysql.user where user = 'root'; - Look at the top to determine whether the password column is called password or authentication_string 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 FLUSH PRIVILEGES; exit sudo vim /etc/mysql/my.cnf Remove the lines added in step 2 if you want to keep your security standards. sudo service mysql restart

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


Finally, this worked for me! I'm running MySQL 5.7.18 on Ubuntu 16.04. I tried dpkg-reconfigure, Anvesh's answer, Christian Mark's answer, and even the instructions on MySQL's help page.
I had to run UPDATE mysql.user SET authentication_string=PASSWORD('newpwd') WHERE User='root'; instead for line 5.
The only solution that worked for me. I have Ubuntu 17.04 and mysql 5.7
Sad this does not work for me mysql 5.7.22 ubuntu 18.04
Everything was working as expected. But when trying to login it's saying Enter password: ERROR 1698 (28000): Access denied for user 'root'@'localhost'
u
user12345

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


Works even when you lost the original MySQL root password – nice.
Works with Ver 14.14 Distrib 5.5.38, for debian-linux-gnu
Worked with mysql v 5.0 as well... sudo dpkg-reconfigure mysql-server-5.0
sudo dpkg-reconfigure mysql-server-5.7 not working for Ubuntu 18.04 for reset root password. Any other alternate?
Ubuntu: sudo dpkg-reconfigure mysql-server-5.7; sudo mysql_secure_installation
S
Siamak Motlagh

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='localhost';
EXIT;

Turn off MySQL.

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

Start the MySQL service normally.

sudo service mysql start

The other ways did not work for me but this one (thanks!), or you can also go here rricketts.com/reset-root-password-mysql-5-7-ubuntu-16-04-lts
Very useful ! In my specific case (Ubuntu 18.04 LTS), I had to replace the host '%' to 'localhost'
This didn't work in ubuntu 18.04. But it worked with this little change described here stackoverflow.com/a/53385753/1591143
Newer versions of MySQL do not support the PASSWORD function.
Worked perfectly
A
Anvesh

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;

Indeed. Also, it is closer to the official documentation. In case you run into this: "ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded”, take a look at stackoverflow.com/questions/37879448/…
I also had to change mysql.user.plugin from 'auth_socket' to 'mysql_native_password' for my root user with host of 'localhost'.
I had to use "sudo /etc/init.d/mysql stop" to stop the service and "UPDATE mysql.user SET authentication_string=PASSWORD('newpwd') WHERE User='root';" to update the password
authenticated _string helped
M
Masoud

At first run this command:

sudo mysql

and then you should check which authentication method of your MySQL user accounts use.So run this command

SELECT user,authentication_string,plugin,host FROM mysql.user;

now you can see something like this already :

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+

in the table that is in the above , you can see that all of your mysql users accounts status & if you have set a password for root account before you see mysql_native_password in plugin column instead auth_socket. All in all for change your root password you should run :

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

Be sure to change password to a strong password of your choosing. Then for reload your server to put your new changes into effect run this;

FLUSH PRIVILEGES;

So again check the authentication methods which has employed by your mysql , by this command:

SELECT user,authentication_string,plugin,host FROM mysql.user;

and now the output is :

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *3636DACC8616D997782ADD0839F92C1571D6D78F | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+

as you can see in the grant table your root account has mysql_native_password . now you can exit MYSQL shell

exit;

That's it.just you should restart mysql by sudo service mysql restart. Now you can login to mysql as a root account with your password easily.


I think this is from DigitalOcean, which was the only solution worked for me on Ubuntu 18.04 running on Virtual Box
This is the correct answer out of others top voted for Ubuntu OS (y)
This should have been the accepted answer
Works better than charm! It worked perfectly on Ubuntu.
J
Jerfeson Guerreiro

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

Try restart mysql-server before execution the comands

sudo service mysql restart

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;

Thanks, I'm also running Ubuntu 18.04 here and the MYSQL-SERVER >= 5.7 version was the only thing that actually solved the problem
This is the one that worked for in Ubuntu 18.04 mysql installation. MYSQL-SERVER >= 5.7 version to be precise.
UPDATE user SET password=PASSWORD('YOUR_PASSWORD') WHERE User='root'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('YOUR_PASSWORD') WHERE User='root'' at line 1
Thanks,I'm running Ubuntu 18.04 and the MYSQL-SERVER = 5.7, it works for me
L
Logan Murphy

Change the MySQL root password.

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

Login through mysql command line tool: mysql -uroot -poldpassword Run this command: SET PASSWORD FOR root@'localhost' = PASSWORD('newpassword');

or

Run this command, which sets a password for the current user ('root' for this case) : SET PASSWORD = PASSWORD('newpassword');


this is Quick way
R
Rahul Raveendran

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 &

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;

Turn off MySQL. sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown Start the MySQL service normally. sudo service mysql start


Hi, this answer works for me, but I had to use another command: UPDATE mysql.user SET authentication_string=PASSWORD('solutionclub3@*^G'), plugin='mysql_native_password' WHERE User='root'; without AND Host='%'. Thanks!
This also worked for me!
u
user2206324

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.


This is a much better way to do it. I was unable to run the previous commands because of some mysql error...
sudo dpkg-reconfigure mysql-server-5.7 on ubuntu 16.04
I ran sudo dpkg-reconfigure mysql-server-5.7 on Ubuntu 16.04, it didn't prompt to enter a new password.
If it says mysql-server is broken or not fully installed, one can use sudo apt purge mysql* and sudo apt install mysql-server
@user2206324 If I have data in databases, Can I use this sudo dpkg-reconfigure mysql-server-5.5 command ? it will just reconfigure root or data will be lost?
I
Imran Zahoor

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';
EXIT;

Turn off MySQL.

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

Start the MySQL service normally.

sudo service mysql start

k
ktscript

In my case this option helped : https://stackoverflow.com/a/49610152/13760371
Thank you, Rahul.

except for the following moment, when I try entered command:

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

the console issued a warning:

1681 'password' is deprecated and will be removed in a future release

cured with this command:

UPDATE mysql.user SET authentication_string=CONCAT('*', UPPER(SHA1(UNHEX(SHA1('NEWPASSWORD'))))), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';

MySQL version 5.7.X

My variant:

1. > sudo service mysql stop
2. > sudo mkdir /var/run/mysqld
3. > sudo chown mysql: /var/run/mysqld
4. > sudo mysqld_safe --skip-grant-tables --skip-networking &
5. > mysql -uroot mysql
6. > UPDATE mysql.user SET authentication_string=CONCAT('*', UPPER(SHA1(UNHEX(SHA1('NEWPASSWORD'))))), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';
7. > \q;
8. > sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown
9. > sudo service mysql start

Thanks, this worked for me for MySQL 8.0 on Ubuntu 20.04
Ok, Flimm. Happy to help.
This worked for me. but in first step mysql stop does not worked for me I have to use mysqladmin shutdown and steps 2 and 3 did not need for me because the file existed.
H
Huseyin

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 .


I couldn't login as sudo mysql -u root but I was able to do it as sudo mysql and run the ALTER USER command.
I was getting this error: "Your password does not satisfy the current policy requirements". I was able to correct this here: stackoverflow.com/a/43094873/9073437 . After setting a modified password policy, everything worked fine.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
j
juanitourquiza

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'; 

Gosh! Thanks a lot! This is the only way that was working in my case. Forget about that stupid secure installation script as it kept my freshly installed 5.7 wide open to users without ever asking for a password ... if Linux/Ubuntu is for safety-aware users why is it this hard to protect a crucial application like MySQL server?
Newer versions of MySQL don't support the PASSWORD function.
M
Masum Billah

For Ubuntu 18.04 and mysql version 14.14 Distrib 5.7.22 follow the below step to reset the mysql password.

Step 1

sudo systemctl stop mysql

Step 2

sudo systemctl edit mysql

This command will open a new file in the nano editor, which you'll use to edit MySQL's service overrides. These change the default service parameters for MySQL. This file will be empty, so add the following content:

[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking

Step 3

sudo systemctl daemon-reload
sudo systemctl start mysql

Step 4

sudo mysql -u root

Step 5

FLUSH PRIVILEGES;

Step 6

UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHEREuser = 'root';

Step 7

UPDATE mysql.user SET plugin ='mysql_native_password' WHERE user = 'root';

Step 8

sudo systemctl revert mysql

and finally

sudo systemctl restart mysql

Now enjoy


I think you missed content on step 2. It's incomplete.
Newer versions of MySQL don't support the PASSWORD function.
F
Franck Dernoncourt

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:

https://i.stack.imgur.com/1xtqQ.png


I wish I had started here instead of wasting an hour banging my head at trying to reset the password.
P
Peter Mutisya

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.


ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
m
mprivat

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;

This is a simplest way! It working for me with mysql 5.7.21-0ubuntu0.17.10.1
Newer versions of MySQL don't support the PASSWORD function.
E
Elie Asmar

Most of the answers on this topic are outdated; two major changes have occurred in MySQL up until the writing of this answer:

1- the 'Password' field in the user table has been replaced by 'authentication_string' column.

2- the 'Password' encryption function : PASSWORD("of some text") is deprecated.

Please refer to this link for further information:dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html


C
Community

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:

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). 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';") 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.


Newer versions of MySQL don't support the PASSWORD function.
a
alvaropaco

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

S
Shadow

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).


These instructions did not work for me with mysqld version 8.0.19 on Ubuntu 19.10. The mysqld socket is not created when I use the --skip-grant-tables option. If you are in the same situation, see my answer here stackoverflow.com/a/60386378/12559612
Thanks for your answer, a combination with an old answer works for me: sudo service mysql stop sudo mkdir /var/run/mysqld sudo chown mysql: /var/run/mysqld sudo mysqld_safe --skip-grant-tables --skip-networking & In another console: mysql -uroot mysql ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword'; systemctl restart mysql
U
Udara Seneviratne

If you know the 'root' users password, log in to mysql with that credentials. Then execute the following query to update the password.

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

N
Nayeem Azad

1.Open nano / vim to create a file with the following content and Save file as ~/mysql-pwd

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

Stop mysql sudo systemctl stop mysql Run sudo mysqld -init-file=~/mysql-pwd Restart mysql sudo systemctl start mysql Now login mysql -u root -p. password will be your NEWPASSWORD


j
joseph

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.


M
Mohd Arshil

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)

M
MSS

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

sudo dpkg-reconfigure mysql-server-5.6

I tried but its not working.. I am getting this bellow error.. could you please help? This installation of MySQL is already upgraded to 5.7.21, use --force if you still need to run mysql_upgrade
J
JGlass

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:

kill the current mysqld pid run mysqld with sudo /usr/sbin/mysqld & 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!


A
Amitesh Kumar

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


P
PyDevSRS

Change the MySQL root password. In Simpler way

All these commands should be run as root.

Login through MySQL command line tool using your old password:

Step-1

mysql -uroot -p"your_old_password"

Then run below command:

Step-2

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

Method-2 (First login using your old password using above command)

Run this command, which sets a password for the current user:

SET PASSWORD = PASSWORD('your_new_password');

Above command is for the current user. If you want to change the password for other user, you can put the user name instead of "root".