36.1. Upgrading the Privilege Tables

MySQL Server controls client access by using the contents of the grant tables in the mysql database. The grant tables list accounts for clients that are allowed to connect to the server. They also enumerate the privileges that each account holds and that determine what operations it can perform, such as creating tables or updating records.

As MySQL development proceeds, it sometimes occurs that new privileges are implemented to go along with new features. For example, in MySQL 5, new features include stored routines and views, each of which is accompanied by privileges that enable clients to use them. The CREATE ROUTINE and CREATE VIEW privileges enable object creation, and other privileges control access to the created objects.

To determine whether a new release of MySQL includes new privileges, check the upgrade notes in the installation chapter of the MySQL Reference Manual. When you upgrade MySQL to a version that implements additional privileges compared to your current version, it is not the case that those privileges become part of your existing grant tables automatically. To upgrade your grant tables to the new structure, you should run the mysql_fix_privilege_tables program:

1.	Make a backup of your mysql database: shell> mysqldump mysql > mysql.sql When you run mysqldump, you'll need to connect to the MySQL server as root or as some other MySQL user that has access to the mysql database.
2.	The procedure for running mysql_fix_privilege_tables differs for Unix and Windows. On Unix, run it like this, where root_password is the MySQL root password: shell> mysql_fix_privilege_tables --password=root_password On Windows, change location to the MySQL installation directory and run the following commands, where root_password is the MySQL root password: shell> bin\mysql -u root -p mysql Enter password: root_password mysql> SOURCE scripts/mysql_fix_privilege_tables.sql When you run mysql_fix_privilege_tables, Duplicate column name errors might occur and can be ignored.
3.	After upgrading the grant tables, stop the server and restart it.
4.	Consider whether any of your MySQL accounts should be given the new privileges. If so, issue the appropriate GRANT statements for those accounts.