MariaDB 使用 root 登入後、新增使用者出現 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 錯誤訊息

使用 cli 模式或是 phpmyadmin 新增使用者、都出現 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 錯誤訊息

MariaDB [(none)]> GRANT ALL PRIVILEGES ON . TO 'tt123'@'192.168.0.%' IDENTIFIED BY PASSWORD '*36F6ABC284EDCE8CBB4D071795AF023333785872';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

一開始照下面文章的方式處理、但是都無效
解決 MYSQL 登入時，ERROR 1045 (28000) using password: NO 的方法
myqsql 登入問題- iT 邦幫忙::一起幫忙解決難題，拯救IT 人的一天 - iThome

後來發現log裡面出現錯誤訊息，跑了分析以後出現
[root@tt0001 ~]# mysqlcheck -a -p mysql
Enter password:
mysql.column_stats Table is already up to date
mysql.columns_priv Table is already up to date
mysql.db Table is already up to date
mysql.event Table is already up to date
mysql.func Table is already up to date
mysql.gtid_slave_pos OK
mysql.help_category Table is already up to date
mysql.help_keyword Table is already up to date
mysql.help_relation Table is already up to date
mysql.help_topic Table is already up to date
mysql.host Table is already up to date
mysql.index_stats Table is already up to date
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist in engine
status : Operation failed
mysql.ndb_binlog_index Table is already up to date
mysql.plugin Table is already up to date
mysql.proc Table is already up to date
mysql.procs_priv Table is already up to date
mysql.proxies_priv Table is already up to date
mysql.roles_mapping Table is already up to date
mysql.servers Table is already up to date
mysql.table_stats Table is already up to date
mysql.tables_priv Table is already up to date
mysql.time_zone Table is already up to date
mysql.time_zone_leap_second Table is already up to date
mysql.time_zone_name Table is already up to date
mysql.time_zone_transition Table is already up to date
mysql.time_zone_transition_type Table is already up to date
mysql.user Table is already up to date

但是去 ls /var/lib/mysql/mysql 又有出現檔案
最後按照這篇操作 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
先 drop 後再手動把檔案移開，最後跑 mysql_upgrade 就好了

[root@tt0001 tools]# mysql_upgrade -p
Enter password:
MySQL upgrade detected
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist in engine
status : Operation failed
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK

Repairing tables
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist in engine
status : Operation failed
Upgrading from a version before MariaDB-10.1
Phase 2/7: Installing used storage engines
Checking for tables with unknown storage engine
Phase 3/7: Fixing views from mysql
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1932 (42S02) at line 586: Table 'mysql.innodb_index_stats' doesn't exist in engine
ERROR 1932 (42S02) at line 590: Table 'mysql.innodb_table_stats' doesn't exist in engine
ERROR 1932 (42S02) at line 593: Table 'mysql.innodb_table_stats' doesn't exist in engine
FATAL ERROR: Upgrade failed
[root@tw01 tools]# mysql_upgrade -p
Enter password:
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1813 (HY000) at line 64: Tablespace for table '`mysql`.`innodb_table_stats`' exists. Please DISCARD the tablespace before IMPORT.
ERROR 1813 (HY000) at line 68: Tablespace for table '`mysql`.`innodb_index_stats`' exists. Please DISCARD the tablespace before IMPORT.
ERROR 1146 (42S02) at line 586: Table 'mysql.innodb_index_stats' doesn't exist
ERROR 1146 (42S02) at line 590: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1146 (42S02) at line 593: Table 'mysql.innodb_table_stats' doesn't exist
FATAL ERROR: Upgrade failed
[root@tw01 tools]#
[root@tw01 tools]# mysql_upgrade -p
Enter password:
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK