Today I had the opportunity to move a local Magento database to an Amazon RDS. Based on the well-documented article Creating a DB Instance Running the MySQL Database Engine I was optimistic to finish the migration pretty soon. But as you know, there is always something that goes wrong.
After creating the database instance class ( db.m3.large ) I have decided to proceed with some test imports before I change any database credentials in Magento.
|
1 2 |
$ mysql -h customer.1xcpdspit6hz.ap-southeast-2.rds.amazonaws.com -u customer -p $ source ~/project_20161014_1135.sql |
Good decision because the first import didn’t run through.
|
1 2 |
ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) Query OK, 0 rows affected (0.01 sec) |
I was able to fix the problem by adding the missing parameter log_bin_trust_function_creators as explained here. Before the next import I had to delete the database and create a new one.
|
1 2 |
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation Query OK, 0 rows affected (0.00 sec) |
Unfortunately there was another problem with a DEFINER which tried to create a trigger as a different user. On Amazon RDS only a user with SUPER privileges can do that. Therefore, I had to remove the DEFINER from the MySQL schema you see below.
|
1 |
$ sed -i 's/DEFINER=[^*]*\*/\*/g' project_20161014_1135_toimport.sql |
That’s it. Now I was able to import my DB on Amazon RDS without errors.