1#First Login with administrative account (Even root itself)
2
3mysql> use mysql;
4mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD("NewPassword");
5mysql> flush privileges;
6
7#Now quit and login
8mysql> quit
9
10mysql -u root -p
11#Click enter and It will prompt you to enter password
12#Just to be safe you should also still try to log in without entering a password
1-- In case the UPDATE command returns "Column 'Password' is not updatable" run
2ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPassword';
3flush privileges;
1$ sudo mysql -u root -p
2Enter password: (enter your root password)
3
4mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';
5mysql> FLUSH PRIVILEGES;
6mysql> exit;
7
8then to confirm
9
10mysql -u root -p
11Enter password: ********
12
13Success!!!
14
1use mysql;
2
3update user set authentication_string=PASSWORD("mynewpassword") where User='root';
4
5flush privileges;
6
7quit
8
1$ sudo cat /etc/mysql/debian.cnf
2Note the lines which read:
3
4user = debian-sys-maint
5password = blahblahblah
6Then:
7
8$ mysql -u debian-sys-maint -p
9Enter password: // type 'blahblahblah', ie. password from debian.cnf
10
11mysql> USE mysql
12mysql> SELECT User, Host, plugin FROM mysql.user;
13+------------------+-----------+-----------------------+
14| User | Host | plugin |
15+------------------+-----------+-----------------------+
16| root | localhost | auth_socket |
17| mysql.session | localhost | mysql_native_password |
18| mysql.sys | localhost | mysql_native_password |
19| debian-sys-maint | localhost | mysql_native_password |
20+------------------+-----------+-----------------------+
214 rows in set (0.00 sec)
22
23mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
24mysql> COMMIT; // When you don't have auto-commit switched on
25Either:
26
27mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
28Or:
29
30// For MySQL 5.7+
31UPDATE mysql.user SET authentication_string=PASSWORD('new_password') where user='root';
32Then:
33
34mysql> FLUSH PRIVILEGES;
35mysql> COMMIT; // When you don't have auto-commit switched on
36mysql> EXIT
37
38$ sudo service mysql restart
39$ mysql -u root -p
40Enter password: // Yay! 'new_password' now works!