How to schedule export and import of Aurora snapshots across AWS accounts

I am trying to build the following process, to be ran on a schedule:

  1. Export, from an AWS account A, the content of an Aurora database
  2. Transfer that dump to another AWS account B
  3. Import the dump in another Aurora database on the account B

Here are two of my constraints:

  • I need to be able to put the system in place for multiple databases with different target DBs
  • The import DB might not have the same name as the export DB
  • Both databases are in the same region

I looked online quite a bit and I was able to find the following article: Automating Cross-Region and Cross-Account Snapshot Copies with the Snapshot Tool for Amazon Aurora

However, it doesn’t cover automatically restoring the backup and my attempts at finding a way to do so (using a Lambda for example) have not yield great results.

Is there a way to do so?

Thanks

RDS Aurora Serverless “Parameter Groups”

I have an RDS Aurora Serverless MySQL cluster, and I am trying to change a MySQL setting (connect_timeout). Normally, you would use a Parameter Group to set the value on the DB instance. But, since this is serverless, the instances are all managed by AWS, so it seems I can only configure the cluster.

Is there a way to set the Parameter Group that is used by the instances that AWS creates?

PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded WITH AWS Aurora

We are using Drupal 7.61 and Aurora 5.6.10. Recently, we migrated our database to AWS Aurora with 16 CPU, 122G RAM. Our website doesn’t have that much of traffic. Before, we were using MYSQL with 4 CPU and 30 RAM and worked perfectly. However, we got lots of Locks since we migrate our database.

Here are some of our configuration:
connect_timeout 60
wait_timeout 300
innodb_lock_wait_timeout 60
max_allowed_packet 1073741824
CPU usage around 8%

Keep getting the error when customer tried to checkout:

PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: SELECT revision.order_number AS order_number, revision.revision_id AS revision_id, revision.revision_uid AS revision_uid, revision.mail AS mail, revision.status AS status, revision.log AS log, revision.revision_timestamp AS revision_timestamp, revision.revision_hostname AS revision_hostname, revision.data AS data, base.order_id AS order_id, base.type AS type, base.uid AS uid, base.created AS created, base.changed AS changed, base.placed AS placed, base.hostname AS hostname FROM {commerce_order} base INNER JOIN {commerce_order_revision} revision ON revision.revision_id = base.revision_id WHERE (base.order_id IN (:db_condition_placeholder_0)) FOR UPDATE; Array ( [:db_condition_placeholder_0] => 123234) in DrupalDefaultEntityController->load() (line 198 of /var/www/html/includes/entity.inc).

Strongly consistent backup of Mongo with Aurora at the exact same time

At my employer we use AWS Aurora DB & MongoDB Atlas hosted databases together on one application.

So we have nail biting scenarios where records are created on both and typically MongoDB points to records on Aurora that it needs. If there is some sync issue, like the data is on Mongo, but not Aurora and vice versa, this translates to terrible UXes with permission issues.

So my question is to do with backups. These systems must be snapshot at the exact same point of time. I am thinking ideally without any client usage at an early hour like 2AM, i.e. it’s brought down for a few minutes whilst snapshots are being made.

Actually I am not sure how to limit client access whilst still being able to run mongodump or an RDS snapshot. Am I missing a better approach to backup these two data stores together?

Perhaps I can take a strongly consistent backup at a certain point in time?

You might be thinking, what happens if you take a snapshot at 2AM and you lose everything at 10AM. This is acceptable RPO.