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
The "Access denied for user 'root'@'localhost'" error in MySQL typically indicates an incorrect password or insufficient privileges for the user. If you're using LangFlow, ensure that your credentials are correct, and consider resetting the password or adjusting user permissions to resolve the issue.
ReplyDelete