Many aMiSTACX customers are migrating from other hosting companies or other AWS stacks. Following our DIY migration path is most of the time flawless; however, you may encounter one weird frustrating anomaly along the way when it comes to importing your database to RDS.
“Access denied; you need (at least one of) the SUPER privilege(s) for this operation”.
Not to get into the specifics, you can research yourself. Let’s just get to the fix!
Step 1. Create a new RDS parameter group.
Step 2. In the new parameter group search and set this log_bin_trust_function_creators to 1
Step 3. Attach this new parameter group to the RDS instance and reboot the instance.
Step 4. All of our new stacks have the Peal library already installed, but if yours does not then install it:
sudo apt install libwww-perl -y
Step 5. Run this perl code to massage the Definer out of your dumped Database:
perl -pe 's/\sDEFINER=`[^`]+`@`[^`]+`//' < mysqldump.sql > mysqldump.fixed.sql
[Use your file naming convention.]
Step 6. Run the import with the new fixed:
sudo mysql -u USER -h -p DATABASE < mysqldump.fixed.sql
That’s it. In like Flynn 🙂