我从5.5升级到mysql 5.6，现在我的日志在启动时满是这样的消息

我在这里找到了可能的解决方案，但它似乎不是官方的。
http：// forums .mysql.com / read.php？22,578559,579891＃msg-579891 

2013-12-06 21:08:00 7f87b1d26700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-12-06 21:08:00 7f87b1d26700 InnoDB: Recalculation of persistent statistics requested for table "drupal"."sessions" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
2013-12-06 21:08:07 7f903c09c700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.


任何官方解决方案或100％修复？

评论

在stackoverflow.com/questions/15767652/…中重复线程操作。您可以下载.sql文件来自己创建表。
自从这个问题以来，事情可能已经发生了变化。 @ 2018，更新了旧的mysql，同样的问题。发现要运行：mysql_upgrade -u root -p --force && systemctl restart mysqld升级mysql模式和所有数据库，解决了这个问题

#1 楼

我之前在以下文章中解决了此问题：无法打开表mysql / innodb_index_stats

这些表是在安装MySQL 5.6时为您创建的。但是，从MySQL 5.5升级不会调用这些表的创建。以下是手动创建脚本的脚本：

innodb_index_stats 

USE mysql;
CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;


innodb_table_stats 

USE mysql;
CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;


slave_master_info 

USE mysql;
CREATE TABLE `slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';


slave_relay_log_info 

USE mysql;
CREATE TABLE `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';


slave_worker_info 

USE mysql;
CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';


替代方法

另一种解决方法是从MySQL 5.6实例创建脚本。

步骤01：转到运行MySQL 5.6的数据库服务器，或者在测试计算机上安装MySQL 5.6。

步骤02：mysqld将这5个表转储到文本文件中

INNODB_TABLES="innodb_index_stats"
INNODB_TABLES="${INNODB_TABLES} innodb_table_stats"
INNODB_TABLES="${INNODB_TABLES} slave_master_info"
INNODB_TABLES="${INNODB_TABLES} slave_relay_log_info"
INNODB_TABLES="${INNODB_TABLES} slave_worker_info"
mysqldump -uroot mysql ${INNODB_TABLES} > InnoDB_MySQL_Tables.sql


然后，您可以运行InnoDB_MySQL_Tables.sql在升级之前在任何运行MySQL 5.5的数据库服务器上运行。

评论

解决了我从5.5-Oracle 5.6进行Linux升级的问题。不得不删除一些幻影表，停止mysql，从/ var / lib / mysql / mysql中移动坏的ibd文件，重新启动mysql，然后运行Rolando的声明...。而且，Yay没有更多的启动错误，并且希望不再不稳定。罗兰多，你真了不起。

–字形
2014年4月10日0:53


谢谢您的解决方案。与mysql-community-server软件包的安装一样，其中包含一个脚本，该脚本基本上具有所有需要的create语句：cat /usr/share/mysql/mysql_system_tables.sql | mysql -uroot -p mysql

– minni
16-2-20在14:11


我遇到了这个问题，并且无法从头开始创建表，因为它们“已经存在”。原来，我正在从InnoDB的一个大文件切换到每个表的文件，并且删除了ibdata1文件。 MySQL并没有在启动时重新创建这些表，并且我不得不手动将代表这些表的文件移出MySQL数据目录，以便使用上述CREATE语句（DROP TABLE不起作用）。

–克里斯托弗·舒尔茨（Christopher Schultz）
17年12月19日在16:38
@ChristopherSchultz感谢您提到这一点。我在2015年8月提到了此问题（dba.stackexchange.com/questions/111616/…）。至少您自己发现了，这很好。

– RolandoMySQLDBA
17年12月19日在16:49


谢谢，这解决了我的问题。顺便说一句，我在执行手动升级的mysql之前恢复数据库的最后一个转储时遇到了上面的错误“表不存在”，这是使用更高版本重新安装的。

–ash_01
18/12/27在2:03

#2 楼

Rolando的回答对我有所帮助。我遇到了同样的问题，通过SHOW TABLES显示了这5个表，但是对表的SELECT或其他操作导致找不到表。

要解决此问题，请使用Rolando的答案，我需要：


DROP TABLE <tablename>-所有5个表
在文件系统中，删除其余的.ibd文件（.frm文件已由DROP TABLE删除）
然后我停止了并启动了mysqld实例（不知道是否需要它-使我感到高兴）
Rolando提供的CREATE TABLE语句随后运行没有问题。


评论

只需提一下，即使启动DROP TABLE 时，我仍然收到错误1051（42S02）：未知表'...'错误消息，但至少.frm文件到那时消失了。

–superjos
17年7月12日在15:38