Automated Nightly Backup of MyDatabase via Cron

Schedule Regular Backups Using a Cron Job

Write a command-line entry to schedule a nightly backup of "MyDatabase" using mysqldump.

Solution:

# This cron job schedules a daily backup of MyDatabase at 2:00 AM
# 0 2 * * * means run at minute 0, hour 2, every day, every month, every weekday
# mysqldump -u root -pYourPassword performs the backup as root with the specified password
# > redirects the output to a file in /backup/ with a date-stamped filename (e.g., MyDatabase_2025-02-28.sql)
# $(date +\%F) inserts the current date in YYYY-MM-DD format (escaped % for crontab)
0 2 * * * mysqldump -u root -pYourPassword MyDatabase > /backup/MyDatabase_$(date +\%F).sql

Explanation:

• Purpose of the Query:
• To automate regular backups of the "MyDatabase" database every night at 2 AM.
• Demonstrates scheduling using cron jobs on Unix-like systems.
• Key Components:
• 0 2 * * * : Cron schedule for 2:00 AM daily.
• $(date +\%F) : Appends the current date to the backup filename.
• Real-World Application:
• Automation minimizes the risk of human error and ensures up-to-date backups.

Note:

• Store the cron job securely and monitor backup logs for failures.
• Replace "YourPassword" with a secure method of password handling.

For more Practice: Solve these Related Problems:

• Write a command-line entry to schedule a nightly backup of "MyDatabase" at 3 AM with a date-stamped filename.
• Write a command-line entry to schedule a backup of "MyDatabase" every Sunday at 2 AM and send an email notification upon completion.
• Write a command-line entry to schedule a backup of "MyDatabase" that compresses the backup file and logs the backup status to a file.
• Write a command-line entry to schedule a backup of "MyDatabase" at 2 AM with error handling to retry on failure.

Go to:

• MySQL Backup and Restore Techniques Exercises Home. ↩
• MySQL Exercises Home ↩

PREV : Backup Database Incrementally Using Binary Logs.
NEXT : Backup Database Using MySQL Enterprise Backup.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.