- 安装完成后表使用没有什么问题
- 做了主从复制后表被损坏
- 查资料寻找答案
5个表损坏
innodb_index_stats,
innodb_tables_stats,
slave_master_info,
slave_relay_log_info,
slave_worker_info
innodb_index_stats,
innodb_tables_stats,
表引擎为 InnoDB
提取初始化SQL 表创建语句 /usr/local/mysql/share/mysql_system_tables.sql
innodb_index_stats,
innodb_tables_stats,
表引擎为 InnoDB
slave_master_info,
slave_relay_log_info,
slave_worker_info
表引擎为 MyISAM
表修复后 注意检查mysql数据库的表文件权限 需要为 755，否则数据无法写入
修复过程如下：
2.问题产生原因：具体原因目前不详，网上查找到的资料:数据库打开这几张表的默认引擎为MyISAM，但是这几张表在建表时的引擎为INNODB
但是能确定的，这几张表确实是在mysql5.6中新入的
innodb_index_stats,
innodb_tables_stats,
slave_master_info,
slave_relay_log_info,
slave_worker_info
3.解决方法：
(1) 登录数据库，进入mysql库，执行如下SQL删除5张表
记住，一定要是drop table if exists
drop table if exists innodb_index_stats;
drop table if exists innodb_table_stats;
drop table if exists slave_master_info;
drop table if exists slave_relay_log_info;
drop table if exists slave_worker_info;
如下是执行的结果，忽略你看到的Warning信息
执行完后，可以用show tables查看一下，看表的数据是否已经比删除之前减少了，如果减少了，说明你成功了！
(2)面这一部操作完成后，停止数据库，并进入到数据库数据文件所在目录，删除表面5个表所对应的idb文件，如下所示：
[meihuo-yunwei@iZ232hat4a3Z mysql]ls .ibd
innodb_index_stats.ibd innodb_table_stats.ibd slave_master_info.ibd slave_relay_log_info.ibd slave_worker_info.ibd
[meihuo-yunwei@iZ232hat4a3Z mysql]rm -f .ibd
(3) 重新启动数据库，进入到mysql库，重建上面被删除的表结构：
数据库的建设表脚本在mysql软件的安装目录的share目录下，我的mysql软件的安装路径为 /usr/local/mysql/share/mysql_system_tables.sql
admin@localhost : (none) 02:23:03> use mysql
Database changed
admin@localhost : mysql 02:23:50> show tables;
+—————————+
| Tables_in_mysql |
+—————————+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| 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 |
+—————————+
23 rows in set (0.00 sec)
如下为执行建表脚本后，表的数量
admin@localhost : mysql 02:23:46> show 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)
(4) 用show create table命令查看表时，也完正常，后台日志中也不再报与上面提到的5张表相关的错误，到此，问题全部解决！
修复用sql脚本：
CREATE TABLE IF NOT EXISTS innodb_table_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
n_rows BIGINT UNSIGNED NOT NULL,
clustered_index_size BIGINT UNSIGNED NOT NULL,
sum_of_other_index_sizes BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (database_name, table_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE IF NOT EXISTS innodb_index_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(64) NOT NULL,
index_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
/* there are at least:
stat_name='size'
stat_name='n_leaf_pages'
stat_name='n_diff_pfx%' */
stat_name VARCHAR(64) NOT NULL,
stat_value BIGINT UNSIGNED NOT NULL,
sample_size BIGINT UNSIGNED,
stat_description VARCHAR(1024) NOT NULL,
PRIMARY KEY (database_name, table_name, index_name, stat_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE IF NOT EXISTS slave_relay_log_info (
Number_of_lines INTEGER UNSIGNED NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
Relay_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
Relay_log_pos BIGINT UNSIGNED NOT NULL COMMENT 'The relay log position of the last executed event.',
Master_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
Master_log_pos BIGINT UNSIGNED NOT NULL COMMENT 'The master log position of the last executed event.',
Sql_delay INTEGER NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
Number_of_workers INTEGER UNSIGNED NOT NULL,
Id INTEGER UNSIGNED NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
PRIMARY KEY(Id)) DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT 'Relay Log Information';
CREATE TABLE IF NOT EXISTS slave_master_info (
Number_of_lines INTEGER UNSIGNED NOT NULL COMMENT 'Number of lines in the file.',
Master_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
Master_log_pos BIGINT UNSIGNED NOT NULL COMMENT 'The master log position of the last read event.',
Host CHAR(64) CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The host name of the master.',
User_name TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
User_password TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
Port INTEGER UNSIGNED NOT NULL COMMENT 'The network port used to connect to the master.',
Connect_retry INTEGER UNSIGNED NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
Enabled_ssl BOOLEAN NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
Ssl_ca TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
Ssl_capath TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
Ssl_cert TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
Ssl_cipher TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
Ssl_key TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
Ssl_verify_server_cert BOOLEAN NOT NULL COMMENT 'Whether to verify the server certificate.',
Heartbeat FLOAT NOT NULL COMMENT '',
Bind TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
Ignored_server_ids TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
Uuid TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
Retry_count BIGINT UNSIGNED NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
Ssl_crl TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
Ssl_crlpath TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
Enabled_auto_position BOOLEAN NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
PRIMARY KEY(Host, Port)) DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT 'Master Information';
CREATE TABLE IF NOT EXISTS slave_worker_info (
Id INTEGER UNSIGNED NOT NULL,
Relay_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
Relay_log_pos BIGINT UNSIGNED NOT NULL,
Master_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
Master_log_pos BIGINT UNSIGNED NOT NULL,
Checkpoint_relay_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
Checkpoint_relay_log_pos BIGINT UNSIGNED NOT NULL,
Checkpoint_master_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
Checkpoint_master_log_pos BIGINT UNSIGNED NOT NULL,
Checkpoint_seqno INT UNSIGNED NOT NULL,
Checkpoint_group_size INTEGER UNSIGNED NOT NULL,
Checkpoint_group_bitmap BLOB NOT NULL,
PRIMARY KEY(Id)) DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT 'Worker Information';
欢迎来到这里！
我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。
注册 关于