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 are not provided the migration can fail in several places.
2. Live migration (CDC) prerequisite: Enable MySQL binary logging from the MySQL configuration file as follows:
   1. On the computer where the MySQL server is installed, navigate to the location of the MySQL configuration file.

      For example: /etc/my.cnf

   2. Open the configuration file to edit it.
   3. In the [mysqld] section, add the following:

      log-bin=mysql-bin.log

   4. Restart the MySQL server for the changes to take effect.
   5. When the MySQL server is restarted, verify that binary logging is enabled by running the following query:

      mysql > show variables like “%log_bin%”

      If binary logging has been enabled correctly, the value of log_bin = ON otherwise if it has not been enabled correctly, the value of log_bin = OFF.

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.