Had a some conversation with friend he is facing this issue for long back and unable to solve it . But i had an thought why we cant replicate in our test environment and fix this quickly . After a long time had a nice troubleshooting hours
Lets deep dive into the troubleshooting , before enter into the troubleshooting requested few sample output for the same
First things requested to login without grant tables and asked to repair the tables
root@ip-100-23-45-122:sudo mysqld_safe --skip-grant-tables --skip-networking --skip-plugin-load &
root@ip-100-23-45-122:/var/lib/mysql/mysql# mysqlcheck -u root -p --repair --databases mysqlEnter password:mysql.columns_priv OKmysql.db OKmysql.engine_costError : Table 'mysql.engine_cost' doesn't existstatus : Operation failedmysql.event OKmysql.func OKmysql.general_log OKmysql.gtid_executedError : Table 'mysql.gtid_executed' doesn't existstatus : Operation failedmysql.help_categoryError : Table 'mysql.help_category' doesn't existstatus : Operation failedmysql.help_keywordError : Table 'mysql.help_keyword' doesn't existstatus : Operation failedmysql.help_relationError : Table 'mysql.help_relation' doesn't existstatus : Operation failedmysql.help_topicError : Table 'mysql.help_topic' doesn't existstatus : Operation failedmysql.innodb_index_statsError : Table 'mysql.innodb_index_stats' doesn't existstatus : Operation failedmysql.innodb_table_statsError : Table 'mysql.innodb_table_stats' doesn't existstatus : Operation failedmysql.ndb_binlog_index OKmysql.plugin
MySQL ERROR 1146 (42S02): Table 'datablogs.tbl_followup' doesn't exist
So issue not with master table corruption , something different
Troubleshooting Step 2 :
I have gone through so many links and references tried below things as well ,
- Stop the MySQL then Remove ib_logfile0 , ib_logfile1 . Again start the MySQL Server - Its not worked
- Move corrupted ibdata1 file in the newly installed MySQL server and then restart MySQL Server - Its not worked
- More links requested to restart multiple times its very funny thing but tried it will or not - Its also not worked
mysql> create database datablogs;Query OK, 1 row affected (0.01 sec)mysql> use datablogs;Database changedmysql> CREATE TABLE tbl_followup (id int(11) NOT NULL AUTO_INCREMENT,table_header text,action varchar(100) DEFAULT NULL,action_button_text varchar(100) DEFAULT NULL,parent_template text,created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,modified_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;Query OK, 0 rows affected (0.03 sec)mysql> ALTER TABLE datablogs.tbl_followup DISCARD TABLESPACE;Query OK, 0 rows affected (0.01 sec)
Copied only tbl_followup.ibd file from the corrupted server to new server then imported the tablespace
mysql> ALTER TABLE datablogs.tbl_followup IMPORT TABLESPACE;Query OK, 0 rows affected, 1 warning (0.04 sec)mysql> select * from datablogs.tbl_followup;Empty set (0.01 sec)
So overall while reading it very simple but its very hard troubleshooting did in lifetime !!!
Anyway instead of doing multiple solution follow my steps to resolve the issue sortly !!!