MySQL backup with AWS Lambda

After a long process of work and many reflections, I want to share with you the general outline of the process of building the database backup system. This system, which leverages the integration between Next.js applications, AWS Lambda, and Amazon S3, has been designed to ensure the efficiency and scalability of backup operations.

Backup Request from Next.js

The user interacts with the Next.js application interface, clicking a "Backup" button that triggers a JavaScript function. The JavaScript function collects the necessary information, such as the database URL (containing credentials and connection details), and sends a POST request to a specific API endpoint exposed by the Next.js application (/api/database/backup). The request includes in its body the necessary details for the backup, such as the database URL.

Interaction with the Lambda Function

The Next.js API endpoint (/api/database/backup) receives the request and acts as an intermediary. It extracts the database details from the request and invokes the AWS Lambda function responsible for executing the backup, passing the database details as part of the payload. The Lambda function, hosted on AWS, runs in response to the call. It has access to preconfigured environment variables and uses these, along with the received database details, to establish a connection to the database.

Executing the Backup

Within the Lambda function, a mysqldump command (or an equivalent command, depending on the type of database) is dynamically generated and executed using spawn from the Node.js child_process module. This command creates a dump of the database and saves it in a temporary file in the /tmp directory of the Lambda environment. Subsequently, the dump file is compressed using tar, also invoked through the child_process module, generating a .tar.gz file.

Uploading to Amazon S3

After creating the compressed archive, the Lambda function proceeds with uploading the file to a specified Amazon S3 bucket set in the environment variables. It uses the S3 client (@aws-sdk/client-s3) to upload the compressed file to the bucket. After a successful upload, the Lambda function generates a presigned S3 URL using @aws-sdk/s3-request-presigner. This URL allows for secure download of the backup file, providing temporary access to the file in the S3 bucket.

Cleanup and Response

Once the upload is complete, the Lambda function performs cleanup by deleting the temporary files in the /tmp directory to avoid exhausting the available space in future executions. The Lambda function sends a response to the Next.js application, including the presigned S3 URL for the backup download. The Next.js application, in turn, conveys this information to the user via the user interface, allowing for direct download of the backup.