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

本贴最后更新于 565 天前,其中的信息可能已经时移世改
  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';

 

 

 

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    547 引用 • 504 回帖 • 701 关注
  • 集群
    27 引用 • 65 回帖
  • 运维

    互联网运维工作,以服务为中心,以稳定、安全、高效为三个基本点,确保公司的互联网业务能够 7×24 小时为用户提供高质量的服务。

    119 引用 • 239 回帖 • 3 关注

赞助商 我要投放

回帖
请输入回帖内容 ...