Error trying to restore AWS MySQL RDS using backup generated by mysqldump
I have an AWS MySQL RDS, let's call it:
I created a snapshot and restored it to
Both RDS instances have the same user name, db name and password.
What I would like to do now, is create a dump of productionrds.xxxx.us-east-1.rds.amazonaws.com and restore it to stagingrds.xxxx.us-east-1.rds.amazonaws.com.
Based on reading multiple threads on Stackoverflow, I used this statement to create the
mysqldump productiondb -h productionrds.xxxx.us-east-1.rds.amazonaws.com -u productiondb -pxxxxxxxx --column-statistics=0 --no-tablespaces > production.sql
This worked fine, but when I tried doing:
mysql productiondb -h stagingrds.xxxx.us-east-1.rds.amazonaws.com -u productiondb -pxxxxxxxx < production.sql
I got the error message:
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
Line 18 reads:
SET @@SESSION.SQL_LOG_BIN= 0;
I deleted line 18, and restarted the process, I got another error message:
ERROR 1227 (42000) at line 23: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
I deleted line 23:
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
and restarted the process. The error message this time was:
ERROR 1046 (3D000) at line 27: No database selected
I found some additional AWS MySQL RDS reference material, and not all MySQL commands are supported when you use the AWS MySQL RDS instances.
So, when restoring, and even though you think you have admin privileges, not all are available. So, I continued commenting out the lines causing the problem, until it finally ran. Then crossing the lines I commented out with the mysqldump docs, I came up with the following command line that works:
mysqldump -h productionrds.xxxx.us-east-1.rds.amazonaws.com -u productiondb -pxxxxxxxx --column-statistics=0 --no-tablespaces --set-gtid-purged=OFF --databases productiondb > production.sql
--set-gtid-purged=OFFgets rid of the SET @@SESSION.SQL_LOG_BIN statements as well as the SET @@GLOBAL.GTID_PURGED statement
--databases productiondbinsure that the USE productiondb; is added