怎么解决MySQL中的5.6.x InnoDB Error Table mysql.innodb

226次阅读
没有评论

共计 6578 个字符,预计需要花费 17 分钟才能阅读完成。

行业资讯    
数据库    
MySQL 数据库    
怎么解决 MySQL 中的 5.6.x InnoDB Error Table mysql.innodb_table_stats not found

这篇文章主要介绍“怎么解决 MySQL 中的 5.6.x InnoDB Error Table mysql.innodb_table_stats not found”,在日常操作中,相信很多人在怎么解决 MySQL 中的 5.6.x InnoDB Error Table mysql.innodb_table_stats not found 问题上存在疑惑,丸趣 TV 小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”怎么解决 MySQL 中的 5.6.x InnoDB Error Table mysql.innodb_table_stats not found”的疑惑有所帮助!接下来,请跟着丸趣 TV 小编一起来学习吧!

【问题描述】:
检查 error log 的时候发现大量 warnings:
[Warning] InnoDB Error Table mysql.innodb_index_stats not found
[Warning] InnoDB Error Table mysql.innodb_table_stats not found
[Warning] InnoDB Error Table mysql.slave_master_info not found
[Warning] InnoDB Error Table mysql.slave_relay_log_info not found
[Warning] InnoDB Error Table mysql.slave_worker_info not found

或在打开 innodb 表时,在 err-log 里会打印出:
 InnoDB: Error: Table mysql . innodb_table_stats not found.
 Error: Fetch of persistent statistics requested for table {databse_name} . {table_name} but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

【解决方案】:
先看看能否 drop table,如果说表不存在,则继续下一步。

DROP TABLE mysql.innodb_index_stats;

DROP TABLE mysql.innodb_table_stats;

DROP TABLE mysql.slave_master_info;

DROP TABLE mysql.slave_relay_log_info;

DROP TABLE mysql.slave_worker_info;

删除 datadir 下 mysql 数据库中的这五张表的 frm 文件(如有 ibd 也一并删除)

rm -rf $datadir/mysql/innodb_index_stats.*

rm -rf $datadir/mysql/innodb_table_stats.*

rm -rf $datadir/mysql/slave_master_info.*

rm -rf $datadir/mysql/slave_relay_log_info.*

rm -rf $datadir/mysql/slave_worker_info.*

再执行如下语句,重新创建这五张表:

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;

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;

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

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

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

最后重启 mysqld 即可。

到此,关于“怎么解决 MySQL 中的 5.6.x InnoDB Error Table mysql.innodb_table_stats not found”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注丸趣 TV 网站,丸趣 TV 小编会继续努力为大家带来更多实用的文章!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-19发表,共计6578字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)