1sudo mysql
2
3ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
4
1UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'unix_socket';
2FLUSH PRIVILEGES;
1UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'unix_socket';
2FLUSH PRIVILEGES;
3
1## ADD user in mysql && have problem with root user ##
2
3# First Login mysql shell..
4sudo mysql -u root -p
5
6## Also check if you are unable to login in mysql without sudo...
7
8## Creating new user and giving permissions...
9# Goto login shell of mysql... and type below commands...
10
11#1 show databases;
12#2 use mysql;
13#3 select user, host, plugin from mysql.user;
14#4 create user 'your_user_name'@'localhost' identified by 'password';
15#5 grant all privileges on *.* to 'your_user_name'@'localhost';
16#6 update user set plugin="caching_sha2_password" where User="your_user_name";
17 # .... Here caching_sha2_password you can see yours in #3 check
18 # your table and see column (plugin) don't use root plugin...
19#7 flush privileges;
20#8 EXIT
21
22#### DONE Now you created your user also you can user without sudo...
23
24mysql -u your_user_name -p
25## For password check your step #4 and enter same password...
26
27## Enjoy ALL DONE....
28## HAVE A NICE DAY!
1sudo /etc/init.d/mysql stop
2
3Now start up MySQL in safe mode, so you’ll skip the privileges table:
4
5sudo mysqld_safe --skip-grant-tables &
6 // if you face issue,[mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists] run below commands] start
7 mkdir -p /var/run/mysqld
8 chown mysql:mysql /var/run/mysqld
9 // end
10
11
12Login with root:
13mysql -uroot
14
15
16And assign the DB that needs to be used:
17use mysql;
18
19SHOW VARIABLES LIKE 'sql_mode';
20SET sql_mode = '';
21
22SET PASSWORD FOR root = 'Dehr@dun@345'; // it is working
23 update user set password=PASSWORD("YOURPASSWORDHERE") where User='root';
24 [On MySql 5.7 version you must replace query to:]
25 update user set authentication_string=PASSWORD("YOURPASSWORDHERE") where user="root";
26
27
28flush privileges;
29
30quit
31
32sudo /etc/init.d/mysql stop
33sudo /etc/init.d/mysql start
34
35Now your root password should be working with the one you just set, check it with:
36mysql -u root -p
37
1ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'insert_password';