ERROR 3009 (HY000): Column count of mysql.user is wrong…..
2018-06-19 13:45 潇湘隐者 阅读(7195) 评论(2) 编辑 收藏 举报在测试备份还原时,使用XtraBackup还原数据库后,创建一个测试账号时遇到了下面错误:
mysql> grant all on house.* to test@'192.168.%' identified by 'test1249';
ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found 43. Created with MySQL 50620, now running 50721. Please use mysql_upgrade to fix this error.
mysql>
因为备份的数据库版本为MySQL 5.6.20,目标数据库的MySQL版本为5.7.21,因为两个数据库版本不一致,在还原过后,忘记升级数据库(还原后没有使用mysql_upgrade升级数据结构)。其实从错误信息也能看出一二,需要执行mysql_upgrade命令会检查所有数据库中的所有表与当前版本的MySQL服务器是否不兼容。 mysql_upgrade还会升级系统表,以便你可以使用一些新特性或功能。
# mysql_upgrade -u root -p
Enter password:
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.db_database_info OK
mysql.db_instance_info OK
mysql.db_server_info OK
mysql.engine_cost OK
......................................................
......................................................
Upgrade process completed successfully.
Checking if update is needed.
关于mysql_upgrade的官方介绍如下,具体细节参考“4.4.7 mysql_upgrade — Check and Upgrade MySQL Tables”:
mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL Server. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.
If mysql_upgrade finds that a table has a possible incompatibility, it performs a table check and, if problems are found, attempts a table repair. If the table cannot be repaired, see Section 2.11.3, “Rebuilding or Repairing Tables or Indexes” for manual table repair strategies.
You should execute mysql_upgrade each time you upgrade MySQL.
参考资料:
https://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2016-06-19 ORACLE关于索引是否需要定期重建争论的整理
2014-06-19 ORA-02429: cannot drop index used for enforcement of unique /primary key
2014-06-19 ORA-06502:PL/SQL :numberic or value error: character string buffer too small
2013-06-19 TreeSize工具介绍