MySQL和MariaDB升级时未知存储引擎错误的解决

知识点:
InnoDB 和 MyISAM 是使用 MySQL/MariaDB 数据库时最常用的两个存储引擎。MyISAM 不支持事务处理等高级处理,而 InnoDB 支持;MyISAM强调的是性能,其执行数度比 InnoDB 更快,但是不提供事务支持,而 InnoDB 提供事务支持以及外部键等高级数据库功能。

在 MyISAM 中,表被存放在分离的单独文件中,使用 MyISAM 数据引擎的每张表对应三个文件,每一个文件的名字均以表的名字开始,扩展名对应相关的文件类型:.frm 文件存储表结构定义,.MYD 存储表数据,.MYI 存储表索引。

InnoDB 是 MySQL 5.5 版本以后的默认存储引擎,它是基于 MySQL 的高可扩展性和高性能存储引擎。InnoDB 把数据和索引存放在表空间里,表空间可以包含多个文件(或原始磁盘分区),InnoDB 表的大小只受限于操作系统的文件大小,也可以每个表使用各自单独的表空间,而只需要启用选项 innodb_file_per_table。

InnoDB 的最大特色就是给 MySQL 提供了具有事务(transaction)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)、多版本并发控制(multi-versioned concurrency control)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供行级锁(locking on row level),和与 Oracle 类似的不加锁读取(non-locking read in SELECTs)。InnoDB 还是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎,在 SQL 查询中,可以自由地将InnoDB类型的表与其它 MySQL 的表的类型混合起来,甚至在同一个查询中也可以混合,这些特性均提高了多用户并发操作的性能表现。

MySQL和MariaDB升级时未知存储引擎错误的解决

MySQL 5.6.x 和 MariaDB 10.x 在改进中新增了几个系统表,表 innodb_table_stats 和 innodb_index_stats,以及 MariaDB 10.x 特有的表 gtid_slave_pos,和 MySQL 5.6.x 特有的表slave_master_info、slave_relay_log_info、slave_worker_info,这些表的默认引擎类型都为 InnoDB。

为了避免使用更多的系统内存,节省系统资源和不备份一些不需要的文件,通常在 my.cnf 配置文件中使用参数 -skip-innodb 关闭了 InnoDB 引擎,而仅仅使用 MyISAM 引擎。这时候,在升级完 MySQL/MariaDB 数据库,运行命令 mysql_upgrade 时,会出现类似下面的错误信息:

mysql.innodb_index_stats
Error    : Unknown storage engine 'InnoDB'
error    : Corrupt
mysql.innodb_table_stats
Error    : Unknown storage engine 'InnoDB'
error    : Corrupt

同时在 MySQL 的日志中,发现类似下面的错误信息:

[Warning] Info table is not ready to be used.
Table 'mysql.slave_master_info' cannot be opened.
[Warning] Info table is not ready to be used.
Table 'mysql.slave_relay_log_info' cannot be opened.

和在 MariaDB 的日志中,发现类似下面的错误信息:

[Warning] Failed to load slave replication state from
table mysql.gtid_slave_pos: 1286: Unknown storage engine 'InnoDB'

Unknown storage engine ‘InnoDB’解决办法:
方法I:更改数据库 my.cnf 配置,切换到使用 Innodb 引擎。

方法II:
更改数据库 my.cnf配置文件,临时启用 InnoDB 引擎,然后使用命令

ALTER TABLE `table_name` ENGINE=MyISAM

把相关的几个表的引擎设置从 InnoDB 修改为 MyISAM:

mysql -uroot -p
use mysql;

# MariaDB 10.0.x:
ALTER TABLE `gtid_slave_pos` ENGINE=MyISAM
ALTER TABLE `innodb_index_stats` ENGINE=MyISAM
ALTER TABLE `innodb_table_stats` ENGINE=MyISAM

# MySQL 5.6.x:
ALTER TABLE `innodb_index_stats` ENGINE=MyISAM
ALTER TABLE `innodb_table_stats` ENGINE=MyISAM
ALTER TABLE `slave_master_info` ENGINE=MyISAM
ALTER TABLE `slave_relay_log_info` ENGINE=MyISAM
ALTER TABLE `slave_worker_info` ENGINE=MyISAM

修改完成相关表后,重新设置 my.cnf 配置文件,切换回 MyISAM,并禁用 InnoDB。

方法III:
删除相关的几个表,然后使用 MyISAM 重建这些表。相关的建表命令如下:

MariaDB 10.0.x:

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=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';

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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

MySQL 5.6.x:

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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

CREATE TABLE `slave_master_info` (
  `Master_id` int(10) unsigned NOT NULL,
  `Number_of_lines` int(10) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Host` text CHARACTER SET utf8 COLLATE utf8_bin,
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin,
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Port` int(10) unsigned NOT NULL,
  `Connect_retry` int(10) unsigned NOT NULL,
  `Enabled_ssl` tinyint(1) NOT NULL,
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ssl_verify_servert_cert` tinyint(1) NOT NULL,
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin,
  `Retry_count` bigint(20) unsigned NOT NULL,
  `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',
  PRIMARY KEY (`Master_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

CREATE TABLE `slave_relay_log_info` (
  `Master_id` int(10) unsigned NOT NULL,
  `Number_of_lines` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Sql_delay` int(11) NOT NULL,
  PRIMARY KEY (`Master_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

2月 1, 2015 | In: 数据库

Comment Form

You must be logged in to post a comment.

分类

归档

ipv6 ready