MySQL5.7.11 授权报错一例

2019独角兽企业重金招聘Python工程师标准>>>

前言：文中涉及公司信息的部分经过处理

问题发现：今天在做一个非核心库备份的时候发现mysql下创建账号授权报错（新接手的服务器，刚开始的时候是找不到sock文件，服务器里面有两个mysql实例），如下，纳尼？

mysql> GRANT xxxx  ON *.* TO 'xx'@'xxxxx' identified by 'password';
ERROR 1524 (HY000): Plugin 'xxxxxxxxxxxx' is not loaded

 

我的怀疑：刚开始怀疑可能是MySQL迁移升级过，但是权限表没有升级，和小伙伴沟通后确定迁移过，版本也是不一样的，但是不记得之前是怎么搞得了，哇擦。求证一下吧，看一下mysql的错误日志，发现有如下大量错误（重启的时候报的）

......
......
......
3277 2017-06-02T15:31:30.239542+08:00 0 [ERROR] Native table 'performance_schema'.'replication_connection_configuration' has the wrong structure
3278 2017-06-02T15:31:30.239558+08:00 0 [ERROR] Native table 'performance_schema'.'replication_group_members' has the wrong structure
3279 2017-06-02T15:31:30.239576+08:00 0 [ERROR] Native table 'performance_schema'.'replication_connection_status' has the wrong structure
3280 2017-06-02T15:31:30.239592+08:00 0 [ERROR] Native table 'performance_schema'.'replication_applier_configuration' has the wrong structure
3281 2017-06-02T15:31:30.239609+08:00 0 [ERROR] Native table 'performance_schema'.'replication_applier_status' has the wrong structure
3282 2017-06-02T15:31:30.239626+08:00 0 [ERROR] Native table 'performance_schema'.'replication_applier_status_by_coordinator' has the wrong structure
3283 2017-06-02T15:31:30.239643+08:00 0 [ERROR] Native table 'performance_schema'.'replication_applier_status_by_worker' has the wrong structure
3284 2017-06-02T15:31:30.239659+08:00 0 [ERROR] Native table 'performance_schema'.'replication_group_member_stats' has the wrong structure
3285 2017-06-02T15:31:30.239675+08:00 0 [ERROR] Native table 'performance_schema'.'prepared_statements_instances' has the wrong structure
3286 2017-06-02T15:31:30.239693+08:00 0 [ERROR] Native table 'performance_schema'.'user_variables_by_thread' has the wrong structure
3287 2017-06-02T15:31:30.239708+08:00 0 [ERROR] Native table 'performance_schema'.'status_by_account' has the wrong structure
......
......
......
242 2017-06-02T15:31:30.211492+08:00 0 [ERROR] Column count of performance_schema.threads is wrong. Expected 17, found 14. Created with MySQL 50623, now running 50711. Please use mysql_upgra     de to fix this error.
3243 2017-06-02T15:31:30.211604+08:00 0 [ERROR] Column count of performance_schema.events_stages_current is wrong. Expected 12, found 10. Created with MySQL 50623, now running 50711. Please u     se mysql_upgrade to fix this error.
3244 2017-06-02T15:31:30.211936+08:00 0 [ERROR] Column count of performance_schema.events_stages_history is wrong. Expected 12, found 10. Created with MySQL 50623, now running 50711. Please u     se mysql_upgrade to fix this error.
......
......
.....,

 

修复过程：（如果不放心，可以把数据全备再操作，这里我就不备份了）

zzzz@xxxx:~# mysql_upgrade -uroot -p'xxxx'
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
......
......
......
zzzz@xxxx:~# systemctl restart mysqld.service
zzzz@xxxx:~# mysql -uroot -p'xxxx'
mysql> alter user user() identified by 'xxxx';
Query OK, 0 rows affected (0.04 sec)
mysql> drop user xxxx@'xxxx';
Query OK, 0 rows affected (0.04 sec)
mysql> GRANT xxx ON *.* TO 'xxxx'@'xxxx' identified by 'xxxx';
Query OK, 0 rows affected, 1 warning (0.02 sec)

至此问题修复，可以正常授权，可以正常登陆。

 

为了方便大家交流，本人开通了微信公众号，和QQ群291519319。喜欢技术的一起来交流吧

转载于:https://my.oschina.net/u/3023401/blog/913475