mysql 3009_MySQL 常见错误 ERROR 3009

(点击上方公众号，可快速关注)

来源：潇湘隐者 ，

www.cnblogs.com/kerrycode/p/9198225.html

在测试备份还原时，使用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

【关于投稿】

如果大家有原创好文投稿，请直接给公号发送留言。

① 留言格式：【投稿】+《 文章标题》+ 文章链接② 示例：【投稿】《不要自称是程序员，我十多年的 IT 职场总结》：http://blog.jobbole.com/94148/③ 最后请附上您的个人简介哈~

看完本文有收获？请转发分享给更多人

关注「ImportNew」，提升Java技能