Mysql – Unable to downgrade MySQL from version 8 to 5.6 nor start MySQL (MySQL Error Log Included)

MySQLmysql-5.5mysql-5.6ssh

I run a Plesk VPS (OS: Centos 6/Linux (Final)), whereby I recently tried to upgrade the MySQL from version 5.5.55 to version 5.6.

Being relatively new to this area, I strictly followed this Plesk Article Specifically following the steps within the 'Plesk on CentOS 6' Tab.

Unfortunately, I made the mistake of assuming version 8 was disabled as default. As a result, MySQL version 8 was installed and is now causing me major issues. To the point, where I cannot start MySQL.

I have tried to downgrade, from version 8 to version 5.6, by opening the file /etc/yum.repos.d/mysql-community.repo Notepad++, where I ensured the below was entered:

# Enable to use MySQL 5.5
[mysql55-community]
name=MySQL 5.5 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-preview]
name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-7.5-community]
name=MySQL Cluster 7.5 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.5-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-7.6-community]
name=MySQL Cluster 7.6 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.6-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

Upon entering the command yum update mysql, the following appeared on the screen:

Loaded plugins: fastestmirror, security
Setting up Update Process
Loading mirror speeds from cached hostfile
 * atomic: www4.atomicorp.com
 * epel: mirror.freethought-internet.co.uk
 * remi-safe: mirror.netweaver.uk
Package(s) mysql available, but not installed.
No Packages marked for Update

I then continued with the steps, in the hope that the installation would still miraculously work somehow.

I got as far as service mysqld start where I generated the same error as before, which was:

MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]

I am not just absolutely stumped as to how to downgrade to MySQL version 5.6 and to start back up MySQL.

MySQL Log:

[Warning] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
[System] [Server] /usr/sbin/mysqld (mysqld 8.0.16) starting as process 14057
[System] [Server] Starting upgrade of data directory.
[ERROR] [InnoDB] Unsupported redo log format (0). The redo log was created before MySQL 5.7.9
[ERROR] [InnoDB] Plugin initialization aborted with error Generic error.
[ERROR] [Server] Failed to initialize DD Storage Engine.
[ERROR] [Server] Data Dictionary initialization failed.
[ERROR] [Server] Aborting
[System] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.16)  MySQL Community Server - GPL.

Additional Information:

I am not sure if the below helps but after entering the command yum repolist all | grep mysql, the following information appeared:

mysql-cluster-7.5-community MySQL Cluster 7.5 Community          disabled
mysql-cluster-7.6-community MySQL Cluster 7.6 Community          disabled
mysql-connectors-community  MySQL Connectors Community           enabled:     94
mysql-tools-community       MySQL Tools Community                enabled:     78
mysql-tools-preview         MySQL Tools Preview                  disabled
mysql55-community           MySQL 5.5 Community Server           disabled
mysql56-community           MySQL 5.6 Community Server           enabled:    512
mysql57-community           MySQL 5.7 Community Server           disabled
mysql80-community           MySQL 8.0 Community Server           disabled

Version 8 References

As per the advise in the Comment section, I have performed the command yum list mysql* as to generate the following results as to identify any relevant 'version 8 packages' to be removed. The only problem is, I am not sure which are 'version 8 packages':

Loaded plugins: fastestmirror, security
Determining fastest mirrors
epel/metalink                                            |  30 kB     00:00
 * atomic: www4.atomicorp.com
 * epel: mirror.freethought-internet.co.uk
 * remi-safe: mirror.netweaver.uk
PLESK_17_8_11-extras                                     | 2.9 kB     00:00
PLESK_17_8_11-extras/primary_db                          |  32 kB     00:00
PLESK_17_PHP56                                           | 2.9 kB     00:00
PLESK_17_PHP56/primary_db                                |  14 kB     00:00
PLESK_17_PHP70                                           | 2.9 kB     00:00
PLESK_17_PHP70/primary_db                                |  15 kB     00:00
PLESK_17_PHP73                                           | 2.9 kB     00:00
PLESK_17_PHP73/primary_db                                |  17 kB     00:00
atomic                                                   | 3.4 kB     00:00
atomic/primary_db                                        | 651 kB     00:00
epel                                                     | 5.3 kB     00:00
epel/primary_db                                          | 6.1 MB     00:00
mysql-connectors-community                               | 2.5 kB     00:00
mysql-connectors-community/primary_db                    |  37 kB     00:00
mysql-tools-community                                    | 2.5 kB     00:00
mysql-tools-community/primary_db                         |  50 kB     00:00
mysql56-community                                        | 2.5 kB     00:00
mysql56-community/primary_db                             | 261 kB     00:00
plesk-migrator                                           | 2.9 kB     00:00
plesk-migrator/primary_db                                | 6.7 kB     00:00
plesk-migrator-tp                                        | 2.9 kB     00:00
plesk-migrator-tp/primary_db                             | 1.2 kB     00:00
remi-safe                                                | 3.0 kB     00:00
remi-safe/primary_db                                     | 1.2 MB     00:00
Available Packages
mysql.i686                       5.5.44-30.el6.art                        atomic
mysql.x86_64                     5.5.55-1659.el6.art                      atomic

Best Answer

yum update will only replace an installed package with a newer version unless you tell it otherwise, so it won't downgrade your 8.0 installation even if the repository it came from has been disabled in its config files.

I would suggest uninstalling the current version with yum remove and then installing the version you want. Make sure to protect the data files themselves and/or make copies. Use yum list mysql* or yum list installed | grep mysql to see what you have installed and what versions and then remove anything version 8 related with yum remove packagename.

Example

yum remove mysql-community-client

Important:
Make a note of them so you know what to install from 5.6.

The challenge you're having with 8.0 is that going straight there from 5.5 is too big a jump for the updates needed to the data files. Each new version only understands the file formats from a certain range of older versions.

I had problems with 8.0.16 too - I ended up reinitialising the database and then loading a mysqldump from the earlier version as it was the quickest way to get up and running.

Related Question