MySQL 5.6 解决InnoDB: Error: Table "mysql"."innodb_table_stats" not found.问题
来源:互联网 发布:反应热效应的测定数据 编辑:程序博客网 时间:2024/04/30 04:23
报错日志:
2016-12-24 11:36:15 7f04ac302700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2016-12-24 11:36:15 7f04ac302700 InnoDB: Error: Fetch of persistent statistics requested for table "cibdg"."t1001" 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.
原因:
这是因为数据库初始化的时候,dba可能删除过ibdata1文件
虽然重启之后,数据库会自动创建一个ibdata1文件,但是上述系统表也是innodb引擎,所以不能访问了.
这虽然不会影响业务,但是使用innobackupex备份的时候,会写入错误日志.
最后错误日志里,都是这种信息.影响日常检查。
解决方法:
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个表的报错日志。
- 解决 InnoDB :: InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
- MySQL 5.6 解决InnoDB: Error: Table "mysql"."innodb_table_stats" not found.问题
- InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
- InnoDB: Error: Table "mysql"."innodb_table_stats" not found
- InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
- 解决mysql error: Table “mysql”.“innodb_table_stats” not found
- Error: Table "mysql"."innodb_table_stats" not found
- zabbix configure: error: MySQL library not found
- MySql innodb 出错: Error Code: 1005 Can't create table
- MySQL innodb_table_stats表…
- bash: mysql: command not found 解决
- MySQL SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine 'InnoDB' 错误的原因及解决
- Mysql Innodb死锁解决
- linux安装mysql问题 g++: not found
- -bash: mysql: command not found 问题
- mysql 出现column not found 问题
- mysql: command not found
- command not found mysql
- iOS应用跳转到app store
- [LeetCode] 53. Maximum Subarray
- int a = 10; int b = 20;method(a, b);//需要在method方法被调用之后,仅打印出a=100,b=200,请写出method方法的代码
- python中lxml模块生成xml文件
- GDT(Global Descriptor Table)全局描述符表
- MySQL 5.6 解决InnoDB: Error: Table "mysql"."innodb_table_stats" not found.问题
- HBase常用命令
- error C4430: 缺少类型说明符 - 假定为 int。注意: C++ 不支持默认 int"错误的解决方法
- AIX升级openssh步骤
- 455. Assign Cookies | 分配饼干
- android常用的一些UI调试命令am,pm
- 一个有趣的遭遇:dubbo超时追踪出来的httpclient超时问题
- Android 实现视频播放的两种方式
- 6人执行任务