使用 Azure 数据库迁移服务联机迁移到 Azure DB for MySQL 的问题和限制Online migration issues & limitations to Azure DB for MySQL with Azure Database Migration Service

以下部分描述了在从 MySQL 联机迁移到 Azure Database for MySQL 时存在的已知问题和限制。Known issues and limitations associated with online migrations from MySQL to Azure Database for MySQL are described in the following sections.

联机迁移配置Online migration configuration

  • 源 MySQL 服务器版本必须是 5.6.35、5.7.18 或以上The source MySQL Server version must be version 5.6.35, 5.7.18 or later

  • Azure Database for MySQL 支持:Azure Database for MySQL supports:

    • MySQL 社区版MySQL community edition
    • InnoDB 引擎InnoDB engine
  • 相同版本的迁移。Same version migration. 不支持将 MySQL 5.6 迁移到 Azure Database for MySQL 5.7。Migrating MySQL 5.6 to Azure Database for MySQL 5.7 isn't supported.

  • 在 my.ini (Windows) 或 my.cnf (Unix) 中启用二进制日志记录Enable binary logging in my.ini (Windows) or my.cnf (Unix)

    • 将 Server_id 设为大于或等于 1 的任意数字,例如 Server_id=1(仅适用于 MySQL 5.6)Set Server_id to any number larger or equals to 1, for example, Server_id=1 (only for MySQL 5.6)
    • 设置 log-bin = <path>(仅适用于 MySQL 5.6)Set log-bin = <path> (only for MySQL 5.6)
    • 设置 binlog_format = rowSet binlog_format = row
    • Expire_logs_days = 5(建议 - 仅适用于 MySQL 5.6)Expire_logs_days = 5 (recommended - only for MySQL 5.6)
  • 用户必须具有 ReplicationAdmin 角色。User must have the ReplicationAdmin role.

  • 为源 MySQL 数据库定义的排序规则与目标 Azure Database for MySQL 中定义的排序规则相同。Collations defined for the source MySQL database are the same as the ones defined in target Azure Database for MySQL.

  • Azure Database for MySQL 中源 MySQL 数据库与目标数据库的架构必须匹配。Schema must match between source MySQL database and target database in Azure Database for MySQL.

  • 目标 Azure Database for MySQL 中的架构不能包含外键。Schema in target Azure Database for MySQL must not have foreign keys. 使用以下查询来删除外键:Use the following query to drop foreign keys:

    SET group_concat_max_len = 8192;
    SELECT SchemaName, GROUP_CONCAT(DropQuery SEPARATOR ';\n') as DropQuery, GROUP_CONCAT(AddQuery SEPARATOR ';\n') as AddQuery
    FROM
    (SELECT 
    KCU.REFERENCED_TABLE_SCHEMA as SchemaName, KCU.TABLE_NAME, KCU.COLUMN_NAME,
        CONCAT('ALTER TABLE ', KCU.TABLE_NAME, ' DROP FOREIGN KEY ', KCU.CONSTRAINT_NAME) AS DropQuery,
        CONCAT('ALTER TABLE ', KCU.TABLE_NAME, ' ADD CONSTRAINT ', KCU.CONSTRAINT_NAME, ' FOREIGN KEY (`', KCU.COLUMN_NAME, '`) REFERENCES `', KCU.REFERENCED_TABLE_NAME, '` (`', KCU.REFERENCED_COLUMN_NAME, '`) ON UPDATE ',RC.UPDATE_RULE, ' ON DELETE ',RC.DELETE_RULE) AS AddQuery
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU, information_schema.REFERENTIAL_CONSTRAINTS RC
        WHERE
          KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
          AND KCU.REFERENCED_TABLE_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
      AND KCU.REFERENCED_TABLE_SCHEMA = ['schema_name']) Queries
      GROUP BY SchemaName;
    

    运行查询结果中的 drop foreign key(第二列)。Run the drop foreign key (which is the second column) in the query result.

  • 目标 Azure Database for MySQL 中的架构不能包含任何触发器。Schema in target Azure Database for MySQL must not have any triggers. 在目标数据库中删除触发器:To drop triggers in target database:

    SELECT Concat('DROP TRIGGER ', Trigger_Name, ';') FROM  information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_schema';
    

数据类型限制Datatype limitations

  • 限制:如果源 MySQL 数据库中存在 JSON 数据类型,则在连续同步期间迁移将会失败。Limitation: If there's a JSON datatype in the source MySQL database, migration will fail during continuous sync.

    解决方法:在源 MySQL 数据库中将 JSON 数据类型修改为中等长度的文本或长文本。Workaround: Modify JSON datatype to medium text or longtext in source MySQL database.

  • 限制:如果表中没有主键,连续同步将会失败。Limitation: If there's no primary key on tables, continuous sync will fail.

    解决方法:暂时为表设置一个主键,以便迁移能够继续。Workaround: Temporarily set a primary key for the table for migration to continue. 数据迁移完成后,可以删除该主键。You can remove the primary key after data migration is complete.

LOB 限制LOB limitations

大型对象 (LOB) 列可能会增大。Large Object (LOB) columns are columns that could grow large in size. MySQL、中等长度文本、长文本、Blob、中等 Blob、长 Blob 等都属于 LOB 数据类型。For MySQL, Medium text, Longtext, Blob, Mediumblob, Longblob, etc., are some of the datatypes of LOB.

  • 限制:如果将 LOB 数据类型用作主键,迁移将会失败。Limitation: If LOB data types are used as primary keys, migration will fail.

    解决方法:将主键替换为不属于 LOB 的其他数据类型或列。Workaround: Replace primary key with other datatypes or columns that aren't LOB.

  • 限制:如果大型对象 (LOB) 列的长度超过 32 KB,目标上的数据可能会截断。Limitation: If the length of Large Object (LOB) column is bigger than 32 KB, data might be truncated at the target. 可使用以下查询检查 LOB 列的长度:You can check the length of LOB column using this query:

    SELECT max(length(description)) as LEN from catalog;
    

    解决方法:如果 LOB 对象大于 32 KB,请联系 Azure 支持Workaround: If you have LOB object that is bigger than 32 KB, contact Azure Support.

从 AWS RDS MySQL 联机迁移时的限制Limitations when migrating online from AWS RDS MySQL

尝试从 AWS RDS MySQL 联机迁移到 Azure Database for MySQL 时,你可能会遇到以下错误。When you try to perform an online migration from AWS RDS MySQL to Azure Database for MySQL, you may come across the following errors.

  • 错误: 数据库“{0}”在目标上有外键。Error: Database '{0}' has foreign key(s) on target. 修复目标并启动新的数据迁移活动。Fix the target and start a new data migration activity. 在目标上执行以下脚本以列出外键Execute below script on target to list the foreign key(s)

    限制:如果架构中有外键,则迁移的初始加载和连续同步会失败。Limitation: If you have foreign keys in your schema, the initial load and continuous sync of the migration will fail. 解决方法:请在 MySQL Workbench 中执行以下脚本来提取“删除外键”脚本和“添加外键”脚本:Workaround: Execute the following script in MySQL workbench to extract the drop foreign key script and add foreign key script:

    SET group_concat_max_len = 8192; SELECT SchemaName, GROUP_CONCAT(DropQuery SEPARATOR ';\n') as DropQuery, GROUP_CONCAT(AddQuery SEPARATOR ';\n') as AddQuery FROM (SELECT KCU.REFERENCED_TABLE_SCHEMA as SchemaName, KCU.TABLE_NAME, KCU.COLUMN_NAME, CONCAT('ALTER TABLE ', KCU.TABLE_NAME, ' DROP FOREIGN KEY ', KCU.CONSTRAINT_NAME) AS DropQuery, CONCAT('ALTER TABLE ', KCU.TABLE_NAME, ' ADD CONSTRAINT ', KCU.CONSTRAINT_NAME, ' FOREIGN KEY (`', KCU.COLUMN_NAME, '`) REFERENCES `', KCU.REFERENCED_TABLE_NAME, '` (`', KCU.REFERENCED_COLUMN_NAME, '`) ON UPDATE ',RC.UPDATE_RULE, ' ON DELETE ',RC.DELETE_RULE) AS AddQuery FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU, information_schema.REFERENTIAL_CONSTRAINTS RC WHERE KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME AND KCU.REFERENCED_TABLE_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA AND KCU.REFERENCED_TABLE_SCHEMA = 'SchemaName') Queries GROUP BY SchemaName;
    
  • 错误: 服务器上不存在数据库“{0}”。Error: Database '{0}' does not exist on server. 提供的 MySQL 源服务器区分大小写。Provided MySQL source server is case sensitive. 请检查数据库名称。Please check the database name.

    限制:使用命令行接口 (CLI) 将 MySQL 数据库迁移到 Azure 时,用户可能会遇到此错误。Limitation: When migrating a MySQL database to Azure using Command Line Interface (CLI), users may hit this error. 服务在源服务器上找不到该数据库,原因可能是提供了错误的数据库名称,或者该数据库不存在于所列的服务器上。The service couldn't locate the database on the source server, which could be because you may have provided incorrect database name or the database doesn't exist on the listed server. 请注意,数据库名称区分大小写。Note database names are case-sensitive.

    解决方法:提供准确的数据库名称,然后重试。Workaround: Provide the exact database name, and then try again.

  • 错误: 数据库“{database}”中存在同名的表。Error: There are tables with the same name in the database '{database}'. Azure Database for MySQL 不支持区分大小写的表。Azure Database for MySQL does not support case sensitive tables.

    限制:如果源数据库中有两个同名的表,则会出现此错误。Limitation: This error happens when you have two tables with the same name in the source database. Azure Database for MySQL 不支持区分大小写的表。Azure Database for MySQL doesn't support case-sensitive tables.

    解决方法:将表名称更新为唯一名称,然后重试。Workaround: Update the table names to be unique, and then try again.

  • 错误: 目标数据库 {database} 为空。Error: The target database {database} is empty. 请迁移架构。Please migrate the schema.

    限制:如果目标 Azure Database for MySQL 数据库没有所需的架构,则会出现此错误。Limitation: This error occurs when the target Azure Database for MySQL database doesn't have the required schema. 若要将数据迁移到目标,需要进行架构迁移。Schema migration is required to enable migrating data to your target.

    解决方法将架构从源数据库迁移到目标数据库Workaround: Migrate the schema from your source database to the target database.

其他限制Other limitations

  • 不支持在开头和末尾包含左右大括号 { } 的密码字符串。A password string that has opening and closing curly brackets { } at the beginning and end of the password string isn't supported. 连接到源 MySQL 和目标 Azure Database for MySQL 时,都存在这种限制。This limitation applies to both connecting to source MySQL and target Azure Database for MySQL.

  • 不支持以下 DDL:The following DDLs aren't supported:

    • 所有分区 DDLAll partition DDLs
    • 删除表Drop table
    • 重命名表Rename table
  • 不支持使用 alter table <表名> add column <列名> 语句将列添加到表的开头或中间。Using the alter table <table_name> add column <column_name> statement to add columns to the beginning or to the middle of a table isn't supported. alter table <表名> add column <列名> 会在表的末尾添加列。THe alter table <table_name> add column <column_name> adds the column at the end of the table.

  • 不支持基于一部分列数据创建的索引。Indexes created on only part of the column data aren't supported. 以下示例语句仅使用一部分列数据创建索引:The following statement is an example that creates an index using only part of the column data:

    CREATE INDEX partial_name ON customer (name(10));
    
  • 在 Azure 数据库迁移服务中,可在单个迁移活动中迁移的数据库数目限制为 4 个。In Azure Database Migration Service, the limit of databases to migrate in one single migration activity is four.

  • 错误: 行太大 (> 8126)。Error: Row size too large (> 8126). 将某些列更改为 TEXT 或 BLOB 可能会有帮助。Changing some columns to TEXT or BLOB may help. 在当前的行格式中,0 字节的 BLOB 前缀以内联方式存储。In current row format, BLOB prefix of 0 bytes is stored inline.

    限制:使用 InnoDB 存储引擎迁移到 Azure Database for MySQL 且任意表行过大(>8126 字节)时,会发生此错误。Limitation: This error happens when you're migrating to Azure Database for MySQL using the InnoDB storage engine and any table row size is too large (>8126 bytes).

    解决方法:更新其行大小大于 8126 字节的表的架构。Workaround: Update the schema of the table that has a row size greater than 8126 bytes. 我们建议不要更改严格模式,因为更改后会截断数据。We don't recommend changing the strict mode because the data will be truncated. 不支持更改 page_size。Changing the page_size isn't supported.