How to Automatically Back Up Your MySQL Database Using a Bash Script

If you run a WordPress blog, a custom web application, or a forum on a Linux server, your MySQL database holds the most critical data of your entire operation. A server crash or a corrupted table could wipe out years of content in seconds. Rather than relying on manual exports via phpMyAdmin, you can write a simple bash script that automatically generates a full SQL dump of your database every single night.

How to Write the Backup Script

We will use the native mysqldump utility, wrapped in a bash script that automatically appends the current date to the filename so your backups are neatly organized.

1. Log into your Linux server via SSH.
2. Create a directory to store your backups safely: mkdir -p ~/mysql_backups
3. Create the shell script file: nano ~/backup_database.sh
4. Paste the following code into the editor. (Be sure to replace the placeholder database name, username, and password with your actual MySQL credentials):

   #!/bin/bash
   # Define variables
   BACKUP_DIR="/home/yourusername/mysql_backups"
   DATE=$(date +%Y-%m-%d_%H-%M-%S)
   DB_USER="your_db_username"
   DB_PASS="your_db_password"
   DB_NAME="your_database_name"
   
   # Run mysqldump
   mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_DIR/db_backup_$DATE.sql
   
   # Optional: Delete backups older than 7 days to save space
   find $BACKUP_DIR -type f -name "*.sql" -mtime +7 -exec rm {} \;

5. Save the file (press Ctrl+O, Enter, then Ctrl+X).
6. Make the script executable: chmod +x ~/backup_database.sh

Note: There is intentionally no space between the -p flag and your password in the mysqldump command.

How to Automate the Script Using Cron

Now that the script is ready, we need to instruct the Linux cron daemon to execute it automatically every night at 3:00 AM.

1. Open your user’s crontab file by running: crontab -e
2. Scroll to the very bottom of the file and paste this line: 0 3 * * * /home/yourusername/backup_database.sh
3. Save and exit the crontab editor.

Your database is now protected. Every night at 3:00 AM, the server will silently generate a fresh .sql backup file in your designated directory, and automatically delete any files that are older than one week to prevent your hard drive from filling up.