MySQL执行show databases/show tables报错1449：mysql.infoschema@localhost用户缺失问题求助

Hey there, let's tackle this MySQL error step by step. The issue you're facing centers on the missing mysql.infoschema system user—this account is critical for accessing MySQL's information schema, and its absence breaks core commands like SHOW DATABASES; or SHOW TABLES;. Here's how to resolve it:

First, shut down your running MySQL instance to make changes safely:

sudo systemctl stop mysql

We need to launch MySQL without grant table checks to access the system tables directly:

sudo mysqld_safe --skip-grant-tables --skip-networking &

The --skip-networking flag ensures no external connections can access the database while we're fixing things.

Connect to MySQL using the root user (no password required in safe mode):

mysql -u root

Once in the MySQL shell, switch to the mysql system database:

USE mysql;

Now recreate the mysql.infoschema user with the correct permissions (this is a locked system account that shouldn't be used for logins):

CREATE USER 'mysql.infoschema'@'localhost' IDENTIFIED BY '';
GRANT SELECT ON *.* TO 'mysql.infoschema'@'localhost';
ALTER USER 'mysql.infoschema'@'localhost' ACCOUNT LOCK;

The error about MyISAM not supporting system tables hints at a misconfiguration. MySQL 8.0 requires all system tables to use InnoDB. Verify the mysql.user table's engine:

SHOW CREATE TABLE user;

Look for ENGINE=InnoDB in the output. If it's using MyISAM, convert it with:

ALTER TABLE user ENGINE=InnoDB;

Repeat this for any other system tables (like db, tables_priv) that might still use MyISAM.

Since mysql_upgrade is deprecated in MySQL 8.0.16+, use the supported upgrade command to ensure all system tables are up to date:

ALTER INSTANCE UPGRADE DIRECTORY NAME;

Exit the MySQL shell, stop the safe mode instance, and restart MySQL normally:

EXIT;

sudo mysqladmin shutdown
sudo systemctl start mysql

Test the commands that were failing to confirm the issue is resolved:

mysql -u root -e "SHOW DATABASES;"
mysql -u root -e "SHOW TABLES;"

If problems persist, check the MySQL error log for clues about failed system updates (unattended Ubuntu upgrades often trigger these issues):

sudo tail -n 50 /var/log/mysql/error.log

内容的提问来源于stack exchange，提问作者m0chan