MySQL 5.7.20 mysql innodb system table damage caused by the problem

After work in the morning, MySQL server encountered a small problem, during the troubleshooting process to view the MySQL error log process found that several InnoDB tables could not be opened, using DESC to view the table structure hints table on the table does not exist, show tables can find a few five tables, The following is a specific error message:

2018-01-12 09:17:41 17235 [Warning] innodb:cannot Open Table mysql/innodb_index_stats from the internal data dictionary O F InnoDB though the. frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how can resolve the problem.

2018-01-12 09:17:41 17235 [Warning] innodb:cannot Open Table mysql/innodb_table_stats from the internal data dictionary O F InnoDB though the. frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how can resolve the problem.

2018-01-12 09:17:41 17235 [Warning] innodb:cannot Open Table mysql/slave_master_info 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 can resolve the problem.

2018-01-12 09:17:41 17235 [Warning] innodb:cannot Open Table mysql/slave_relay_log_info 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 can resolve the problem.

2018-01-12 09:17:41 17235 [Warning] innodb:cannot Open Table mysql/slave_worker_info 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 can resolve the problem.

Online Check the information, the problem of the cause: the database opens the default engine for these tables is MyISAM, but these tables in the table when the engine is INNODB,resulting in MySQL error

MSYQL introduced the following five tables in version 5.6

Innodb_index_stats,

Innodb_tables_stats,

Slave_master_info,

Slave_relay_log_info,

Slave_worker_info

After locating the cause of the problem, we begin to prepare to resolve the problem, and the idea is to remove the problematic tables and data files, and recreate the 5 problematic tables using the official self-built table script that installs MSYQL.

Here are the steps:

1, log in the database to do the following,SQL statement plus if judge, if the table exists, then delete

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;

Mysql> Show tables; Verify execution results, the table above has been deleted

2. Stop the MySQL database service and go to the directory where the database data files are located, delete The IDB files corresponding to the 5 tables above , the Linux system environment, The Basedir directory of my msyql is /usr/local/mysql/

DataDir directory is /data/mysql/var/mysql/

[[email protected]]# systemctl restart stop

[Email protected] root]# cd/data/mysql/var/mysql/

[Email protected] mysql]# ls-l *.IBD

-RW-RW----1 mysql mysql 98304 3 months 7 innodb_index_stats.ibd

-RW-RW----1 mysql mysql 98304 3 months 7 innodb_table_stats.ibd

-RW-RW----1 mysql mysql 98304 3 months 7 slave_master_info.ibd

-RW-RW----1 mysql mysql 98304 3 months 7 slave_relay_log_info.ibd

-RW-RW----1 mysql mysql 98304 3 months 7 slave_worker_info.ibd

[[email protected] MySQL] #rm-rf *.ibd

3, Restart the MySQL service, and rebuild the table structure of the five tables that were deleted, Build the table script in the installation directory of the MySQL software under the Share directory or in the script directory of the MySQL installation package

[Email protected] mysql]# cd/usr/local/mysql/share/

[[email protected] share]# ls-l *.sql// Look up all the build table scripts

-rw-r--r--. 1 root root 932622 9 months 23:56 fill_help_tables.sql

-rw-r--r--. 1 root root 3999 9 months 23:48 innodb_memcached_config.sql

-rw-r--r--. 1 root root 1812 month 7 11:42 install_rewriter.sql

-rw-r--r--. 1 root root 1760 9 months 23:48 mysql_security_commands.sql

-rw-r--r--. 1 root root 287110 9 months 23:48 mysql_sys_schema.sql

-rw-r--r--. 1 root root 811 9 months 23:48 mysql_system_tables_data.sql

-rw-r--r--. 1 root root 154624 9 months 23:48 mysql_system_tables.sql

-rw-r--r--. 1 root root 10410 9 months 23:48 mysql_test_data_timezone.sql

-rw-r--r--. 1 root root 834 month 7 11:42 uninstall_rewriter.sql

[Email protected] share]# systemctl restart mysqld

mysql> use MYSQL;

Mysql> Source/usr/local/mysql/share/innodb_memcached_config.sql

Mysql> Show tables; Delete the 5 table has been restored

Mysql> DESC innodb_table_stats;

The remaining four table structures are viewed in a slightly

in view of the MySQL error log, there is no message

[Email protected] share]# Tail/data/mysql/var/mysqld.err

MySQL 5.7.20 mysql innodb system table damage caused by the problem