Access denied for user '@'localhost' code example

Example 1: access denied for user 'root'@'localhost' mariadb

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'unix_socket';
FLUSH PRIVILEGES;

Example 2: 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 3: access denied for user 'root'@'localhost'

mysql -u root -p

Tags:

Sql Example