Backing up databases regularly is essential for data recovery and continuity. In this guide, we’ll automate backups for MySQL and PostgreSQL using Bash and Python, and for MSSQL with PowerShell. Each script will include scheduling options and a way to manage backup retention.
Prerequisites
- Database Clients: Ensure mysql, pg_dump, and sqlcmd (or SQL Server Management Objects) are installed.
- Permissions: Ensure the script user has permissions to read databases and write backups.
- Scheduling: We’ll cover how to schedule these scripts using cron for Linux and Task Scheduler for Windows.
Part 1: MySQL Backup Automation
Method 1: Bash Script
Bash Script for MySQL Backup:
#!/bin/bash
# Configuration
DB_NAME="your_database"
DB_USER="your_user"
DB_PASSWORD="your_password"
BACKUP_DIR="/path/to/backup/dir"
TIMESTAMP=$(date +"%F")
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_backup_$TIMESTAMP.sql"
# MySQL Dump Command
mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_FILE
# Compression (optional)
gzip $BACKUP_FILE
# Log completion
echo "MySQL Backup for $DB_NAME completed: $BACKUP_FILE.gz"
- Save this script as
mysql_backup.sh. -
Make it executable:
chmod +x mysql_backup.sh. - Add to cron for daily backups at midnight:
0 0 * * * /path/to/mysql_backup.sh >> /path/to/backup/logs/backup.log 2>&1
Method 2: Python Script
Python Script for MySQL Backup:
import os
from datetime import datetime
import subprocess
# Configuration
DB_NAME = "your_database"
DB_USER = "your_user"
DB_PASSWORD = "your_password"
BACKUP_DIR = "/path/to/backup/dir"
TIMESTAMP = datetime.now().strftime("%Y-%m-%d")
BACKUP_FILE = os.path.join(BACKUP_DIR, f"{DB_NAME}_backup_{TIMESTAMP}.sql")
# MySQL Dump Command
command = f"mysqldump -u {DB_USER} -p{DB_PASSWORD} {DB_NAME} > {BACKUP_FILE}"
subprocess.call(command, shell=True)
# Optional compression
subprocess.call(["gzip", BACKUP_FILE])
print(f"MySQL backup for {DB_NAME} completed: {BACKUP_FILE}.gz")
- Run as a cron job similar to the Bash script:
0 0 * * * python3 /path/to/mysql_backup.py >> /path/to/backup/backup.log 2>&1
Part 2: PostgreSQL Backup Automation
Method 1: Bash Script
Bash Script for PostgreSQL Backup:
#!/bin/bash
# Configuration
DB_NAME="your_database"
DB_USER="your_user"
BACKUP_DIR="/path/to/backup/dir"
TIMESTAMP=$(date +"%F")
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_backup_$TIMESTAMP.sql"
# PostgreSQL Dump Command
PGPASSWORD="your_password" pg_dump -U $DB_USER $DB_NAME > $BACKUP_FILE
# Compression (optional)
gzip $BACKUP_FILE
# Log completion
echo "PostgreSQL Backup for $DB_NAME completed: $BACKUP_FILE.gz"
- Save as
postgres_backup.sh. - Set cron job:
0 0 * * * /path/to/postgres_backup.sh >> /path/to/backup/backup.log 2>&1
Method 2: Python Script
Python Script for PostgreSQL Backup:
import os
from datetime import datetime
import subprocess
# Configuration
DB_NAME = "your_database"
DB_USER = "your_user"
BACKUP_DIR = "/path/to/backup/dir"
TIMESTAMP = datetime.now().strftime("%Y-%m-%d")
BACKUP_FILE = os.path.join(BACKUP_DIR, f"{DB_NAME}_backup_{TIMESTAMP}.sql")
# PostgreSQL Dump Command
command = f"PGPASSWORD='your_password' pg_dump -U {DB_USER} {DB_NAME} > {BACKUP_FILE}"
subprocess.call(command, shell=True)
# Optional compression
subprocess.call(["gzip", BACKUP_FILE])
print(f"PostgreSQL backup for {DB_NAME} completed: {BACKUP_FILE}.gz")
- Schedule with cron:
0 0 * * * python3 /path/to/postgres_bkp.py >> /path/to/backup/backup.log 2>&1
Part 3: MSSQL Backup Automation with PowerShell
PowerShell Script for MSSQL Backup:
# Configuration
$serverName = "your_server"
$dbName = "your_database"
$backupDir = "C:\path\to\backup\dir"
$timestamp = Get-Date -Format "yyyy-MM-dd"
$backupFile = "$backupDir\$dbName`_backup_$timestamp.bak"
# SQL Backup Command
Invoke-Sqlcmd -ServerInstance $serverName -Query "BACKUP DATABASE [$dbName] TO DISK = N'$backupFile' WITH NOFORMAT, NOINIT, NAME = '$dbName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
# Optional compression with 7-Zip (if installed)
& "C:\Program Files\7-Zip\7z.exe" a "$backupFile.zip" "$backupFile"
Remove-Item $backupFile
Write-Output "MSSQL Backup for $dbName completed: $backupFile.zip"
- Save as
mssql_backup.ps1. - Schedule with Windows Task Scheduler: Create a new task, set the action to run powershell.exe with the script path as an argument. Set the schedule (e.g., daily at midnight).
Backup Retention Policy
To avoid running out of storage, consider setting a retention policy to delete backups older than a specified number of days.
Bash Command:
find /path/to/backup/dir -type f -mtime +30 -name "*.gz" -exec rm {} \;
PowerShell Command:
$backupDir = "C:\path\to\backup\dir"
Get-ChildItem -Path $backupDir -Filter "*.zip" | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-30) } | Remove-Item
Add these commands to the end of each script or as separate scheduled tasks to keep backup directories clean and organized.
Conclusion
With these scripts, you’ll have a reliable way to automatically back up MySQL, PostgreSQL, and MSSQL databases, reducing the risk of data loss. Remember to test the backups regularly to ensure data integrity and be ready to restore when needed!
🔗 Support my Work
▶️ Support by Subscribing my YouTube
▶️ Explore more open-source tutorials on my website
▶️ Follow me on X
☕ Buy me a Coffee
Learn Complete Bash CLI Below:
Top comments (3)
Nice article. A few comments.
You really can't have passwords in the clear in any non-compiled code and they're only slightly safer in compiled code. This leaves whatever doors they open completely unlocked.
The bash versions may even put the clear password in the system logs that anyone can probably read.
Fixing this is a bit tricky for scripts that need to run unattended. This discussion is pretty good.
A few of your scripts have echos or print()s to the console. That works great when you run them manually in a terminal, but if you run them from cron or even from a GUI, those go to /dev/null by default. You have to do something like explicitly write them to a log file or display them in a dialog manager pop-up for GUI execution.
There's a limit to how many details you want in examples for articles, but since you depend on backups, you really need to check the actual backup command return codes for successful completion or handle exceptions in Python. The last thing you need is a false sense of security!
Joe, this has been the most comprehensive feedback i received. These are valuable suggestions and could help many.
In my old, mostly dead project, sourceforge.net/projects/duplexpr/, I included a line in the README with something like, "I stand of the toes of giants." (Nowhere near as high as "shoulders" and much more painful based on all the questions they had to answer for me.) :)
If it hadn't been for the local LUG I joined, I might not have even used Linux for many more years even though I already had some familiarity with UNIX and a little with C. (Stackexchange wasn't a thing yet.)
I can never pay it all forward.