未做mysqldump执行mysql_upgrade的风险及升级后未执行该命令的问题求助

Hey there, let's tackle your two questions one by one—first the risks of running mysql_upgrade without a mysqldump backup, then a step-by-step fix for your post-upgrade issues that minimizes impact on your 10GB business database.

Skipping that pre-upgrade backup exposes you to several critical issues:

• Corrupted system metadata: mysql_upgrade rewrites the table structures of system databases like mysql, sys, and performance_schema to match the new MySQL version. If the upgrade hits a glitch (like a service crash or compatibility quirk), these system tables can get corrupted. Without a backup, restoring core functionality (like user permissions or performance monitoring) becomes incredibly hard.
• Indirect business data damage: While mysql_upgrade targets system databases, a failed upgrade can cause MySQL to terminate unexpectedly. This can leave transaction logs in an inconsistent state or corrupt table caches—worst case, this might spill over to your business tables. No backup means you’re stuck trying to repair corrupted data instead of rolling back to a known good state.
• No easy rollback: If you discover post-upgrade issues (like broken custom functions or permission rule changes), you can’t quickly revert to the pre-upgrade setup. You’ll have to troubleshoot blindly, extending downtime for your application.

Your problem stems from missing/corrupted system databases (since mysql_upgrade was never run) and broken root permissions. The goal here is to rebuild the system databases without touching your business data. Here’s how:

1. First, Securely Backup Your Business Database #

Before doing anything else, lock in your business data to eliminate risk:

• Stop the MySQL service: systemctl stop mysqld (adjust to service mysqld stop if you’re on an older distro)
• Copy your business database folder (usually located in /var/lib/mysql/) to a safe backup location:

  cp -r /var/lib/mysql/your_business_db_name /path/to/secure/backup/
  

• If MySQL is still operational, you can also run a hot backup with mysqldump for extra safety:

  mysqldump -u root -p your_business_db_name > /path/to/backup/your_business_db.sql
  

• Start MySQL back up if you stopped it: systemctl start mysqld

2. Rebuild the System Databases #

Since your mysql, sys, and performance_schema are damaged, we’ll use MySQL’s built-in tool to recreate them:

• Stop MySQL again: systemctl stop mysqld
• Start MySQL in privilege-skip mode (this lets you access the server without valid credentials):

  mysqld_safe --skip-grant-tables --skip-networking &
  

  The --skip-networking flag prevents external connections during this sensitive step.
• Exit the temporary MySQL session (if it auto-connected) and kill the safe mode process:

  exit
  pkill mysqld_safe
  

• Run mysql_install_db to rebuild the system databases—this only touches system tables and leaves your business data untouched:

  mysql_install_db --user=mysql --datadir=/var/lib/mysql
  

  Double-check the --datadir path matches your MySQL data directory (common paths are /var/lib/mysql or /usr/local/mysql/data).

3. Fix Root Permissions and Run mysql_upgrade #

Now that the system databases are restored, we’ll get root access working and finish the upgrade:

• Start MySQL normally: systemctl start mysqld
• Find the temporary root password generated by mysql_install_db—it’s in the MySQL log file:

  grep 'temporary password' /var/log/mysqld.log
  

• Log in with the temporary password:

  mysql -u root -p
  

• Set a new, strong root password:

  ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_secure_new_password';
  

• Grant full privileges to root to fix the "Access denied" errors:

  GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
  FLUSH PRIVILEGES;
  

• Exit MySQL and run mysql_upgrade to align all tables (including business tables) with MySQL 5.7’s structure—this won’t modify your actual business data, just update table metadata:

  mysql_upgrade -u root -p
  

4. Verify Everything Works #

• Log back into MySQL and check that your business database is intact:

  USE your_business_db_name;
  SHOW TABLES;
  SELECT COUNT(*) FROM your_core_business_table; -- Spot-check a key table’s data count
  

• Test permission operations (like creating a test user) to confirm no more "Access denied" errors:

  CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'test_pass';
  GRANT SELECT ON your_business_db_name.* TO 'test_user'@'localhost';
  

• Restart MySQL one final time to ensure all changes take effect: systemctl restart mysqld

Critical Notes #

• Never delete your business database folder during this process—mysql_install_db and mysql_upgrade won’t overwrite or remove it.
• If you encounter table corruption in your business database, use CHECK TABLE your_table_name; to diagnose, and REPAIR TABLE your_table_name; to fix (you have a backup, so this is safe).

内容的提问来源于stack exchange，提问作者Deckard