题 `mysql_upgrade`失败,没有给出真正的理由


我正在从MySQL 5.1升级到5.5,正在运行 mysql_upgrade 得到这个输出:

# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed

关于在哪里寻找正在发生的事情(或者,没有发生?)的任何想法,以便我可以解决任何错误并实际运行 mysql_upgrade

谢谢!

更多输出:

# mysql_upgrade --verbose
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed

# mysql_upgrade --debug-check --debug-info
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed

# mysql_upgrade --debug-info
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed

User time 0.00, System time 0.00
Maximum resident set size 1260, Integral resident set size 0
Non-physical pagefaults 447, Physical pagefaults 0, Swaps 0
Blocks in 0 out 16, Messages in 0 out 0, Signals 0
Voluntary context switches 9, Involuntary context switches 5

# mysql_upgrade --debug-check
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed

关机后 mysqld --skip-grant-tables 通过 mysqladmin shutdown 并通过重新启动mysql service mysql start,错误日志一遍又一遍地遍历这组错误:

130730 21:03:27 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist
130730 21:03:27 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
130730 21:03:27 InnoDB: The InnoDB memory heap is disabled
130730 21:03:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130730 21:03:27 InnoDB: Compressed tables use zlib 1.2.3.4
130730 21:03:27 InnoDB: Initializing buffer pool, size = 20.0G
130730 21:03:29 InnoDB: Completed initialization of buffer pool
130730 21:03:30 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 588190222435
130730 21:03:30  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 588192055067
130730 21:03:30  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 81298895, file name /var/log/mysql/mysql-bin.006008
130730 21:03:33  InnoDB: Waiting for the background threads to start
130730 21:03:34 InnoDB: 5.5.32 started; log sequence number 588192055067
130730 21:03:34 [Note] Recovering after a crash using /var/log/mysql/mysql-bin
130730 21:03:34 [Note] Starting crash recovery...
130730 21:03:34 [Note] Crash recovery finished.
130730 21:03:34 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130730 21:03:34 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
130730 21:03:34 [Note] Server socket created on IP: '0.0.0.0'.
130730 21:03:34 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

通过启动时的MySQL日志 mysqld_safe --skip-grant-tables

130730 21:19:36 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130730 21:19:36 [Note] Plugin 'FEDERATED' is disabled.
130730 21:19:36 InnoDB: The InnoDB memory heap is disabled
130730 21:19:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130730 21:19:36 InnoDB: Compressed tables use zlib 1.2.3.4
130730 21:19:37 InnoDB: Initializing buffer pool, size = 20.0G
130730 21:19:39 InnoDB: Completed initialization of buffer pool
130730 21:19:39 InnoDB: highest supported file format is Barracuda.
130730 21:19:42  InnoDB: Warning: allocated tablespace 566, old maximum was 0
130730 21:19:42  InnoDB: Waiting for the background threads to start
130730 21:19:43 InnoDB: 5.5.32 started; log sequence number 588192055067
130730 21:19:43 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130730 21:19:43 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
130730 21:19:43 [Note] Server socket created on IP: '0.0.0.0'.
130730 21:19:43 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
130730 21:19:43 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
130730 21:19:43 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
130730 21:19:43 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
130730 21:19:43 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-0ubuntu0.12.04.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

据我了解,所有表结构/存在问题(因为它与mysql系统表有关)应该通过运行来纠正 mysql_upgrade :


68
2017-07-30 20:21




也许可能一无所获, mysqld 正在运行,有 --skip-grant-tables 选项。我可以通过连接 mysql 在没有凭证的终端上,我通过系统日志或我运行时可以想到的其他任何地方都没有错误 mysql_upgrade - Jim Rubenstein
该 MySQL参考手册 涵盖从5.1升级到5.5很好。如果您已按照此处的所有说明进行操作,那么值得一提。如果你还没有,那么...... - Aaron Copley
如果您的mysql root用户没有密码,请不要在`mysql_upgrade -u root -p`中包含`-p` - Jeferex


答案:


我认为它需要用户名和密码

mysql_upgrade -u root -p

如果我没有通过它们,我会收到你的错误

编辑:感谢现在的评论我知道还有其他原因,可能不那么频繁,但最好也要注意它们

所以当你得到那个错误

  • 你没有传递用户名和密码
  • 你通过了你的证书,但他们错了
  • MySQL服务器没有运行
  • 权限的表被破坏(然后你必须重启MySQL mysqld --skip-grant-table
  • 缺少表mysql.plugin(启动MySQL时会看到一个错误,建议运行... mysql_upgrade,那就失败。你可能在my.cnf中有一些过时的配置)

93
2017-08-14 10:01



这正是我遇到的问题 - 为什么地狱不能只是说“无法验证”或“连接错误”或其他什么?太生气了 ... - les2
伙计们,如果你的密码错误,你会得到同样的错误。所以要通知。 - Yoosaf Abdulla
如果服务器没有运行,你会得到相同的错误,即使它似乎接受密码。 - Raman
当数据库表或数据库格式也被破坏时,它也不起作用,那么你需要用“mysqld --skip-grant-tables”启动守护进程并在另一个终端运行mysql_upgrade! - Henning
+1为此。我讨厌MySQL的另一个原因 - Excalibur


我从5.5升级到5.6时遇到了这些精确的症状,结果证明是服务可达性问题。

即使cli MySQL客户端可以只提供-u和-p连接到我的本地数据库实例,我还需要为mysql_upgrade指定-h 127.0.0.1,因为它正在尝试套接字文件连接并且在尝试中失败。


9
2017-08-19 19:33



这正是我的问题,因为我像这样运行mysqd:mysqld --skip-grant-tables --user = mysql - Rodo


这似乎是Plesk服务器,当使用Plesk时,Mysql没有root,但Mysql的管理员称为admin,所以这个命令应该在Plesk上工作,因为我之前尝试过:

mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`

9
2017-10-09 08:56



这对我来说非常合适 - Carlos Alberto Martínez Gadea


同样的问题!我的解决方案来自 http://www.freebsd.org/cgi/query-pr.cgi?pr=180624

简而言之:该错误具有误导性!跑 mysql_upgrade -u root -p 使用DB联机并提供root密码。


5
2017-11-30 10:23





您可以尝试逐个运行这些以查看它失败的位置:

mysql_upgrade执行以下命令来检查和修复表并升级系统表:

mysqlcheck --all-databases --check-upgrade --auto-repair  
mysql < fix_priv_tables  
mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names

http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html


4
2017-07-30 21:10



想到这一点,但是 fix_priv_tables 是由...生成的脚本 mysql_upgrade 为了修复privelege表 - Jim Rubenstein
好点,也许只是尝试第一个mysqlcheck线?并尝试直接从bin文件夹运行,fwiw, /usr/bin/mysql_upgrade - user16081-JoeT


这个问题非常通用,我为此道歉。

我找不到我遇到的问题的直接原因和解决方案,所以我使用重新安装MySQL来查看是否可行。事实证明,重新安装就可以了。这是修复它的一种蹩脚方式,但这是我唯一的选择。

关于这个问题的很多其他答案都是我必须努力使mysql_upgrade最初运行的问题,但无论出于何种原因 - 它失败了,因为它试图运行一些自动查询,我找不到哪个文档查询它正在运行所以我可以修复它们。


3
2017-11-30 14:00



是的,一旦mysql的数据目录被破坏,你几乎无能为力 - Krauser


您必须在mysql数据下检查权限所有文件。它应该是mysql PID(mysql或_mysql)的所有者。这有时会发生,因为没有适当的权限从文件恢复数据。例如,如果你的mysql数据在/ var / lib / mysql下

chown -R mysql /var/lib/mysql

2
2017-11-07 00:12