Backup MySQL Amazon RDS

user_665616 0
Viewed 12746

I am trying to setup Replica outside of AWS and master is running at AWS RDS. And I do not want any downtime at my master. So I setup my slave node and now I want to backup my current database which is at AWS.

mysqldump -h RDS ENDPOINT -u root -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 --all-databases > /root/dump.sql

I tested it on my VM and it worked fine but when tying it with RDS it gives me error

mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'root'@'%' (using password: YES) (1045)

Is it because i do not have super user privilege or how to I fix this problem? Please someone suggest me.

amazon-rds amazon-web-services mysqldump mysql
7 Answers

Thanks Michael, I think the most correct solution and the recommended by AWS is do the replication using a read replica as a source as explained here.

Having a RDS master, RDS read replica and an instance with MySQL ready, the steps to get an external slave are:

  1. On master, increase binlog retention period.

mysql> CALL mysql.rds_set_configuration('binlog retention hours', 12);

  1. On read replica stop replication to avoid changes during the backup.

mysql> CALL mysql.rds_stop_replication;

  1. On read replica annotate the binlog status (Master_Log_File and Read_Master_Log_Pos)

mysql> SHOW SLAVE STATUS;

  1. On server instance do a backup and import it (Using mydumper as suggested by Max can speed up the process).

mysqldump -h RDS_READ_REPLICA_IP -u root -p YOUR_DATABASE > backup.sql

mysql -u root -p YOUR_DATABASE < backup.sql

  1. On server instance set it as slave of RDS master.

mysql> CHANGE MASTER TO MASTER_HOST='RDS_MASTER_IP',MASTER_USER='myrepladmin', MASTER_PASSWORD='pass', MASTER_LOG_FILE='mysql-bin-changelog.313534', MASTER_LOG_POS=1097;

Relace MASTER_LOG_FILE and MASTER_LOG_POS to the values of Master_Log_File Read_Master_Log_Pos you saved before, also you need an user in RDS master to be used by slave replication.

mysql> START SLAVE;

  1. On server instance check if replication was success.

mysql> SHOW SLAVE STATUS;

  1. On RDS read replica resume replication. mysql> CALL mysql.rds_start_replication;
user_11994030

I had faced a similar problem a quick workaround to this is:

  1. Create a EBS Volume to have an extra space or extend current EBS volume on EC2. (or if you have an extra space you can use that).

  2. Use mysqldump command without --master-data or --flush-data directive to generate a complete (FULL) backup of db.

    mysqldump -h hostname --routines -uadmin -p12344 test_db > filename.sql

admin is DB name and 12344 is the password

Above is for taking backup of one single DB, if required to take all DBs then specify --all-databases and also mention DB Names.

  1. Create a Cron of this command to run once a day that will automatically generate the dump.

Please note that this will incur an extra cost if your DB Size is huge. as it creates a complete DB dump.

hope this helps

user_92719340

You need to

1- create a read replica on aws

2- make sure that this instance is catching up with the master

3- stop the replication and get the log_file and log_position parameters by

show slave status \G

4- dump the database and use the parameters logged in step 3 to start the replication on your own server.

5- start the slave.

Detailed instructions here

user_51472620
Related