普通用户二进制安装mariadb10.1.16 mysql库表损坏修改

67rt

1）mariadb日志：

1 2 3 4 5 6 7 8 9 10 11 12

10:36:48 140397816809216 [Note] InnoDB: Dumping buffer pool(s) not yet started 2016-09-01 10:36:48 140510705071872 [Warning] InnoDB: Cannot open table mysql/gtid_slave_pos from the internal data dictionary of InnoDB though the .frm file for the t able exists. See http://dev.mysql.com/doc/refman/ ... roubleshooting.html for how you can resolve the problem. 2016-09-01 10:36:48 140510705071872 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table ' mysql.gtid_slave_pos ' doesn't exist in engine 2016-09-01 10:36:48 140510705289088 [Note] Server socket created on IP: '0.0.0.0'. 2016-09-01 10:36:48 140510704572160 [Warning] InnoDB: Cannot open table mysql/gtid_slave_pos from the internal data dictionary of InnoDB though the .frm file for the t able exists. See http://dev.mysql.com/doc/refman/ ... roubleshooting.html for how you can resolve the problem. 2016-09-01 10:36:48 140510704572160 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine

2）我本机mariadb损坏了三张表 ：

1 2 3

innodb_table_stats innodb_index_stats gtid_slave_pos

3）网上部分方法如下：
3.1找到mariadb安装目录share下mysql_system_tables.sql这调sql进入mysql库下执行source 依然问题有问题
3.2 打开mysql_system_tables.sql找到create 这三张表语句，手动执行，问题还是存在
4）我的方法：
4.1登陆数据库----use mysql;

1 2 3

drop table innodb_table_stats;   drop table innodb_index.stats;   drop table gtid_slave_pos;

\q 退出mariadb
4.3进入mariadb安装目录data下mysql删除 三张表以为ibd结尾的表

1 2 3

innodb_table_stats.ibd innodb_index_stats.ibd gtid_slave_pos.ibd

4.4在次登陆mariadb进入mysql库执行source

1 2 3

source /home/innodb_table_stats.sql source /home/innodb_index_stats.sql source /home/gtid_slave_pos.sql

重新启动mariadb查看日志没有错误了（注意在操作前如果有数据要提前备份，为了以防出错还原）

这三个sql表结构是我从mysql_system_tables.sql整理出来的。我已经打包上传了。


第一张：innodb_table_stats

1 2 3 4 5 6 7 8 9 10 11

SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS `innodb_table_stats`; CREATE TABLE `innodb_table_stats` (   `database_name` varchar(64) COLLATE utf8_bin NOT NULL,   `table_name` varchar(64) COLLATE utf8_bin NOT NULL,   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   `n_rows` bigint(20) unsigned NOT NULL,   `clustered_index_size` bigint(20) unsigned NOT NULL,   `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,   PRIMARY KEY (`database_name`,`table_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

第二张：innodb_index_stats

1 2 3 4 5 6 7 8 9 10 11 12 13

SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS `innodb_index_stats`; 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;

第三张：gtid_slave_pos

1 2 3 4 5 6 7 8 9

SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS `gtid_slave_pos`; CREATE TABLE `gtid_slave_pos` (   `domain_id` int(10) unsigned NOT NULL,   `sub_id` bigint(20) unsigned NOT NULL,   `server_id` int(10) unsigned NOT NULL,   `seq_no` bigint(20) unsigned NOT NULL,   PRIMARY KEY (`domain_id`,`sub_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Replication slave GTID position';