Migrating to External MySQL 8 (Azure) -- MySQLDump Import Issues

Products

• CA API Gateway

Issue/Introduction

We had started with the "demo" db that is MySQL 8 in the values.yaml and are now trying to move to an external MySQL 8 DB hosted in Azure. We made sure to use all the appropriate server configurations.

We are facing issues on completing the import for both ssg and otk_db.

---

ERROR 1227 (42000) at line 14: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

----

mysqldump -u root -p --verbose --routines --triggers --single-transaction --column-statistics=0 --databases ssg otk_db > /tmp/mysqldump--devcopy-8-25.sql

mysql -u <user> -p --port=3306 -hazurehost.mysql.database.azure.com --verbose < /tmp/mysqldump--devcopy-8-25.sql

---

What is the correct mysqldump command? This is the dump command used when externalizing our Container API Portal and did not find anything online in terms of the correct way to use mysqlpdump for container gateways.

Environment

API Container Gateway 10.1 on AKS

Resolution

There is a permissions issue in Azure and this limitation is likely due to the DEFINER text in the dump. 

 

There is Azure limitation: 

• DEFINER: Requires super privileges to create and is restricted. If importing data using a backup, remove the CREATE DEFINER commands manually or by using the --skip-definer command when performing a mysqlpump.

 (https://docs.microsoft.com/en-us/azure/mysql/single-server/concepts-limits)

 

To resolve the problem, use the new 8.0 utility called mysqlpump. The commands below work for the database backup: 

 

mysqlpump-u root -p --routines --triggers --single-transaction--column-statistics=0 --skip-definer --databases ssg otk_db >/tmp/mysqlpump--devcopy-8-25.sql

 

mysql-u <user> -p --port=3306 -hazurehost.mysql.database.azure.com --verbose </tmp/mysqlpump--devcopy-8-25.sql