• MySQL 5.6 解决InnoDB: Error: Table "mysql"."innodb_table_stats" not found.问题


    在安装MySQL 5.6.30时,安装完成后,后台日志报如下警告信息:
    2016-05-27 12:25:27 7fabf86f7700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
    2016-05-27 12:25:27 7fabf86f7700 InnoDB: Error: Fetch of persistent statistics requested for table "hj_web"."wechat_res" 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.

    2016-05-27 14:03:52 28585 [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.

    2016-05-27 14:03:52 28585 [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.

    2016-05-27 14:03:52 28585 [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.

    问题产生原因:具体原因目前不详,网上查找到的资料:数据库打开这几张表的默认引擎为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
    mysql> use mysql;
    mysql> drop table if exists innodb_index_stats;
    mysql> drop table if exists innodb_table_stats;
    mysql> drop table if exists slave_master_info;
    mysql> drop table if exists slave_relay_log_info;
    mysql> drop table if exists slave_worker_info;
    执行完后,可以用show tables查看一下,看表的数据是否已经比删除之前减少了,如果减少了,说明你成功了!

    2、上一步操作完成后,停止数据库,并进入到数据库数据文件所在目录,删除上面5个表所对应的idb文件,如下所示:

    # /etc/init.d/mysqld stop
    # cd /data/mysql/data/mysql/
    # ls -l *.ibd
    -rw-rw---- 1 mysql mysql 98304 May 27 14:17 innodb_index_stats.ibd
    -rw-rw---- 1 mysql mysql 98304 May 27 14:17 innodb_table_stats.ibd
    -rw-rw---- 1 mysql mysql 98304 May 27 14:14 slave_master_info.ibd
    -rw-rw---- 1 mysql mysql 98304 May 27 14:14 slave_relay_log_info.ibd
    -rw-rw---- 1 mysql mysql 98304 May 27 14:14 slave_worker_info.ibd

    # /bin/rm -rf *.ibd

    3、重新启动数据库,进入到mysql库,重建上面被删除的表结构:
    数据库的建表脚本在mysql软件的安装目录的share目录下或者mysql的安装包的script目录下,我的mysql软件的安装路径为/data/mysql/

    # /etc/init.d/mysqld start
    mysql> use mysql;
    mysql> source /data/mysql/share/mysql_system_tables.sql
    mysql> 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)


    mysql> desc innodb_table_stats;
    +--------------------------+---------------------+------+-----+-------------------+-----------------------------+
    | Field | Type | Null | Key | Default | Extra |
    +--------------------------+---------------------+------+-----+-------------------+-----------------------------+
    | database_name | varchar(64) | NO | PRI | NULL | |
    | table_name | varchar(64) | NO | PRI | NULL | |
    | last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    | n_rows | bigint(20) unsigned | NO | | NULL | |
    | clustered_index_size | bigint(20) unsigned | NO | | NULL | |
    | sum_of_other_index_sizes | bigint(20) unsigned | NO | | NULL | |
    +--------------------------+---------------------+------+-----+-------------------+-----------------------------+
    6 rows in set (0.00 sec)


    mysql> desc slave_master_info;
    +------------------------+---------------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +------------------------+---------------------+------+-----+---------+-------+
    | Number_of_lines | int(10) unsigned | NO | | NULL | |
    | Master_log_name | text | NO | | NULL | |
    | Master_log_pos | bigint(20) unsigned | NO | | NULL | |
    | Host | char(64) | NO | PRI | | |
    | User_name | text | YES | | NULL | |
    | User_password | text | YES | | NULL | |
    | Port | int(10) unsigned | NO | PRI | NULL | |
    | Connect_retry | int(10) unsigned | NO | | NULL | |
    | Enabled_ssl | tinyint(1) | NO | | NULL | |
    | Ssl_ca | text | YES | | NULL | |
    | Ssl_capath | text | YES | | NULL | |
    | Ssl_cert | text | YES | | NULL | |
    | Ssl_cipher | text | YES | | NULL | |
    | Ssl_key | text | YES | | NULL | |
    | Ssl_verify_server_cert | tinyint(1) | NO | | NULL | |
    | Heartbeat | float | NO | | NULL | |
    | Bind | text | YES | | NULL | |
    | Ignored_server_ids | text | YES | | NULL | |
    | Uuid | text | YES | | NULL | |
    | Retry_count | bigint(20) unsigned | NO | | NULL | |
    | Ssl_crl | text | YES | | NULL | |
    | Ssl_crlpath | text | YES | | NULL | |
    | Enabled_auto_position | tinyint(1) | NO | | NULL | |
    +------------------------+---------------------+------+-----+---------+-------+
    23 rows in set (0.00 sec)

    说明表都正常了,再次查看mysql报错日志,就会发现没有了关于这5个表的报错日志。

     

  • 相关阅读:
    进击的实时数仓:Flink 在 OPPO 实时计算平台的研发与应用实践
    vue中将时间戳转换为YYYY-MM-dd hh:mm格式时间的组件
    vue 将时间戳转换成日期格式 (一)
    element-ui DatePicker 日期选择器 让结束日期大于开始日期
    css 边框上如何写入文字?
    2019最新Web前端经典面试试题(含答案)
    Nginx 安装及配置
    前端基础面试题(JS部分)
    const与指针
    linux配置-------redhat虚拟机的中文输入法配置
  • 原文地址:https://www.cnblogs.com/fjping0606/p/5534553.html
Copyright © 2020-2023  润新知