MYSQL主从复制中InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 问题修复

1. 安装完成后表使用没有什么问题
2. 做了主从复制后表被损坏
3. 查资料寻找答案

5个表损坏

innodb_index_stats,

innodb_tables_stats,

slave_master_info,

slave_relay_log_info,

slave_worker_info

innodb_index_stats,

innodb_tables_stats,

表引擎为 InnoDB

提取初始化SQL 表创建语句 /usr/local/mysql/share/mysql_system_tables.sql

innodb_index_stats,

innodb_tables_stats,

表引擎为 InnoDB

slave_master_info,

slave_relay_log_info,

slave_worker_info

表引擎为 MyISAM

表修复后 注意检查mysql数据库的表文件权限 需要为 755，否则数据无法写入

修复过程如下：

2.问题产生原因：具体原因目前不详，网上查找到的资料:数据库打开这几张表的默认引擎为MyISAM，但是这几张表在建表时的引擎为INNODB

但是能确定的，这几张表确实是在mysql5.6中新入的

innodb_index_stats,

innodb_tables_stats,

slave_master_info,

slave_relay_log_info,

slave_worker_info

3.解决方法：

(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信息

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

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

[meihuo-yunwei@iZ232hat4a3Z mysql]ls .ibd

innodb_index_stats.ibd innodb_table_stats.ibd slave_master_info.ibd slave_relay_log_info.ibd slave_worker_info.ibd

[meihuo-yunwei@iZ232hat4a3Z mysql]rm -f .ibd

(3) 重新启动数据库，进入到mysql库，重建上面被删除的表结构：

数据库的建设表脚本在mysql软件的安装目录的share目录下，我的mysql软件的安装路径为  /usr/local/mysql/share/mysql_system_tables.sql

admin@localhost : (none) 02:23:03> use mysql

Database changed

admin@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)

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

admin@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张表相关的错误，到此，问题全部解决！

修复用sql脚本：

CREATE TABLE IF NOT EXISTS innodb_table_stats (

database_name                        VARCHAR(64) NOT NULL,

table_name                        VARCHAR(64) NOT NULL,

last_update                        TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

n_rows                                BIGINT UNSIGNED NOT NULL,

clustered_index_size                BIGINT UNSIGNED NOT NULL,

sum_of_other_index_sizes        BIGINT UNSIGNED NOT NULL,

PRIMARY KEY (database_name, table_name)

) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

CREATE TABLE IF NOT EXISTS innodb_index_stats (

database_name                        VARCHAR(64) NOT NULL,

table_name                        VARCHAR(64) NOT NULL,

index_name                        VARCHAR(64) NOT NULL,

last_update                        TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

/* there are at least:

stat_name='size'

stat_name='n_leaf_pages'

stat_name='n_diff_pfx%' */

stat_name                        VARCHAR(64) NOT NULL,

stat_value                        BIGINT UNSIGNED NOT NULL,

sample_size                        BIGINT UNSIGNED,

stat_description                VARCHAR(1024) 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 IF NOT EXISTS slave_relay_log_info (

  Number_of_lines INTEGER 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 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 UNSIGNED NOT NULL COMMENT 'The master log position of the last executed event.',

  Sql_delay INTEGER NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',

  Number_of_workers INTEGER UNSIGNED NOT NULL,

  Id INTEGER UNSIGNED NOT NULL COMMENT 'Internal Id that uniquely identifies this record.', 

  PRIMARY KEY(Id)) DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT 'Relay Log Information';

CREATE TABLE IF NOT EXISTS slave_master_info (

  Number_of_lines INTEGER 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 UNSIGNED NOT NULL COMMENT 'The master log position of the last read event.',

  Host CHAR(64) CHARACTER SET utf8 COLLATE utf8_bin 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 INTEGER UNSIGNED NOT NULL COMMENT 'The network port used to connect to the master.',

  Connect_retry INTEGER UNSIGNED NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',

  Enabled_ssl BOOLEAN 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 BOOLEAN NOT NULL COMMENT 'Whether to verify the server certificate.',

  Heartbeat FLOAT NOT NULL COMMENT '',

  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 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 BOOLEAN NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',

  PRIMARY KEY(Host, Port)) DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT 'Master Information';

CREATE TABLE IF NOT EXISTS slave_worker_info (

  Id INTEGER UNSIGNED NOT NULL,

  Relay_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  Relay_log_pos BIGINT UNSIGNED NOT NULL,

  Master_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  Master_log_pos BIGINT UNSIGNED NOT NULL,

  Checkpoint_relay_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  Checkpoint_relay_log_pos BIGINT UNSIGNED NOT NULL,

  Checkpoint_master_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  Checkpoint_master_log_pos BIGINT UNSIGNED NOT NULL,

  Checkpoint_seqno INT UNSIGNED NOT NULL,

  Checkpoint_group_size INTEGER UNSIGNED NOT NULL,

  Checkpoint_group_bitmap BLOB NOT NULL,

  PRIMARY KEY(Id)) DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT 'Worker Information';