卧槽，安装完MySQL竟然提示数据表不存在！！

冰河

发布于 2021-05-18 13:22:23

2K00

代码可运行

文章被收录于专栏：冰河技术冰河技术

运行总次数：0

代码可运行

大家好，我是冰河~~

五一假期过去一半了，不知道小伙伴们过的如何，相信很多小伙伴都出去玩了吧？我是在家研究了两天Seata源码。

本来想基于MySQL 8.0研究Seata源码，结果我不小心把MySQL 8.0的源码安装包删除了，我这的网又很慢，下载MySQL 8.0源码安装包下载了半天，没下载下来。只好安装我电脑上存在的MySQL 5.6来研究Seata源码了。安装完MySQL 5.6竟然提示我数据表不存在！！

今天，就给小伙伴们分享下我是如何解决这个问题的。

产生的问题

在安装MySQL 5.6时，安装完成后，后台日志报如下警告信息。

2021-05-03 13:47:34 22946 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2021-05-03 13:47:34 22946 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2021-05-03 13:47:34 22946 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2021-05-03 13:47:34 22946 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2021-05-03 13:47:34 22946 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.
.......
2021-05-03 13:49:33 22946 [Warning] InnoDB: Cannot open table mysql/innodb_index_stats from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2021-05-03 13:49:49 7f3ae82a5700  InnoDB: Error: table `mysql`.`innodb_index_stats` does not exist in the InnoDB internal

问题原因

网上查找到的资料表示：数据库打开这几张表的默认引擎为MyISAM，但是这几张表在建表时的引擎为InnoDB，但是能确定的，这几张表确实是在mysql5.6中新入的。

innodb_index_stats,
innodb_tables_stats,
slave_master_info,
slave_relay_log_info,
slave_worker_info

解决方法

(1) 登录数据库，进入MySQL库，执行如下SQL删除5张表

记住，一定要用drop table if exists

drop table if exists innodb_index_stats;
drop table if exists innodb_table_stats;
drop table if exists slave_master_info;
drop table if exists slave_relay_log_info;
drop table if exists slave_worker_info;

如下是执行的结果，忽略你看到的Warning信息

binghe@localhost : mysql 02:12:26> drop table if exists innodb_index_stats;
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
Warning (Code 155): Table 'mysql.innodb_index_stats' doesn't exist
binghe@localhost : mysql 02:12:26> drop table if exists innodb_table_stats;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Warning (Code 155): Table 'mysql.innodb_table_stats' doesn't exist
binghe@localhost : mysql 02:12:26> drop table if exists slave_master_info;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
 
Warning (Code 155): Table 'mysql.slave_master_info' doesn't exist
binghe@localhost : mysql 02:12:27> drop table if exists slave_relay_log_info;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Warning (Code 155): Table 'mysql.slave_relay_log_info' doesn't exist
binghe@localhost : mysql 02:12:27> drop table if exists slave_worker_info;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Warning (Code 155): Table 'mysql.slave_worker_info' doesn't exist

执行完后，可以用show tables查看一下，看表的数据是否已经比删除之前减少了，如果减少了，说明你成功了！

(2)面这一部操作完成后，停止数据库，并进入到数据库数据文件所在目录，删除表面5个表所对应的idb文件，如下所示：

[binghe@localhost] /data/mysqldata3/mydata/mysql]ls *.ibd
innodb_index_stats.ibd  innodb_table_stats.ibd  slave_master_info.ibd  slave_relay_log_info.ibd  slave_worker_info.ibd
[binghe@localhost] /data/mysqldata3/mydata/mysql]rm -f *.ibd

(3) 重新启动数据库，进入到mysql库，重建上面被删除的表结构：数据库的建设表脚本在mysql软件的安装目录的share目录下，我的mysql软件的安装路径为/usr/test/mysql

binghe@localhost : (none) 02:23:03> use mysql
Database changed

如下是执行建表脚本前表的数量：

binghe@localhost : mysql 02:23:48> source /usr/test/mysql/share/mysql_system_tables.sql
binghe@localhost : mysql 02:23:50> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
23 rows in set (0.00 sec)

如下为执行建表脚本后，表的数量

binghe@localhost : mysql 02:23:46> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

(4) 用show create table命令查看表时，也很正常，后台日志中也不再报与上面提到的5张表相关的错误，到此，问题全部解决！

后记：在后面的环境中，配置双主架构时，又遇到了如下问题：

error:
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

最后通过如下的操作解决的问题。

(1)登录数据库后，删除5张表，并重新导入脚本

use mysql;
drop table  slave_master_info;
drop table  slave_relay_log_info;
drop table  slave_worker_info;
drop table  innodb_index_stats;
drop table  innodb_table_stats;
source /usr/local/mysql/share/mysql_system_tables.sql;

(2)重新启动数据库

注：这一步是必须的，否则无法正常配置双主架构。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-05-03，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库