Export AWS RDS mysql Database to S3 Bucket using xtrabackup or any other method

Question

I have an AWS RDS MySQL Database. The size of the Database is around of 1.5 GB. We are using MySQL 5.7.32.

We tried with SQL dump but in dump data is missing in Dump.

We found out that we can use xtrabackup. We are using xtrabackup version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64).

We used below command

sudo ./xtrabackup --backup --user=user --host=project.eu-central-1.rds.amazonaws.com --password=password --databases=database--port=3306

Error was like

xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments: --backup=1 --user=user --host=project.eu-central-1.rds.amazonaws.com --password=* --databases=database --port=3306
210811 05:31:47  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=project.eu-central-1.rds.amazonaws.com;port=3306' as 'user'  (using password: YES).
210811 05:31:47  version_check Connected to MySQL server
210811 05:31:47  version_check Executing a version check against the server...
210811 05:31:48  version_check Done.
210811 05:31:48 Connecting to MySQL server host: project.eu-central-1.rds.amazonaws.com, user: user, password: set, port: 3306, socket: not set
Using server version 5.7.12-log
./xtrabackup version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5)
xtrabackup: uses posix_fadvise().
xtrabackup: Can't change dir to '/rdsdbdata/db/' (Errcode: 2 - No such file or directory)
xtrabackup: cannot my_setwd /rdsdbdata/db/

Answer 1

RDS already has an 'Export to S3' button. Alternatively you can use AWS Data Migration Service (DMS) to export the data to S3 in real-time.

And as you point out, regular mydumper will also work because RDS is a regular MySQL-compatible database.

Answer 2

We need to trace why data missing, we have taken backup many time from rds without any issues. We have more than 80gb data. 2ndly xtrabackup needs access to mysql data directory so can't be used with rds.

Just mysql dump can do the job. Also rds offers S3 export per table in few format like parquet etc

Answer 3

Xtrabackup forum come to the rescue and cleared our doubt that it works only when we have physical access to the Database and suggested to use mydumper

mydumper - GitHub - maxbube/mydumper: Official mydumper project 1

It is working perfectly.