Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to deal with the damage to the mysql innodb system table in Mysql 5.7.20, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand how to deal with the damage to the mysql innodb system table in Mysql 5.7.20.

After going to work in the morning, mysql CVM encountered a minor problem. In the process of troubleshooting and checking the mysql error log, it was found that several innodb tables could not be opened. Using desc to check the table structure of the table indicates that the table does not exist. Show tables can find the following five tables. The following is the specific error information:

2018-01-12 09:17:41 17235 [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.

2018-01-12 09:17:41 17235 [Warning] InnoDB: Cannot open table mysql/innodb_table_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.

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 you 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 you 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 you can resolve the problem.

Check the information on the Internet, the cause of the problem: the default engine for the database to open these tables is MyISAM, but when these tables are created, the engine is INNODB;, which causes mysql to report an 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 began to prepare to solve the above problems. The solution is to delete the problematic tables and data files, use the official table creation script that installed msyql, and recreate the five problematic tables.

The steps are as follows

1. Log in to the database to perform the following operations, sql statement plus if judgment, and delete if the table exists

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 the execution result. Whether the above table has been deleted.

2. Stop the mysql database service, go to the directory where the database data file is located, delete the idb file corresponding to the above five tables, linux system environment, the basedir directory of my msyql is / usr/local/mysql/

The datadir directory is / data/mysql/var/mysql/

[root@mysql5] # systemctl restart stop

[root@sql5 root] # cd / data/mysql/var/mysql/

[root@sql5 mysql] # ls-l * .ibd

-rw-rw---- 1 mysql mysql 98304 March 7 2017 innodb_index_stats.ibd

-rw-rw---- 1 mysql mysql 98304 March 7 2017 innodb_table_stats.ibd

-rw-rw---- 1 mysql mysql 98304 March 7 2017 slave_master_info.ibd

-rw-rw---- 1 mysql mysql 98304 March 7 2017 slave_relay_log_info.ibd

-rw-rw---- 1 mysql mysql 98304 March 7 2017 slave_worker_info.ibd

[root@sql5 mysql] # rm-rf * .ibd

3. Restart the mysql service and rebuild the table structure of the deleted five tables. The table-building script can be found in the share directory of the mysql software installation directory or the script directory of the mysql installation package.

[root@mysql5 mysql] # cd / usr/local/mysql/share/

[root@mysql5 share] # ls-l * .sql / / View all table creation scripts

-rw-r--r--. 1 root root 932622 September 13 23:56 fill_help_tables.sql

-rw-r--r--. 1 root root 3999 September 13 23:48 innodb_memcached_config.sql

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

-rw-r--r--. 1 root root 1760 September 13 23:48 mysql_security_commands.sql

-rw-r--r--. 1 root root 287110 September 13 23:48 mysql_sys_schema.sql

-rw-r--r--. 1 root root 811 September 13 23:48 mysql_system_tables_data.sql

-rw-r--r--. 1 root root 154624 September 13 23:48 mysql_system_tables.sql

-rw-r--r--. 1 root root 10410 September 13 23:48 mysql_test_data_timezone.sql

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

[root@mysql5 share] # systemctl restart mysqld

Mysql > USE MYSQL

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

5 tables deleted by mysql > show tables; have been restored

Mysql > DESC innodb_table_stats

The viewing process of the remaining four table structures is brief.

After checking the error log of mysql, the error message is gone.

[root@mysql5 share] # tail / data/mysql/var/mysqld.err

After reading the above about how to deal with mysql innodb system table damage in Mysql 5.7.20, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.