XtraBackup | Replication | Table 'mysql.slave_master_info' cannot be opened.

[dchambers]

Hello - I am looking for some advise, I used Xtrabackup with no issues, fantastic bit of kit.
I took a backup of my database from the master and then moved the files over to the slave without issue

It’s running but I am unable to get replication working now, it had worked before but the DB was fluffed!

2015-07-13 00:29:02 57003 [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 [url]http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html[/url] for how you can resolve the problem.
2015-07-13 00:29:02 57003 [Warning] Info table is not ready to be used. Table ‘mysql.slave_master_info’ cannot be opened.
2015-07-13 00:29:02 57003 [ERROR] Error in checking mysql.slave_master_info repository info type of TABLE.
2015-07-13 00:29:02 57003 [ERROR] Error creating master info: Error checking repositories.
2015-07-13 00:29:02 57003 [ERROR] Failed to create or recover replication info repository.
2015-07-13 00:29:02 57003 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved.

It can not read the following tables for some reason:

| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |

mysql> SELECT * from slave_worker_info;
ERROR 1146 (42S02): Table ‘mysql.slave_worker_info’ doesn’t exist
mysql>

The issue is extremely similar to to:
[url]innodb - Cannot open table mysql/innodb_index_stats - Database Administrators Stack Exchange

I have tried to drop these and re-create them but it keeps saying it exists.
I have dropped the tables, deleted the files from /var/lib/mysql/mysql and then ran the mysql_install_db

rm -rf slave_master_info.ibd slave_relay_log_info.frm slave_relay_log_info.ibd slave_worker_info.frm slave_worker_info.ibd slave_master_info.frm

I have also tried the above steps, minus the install_db and tried to create the tables however MySQL says the tables already exist?

They are no longer showing below but I cant create them?! as it says they exist

mysql> use mysql;
Database changed
mysql> show tables;
±--------------------------+
| Tables_in_mysql |
±--------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
±--------------------------+
24 rows in set (0.00 sec)

mysql> 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;
ERROR 1050 (42S01): Table ‘mysql.innodb_index_stats’ already exists

Can anyone help me?

[dchambers]

2015-04-09 13:05:09 24242 [ERROR] Error in checking mysql.slave_master_info repository info type of TABLE.
2015-04-09 13:05:09 24242 [ERROR] Error creating master info: Error checking repositories.
2015-04-09 13:05:09 24242 [ERROR] Failed to create or recover replication info repository.
2015-04-09 13:05:09 24242 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted.

[dchambers]

All files are owned by mysql:mysql

drwx------ 2 mysql mysql 4.0K Apr 8 04:50 common_schema
drwx------ 2 mysql mysql 2.9M Jul 12 22:49 database
-rw-r----- 1 mysql mysql 1.2G Jul 14 16:03 ibdata1
-rw-r–r-- 1 mysql mysql 256M Jul 14 16:03 ib_logfile0
-rw-r–r-- 1 mysql mysql 256M Jul 12 22:50 ib_logfile1
-rw-rw---- 1 mysql mysql 256M Jul 13 00:17 ib_logfile101
drwx------ 2 mysql mysql 4.0K Jul 14 16:03 mysql
-rw-rw---- 1 mysql mysql 6 Jul 13 02:32 mysql.pid
-rw-rw---- 1 mysql mysql 685M Sep 17 2014 mysql-slow.log
srwxrwxrwx 1 mysql mysql 0 Jul 13 02:32 mysql.sock
-rw-r–r-- 1 mysql mysql 11 Sep 30 2014 mysql_upgrade_info
drwx------ 2 mysql mysql 4.0K Sep 30 2014 performance_schema
-rw-rw---- 1 mysql mysql 162 Jul 13 00:08 relaylog.000005
-rw-rw---- 1 mysql mysql 69M Jul 13 00:11 relaylog.000006
-rw-rw---- 1 mysql mysql 139 Jul 13 00:12 relaylog.000007
-rw-rw---- 1 mysql mysql 54 Jul 13 00:11 relaylog.index
-rw-r–r-- 1 mysql mysql 388 Sep 30 2014 RPM_UPGRADE_HISTORY
-rw-r–r-- 1 mysql mysql 112 Sep 30 2014 RPM_UPGRADE_MARKER-LAST
drwx------ 2 mysql mysql 4.0K Jul 13 01:35 test
drwx------ 2 mysql mysql 4.0K Apr 8 04:50 time_survey
-rw-r–r-- 1 root root 24 Jul 12 22:48 xtrabackup_binlog_info
-rw-r–r-- 1 root root 24 Jul 12 22:50 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 101 Jul 12 22:50 xtrabackup_checkpoints
-rw-r–r-- 1 root root 573 Jul 12 22:48 xtrabackup_info
-rw-r----- 1 root root 18M Jul 12 22:50 xtrabackup_logfile

-rw-rw---- 1 mysql mysql 8.7K Apr 8 04:50 columns_priv.frm
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 columns_priv.MYD
-rw-rw---- 1 mysql mysql 4.0K Apr 8 04:50 columns_priv.MYI
-rw-rw---- 1 mysql mysql 9.4K Apr 8 04:50 db.frm
-rw-rw---- 1 mysql mysql 13K May 20 12:13 db.MYD
-rw-rw---- 1 mysql mysql 5.0K May 21 14:06 db.MYI
-rw-rw---- 1 mysql mysql 10K Apr 8 04:50 event.frm
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 event.MYD
-rw-rw---- 1 mysql mysql 2.0K Apr 8 04:50 event.MYI
-rw-rw---- 1 mysql mysql 8.5K Apr 8 04:50 func.frm
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 func.MYD
-rw-rw---- 1 mysql mysql 1.0K Apr 8 04:50 func.MYI
-rw-rw---- 1 mysql mysql 35 Jul 13 01:42 general_log.CSM
-rw-rw---- 1 mysql mysql 0 Sep 30 2014 general_log.CSV
-rw-rw---- 1 mysql mysql 8.6K Sep 30 2014 general_log.frm
-rw-rw---- 1 mysql mysql 8.5K Apr 8 04:50 help_category.frm
-rw-rw---- 1 mysql mysql 23K Apr 8 04:50 help_category.MYD
-rw-rw---- 1 mysql mysql 3.0K Apr 8 04:50 help_category.MYI
-rw-rw---- 1 mysql mysql 8.5K Apr 8 04:50 help_keyword.frm
-rw-rw---- 1 mysql mysql 88K Apr 8 04:50 help_keyword.MYD
-rw-rw---- 1 mysql mysql 16K Apr 8 04:50 help_keyword.MYI
-rw-rw---- 1 mysql mysql 8.5K Apr 8 04:50 help_relation.frm
-rw-rw---- 1 mysql mysql 8.9K Apr 8 04:50 help_relation.MYD
-rw-rw---- 1 mysql mysql 16K Apr 8 04:50 help_relation.MYI
-rw-rw---- 1 mysql mysql 8.6K Apr 8 04:50 help_topic.frm
-rw-rw---- 1 mysql mysql 446K Apr 8 04:50 help_topic.MYD
-rw-rw---- 1 mysql mysql 19K Apr 8 04:50 help_topic.MYI
-rw-rw---- 1 mysql mysql 9.3K Apr 8 04:50 host.frm
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 host.MYD
-rw-rw---- 1 mysql mysql 2.0K Apr 8 04:50 host.MYI
-rw-rw---- 1 mysql mysql 8.6K Apr 8 04:50 ndb_binlog_index.frm
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 ndb_binlog_index.MYD
-rw-rw---- 1 mysql mysql 1.0K Apr 8 04:50 ndb_binlog_index.MYI
-rw-rw---- 1 mysql mysql 8.4K Apr 8 04:50 plugin.frm
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 plugin.MYD
-rw-rw---- 1 mysql mysql 1.0K Apr 8 04:50 plugin.MYI
-rw-rw---- 1 mysql mysql 9.8K Apr 8 04:50 proc.frm
-rw-rw---- 1 mysql mysql 518K Apr 8 04:50 proc.MYD
-rw-rw---- 1 mysql mysql 12K Apr 8 04:50 proc.MYI
-rw-rw---- 1 mysql mysql 8.7K Apr 8 04:50 procs_priv.frm
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 procs_priv.MYD
-rw-rw---- 1 mysql mysql 4.0K Apr 8 04:50 procs_priv.MYI
-rw-rw---- 1 mysql mysql 8.6K Apr 8 04:50 proxies_priv.frm
-rw-rw---- 1 mysql mysql 693 Apr 8 04:50 proxies_priv.MYD
-rw-rw---- 1 mysql mysql 5.0K Apr 8 04:50 proxies_priv.MYI
-rw-rw---- 1 mysql mysql 8.7K Apr 8 04:50 servers.frm
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 servers.MYD
-rw-rw---- 1 mysql mysql 1.0K Apr 8 04:50 servers.MYI
-rw-rw---- 1 mysql mysql 35 Jul 13 01:42 slow_log.CSM
-rw-rw---- 1 mysql mysql 0 Sep 30 2014 slow_log.CSV
-rw-rw---- 1 mysql mysql 8.9K Sep 30 2014 slow_log.frm
-rw-rw---- 1 mysql mysql 8.8K Apr 8 04:50 tables_priv.frm
-rw-rw---- 1 mysql mysql 21K Apr 8 04:50 tables_priv.MYD
-rw-rw---- 1 mysql mysql 8.0K Apr 8 04:50 tables_priv.MYI
-rw-rw---- 1 mysql mysql 8.5K Apr 8 04:50 time_zone.frm
-rw-rw---- 1 mysql mysql 8.5K Apr 8 04:50 time_zone_leap_second.frm
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 time_zone_leap_second.MYD
-rw-rw---- 1 mysql mysql 1.0K Apr 8 04:50 time_zone_leap_second.MYI
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 time_zone.MYD
-rw-rw---- 1 mysql mysql 1.0K Apr 8 04:50 time_zone.MYI
-rw-rw---- 1 mysql mysql 8.5K Apr 8 04:50 time_zone_name.frm
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 time_zone_name.MYD
-rw-rw---- 1 mysql mysql 1.0K Apr 8 04:50 time_zone_name.MYI
-rw-rw---- 1 mysql mysql 8.5K Apr 8 04:50 time_zone_transition.frm
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 time_zone_transition.MYD
-rw-rw---- 1 mysql mysql 1.0K Apr 8 04:50 time_zone_transition.MYI
-rw-rw---- 1 mysql mysql 8.6K Apr 8 04:50 time_zone_transition_type.frm
-rw-rw---- 1 mysql mysql 0 Apr 8 04:50 time_zone_transition_type.MYD
-rw-rw---- 1 mysql mysql 1.0K Apr 8 04:50 time_zone_transition_type.MYI
-rw-rw---- 1 mysql mysql 11K Apr 8 04:50 user.frm
-rw-rw---- 1 mysql mysql 5.9K Jul 2 12:14 user.MYD
-rw-rw---- 1 mysql mysql 4.0K Jul 2 20:45 user.MYI

[dchambers]

Even those all this throws errors… This fixed it!

drop table slave_master_info;
ALTER TABLE slave_master_info DISCARD TABLESPACE;

drop table slave_relay_log_info;
ALTER TABLE slave_relay_log_info DISCARD TABLESPACE;

drop table slave_worker_info;
ALTER TABLE slave_worker_info DISCARD TABLESPACE;

rm slave_master_info
rm slave_relay_log_info
rm slave_worker_info

mysql mysql < /root/pulsant/broken-tables.sql

The import is just a dump from a working DB of the above tables… 6 hours boom!