MySQL - Access denied for user 'root'@'localhost'

Troubleshoot Access denied for user 'root'@'localhost':

You might have faced Access denied for user root error message and observer following errors in MySQL error log

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[ERROR] [FATAL] InnoDB: Tablespace id is 585 in the data dictionary but in file ./mysql/engine_cost.ibd it is 20!
[ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure
[ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure
[ERROR] Native table 'performance_schema'.'replication_connection_status' has the wrong structure
Current locks:
lock: 0x1f45ae0:
lock: 0x1f45a00:
lock: 0x1f45920:

Solution:
   
01. Shutdown MySQL Daemon, and backup DATA_DIR
02. Create a new mysql instance in a temporary directory
    #mkdir /tmp/mysqltmp
    #mysqld --no-defaults --datadir=/tmp/mysqltmp --initialize
    Rename existing mysql directory and performance_schema
    Copy the mysql schema directory from the temporary directory to the production directory
    Copy the performance_schema from the temporary directory to the production directory
03. Execute mysql_upgrade --force
    #mysql_upgrade --force
    To make sure they are recreated
   
Edit /etc/my.cnf file:
Include following line in the /etc/my.cnf file so that you can connect MySQl instance
--skip-grant-tables

Start MySQl instance:
#systemctl start mysqld

Switch to user mysql

$mysql -uroot -p -S/mysql/data/mysql.sock
mysql> FLUSH PRIVILEGES;

Create the root user with root privileges:
mysql> SET sql_mode='';
mysql> CREATE USER root@localhost IDENTIFIED BY '<root password here>';
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost WITH GRANT OPTION;
mysql> /*!50500 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION; */  
mysql> exit;

Re-create all other users and grant appropriate privileges

Edit /etc/my.cnf and comment line --skip-grant-tables

Re-start MySQl instance:
#systemctl restart mysqld

#systemctl restart mysqld