MySQL :: MySQL 8.4 参考手册 :: 19.4.4 使用具有不同源和副本存储引擎的复制

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 19 复制 / 19.4 复制解决方案 / 19.4.4 使用具有不同源和副本存储引擎的复制

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

19.4.4 使用不同源和副本存储引擎进行复制19.4.4 Using Replication with Different Source and Replica Storage Engines

对于复制过程而言，源上的原始表和副本上的复制表是否使用不同的存储引擎类型并不重要。事实上，default_storage_engine 系统变量不会被复制。It does not matter for the replication process whether the original table on the source and the replicated table on the replica use different storage engine types. In fact, the default_storage_engine system variable is not replicated.

在复制过程中，这提供了许多好处，因为你可以针对不同的复制场景利用不同的存储引擎类型。例如，在典型的扩展场景中（参见第 19.4.5 节，“使用复制进行扩展”），你希望在源上使用InnoDB表以利用事务功能，但在副本上使用MyISAM，因为只读数据不需要事务支持。在数据记录环境中使用复制时，你可能希望在副本上使用Archive存储引擎。This provides a number of benefits in the replication process in that you can take advantage of different engine types for different replication scenarios. For example, in a typical scale-out scenario (see Section 19.4.5, “Using Replication for Scale-Out”), you want to use InnoDB tables on the source to take advantage of the transactional functionality, but use MyISAM on the replicas where transaction support is not required because the data is only read. When using replication in a data-logging environment you may want to use the Archive storage engine on the replica.

在源和副本上配置不同的引擎取决于你如何设置初始复制过程：Configuring different engines on the source and replica depends on how you set up the initial replication process:

如果你在源端使用mysqldump创建数据库快照，你可以编辑转储文件的文本以更改每个表使用的存储引擎类型。If you used mysqldump to create the database snapshot on your source, you could edit the dump file text to change the engine type used on each table.
对于 mysqldump 的另一种替代方法是在使用转储在副本上构建数据之前，先禁用您不想在副本上使用的存储引擎类型。例如，您可以在副本上添加 --skip-federated 选项以禁用 FEDERATED 引擎。如果要创建的表未指定特定引擎，MySQL 会使用默认引擎类型，通常是 InnoDB。（这要求 NO_ENGINE_SUBSTITUTION SQL 模式未启用。）如果您想以这种方式禁用其他引擎，您可能需要考虑构建一个用于副本的特殊二进制文件，该文件仅支持您想要的引擎。Another alternative for mysqldump is to disable engine types that you do not want to use on the replica before using the dump to build the data on the replica. For example, you can add the --skip-federated option on your replica to disable the FEDERATED engine. If a specific engine does not exist for a table to be created, MySQL uses the default engine type, usually InnoDB. (This requires that the NO_ENGINE_SUBSTITUTION SQL mode is not enabled.) If you want to disable additional engines in this way, you may want to consider building a special binary to be used on the replica that supports only the engines you want.
如果您使用原始数据文件（二进制备份）来设置副本，则无法更改初始表格式。相反，请在副本启动后使用 ALTER TABLE 来更改表类型。If you use raw data files (a binary backup) to set up the replica, it is not possible to change the initial table format. Instead, use ALTER TABLE to change the table types after the replica has been started.
对于当前源上没有表的新源/副本复制设置，在创建新表时避免指定引擎类型。For new source/replica replication setups where there are currently no tables on the source, avoid specifying the engine type when creating new tables.

如果您已经在运行复制解决方案并且想将现有表转换为另一种引擎类型，请按照以下步骤操作：If you are already running a replication solution and want to convert your existing tables to another engine type, follow these steps:

停止副本执行复制更新：Stop the replica from running replication updates:
```
mysql> STOP REPLICA;
```
这使得可以在不中断的情况下更改引擎类型。This makes it possible to change engine types without interruption.
对每个要更改的表执行 ALTER TABLE ... ENGINE='engine_type'。Execute an ALTER TABLE ... ENGINE='engine_type' for each table to be changed.
重新启动复制过程：Start the replication process again:
```
mysql> START REPLICA;
```

虽然 default_storage_engine 变量不会被复制，但请注意，包含存储引擎指定的 CREATE TABLE 和 ALTER TABLE 语句会被正确复制到副本。如果在 CSV 表的情况下，你执行以下语句：Although the default_storage_engine variable is not replicated, be aware that CREATE TABLE and ALTER TABLE statements that include the engine specification are replicated to the replica correctly. If, in the case of a CSV table, you execute this statement:

mysql> ALTER TABLE csvtable ENGINE='MyISAM';

该语句已被复制；复制表上的表引擎类型会被转换为 InnoDB，即使您之前已将复制表的表类型更改为除 CSV 之外的其他引擎。如果您希望在源和复制表上保留引擎差异，应在创建新表时小心使用源上的 default_storage_engine 变量。例如，不要使用：This statement is replicated; the table's engine type on the replica is converted to InnoDB, even if you have previously changed the table type on the replica to an engine other than CSV. If you want to retain engine differences on the source and replica, you should be careful to use the default_storage_engine variable on the source when creating a new table. For example, instead of:

mysql> CREATE TABLE tablea (columna int) Engine=MyISAM;

请使用此格式：Use this format:

mysql> SET default_storage_engine=MyISAM;
mysql> CREATE TABLE tablea (columna int);

在复制时，default_storage_engine 变量会被忽略，CREATE TABLE 语句会在副本上使用副本的默认引擎执行。When replicated, the default_storage_engine variable is ignored, and the CREATE TABLE statement executes on the replica using the replica's default engine.

首页上一级下一节