MySQL innodb_table_stats表不存在的解决方法

喜欢ヅ旅行 2022-11-28 15:08 170阅读 0赞

在做实验时，使用mysqldump命令报错

\[root@linux-mysql02 3306\]\# mysqldump -uroot -p123456 -S /u02/data/3306/mysql.sock -A -B --events | gzip > /opt/rep.sql.gz

mysqldump: Got error: 1146: Table 'mysql.innodb\_index\_stats' doesn't exist when using LOCK TABLES

然后查看错误日志：

\[root@linux-mysql02 ~\]\# vim /u02/data/3306/mysql\_oldboy3306.err

2017-08-09 15:21:10 19726 \[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.

\[root@linux-mysql02 ~\]\# mysql -uroot -p123456 -S /u02/data/3306/mysql.sock

mysql> use mysql

mysql> show tables;

mysql> show create table slave\_master\_info\\G

ERROR 1146 (42S02): Table 'mysql.slave\_master\_info' doesn't exist

最后测试发现以下几个系统表不存在.  
innodb\_table\_stats  
innodb\_index\_stats  
slave\_master\_info  
slave\_relay\_log\_info  
slave\_worker\_info

这是因为数据库初始化的时候,dba可能删除过ibdata1文件  
虽然重启之后,数据库会自动创建一个ibdata1文件,但是上述系统表也是innodb引擎,所以不能访问了.  
这虽然不会影响业务,但是使用innobackupex备份的时候,会写入错误日志.  
最后错误日志里,都是这种信息.影响日常检查。

解决的方法.  
1.删除上述系统表  
drop table mysql.innodb\_index\_stats;  
drop table mysql.innodb\_table\_stats;  
drop table mysql.slave\_master\_info;  
drop table mysql.slave\_relay\_log\_info;  
drop table mysql.slave\_worker\_info;

2.删除相关的.frm .ibd文件  
rm -f innodb\_index\_stats\*  
rm -f innodb\_table\_stats\*  
rm -f slave\_master\_info\*  
rm -f slave\_relay\_log\_info\*  
rm -f slave\_worker\_info\*

3.重新创建上述系统表  
CREATE TABLE \`innodb\_index\_stats\` (  
  \`database\_name\` varchar(64) COLLATE utf8\_bin NOT NULL,  
  \`table\_name\` varchar(64) COLLATE utf8\_bin NOT NULL,  
  \`index\_name\` varchar(64) COLLATE utf8\_bin NOT NULL,  
  \`last\_update\` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP,  
  \`stat\_name\` varchar(64) COLLATE utf8\_bin NOT NULL,  
  \`stat\_value\` bigint(20) unsigned NOT NULL,  
  \`sample\_size\` bigint(20) unsigned DEFAULT NULL,  
  \`stat\_description\` varchar(1024) COLLATE utf8\_bin 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 \`innodb\_table\_stats\` (  
  \`database\_name\` varchar(64) COLLATE utf8\_bin NOT NULL,  
  \`table\_name\` varchar(64) COLLATE utf8\_bin NOT NULL,  
  \`last\_update\` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP,  
  \`n\_rows\` bigint(20) unsigned NOT NULL,  
  \`clustered\_index\_size\` bigint(20) unsigned NOT NULL,  
  \`sum\_of\_other\_index\_sizes\` bigint(20) unsigned NOT NULL,  
  PRIMARY KEY (\`database\_name\`,\`table\_name\`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8\_bin STATS\_PERSISTENT=0;

CREATE TABLE \`slave\_master\_info\` (  
  \`Number\_of\_lines\` int(10) 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(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',  
  \`Host\` char(64) CHARACTER SET utf8 COLLATE utf8\_bin NOT NULL DEFAULT '' 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\` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',  
  \`Connect\_retry\` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',  
  \`Enabled\_ssl\` tinyint(1) 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\` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',  
  \`Heartbeat\` float NOT NULL,  
  \`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(20) 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\` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',  
  PRIMARY KEY (\`Host\`,\`Port\`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS\_PERSISTENT=0 COMMENT='Master Information';

CREATE TABLE \`slave\_relay\_log\_info\` (  
  \`Number\_of\_lines\` int(10) 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(20) 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(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',  
  \`Sql\_delay\` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',  
  \`Number\_of\_workers\` int(10) unsigned NOT NULL,  
  \`Id\` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',  
  PRIMARY KEY (\`Id\`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS\_PERSISTENT=0 COMMENT='Relay Log Information';

CREATE TABLE \`slave\_worker\_info\` (  
  \`Id\` int(10) unsigned NOT NULL,  
  \`Relay\_log\_name\` text CHARACTER SET utf8 COLLATE utf8\_bin NOT NULL,  
  \`Relay\_log\_pos\` bigint(20) unsigned NOT NULL,  
  \`Master\_log\_name\` text CHARACTER SET utf8 COLLATE utf8\_bin NOT NULL,  
  \`Master\_log\_pos\` bigint(20) unsigned NOT NULL,  
  \`Checkpoint\_relay\_log\_name\` text CHARACTER SET utf8 COLLATE utf8\_bin NOT NULL,  
  \`Checkpoint\_relay\_log\_pos\` bigint(20) unsigned NOT NULL,  
  \`Checkpoint\_master\_log\_name\` text CHARACTER SET utf8 COLLATE utf8\_bin NOT NULL,  
  \`Checkpoint\_master\_log\_pos\` bigint(20) unsigned NOT NULL,  
  \`Checkpoint\_seqno\` int(10) unsigned NOT NULL,  
  \`Checkpoint\_group\_size\` int(10) unsigned NOT NULL,  
  \`Checkpoint\_group\_bitmap\` blob NOT NULL,  
  PRIMARY KEY (\`Id\`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS\_PERSISTENT=0 COMMENT='Worker Information';

4.如果是Slave备机,停止复制,记录pos等信息  
mysql> stop slave;  
Query OK, 0 rows affected (0.09 sec)

5.重启数据库

6.如果Slave启动报错,则使用步骤4的信息,重新change master

再删除ibdata1文件的时候,一定要想起系统数据库还有5张innodb表...