Quickly master upgrade MySQL system table

Some MySQL publications have made changes to the structure of the system tables in the MySQL database, adding new permissions or attributes. When you update to the new version of MySQL, you should also update the system tables to ensure that their structure is up to date. First back up the MySQL database, and then follow the procedure below.

In Unix or Unix class systems, run the Mysql_fix_privilege_tables script to update the system tables:

shell> mysql_fix_privilege_tables

You must execute the script while the server is running. It attempts to connect to a server running on this computer with root. If the root account requires a password, give the password at the command line as follows:

shell> mysql_fix_privilege_tables--password=root_password

The Mysql_fix_privilege_tables script can perform any action that converts a system table to the current format. You may see some duplicate column name warnings at run time, and you can ignore them.

After you run the script, stop the server and reboot.

In Windows systems, MySQL distribution includes mysql_fix_privilege_tables.sql SQL scripts that you can run with MySQL clients. For example, if MySQL is installed to C:\Program files\mysql\mysql Server 5.1, the command should be:

C:\>
C:\Program Files\MySQL\MySQL Server 5.1
\bin\mysql -u root -p mysql
mysql> SOURCE C:/Program Files/MySQL/MySQL Server
5.1/scripts/mysql_fix_privilege_tables.sql

If you install to a different directory, change the path name accordingly.

The MySQL command prompts for the root password and enters the password as prompted.

In Unix, you may see some duplicate column name warnings when MySQL processes the statements in Mysql_fix_privilege_tables.sql script scripts; you can ignore them.

After you run the script, stop the server and reboot.