Error: Access denied for user 'root'@'localhost' (using password: YES) code example

Example 1: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

# Stop Your Server First
sudo service mysql stop

# Make MySQL service directory.
sudo mkdir /var/run/mysqld

# Give MySQL permission to work with the created directory
sudo chown mysql: /var/run/mysqld

# Start MySQL, without permission and network checking
sudo mysqld_safe --skip-grant-tables --skip-networking &

# Log in to your server without any password.
mysql -u root mysql


# Update the password for the root user:
UPDATE mysql.user SET authentication_string=PASSWORD('YourNewPasswordBuddy'), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';

#if you omit (AND Host='localhost') section, it updates the root pass regardless of its host

FLUSH PRIVILEGES;
EXIT;

#kill mysqld_safe process
sudo service mysql restart

#Now you can use your new password to login to your Server
mysql -u root -p

#take note for remote access you should create a remote user and then grant all privileges to that remote user

Example 2: mysql access denied for user 'root'@'localhost'

sudo mysql

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

Example 3: 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 4: 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

Example 5: access denied for user 'root'@'localhost'

mysql -u root -p

Example 6: access denied for user 'root'@'localhost' (using password no) stackoverflow

-skip-grant-tables

Tags:

Misc Example