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

Recently, during the inspection, I found the following error in the mysql error log:

1779986 2015-10-06 00:06:37 7f5215214700 InnoDB: Error: Table "mysql". "innodb_table_stats" not found.1779987 2015-10-06 00:06:37 7f5215214700 InnoDB: Recalculation of persistent statistics requested for table "xxxx". "xxxxxx" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead. After investigation, it was found that several system tables of mysql were missing. MySQL 5.6's ibdata1 tablespace contains five InnoDB base tables, as follows:mysql> select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB';+----------------------------+| table_name |+----------------------+| innodb_index_stats || innodb_table_stats || slave_master_info || slave_relay_log_info || slave_worker_info |+----------------------+5 rows in set (0.00 sec) Before MySQL 5.6, if you delete ibdata1 after closing MySQL and restart MySQL, ibdata1 will be recreated, but since MySQL 5.6, these five tables will not be rebuilt. The reason is that the storage engine of these tables is innodb, and these tables are not available in MySQL 5.5. Let's look at the differences between 5.6 and 5.5:

MySQL 5.5 System Tables +--------------------------+| columns_priv || db || event || func || help_category || help_keyword || help_relation || help_topic || host || ndb_binlog_index || plugin || proc || procs_priv || proxies_priv || servers || tables_priv || time_zone || time_zone_leap_second || time_zone_name || time_zone_transition || time_zone_transition_type || user |+-----------------------------+22 rows in set (0.00 sec)MySQL 5.6 System 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) From the above statistics, it can be seen that in mysql 5.6, in addition to the above five tables, general_log and slow_log tables are added, and the host table in 5.5 is merged into the user table in 5.6.

Tracing the cause in the error log, we found that the ibdata1 file was deleted during a failure migration, and the ibdata1 file was automatically created after restarting, but these innodb system tables were not loaded into the table space. Although these errors do not affect business, they affect backups very much. They appear in large numbers in the error log, which is also very annoying.

So how do we deal with such mistakes?

1, if it is slave standby machine, stop copying, record pos and other information (for not copying mysql library, there is no need to do this step) mysql>stop slave ;

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 ;

Delete the related.frm and.ibd files

rm -rf innodb_*_stats* rm -rf slave_*

4. Re-create the above system table

From another machine, use mysqldump to back up the table structure of these tables and execute it again on this machine. The backup script is as follows: #!/ bin/shTABLELIST="innodb_index_stats"TABLELIST="${TABLELIST} innodb_table_stats"TABLELIST="${TABLELIST} slave_master_info"TABLELIST ="${TABLELIST} slave_relay_log_info" TABLELIST ="${TABLELIST} slave_worker_info"mysqldump -uroot -p mysql ${TABLELIST} > mysql_innodb_tables.sql Copy mysql_innodb_tables.sql to the database machine with the bad table error and import it into the system library mysql library.# mysql -uroot -p mysql < mysql_innodb_tables.sql Run FLUSH TABLES(optional)5. Restart the database 6. Start slave. If an error is reported, change master again 7. Reconfirm whether there is a similar error in the error log.

Note: Do not easily delete the ibdata1 file, in different versions, different branches, system tables may not be the same, such as mysql 5.6 and MariaDB 10.0 system tables, mysql 5.5 and mysql 5.6 system tables, etc., in the migration or upgrade, it is easy to ignore these details, so before doing, be sure to backup, do not willful.

Reference article: bugs.mysql.com/bug.php? id=67179

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.