Unsupported redo log format (0). The redo log was created before MySQL 5.7.9

When mysql is jump upgraded from before MySQL 5.7 to MySQL 8 on Mac OS, the MySQL server will stop working and throw many errors in the error log.

Usually the error log for MySQL can be found here on Mac OS:

/usr/local/var/mysql/*.local.err

When trying to start mysql from the command line by mysql.server start, this error might be showing up on the console.
Can’t connect to local MySQL server through socket ‘/var/mysql/mysql.sock’ (38)

The important errors from the error logs are:
– Cannot upgrade server earlier than 5.7 to 8.0
– Unsupported redo log format (0). The redo log was created before MySQL 5.7.9

The errors in the error log due to the jump upgrade from before MySQL 5.7 to MySQL 8 can be the following but not limited to them.

2021-08-06T01:12:50.114063Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2021-08-06T01:12:50.152693Z 1 [ERROR] [MY-013168] [InnoDB] Cannot upgrade server earlier than 5.7 to 8.0
2021-08-06T01:12:55.228611Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2021-08-06T01:12:55.232078Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2021-08-06T01:12:55.237784Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-08-06T01:12:55.238402Z 0 [System] [MY-010910] [Server] /usr/local/opt/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.26)  Homebrew.
2021-08-06T01:12:55.6NZ mysqld_safe mysqld from pid file /usr/local/var/mysql/MacBook-Pro-2.local.pid ended
2021-08-06T01:12:59.6NZ mysqld_safe Logging to '/usr/local/var/mysql/MacBook-Pro-2.local.err'.
2021-08-06T01:12:59.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2021-08-06T01:12:59.621661Z 0 [System] [MY-010116] [Server] /usr/local/opt/mysql/bin/mysqld (mysqld 8.0.26) starting as process 37994
2021-08-06T01:12:59.624741Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2021-08-06T01:12:59.634795Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2021-08-06T01:12:59.634877Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-08-06T01:12:59.732079Z 1 [ERROR] [MY-013090] [InnoDB] Unsupported redo log format (0). The redo log was created before MySQL 5.7.9
2021-08-06T01:12:59.732219Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2021-08-06T01:12:59.734061Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2021-08-06T01:12:59.734489Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2021-08-06T01:12:59.734736Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-08-06T01:12:59.735235Z 0 [System] [MY-010910] [Server] /usr/local/opt/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.26)  Homebrew.
2021-08-06T01:12:59.6NZ mysqld_safe mysqld from pid file /usr/local/var/mysql/MacBook-Pro-2.local.pid ended
2021-08-06T01:13:09.6NZ mysqld_safe Logging to '/usr/local/var/mysql/MacBook-Pro-2.local.err'.

In order to get back to the mysql database and retrieve/backup the databases before the upgrade to MySQL 8, mysql has to be downgrade to 5.6. Fortunately, homebrew can install different versions of mysql, to make the mac os to use the older version of mysql, one just have to link all mysql commands to the mysql installation direction for the older version. Here are the steps to do just that.

1. Install mysql 5.6 via brew install

brew install mysql@5.6

2. Open .bash_profile from home directory and add the following to point to mysql5.6

export PATH="/usr/local/opt/mysql@5.6/bin:$PATH"
export LDFLAGS="-L/usr/local/opt/mysql@5.6/lib"
export CPPFLAGS="-I/usr/local/opt/mysql@5.6/include"

3. To have launchd start mysql@5.6 now and restart at login:

brew services start mysql@5.6

4. Stop the auto start for mysql 8 by removing this file.

rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

5. If mysql is still not starting and throw this error in the error log: [ERROR] unknown variable ‘mysqlx-bind-address=127.0.0.1’, find the my.cnf and comment out this line. The my.cnf on mac is usually located at /usr/local/etc/my.cnf

mysqlx-bind-address = 127.0.0.1

6. Restart Mac OS

7. Make a backup copies of databases with mysqldumb

mysqldump mydatabase > mydatabase.bk.sql