How to Change the MySQL Root Password

MySQL root password change

I found it! I forgot to hash the password when I changed it. I used this query to solve my problem:

update user set password=PASSWORD('NEW PASSWORD') where user='root';

I forgot the PASSWORD('NEW PASSWORD') and just put in the new password in plain text.

How to reset the root password in MySQL 8.0.11?

as here says:

This function was removed in MySQL 8.0.11

1.if you in skip-grant-tables mode

in mysqld_safe:

UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;

and then, in terminal:

mysql -u root

in mysql:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

2.not in skip-grant-tables mode

just in mysql:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

Reset mysql root password without mysql client access

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

B.5.3.2.3 Resetting the Root Password: Generic Instructions

1. 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, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.

-or-

https://www.codero.com/knowledge-base/content/33/296/en/how-to-reset-your-root-mysql-password.html

$  mysqld --skip-grant-tables --skip-networking &
$  mysql -u root

 ...change your password
 ...reset mysql without --skip-grant-tables and --skip-networking

UPDATE

It likely the mysqld cannot run as root or your system account. See here.

$ mysqld --skip-grant-tables --skip-networking --user=root &

or you can revise the my.cnf file directly after stop the mysql server. I recommend the way.

[mysqld]
...
# add the following two settings to configure variables for reseting password.
skip-grant-tables
skip-networking

After restart mysqld. Using the mysql cli client execute the following sql statement to change your password.

UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPass')
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

Finally, stop mysqld, and rollback your my.cnf, then restart mysqld.

Change MySQL Root Password in XAMPP

Open C:\xampp\phpMyAdmin folder, and then open config.inc.php:

And then, change the line like this:

$cfg['Servers'][$i]['auth_type'] = 'config'; //change this to cookie from config
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';
$cfg['Servers'][$i]['extension'] = 'mysqli';

To:

$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';
$cfg['Servers'][$i]['extension'] = 'mysqli';

And login with blank password, and go to SQL tab, and run this:

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

---

Related Topics