MySQL升级（5.0->5.5）时，遇到一个故障

MySQL升级（5.0->5.5）时，遇到一个故障
分类： Mysql/postgreSQL2011-05-11 21:09:57
启动MySQL，观察错误日志
110511 20:38:49 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50077, now running 50511. Please use mysql_upgrade to fix this error.

110511 20:38:49 [ERROR] mysql.user has no Event_priv column at position 29

110511 20:38:49 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

110511 20:38:49 [Note] /usr/sbin/mysqld: ready for connections.

Version: ‘5.5.11’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Community Server (GPL)

日志提示：可以执行mysql_upgrade命令，进行修复。
查找资料
[root@ora01 ~]# man mysql_upgrade

MYSQL_UPGRADE(1) MySQL Database System MYSQL_UPGRADE(1)

NAME

   mysql_upgrade - check tables for MySQL upgrade#检查表，进行数据库升级

SYNOPSIS

   mysql_upgrade [options]

DESCRIPTION

   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.#检查所有库的所有表是否与当前运行的服务器版本一致。也可以用来升级系统表，赋予新的权限和功能

   mysql_upgrade should be executed each time you upgrade MySQL. It supersedes the older mysql_fix_privilege_tables script, which has been removed in MySQL 5.5.#在每次升级MySQL后，执行。

   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.4, “Rebuilding or Repairing Tables or Indexes” for manual table repair strategies.#如果该工具找到不兼容的表，会执行检查表，如果问题被找到，会试图修复这个表。如果不能修复，则需要重建表或索引。

Note

On Windows Server 2008, Vista, and newer, you must run mysql_upgrade with administrator privileges. You can do this by running a Command Prompt as Administrator and running the command. Failure to do so may result in the upgrade failing to execute correctly.

Caution

You should always back up your current MySQL installation before performing an upgrade. See Section 6.2, “Database Backup Methods”.

Some upgrade incompatibilities may require special handling before you upgrade your MySQL installation and run mysql_upgrade. See Section 2.11.1, “Upgrading MySQL”, for instructions on determining whether any such incompatibilities apply to your installation and how to handle them.

To use mysql_upgrade, make sure that the server is running, and then invoke it like this:

shell> mysql_upgrade [options]

After running mysql_upgrade, stop the server and restart it so that any changes made to the system tables take effect.

mysql_upgrade executes the following commands to check and repair tables and to upgrade the system tables:

mysqlcheck --all-databases --check-upgrade --auto-repair

mysql < fix_priv_tables

mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names

在命令行下执行
[root@ora01 ~]# mysql_upgrade

Looking for ‘mysql’ as: mysql

Looking for ‘mysqlcheck’ as: mysqlcheck

Running 'mysqlcheck with default connection arguments

Running 'mysqlcheck with default connection arguments

mysql.columns_priv OK

mysql.db OK

mysql.event OK

mysql.func OK

mysql.general_log OK

mysql.help_category

error : Table upgrade required. Please do "REPAIR TABLE help_category" or dump/reload to fix it!

mysql.help_keyword

error : Table upgrade required. Please do "REPAIR TABLE help_keyword" or dump/reload to fix it!

……………………

进入MySQL，执行
mysql> REPAIR TABLE mysql.help_category;

±--------------------±-------±---------±---------+

| Table | Op | Msg_type | Msg_text |

±--------------------±-------±---------±---------+

| mysql.help_category | repair | status | OK |

±--------------------±-------±---------±---------+

1 row in set (0.00 sec)

mysql> REPAIR TABLE mysql.help_keyword;

±-------------------±-------±---------±---------+

| Table | Op | Msg_type | Msg_text |

±-------------------±-------±---------±---------+

| mysql.help_keyword | repair | status | OK |

±-------------------±-------±---------±---------+

1 row in set (0.00 sec)

……………………

再次运行（强制运行）
[root@ora01 ~]# mysql_upgrade --force

Looking for ‘mysql’ as: mysql

Looking for ‘mysqlcheck’ as: mysqlcheck

Running 'mysqlcheck with default connection arguments

Running 'mysqlcheck with default connection arguments

mysql.columns_priv OK

mysql.db OK

mysql.event OK

mysql.func OK

mysql.general_log OK

mysql.help_category OK

mysql.help_keyword OK

mysql.help_relation OK

……………………

总结
重启MySQL后，日志不再报错。

出现此故障的原因是由于，MySQL5.0的mysql库下的help_category、help_keyword等几个表，与MySQL5.5不兼容。