Implement the functionality to back up MySQL databases within the SafeBase project and automate the process using a scheduling tool. This will ensure regular database backups and provide the ability to restore specific versions when needed.
Context
Data loss can have significant consequences for any application. Backing up the database at regular intervals is crucial to ensure data security and availability. Automating this process will remove the need for manual intervention and minimize the risk of human error.
Objectives
Create a route (/backup) for manually triggering a database backup.
Implement a system to automate database backups at regular intervals using a scheduler (e.g., node-cron).
Save each backup with a timestamp to manage multiple versions.
Provide basic logging to monitor backup success or failure.
Acceptance Criteria
The /backup route triggers a database backup and stores it with a timestamp.
Automatic database backups occur at specified intervals.
Each backup includes relevant details (e.g., timestamp, database name).
Logs indicate whether the backup was successful or failed.
Compatible with Docker and containerized MySQL instances.
Tasks
Task 1: Create a route for manual database backup
[x] Subtask 1.1: Set up the /backup route in backup.js
[x] Create a new file backup.js in the routes directory.
[x] Implement the /backup route that handles POST requests.
[x] Import the route into the routes/index.js file and register it with Fastify.
[x] Subtask 1.2: Create a backup directory in the backend
[x] Use Node's fs module to check for the existence of a backup folder (e.g., backups/).
[x] If the directory does not exist, create it using fs.mkdirSync() before executing any backup commands.
[x] Subtask 1.3: Implement the database backup logic in the /backup route
[x] Use child_process.exec to run the mysqldump command for MySQL.
[x] Construct the command to include the database credentials (host, user, password) and specify the database to back up.
[x] Save the output of mysqldump into a file in the backups/ directory, using a timestamp in the filename for unique identification (e.g., backups/mydb_backup_<timestamp>.sql).
[x] Subtask 1.4: Add error handling and logging for the backup process
[x] Check the command execution status and log success or error messages.
[x] Send an appropriate response back to the client indicating whether the backup was successful or not.
[x] Handle potential errors (e.g., database connection issues, mysqldump command errors) and return a 500 status code with error details.
Task 2: Implement automated backups using node-cron
[x] Subtask 2.1: Install and set up node-cron for job scheduling
[x] Install node-cron as a dependency in the backend (npm install node-cron).
[x] Create a function to schedule automatic backups (e.g., daily at midnight).
[x] Within this function, reuse the logic from the /backup route to perform the database backup.
[x] Subtask 2.2: Handle the backup process in a reusable function
[x] Extract the backup logic from the /backup route into a separate utility function (e.g., performBackup()).
[x] Use this utility function in both the /backup route and the node-cron scheduler.
Task 3: Update Docker configuration for backup persistence
[x] Subtask 3.1: Add a volume to the Docker Compose file for backup persistence
[x] In docker-compose.yml, add a new volume for the backend service to store backups (e.g., backups-data).
[x] Mount this volume to the backups/ directory in the backend container to ensure backups are persisted.
Task 4: Testing and Validation
[x] Subtask 4.1: Test the /backup route
[x] Use Postman to send a POST request to /backup and verify that a backup file is created in the backups/ directory.
[x] Check the logs to ensure that success and error messages are logged correctly.
[x] Subtask 4.2: Test automated backups
[x] Start the application and verify that the scheduled backup occurs at the specified interval.
[x] Ensure that backup files are correctly named with timestamps and saved in the backups/ directory.
[x] Subtask 4.3: Test Docker persistence
[x] Stop and remove the backend container.
[x] Restart the container and verify that previous backups in the backups/ directory are still available.
Summary
Implement the functionality to back up MySQL databases within the SafeBase project and automate the process using a scheduling tool. This will ensure regular database backups and provide the ability to restore specific versions when needed.
Context
Data loss can have significant consequences for any application. Backing up the database at regular intervals is crucial to ensure data security and availability. Automating this process will remove the need for manual intervention and minimize the risk of human error.
Objectives
/backup) for manually triggering a database backup.node-cron).Acceptance Criteria
/backuproute triggers a database backup and stores it with a timestamp.Tasks
Task 1: Create a route for manual database backup
[x] Subtask 1.1: Set up the
/backuproute inbackup.jsbackup.jsin theroutesdirectory./backuproute that handlesPOSTrequests.routes/index.jsfile and register it with Fastify.[x] Subtask 1.2: Create a backup directory in the backend
fsmodule to check for the existence of a backup folder (e.g.,backups/).fs.mkdirSync()before executing any backup commands.[x] Subtask 1.3: Implement the database backup logic in the
/backuproutechild_process.execto run themysqldumpcommand for MySQL.mysqldumpinto a file in thebackups/directory, using a timestamp in the filename for unique identification (e.g.,backups/mydb_backup_<timestamp>.sql).[x] Subtask 1.4: Add error handling and logging for the backup process
mysqldumpcommand errors) and return a500status code with error details.Task 2: Implement automated backups using
node-cron[x] Subtask 2.1: Install and set up
node-cronfor job schedulingnode-cronas a dependency in the backend (npm install node-cron)./backuproute to perform the database backup.[x] Subtask 2.2: Handle the backup process in a reusable function
/backuproute into a separate utility function (e.g.,performBackup())./backuproute and thenode-cronscheduler.Task 3: Update Docker configuration for backup persistence
docker-compose.yml, add a new volume for the backend service to store backups (e.g.,backups-data).backups/directory in the backend container to ensure backups are persisted.Task 4: Testing and Validation
[x] Subtask 4.1: Test the
/backuproutePOSTrequest to/backupand verify that a backup file is created in thebackups/directory.[x] Subtask 4.2: Test automated backups
backups/directory.[x] Subtask 4.3: Test Docker persistence
backups/directory are still available.Resources
mysqldumpDocumentationfsModuleUML Diagrams References
Branch Name
feature/backup-automation