MySQL upgrade failed error

While I upgraded a MySQL 8 database to the latest version, I got an error with the sysconfig file in the sys table.

If your unable to start MySQL 8.


Even MySQL repair table was unable to repair the sys table.
You can try it but if it fail, here is the procedure to repair a failed upgrade for MySQL 8.

-Start MySQL


Add upgrade=MINIMAL in my.cnf file cause mysql_upgrade no longer exists.
systemctl start mysqld

- First dump all databases:


mysqldump -u root -p --all-databases > alldb.sql
Look up the documentation for mysqldump. You may want to use some of the options mentioned in comments:
mysqldump -u root -p --opt --all-databases > alldb.sql
mysqldump -u root -p --all-databases --skip-lock-tables
Check if routines & triggers are also saved.

- Stop MySQL:


systemctl stop mysqld

- Remove all the database in the MySQL directory :


rm -rf database/*

- Reinitialise :


Remove upgrade=MINIMAL from my.cnf
mysqld --initialize
Grab the new root password in the log file
cat log/mysqld.log

- Start MySQL


systemctl start mysqld

- Change root password


mysql -u root -p
Enter temporary password
change the root password to the real one:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD';

- Import the databases


mysql -u root -p < alldb.sql

It's done now you've got a clean MySQL 8.

PS: I recommend you to dump your databases before an upgrade.

2020-01-24 11:40:55

Comments

Add a Comment

Login or Register to post a Comment.

Homepage