MySQL5.7 数据字典表损坏

冷不防 2022-06-15 07:45 226阅读 0赞

在进行xtrabackup备份的时候，出现了mysql用户下的表损坏的情况，用show tables可以看到这些表，但查询报错。然后用源码包中的创建语句脚本重新创建表，还是报错。经查证，这是一个bug。

xtrabackup --defaults-file=/usr/local/mysql/my.cnf --backup --target-dir=/opt/mysql/mysqldata/backup

\-bash-4.1$ xtrabackup\_56 --defaults-file=/usr/local/mysql/my.cnf --backup --target-dir=/opt/mysql/mysqldata/backup  
\-bash: xtrabackup\_56: command not found  
\-bash-4.1$ xtrabackup --defaults-file=/usr/local/mysql/my.cnf --backup --target-dir=/opt/mysql/mysqldata/backup  
  
170519 00:09:41  version\_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql\_read\_default\_group=xtrabackup;port=3306;mysql\_socket=/opt/mysql/mysqldata/3306/mysql.sock' as 'mysql'  (using password: NO).  
Failed to connect to MySQL server as DBD::mysql module is not installed at - line 1327.  
170519 00:09:41 Connecting to MySQL server host: localhost, user: mysql, password: not set, port: 3306, socket: /opt/mysql/mysqldata/3306/mysql.sock  
Using server version 5.6.14-log  
xtrabackup version 2.3.6 based on MySQL server 5.6.24 Linux (x86\_64) (revision id: )  
xtrabackup: uses posix\_fadvise().  
xtrabackup: cd to /opt/mysql/mysqldata/3306/data  
xtrabackup: open files limit requested 10240, set to 10240  
xtrabackup: using the following InnoDB configuration:  
xtrabackup:   innodb\_data\_home\_dir = ./  
xtrabackup:   innodb\_data\_file\_path = ibdata1:2048M:autoextend  
xtrabackup:   innodb\_log\_group\_home\_dir = ./  
xtrabackup:   innodb\_log\_files\_in\_group = 2  
xtrabackup:   innodb\_log\_file\_size = 268435456  
170519 00:09:41 >> log scanned up to (1606489)  
xtrabackup: Generating a list of tablespaces  
InnoDB: Attempted to open a previously opened tablespace. Previous tablespace test/t uses space ID: 1 at filepath: ./test/t.ibd. Cannot open tablespace mysql/innodb\_table\_stats which uses space ID: 1 at filepath: ./mysql/innodb\_table\_stats.ibd  
2017-05-19 00:09:42 7f5444a86720  InnoDB: Operating system error number 2 in a file operation.  
InnoDB: The error means the system cannot find the path specified.  
InnoDB: Error: could not open single-table tablespace file ./mysql/innodb\_table\_stats.ibd  
InnoDB: We do not continue the crash recovery, because the table may become  
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.  
InnoDB: To fix the problem and start mysqld:  
InnoDB: 1) If there is a permission problem in the file and mysqld cannot  
InnoDB: open the file, you should modify the permissions.  
InnoDB: 2) If the table is not needed, or you can restore it from a backup,  
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal  
InnoDB: crash recovery and ignore that table.  
InnoDB: 3) If the file system or the disk is broken, and you cannot remove  
InnoDB: the .ibd file, you can set innodb\_force\_recovery > 0 in my.cnf  
InnoDB: and force InnoDB to continue crash recovery here.  
  
  
mysql日志里面有：  
2017-05-18 19:15:29 7fe4fc093700 InnoDB: Error: Table "mysql"."innodb\_table\_stats" not found.  
2017-05-18 19:15:34 7fe4fc093700 InnoDB: Error: Table "mysql"."innodb\_table\_stats" not found.  
2017-05-18 19:15:34 7fe4fc093700 InnoDB: Error: Fetch of persistent statistics requested for table "test"."t" 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.  
2017-05-18 19:15:48 7fe4843f9700 InnoDB: Error: Table "mysql"."innodb\_table\_stats" not found.  
  
查询报错。  
mysql> select count(\*)from innodb\_index\_stats        ;  
ERROR 1146 (42S02): Table 'mysql.innodb\_index\_stats' doesn't exist  
mysql> select count(\*)from innodb\_table\_stats        ;  
ERROR 1146 (42S02): Table 'mysql.innodb\_table\_stats' doesn't exist  
mysql> select count(\*)from slave\_master\_info         ;  
ERROR 1146 (42S02): Table 'mysql.slave\_master\_info' doesn't exist  
mysql> select count(\*)from slave\_relay\_log\_info      ;  
ERROR 1146 (42S02): Table 'mysql.slave\_relay\_log\_info' doesn't exist  
mysql> select count(\*)from slave\_worker\_info         ;

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

按下列步骤执行：

/\*   
  temporary fix for problem with windows installer for MySQL 5.6.10 on Windows 7 machines.  
  I did the procedure on a clean installed MySql, and it worked for me, at least it stopped  
  lines of innodb errors in the log and the use of transient innodb tables. So, do it at  
  your own risk..  
    
  1. drop these tables from mysql:  
     innodb\_index\_stats  
     innodb\_table\_stats  
     slave\_master\_info  
     slave\_relay\_log\_info  
     slave\_worker\_info  
   
  2. delete all .frm & .ibd of the tables above.  
    
  3. run this file to recreate the tables above (source five-tables.sql).  
    
  4. restart mysqld.  
    
  Cheers,   
  CNL  
\*/  
  
  
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';