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
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
Comments
Post a Comment