MySQL Prerequisites

Source DB

  1. The following permissions are mandatory for the source user:
    GRANT CREATE USER, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, PROCESS ON *.* TO `<source_user>`@`%` WITH GRANT OPTION;
    GRANT SELECT ON mysql.user TO `<source_user>`@`%`;

    -- For mysql version >= 8.0
    GRANT SHOW_ROUTINE ON *.* To `<source_user> WITH GRANT OPTION;

    -- For mysql versions 5.5, 5.6 and 5.7
    GRANT SELECT ON mysql.proc TO `<source_user>`@`%` with GRANT OPTION;

    -- For each database to be migrated ( say, <source_db_name> ):
    GRANT SELECT, CREATE, DROP, ALTER, INSERT, INDEX, SHOW VIEW, TRIGGER on `<source_db_name>`.* TO `<source_user>`@`%` WITH GRANT OPTION;
    Note: If the required privileges aren't provided the migration can fail in several places
  2. Live migration (CDC) prerequisite: Enable binary logging.

Target DB

  1. The following permissions are mandatory for the target user:
    GRANT ALL PRIVILEGES ON awsdms_control.* TO '<target_user>'@'%'; 
    GRANT SHOW DATABASES, CREATE USER, PROCESS ON *.* to `<target_user>`@`%` WITH GRANT OPTION;
    GRANT SELECT ON mysql.user TO `<target_user>`@`%`;

    -- For mysql version >= 8.0
    GRANT SHOW_ROUTINE ON *.* To `<source_user> WITH GRANT OPTION`

    -- For mysql versions 5.5, 5.6 and 5.7
    GRANT SELECT ON mysql.proc TO `<source_user>`@`%` with GRANT OPTION;

    -- For each database to be migrated ( say, <target_db_name> ):
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, EVENT, TRIGGER ON <target_db_name>.* TO '<target_user>'@'%' WITH GRANT OPTION;

    Note:

    1. If the required privileges aren't provided the migration can fail in several places
    2. Try making sure “binary logging” is disabled in the target DB, as it might lead to certain privilege errors OR `SET GLOBAL log_bin_trust_function_creators = 1` in the target DB. If neither of the above is done, it can lead to issues during certain object migrations (ex: triggers).

For both Source and Target

Ensure that max_connections is at least 500. See this KB article.

Increase the max_connect_errors to 999999.