MySQL official source cannot be started after direct yum upgrade from 8.0.3 to 8.0.4

MySQL official source cannot be started after direct yum upgrade from 8.0.3 to 8.0.4

Server Environment: CentOS 7.4.14

Problem: MySQL cannot be started normally

View logs

2018-02-23T02: 10: 08.371209Z 0 [System] [MY-010116]/usr/sbin/mysqld (mysqld 8.0.4-rc-log) starting as process 27702...
2012-2-23t02: 10: 08.737713Z 1 [ERROR] [MY-011096] No data dictionary version number found.
2012-2-23t02: 10: 08.742589z 0 [ERROR] [MY-010020] Data Dictionary initialization failed.
2018-02-23T02: 10: 08.738038Z 0 [ERROR] [MY-010119] Aborting

Later, it was found that the caching_sha2_password authentication mechanism was enabled by default after the upgrade, and previous data cannot be directly run.

You can only restore the original version, export the data from the original version, re-import the data, or upgrade the import.

1. Uninstall MySQL8.0.4

Back up data

Mv/var/lib/{mysql, mysql. bak}
Chown-R mysql: mysql/var/lib/mysql. bak
Chmod 755-R/var/lib/mysql. bak

Uninstall yum directly

Rpm-qa | grep '^ mysql'

Then Uninstall all yum of the 8.0.4 package.

Ii. Install MySQL8.0.3

Download 8.0.3 from the official website

Download the package mysql-8.0.3-0.1.rc.el7.x86_64.rpm-bundle.tar

Extract

Tar-xvf mysql-8.0.3-0.1.rc.el7.x86_64.rpm-bundle.tar

Install common, libs, client, and server in sequence

Rpm-ivh mysql-community-common-8.0.3-0.1.rc.el7.x86_64.rpm
Rpm-ivh mysql-community-libs-8.0.3-0.1.rc.el7.x86_64.rpm
Rpm-ivh mysql-community-client-8.0.3-0.1.rc.el7.x86_64.rpm
Rpm-ivh mysql-community-server-8.0.3-0.1.rc.el7.x86_64.rpm

Start mysql

Systemctl start msyqld. service

At this time,/var/lib/generates a new mysql folder.

Iii. Data Export

Modify/etc/my. cnf to point to old data

[Mysqld]
Innodb_force_recovery = 6
Datadir =/var/lib/mysql. bak

Forcing InnoDB Recovery provides six levels of Repair Modes
Innodb_force_recovery reference: https://www.bkjia.com/Linux/2018-02/151049.htm

Full Database Export:
Mysqldump-uroot-p -- all-databases>/home/sqlfile. SQL

Iv. Data Recovery

Modify the/etc/my. cnf data path and remove the repair mode. The data cannot be written in the repair mode.

[Mysqld]
Datadir =/var/lib/mysql

View temporary password

[Root @ soft] # cat/var/log/mysqld. log | grep password
2018-02-23T08: 06: 36.785974Z 5 [Note] A temporary password is generated for root @ localhost: Leyk2qO_75ER

Log on to mysql with a temporary password and change the password.

Abandon the upgrade and directly import data

Mysql-uroot-p

Mysql> grant all privileges on *. * TO 'root' @ 'localhost' identified by 'abcdef @ 'with grant option;
Mysql> flush privileges;
Mysql> source/home/sqlfile. SQL;

After the restart, everything is normal. You must back up data before upgrading.

This article permanently updates link: https://www.bkjia.com/Linux/2018-02/151050.htm