How to quickly upgrade MySQL system tables

Some MySQL releases have changed the structure of the system table in the mysql database and added new permissions or features. When you update to the new MySQL version, you should update the system tables at the same time 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 systems, run the mysql_fix_privilege_tables script to update the system table:

shell> mysql_fix_privilege_tables

You must execute this script when the server is running. It tries to connect to the server running with root on the local machine. If the root account requires a password, use the following method in the command line:

shell> mysql_fix_privilege_tables--password=root_password

The mysql_fix_privilege_tables script can convert the system table to the current format. You may see some Duplicate column name warnings at runtime; you can ignore them.

After the script is run, stop the server and restart it.

In Windows, MySQL distribution includes mysql_fix_privilege_tables. SQL scripts, which can be run on the mysql client. For example, if MySQL is installed in C: Program FilesMySQLMySQL Server 5.1, the command should be:

C:> C:Program FilesMySQLMySQL Server 5.1binmysql -u root -p mysqlmysql> SOURCE C:/Program Files/MySQL/MySQL Server 5.1/scripts/mysql_fix_privilege_tables.sql

If it is installed in another directory, change the path name accordingly.

The mysql command prompts you to enter the root password. Follow the prompts to enter the password.

In Unix, when mysql processes statements in mysql_fix_privilege_tables. SQL script, you may see Duplicate column name warnings. You can ignore them.

After the script is run, stop the server and restart it.

Note: For more exciting tutorials, please pay attention to the graphic tutorial channel of the customer's house,