MySQL Backup Automation

Automated MySQL database backup solution with Azure integration for non-production environments.

Purpose

This script creates secure, manual backups of non-production MySQL databases with the following features:

• Creates complete dumps of multiple non-production databases
• Uses SecureString (DPAPI) for password storage
• Cleans up backups older than 7 days
• Uploads backups to Azure File Share
• Provides detailed logging of all operations

Prerequisites (run ONCE before first run .\Manual_db_backups_nonprod.ps1)

1. Encrypt and store MySQL password (Password should be retrieved from Azure Key Vault)

Navigate to Azure Portal:

1. In the search bar, type "AKV-OBOBA-EUS-NONPROD-001" and open the Key Vault
2. Under "Objects" section, click on "Secrets"
3. Find and click on the secret named "db-password"
4. Click "Show Secret" to reveal the value
5. Copy the secret value

Use the Azure Key Vault secret value in the next command:

Read-Host "Enter MySQL password" -AsSecureString |
    ConvertFrom-SecureString |
    Set-Content C:\Scripts\Manual_db_backups_nonprod_pwd.txt

2️. Encrypt and store Azure Storage Account Access Key

Navigate to Azure Portal:

1. In the search bar, type "saobobanonprod001" and open the Storage Account
2. Under "Security + networking" section, click on "Access keys"
3. Find "key1" and click "Show" to reveal the key
4. Copy the access key value

Use the Azure Storage Account Access Key in the next command:

   Read-Host "Enter Storage Account Access Key" -AsSecureString |
       ConvertFrom-SecureString |
       Set-Content C:\Scripts\Manual_db_backups_nonprod_storage_key.txt

Running the Backup

1. Navigate to the script directory:

   cd C:\Scripts

2. Execute the backup script:

   .\Manual_db_backups_nonprod.ps1

What Happens When You Run the Script

1. Connection Setup

   • Loads stored credentials securely
   • Establishes connection to MySQL database
   • Verifies Azure Storage access

2. Backup Process

   • Creates a timestamped SQL dump file
   • Saves to: C:\sql_backup\dev\backup_oboba_cms_db_dev_<timestamp>.sql C:\sql_backup\qa\backup_oboba_cms_db_qa_<timestamp>.sql C:\sql_backup\stage\backup_oboba_cms_db_stage_<timestamp>.sql C:\sql_backup\uat\backup_oboba_cms_db_<timestamp>.sql
   • Logs all actions to: C:\sql_backup\logs\Manual_backup_nonprod_<timestamp>.log

3. Cloud Sync

   • Uploads the backup to Azure File Share: saobobanonprod001/mysql-backups-nonprod/
   • Organizes files in subfolders by environment (dev/qa/stage/uat)
   • Verifies successful upload

4. Cleanup

   • Removes local backups older than 7 days
   • Cleans up old backups in Azure File Share
   • Securely clears all sensitive data from memory