# 缩短物理迁移到 Amazon Aurora MySQL 的时间
<a name="AuroraMySQL.Migrating.ExtMySQL.Prechecks"></a>

您可以进行以下数据库修改，以加快将数据库迁移到 Amazon Aurora MySQL 的过程。

**重要**  
请确保在生产数据库的副本而不是在生产数据库上执行这些更新。然后，您可以备份副本并将其还原到 Aurora MySQL 数据库集群，以避免生产数据库上产生任何服务中断。

## 不支持的表类型
<a name="AuroraMySQL.Migrating.ExtMySQL.Prechecks.Tables"></a>

Aurora MySQL 仅支持用于数据库表的 InnoDB 引擎。如果您的数据库中有 MyISAM 表，则必须先转换这些表，然后才能迁移到 Aurora MySQL 中。迁移过程中，转换过程需要额外的空间以便将 MyISAM 转换为 InnoDB。

若要降低空间用尽的可能性或加快迁移过程，请先将所有 MyISAM 表转换为 InnoDB 表，然后再迁移这些表。生成的 InnoDB 表的大小与 Aurora MySQL 要求该表具有的大小相同。要将 MyISAM 表转换为 InnoDB 表，请运行以下命令：

```
ALTER TABLE schema.table_name engine=innodb, algorithm=copy;
```

Aurora MySQL 不支持压缩的表或页（即，使用 `ROW_FORMAT=COMPRESSED` 或 `COMPRESSION = {"zlib"|"lz4"}` 创建的表）。

为了避免用完空间或为了加速迁移过程，请通过将 `ROW_FORMAT` 设置为 `DEFAULT`、`COMPACT`、`DYNAMIC` 或 `REDUNDANT` 来扩展您的压缩表。对于压缩的页，请设置 `COMPRESSION="none"`。

有关更多信息，请参阅 MySQL 文档中的 [InnoDB 行格式](https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html)和 [InnoDB 表和页压缩](https://dev.mysql.com/doc/refman/8.0/en/innodb-compression.html)。

您可以在现有 MySQL 数据库实例上使用以下 SQL 脚本来列出数据库中属于 MyISAM 表或压缩表的表。

```
-- This script examines a MySQL database for conditions that block
-- migrating the database into Aurora MySQL.
-- It must be run from an account that has read permission for the
-- INFORMATION_SCHEMA database.

-- Verify that this is a supported version of MySQL.

select msg as `==> Checking current version of MySQL.`
from
  (
  select
    'This script should be run on MySQL version 5.6 or higher. ' +
    'Earlier versions are not supported.' as msg,
    cast(substring_index(version(), '.', 1) as unsigned) * 100 +
      cast(substring_index(substring_index(version(), '.', 2), '.', -1)
      as unsigned)
    as major_minor
  ) as T
where major_minor <> 506;


-- List MyISAM and compressed tables. Include the table size.

select concat(TABLE_SCHEMA, '.', TABLE_NAME) as `==> MyISAM or Compressed Tables`,
round(((data_length + index_length) / 1024 / 1024), 2) "Approx size (MB)"
from INFORMATION_SCHEMA.TABLES
where
  ENGINE <> 'InnoDB'
  and
  (
    -- User tables
    TABLE_SCHEMA not in ('mysql', 'performance_schema',
                         'information_schema')
    or
    -- Non-standard system tables
    (
      TABLE_SCHEMA = 'mysql' and TABLE_NAME not in
        (
          'columns_priv', 'db', 'event', 'func', 'general_log',
          'help_category', 'help_keyword', 'help_relation',
          'help_topic', 'host', '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'
        )
    )
  )
  or
  (
    -- Compressed tables
       ROW_FORMAT = 'Compressed'
  );
```

## 具有不支持的权限的用户账户
<a name="AuroraMySQL.Migrating.ExtMySQL.Prechecks.Users"></a>

如果用户账户的权限不受 Aurora MySQL 支持，则在导入这些账户时将不包含这些不受支持的权限。有关支持的权限列表，请参阅[基于角色的权限模型](AuroraMySQL.Compare-80-v3.md#AuroraMySQL.privilege-model)。

可以在源数据库上运行以下 SQL 查询，以列出具有不受支持的权限的用户账户。

```
SELECT
    user,
    host
FROM
    mysql.user
WHERE
    Shutdown_priv = 'y'
    OR File_priv = 'y'
    OR Super_priv = 'y'
    OR Create_tablespace_priv = 'y';
```

## Aurora MySQL 版本 3 中的动态权限
<a name="AuroraMySQL.Migrating.ExtMySQL.Prechecks.Dynamic"></a>

不会导入动态权限。Aurora MySQL 版本 3 支持以下动态权限。

```
'APPLICATION_PASSWORD_ADMIN',
'CONNECTION_ADMIN',
'REPLICATION_APPLIER',
'ROLE_ADMIN',
'SESSION_VARIABLES_ADMIN',
'SET_USER_ID',
'XA_RECOVER_ADMIN'
```

以下示例脚本向 Aurora MySQL 数据库集群中的用户账户授予支持的动态权限。

```
-- This script finds the user accounts that have Aurora MySQL supported dynamic privileges 
-- and grants them to corresponding user accounts in the Aurora MySQL DB cluster.

/home/ec2-user/opt/mysql/8.0.26/bin/mysql -uusername -pxxxxx -P8026 -h127.0.0.1 -BNe "SELECT
  CONCAT('GRANT ', GRANTS, ' ON *.* TO ', GRANTEE ,';') AS grant_statement
  FROM (select GRANTEE, group_concat(privilege_type) AS GRANTS FROM information_schema.user_privileges 
      WHERE privilege_type IN (
        'APPLICATION_PASSWORD_ADMIN',
        'CONNECTION_ADMIN',
        'REPLICATION_APPLIER',
        'ROLE_ADMIN',
        'SESSION_VARIABLES_ADMIN',
        'SET_USER_ID',
        'XA_RECOVER_ADMIN')
      AND GRANTEE NOT IN (\"'mysql.session'@'localhost'\",\"'mysql.infoschema'@'localhost'\",\"'mysql.sys'@'localhost'\") GROUP BY GRANTEE)
      AS PRIVGRANTS; " | /home/ec2-user/opt/mysql/8.0.26/bin/mysql -u master_username -p master_password -h DB_cluster_endpoint
```

## 以 'rdsadmin'@'localhost' 作为定义程序的存储对象
<a name="AuroraMySQL.Migrating.ExtMySQL.Prechecks.Objects"></a>

不会导入以 `'rdsadmin'@'localhost'` 作为定义程序的函数、过程、视图、事件和触发器。

您可以在源 MySQL 数据库上使用以下 SQL 脚本来列出具有不受支持的定义程序的存储对象。

```
-- This SQL query lists routines with `rdsadmin`@`localhost` as the definer.

SELECT
    ROUTINE_SCHEMA,
    ROUTINE_NAME
FROM
    information_schema.routines
WHERE
    definer = 'rdsadmin@localhost';

-- This SQL query lists triggers with `rdsadmin`@`localhost` as the definer.

SELECT
    TRIGGER_SCHEMA,
    TRIGGER_NAME,
    DEFINER
FROM
    information_schema.triggers
WHERE
    DEFINER = 'rdsadmin@localhost';

-- This SQL query lists events with `rdsadmin`@`localhost` as the definer.

SELECT
    EVENT_SCHEMA,
    EVENT_NAME
FROM
    information_schema.events
WHERE
    DEFINER = 'rdsadmin@localhost';

-- This SQL query lists views with `rdsadmin`@`localhost` as the definer.
SELECT
    TABLE_SCHEMA,
    TABLE_NAME
FROM
    information_schema.views
WHERE
    DEFINER = 'rdsadmin@localhost';
```