Hi
Wonder if you can help, we are looking for an enterprise grade backup solution for RDS MySQL/Maria. Percona XtraBackup looks the most suitable Candidate for consistent MYSQLL backups however when we try we get
ups’ (using password: YES).
230627 13:24:02 version_check Connected to MySQL server
230627 13:24:02 version_check Executing a version check against the server…
230627 13:24:02 version_check Done.
230627 13:24:02 Connecting to MySQL server host: .eu-west-1.rds.amazonaws.com, user: backups, password: set, port: 3306, socket: not set
Using server version 10.6.12-MariaDB-log
xtrabackup version 2.4.28 based on MySQL server 5.7.40 Linux (x86_64) (revision id: 44a8f7b)
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/
We understand that this is in part is due to percona not being able to access the rds file system.
To set the scene we ostensively come from a large SQL Server estate and our looking for a corporate grade backup solution for RDS with Transaction Logging etc. We currently using Redgate SQL Backup for point in time recovery to offshore backups to S3 etc can you recommend any solutions on RDS. MySQLDump and Maria-dump dont seem to offer the capacity for large database backups. How can we do this as such in the RDS world.
Any help or guidance would be appreciated
We understand that this is in part is due to percona not being able to access the rds file system.
Correct. Because this is RDS, there is no option to use external tools that need filesystem access.
How can we do this as such in the RDS world.
There are two options: 1) Use [mydumper](https://mydumper/mydumper: Official MyDumper project) which can create logical backups. The benefit to mydumper is that it is multi-threaded (mysqldump is single table/thread). 2) RDS snapshots. This is the native functionality provided by AWS which are just disk-based snapshots. The snaps go into S3 along with transaction logs (known in the MySQL world as “the binary logs”).
This is the blessing/curse behind managed DBaaS. You become limited by what you can do and are at the mercy of the DBaaS provided in what they give you.
You can also create another MySQL server, anywhere, and configure that as a replica of your RDS which in effect becomes a hot standby/DR. Since this is native functionality of MySQL, you wouldn’t need Redgate anymore.
As a final option, scrap the RDS and just run MySQL yourself on an EC2 instance. The performance is the same (might be a tad better actually because then you can install Percona MySQL vs Community MySQL). You’d have complete control and be able to do backups with Percona Xtrabackup, if that was the original goal.
Hi Matthew, really appreciate you taking the time to respond the mysql world seems a little more like working in the 1990’s to SQL Server DBAs’ with the toolsets we use. Thanks for the definitive answer
With a MySQL Replica, could the hot standby be used for the purpose of backups in a similar manner to the concept you have in SQL Server with Availability Groups with Full Backups being made from a secondary replica for the mysql equivalent and or binary logs. i.e. to use Xtra Backup to backup from the replica node if that node was not in RDS.
Or alternatively could Percona on the mysql replica to used take a full onetime transactionally consistent backup, we call this a copy_backup. These do not break the log sequence in the SQL Server world.
If this were then we might consider falling back to RDS snapshotting for the last 48 hours with a fallback keeping a longer retention full copy only or such like from Percona in S3. Our aim is to keep longer term backups somewhere else than RDS/RDS Snapshots e.g in S3 or Vault.
Appreciate we may be at the mercy of AWS, RDS and MySQL Constraints.
Thanks Again it all seems a dark art.
Despite historically (10+ yrs), and currently, being a more popular database than SQL Server. 
I’ve never touched SQL Server, so your tools would be completely unfamiliar and a dark art to me.
Yes. If the replica was a non-RDS MySQL install, you would be able to use PXB to perform physical backups.
Backups taken with PXB are always like this, transactionally consistent; unless you purposefully tell it not to.