Not the answer you need?
Register and ask your own question!

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

dchambersdchambers EntrantInactive User Role Beginner
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 http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html 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:
http://dba.stackexchange.com/questions/48166/cannot-open-table-mysql-innodb-index-stats/48189#48189

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?

Comments

  • dchambersdchambers Entrant Inactive User Role Beginner
    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.
  • dchambersdchambers Entrant Inactive User Role Beginner
    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
  • dchambersdchambers Entrant Inactive User Role Beginner
    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!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.