mysql - 降级后无法启动mysql，无法运行mysql_upgrade，无法更改root密码

Question

我将 mysql 服务器从 5.7.14 降级到 5.6.32。

这样做之后，我假设我应该根据我遇到的说明运行 mysql_upgrade。

但是，为此，我需要提供我拥有的根用户/p。但是，当我尝试这样做时，它似乎并没有接受这些:

mysql_upgrade -u root -p

Enter password:

Looking for 'mysql' as: mysql

Looking for 'mysqlcheck' as: mysqlcheck

Error: Failed while fetching Server version! Could be due to unauthorized access.

FATAL ERROR: Upgrade failed

所以我尝试通过创建一个初始化文件来重置密码，方法如下:为 'root'@'localhost' 设置密码 = PASSWORD('abcd1234');然后用 mysqld_safe --init-file=/home/root/mysql-init &

调用它

但是这样好像不行，估计是因为mysql无法正常启动而报错。

0802 12:50:45 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

2016-08-02 12:50:46 0 [Warning] TIMESTAMP with implicit DEFAULT value is 

2016-08-02 12:50:46 0 [Note] /usr/sbin/mysqld (mysqld 5.6.32) starting as process 2635 ...

2016-08-02 12:50:46 2635 [Note] Plugin 'FEDERATED' is disabled.

/usr/sbin/mysqld: Unknown storage engine 'InnoDB'

2016-08-02 12:50:46 2635 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

2016-08-02 12:50:46 2635 [Note] InnoDB: Using atomics to ref count buffer pool pages

2016-08-02 12:50:46 2635 [Note] InnoDB: The InnoDB memory heap is disabled

2016-08-02 12:50:46 2635 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2016-08-02 12:50:46 2635 [Note] InnoDB: Memory barrier is not used

2016-08-02 12:50:46 2635 [Note] InnoDB: Compressed tables use zlib 1.2.3

2016-08-02 12:50:46 2635 [Note] InnoDB: Using Linux native AIO

2016-08-02 12:50:46 2635 [Note] InnoDB: Using CPU crc32 instructions

2016-08-02 12:50:46 2635 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2016-08-02 12:50:46 2635 [Note] InnoDB: Completed initialization of buffer pool

2016-08-02 12:50:46 2635 [Note] InnoDB: Highest supported file format is Barracuda.

2016-08-02 12:50:46 2635 [Note] InnoDB: The log sequence numbers 2512357 and 

2512357 in ibdata files do not match the log sequence number 2512396 in $

2016-08-02 12:50:46 2635 [Note] InnoDB: Database was not shutdown normally!

2016-08-02 12:50:46 2635 [Note] InnoDB: Starting crash recovery.

2016-08-02 12:50:46 2635 [Note] InnoDB: Reading tablespace information from the .ibd files...

2016-08-02 12:50:46 2635 [Note] InnoDB: Restoring possible half-written data pages

2016-08-02 12:50:46 2635 [Note] InnoDB: from the doublewrite buffer...

InnoDB: wrong number of columns in SYS_INDEXES record

InnoDB: wrong number of columns in SYS_INDEXES record

InnoDB: wrong number of columns in SYS_INDEXES record

InnoDB: wrong number of columns in SYS_INDEXES record

InnoDB: wrong number of columns in SYS_INDEXES record
17:50:46 UTC - mysqld got signal 11 ;

这似乎让我陷入无法运行 mysql_upgrade 来升级数据库的循环，但我无法成功重置 root 密码(虽然我 99.9% 确定我有正确的 root密码，它似乎不接受它。)

堆栈跟踪..

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...

stack_bottom = 0 thread_stack 0x40000

/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8e2375]

/usr/sbin/mysqld(handle_fatal_signal+0x494)[0x666da4]

/lib64/libpthread.so.0[0x398620f790]

/usr/sbin/mysqld[0xac1017]

/usr/sbin/mysqld[0xac25fd]

/usr/sbin/mysqld[0xa3de77]

/usr/sbin/mysqld[0x9822dd]

/usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x48)[0x5aa958]

/usr/sbin/mysqld[0x6f01e1]

/usr/sbin/mysqld(_Z11plugin_initPiPPci+0xbb6)[0x6f4046]

/usr/sbin/mysqld[0x59ce38]

/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x425)[0x5a2055]

/lib64/libc.so.6(__libc_start_main+0xfd)[0x3985e1ed5d]

/usr/sbin/mysqld[0x593ba5]

有什么建议吗？谢谢

Answer 1

您遇到的说明完全错误。唯一重要的说明是那些 from MySQL :

Supported downgrade methods include:

In-place Downgrade: Involves shutting down the new MySQL version, replacing the new MySQL binaries or packages with the old ones, and restarting the old MySQL version on the existing data directory. In-place downgrades are supported for downgrades between GA versions within the same release series. For example, in-place downgrades are supported for downgrades from 5.7.10 to 5.7.9.

Logical Downgrade: Involves using mysqldump to dump all tables from the new MySQL version, and then loading the dump file into the old MySQL version. Logical downgrades are supported for downgrades between GA versions within the same release series and for downgrades between release levels. For example, logical downgrades are supported for downgrades from 5.7.10 to 5.7.9 and for downgrades from 5.7 to 5.6.

For procedures, see Performing an In-place Downgrade, and Performing a Logical Downgrade.

注意我的粗体。唯一支持的选项是使用 mysqldump 提取数据库的副本，然后将其导入旧版本。希望你有一些备份!