sql access denied for user 'root'@'localhost' code example

Example 1: mysql root access denied

## ADD user in mysql && have problem with root user ##

# First Login mysql shell..
sudo mysql -u root -p 

## Also check if you are unable to login in mysql without sudo...

## Creating new user and giving permissions...
# Goto login shell of mysql... and type below commands...

#1 show databases;
#2 use mysql;
#3 select user, host, plugin from mysql.user;
#4 create user 'your_user_name'@'localhost' identified by 'password';
#5 grant all privileges on *.* to 'your_user_name'@'localhost';
#6 update user set plugin="caching_sha2_password" where User="your_user_name";
	#     .... Here caching_sha2_password you can see yours in #3 check 
	#		your table and see column (plugin) don't use root plugin...
#7 flush privileges;
#8 EXIT

#### DONE Now you created your user also you can user without sudo...

mysql -u your_user_name -p 
## For password check your step #4 and enter same password...

## Enjoy ALL DONE....
## HAVE A NICE DAY!

Example 2: access denied for user 'root'@'localhost' python sql-connect error

SELECT user,authentication_string,plugin,host FROM mysql.user;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';
FLUSH PRIVILEGES;

Example 3: Access denied for user 'root'@'localhost' (using password: YES)

sudo /etc/init.d/mysql stop

Now start up MySQL in safe mode, so you’ll skip the privileges table:

sudo mysqld_safe --skip-grant-tables &
    // if you face issue,[mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists] run below commands] start
        mkdir -p /var/run/mysqld
        chown mysql:mysql /var/run/mysqld
    // end


Login with root:
mysql -uroot


And assign the DB that needs to be used:
use mysql;

SHOW VARIABLES LIKE 'sql_mode';
SET sql_mode = '';

SET PASSWORD FOR root = 'Dehr@dun@345';	// it is working
    update user set password=PASSWORD("YOURPASSWORDHERE") where User='root';
        [On MySql 5.7 version you must replace query to:]
         update user set authentication_string=PASSWORD("YOURPASSWORDHERE") where user="root"; 


flush privileges;

quit

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

Now your root password should be working with the one you just set, check it with:
mysql -u root -p