0

Error trying to restore AWS MySQL RDS using backup generated by mysqldump

MySQL 8

I have an AWS MySQL RDS, let's call it:

productionrds.xxxx.us-east-1.rds.amazonaws.com

I created a snapshot and restored it to

stagingrds.xxxx.us-east-1.rds.amazonaws.com

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

Any ideas?

SOLUTION:

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=OFF gets rid of the SET @@SESSION.SQL_LOG_BIN statements as well as the SET @@GLOBAL.GTID_PURGED statement
  • --databases productiondb insure that the USE productiondb; is added
Improve this question
3
  • --databases productiondb insure that the USE productiondb; This is a side effect. Mainly this option specifies the databases list to be dumped and allows to dump multiple databases (but forbids to specify separate table list to be dumped w/o explicit --tables option).
    – Akina
    Sep 23, 2022 at 6:52
  • mysqldump [options] [db_name [tbl_name ...]] -- That is, you may need to move the db_name to after all the options.
    – Rick James
    Sep 23, 2022 at 15:51
  • 1
    The "solution" part should go into the Answer field 👇; that way people can find it more easily and upvote it if they find it helpful.
    – mustaccio
    Sep 23, 2022 at 19:43

1 Answer 1

Reset to default
2

I tried you solution and it succeeded and I tried another solution where you give the master user these privilege with

GRANT ROLE_ADMIN on *.* to NAME_OF_YOUR_MASTER_YSER;

GRANT SESSION_VARIABLES_ADMIN on *.* to NAME_OF_YOUR_MASTER_YSER;

GRANT SYSTEM_VARIABLES_ADMIN on *.* to NAME_OF_YOUR_MASTER_YSER;

I got this solution from here.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.