Getting Started

1. The tools to be used for the migration:

• MySQL Shell 8.0.26 - on Windows

• MySQL Shell Dump & Load utilities

• AWS account

• AWS CLI v2 + JMESPath query language for JSON

On the AWS CLI I have set the default outputs as json however it is possible to specify the --output as table, text, yaml and yaml-stream.

2. Important considerations before loading the DB to AWS

• Make sure Local_infile configuration needs to be enabled.

To check the status of the Local_infile variable:

show variables like 'local_infile';

To turn ON the variable:

SET PERSIST local_infile = 1;

• A common error message when loading databases to AWS RDS is:

⚠️

Error processing schema •name_of_schema_or_instance• : You do not have the SUPER privilege and binary logging is enabled (you want to use the less safe variable) il.10adDump: You do not have the SUPER privilege and binary logging is enabled (you want to use t he less safe 10 variable) (MYSQLSH 1419) STUDENT > disconnect

---

---

ℹ️

Cause: The error arises when we try to import or load schema(s) or an instance dump that contains functions, store procedures, triggers, basically any type of routine or user-defined code. These elements have attached with them a DEFINER clause that specifies the MySQL account to be used when checking access privileges at routine execution. Not everyone has privileges for routine execution, but the account that created such an element. Solutions: 1.- Exporting the schema or instance without routines by applying the {routines: false} filter option. 2.- Exporting the schema or instance with the routines applying the compatibility option {compatibility: ["strip_definers"]} filter option. 3.- Exporting the schema or instance as it is, with no filtering, the following its needed: SUPER user privileges are required on the AWS RDS user running the import to be able to load the database with user-defined SQL code. This can be achieved by creating a new Parameter Group, adding the log_bin_trust_function_creators and setting its value to ’1′. The above steps should only be done temporarily in order to import databases. Once the import is complete, we should replace the custom parameter group my the default one. This will ensure that the AWS RDS instance remains secure. The above steps should only be done temporarily in order to import databases. Once the import is complete, we should replace the customed parameter group my the default one. This will ensure that the AWS RDS instance remains secure. In this case, I want to be able to import the DB instance with all the store procedures, triggers and functions it has already done by me, stripping the definer as I only want this database to practice.

---