Analysis and Solution for MySQL Root User Privilege Upgrade Issues

Problem Background and Phenomenon Analysis

In MySQL database management, users frequently encounter root user privilege anomalies, particularly after version upgrades. This paper analyzes the root causes and solutions for MySQL 5.5 root user privilege upgrade issues based on actual case studies.

Privilege Verification Process

First, it's essential to confirm the current user's identity and privilege status. The following command sequence provides comprehensive privilege verification:

mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'root'@'localhost';

In a normal system, the SHOW GRANTS command should display "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION", but in problematic systems, the privilege list is expanded in detail, indicating privilege system abnormalities.

Table Structure Change Analysis

MySQL version 5.5 introduced significant privilege table structure changes, adding critical columns:

mysql> SELECT * FROM mysql.user WHERE User='root' and Host='localhost'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: 
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y <----------------------------- New column in 5.5
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: <------------------------------- New column in 5.5
 authentication_string: <------------------------------- New column in 5.5
1 row in set (0.00 sec)

The newly added Create_tablespace_priv, plugin, and authentication_string columns are important features of MySQL 5.5. If these columns are missing during the upgrade process, it will cause privilege system anomalies.

Root Cause Analysis

The fundamental cause of privilege issues lies in incomplete privilege table structure updates during version upgrades. When upgrading from older MySQL versions to 5.5, if the mysql_upgrade command is not properly executed, system table structures are not automatically updated, resulting in:

• Missing new columns in privilege tables
• Absence of new system tables (such as mysql.proxies_user)
• Abnormal privilege verification logic

This structural mismatch causes GRANT ALL PRIVILEGES commands to fail, even though the root user appears to have all privileges.

Solution: mysql_upgrade Tool

mysql_upgrade is the official MySQL upgrade tool specifically designed for system table updates after version upgrades:

C:\MySQL Server 5.5\bin> mysql_upgrade

This tool performs the following critical operations:

• Checks all system table structures
• Adds missing columns and tables
• Updates table engines and character sets
• Repairs potential data inconsistencies

Complete Repair Procedure

Complete repair procedure for MySQL 5.5 privilege issues:

1. Stop MySQL Service: Ensure complete database service shutdown
2. Data Backup: Perform complete backup of existing databases
3. Execute mysql_upgrade: Run mysql_upgrade command in MySQL installation bin directory
4. Restart MySQL Service: Activate system table changes
5. Verify Repair Results: Recheck privilege status

Verification of Repair Effectiveness

After repair completion, re-verify privilege status:

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Preventive Measures and Best Practices

To avoid similar issues, follow these best practices:

• Carefully read official upgrade documentation before version upgrades
• Strictly follow officially recommended upgrade procedures
• Execute mysql_upgrade command immediately after upgrade
• Regularly check system table structure integrity
• Establish comprehensive backup and recovery mechanisms

Conclusion

MySQL privilege upgrade issues represent typical version compatibility problems that can be effectively resolved through proper use of the mysql_upgrade tool. Database administrators should fully understand the complete MySQL version upgrade process, establish standardized upgrade operation procedures, and ensure database system stability and security.