将 mySQL 迁移到带有 mySQL 8.0.17 的新主机,无法更新/启动 mysqld

将 mySQL 迁移到带有 mySQL 8.0.17 的新主机,无法更新/启动 mysqld

我最近使用 mySQL ver 8.0.17 设置了一个新主机,并使用 移动了一个旧的 mySQL 数据库(ver 5.1.73)到其中mysqldump -u root -p --all-databases > somefile.sql,然后使用 导入它mysql -u root -p < /pathtodatase.sql。现在,我无法启动mysqld,也无法运行更新来修复表。

我尝试按照日志中所述运行 mysql 升级,但似乎没有帮助。最初我运行了mysql_upgrade该功能,但该功能已被弃用,因此我改为运行该功能mysqld --upgrade=AUTO,但服务启动时仍然出现问题。我做错了什么?

以下是 的输出mysqld.log

2020-02-07T01:09:23.054386Z 0 [Warning] [MY-011070] [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. 2020-02-07T01:09:23.056223Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.17) starting as process 1533 2020-02-07T01:09:24.005561Z 0 [Warning] [MY-010727] [Server] System table 'plugin' is expected to be transactional. 2020-02-07T01:09:24.188459Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2020-02-07T01:09:24.206417Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.user]. 2020-02-07T01:09:24.206431Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.db]. 2020-02-07T01:09:24.206442Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.tables_priv]. 2020-02-07T01:09:24.206466Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.columns_priv]. 2020-02-07T01:09:24.206486Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.procs_priv]. 2020-02-07T01:09:24.206719Z 0 [Warning] [MY-013143] [Server] Column count of mysql.user is wrong. Expected 51, found 39. The table is probably corrupted 2020-02-07T01:09:24.206740Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.db. The table is probably corrupted! 2020-02-07T01:09:24.206762Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.db. The table is probably corrupted! 2020-02-07T01:09:24.206799Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.tables_priv. The table is probably corrupted! 2020-02-07T01:09:24.206818Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.tables_priv. The table is probably corrupted! 2020-02-07T01:09:24.206834Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.tables_priv. The table is probably corrupted! 2020-02-07T01:09:24.206853Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.columns_priv. The table is probably corrupted! 2020-02-07T01:09:24.206876Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.columns_priv. The table is probably corrupted! 2020-02-07T01:09:24.206892Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.procs_priv. The table is probably corrupted! 2020-02-07T01:09:24.206912Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.procs_priv. The table is probably corrupted! 2020-02-07T01:09:24.206942Z 0 [Warning] [MY-013139] [Server] Cannot load from mysql.procs_priv. The table is probably corrupted! 2020-02-07T01:09:24.207159Z 0 [ERROR] [MY-010316] [Server] Fatal error: Could not read the column 'authentication_string' from table 'mysql.user'. Please perform the MySQL upgrade procedure. 2020-02-07T01:09:24.207286Z 0 [ERROR] [MY-010952] [Server] The privilege system failed to initialize correctly. For complete instructions on how to upgrade MySQL to a new version please see the 'Upgrading MySQL' section from the MySQL manual. 2020-02-07T01:09:24.208023Z 0 [ERROR] [MY-010119] [Server] Aborting 2020-02-07T01:09:25.468177Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.17) Source distribution.

答案1

您不能直接复制数据文件。您必须从 MySQL 转储 SQL,然后重新导入。

这里有一个关于如何使用这些工具来执行此操作的精彩教程:https://www.digitalocean.com/community/tutorials/how-to-import-and-export-databases-in-mysql-or-mariadb

答案2

您跳过了 3 个主要版本。最好保留最后一个版本 5.7。

那是,

  1. 安装 5.7
  2. 加载 5.1 转储
  3. 解决所有出现的问题
  4. 就地升级至 8.0。

升级到 8.0 后,一些“隐藏”的东西发生了重大变化。但 8.0 可以理解 5.7 版本(但不能理解旧版本)。

相关内容