How to Backup MySQL Databases on Ubuntu Automatically Daily

If you’re using Ubuntu and need to back up your MySQL databases regularly, automating the backup process is a great solution. In this guide, we’ll show you how to create a bash script to back up all your MySQL databases, compress the backups, and schedule them to run automatically every day at 11:00 PM.

Step 1: Write a Bash Script to Backup MySQL Databases

Create a new file for the backup script (e.g., mysql_backup.sh) and paste the following content:

#!/bin/bash

# MySQL login information
MYSQL_USER="root"         # MySQL username
MYSQL_PASS="your_password" # MySQL password
BACKUP_DIR="/home/mysql/backup"  # Backup directory

# Get current date in MM_DD_YYYY format
DATE=$(date +"%m_%d_%Y")

# Create a directory for today's backup if it doesn't exist
mkdir -p "$BACKUP_DIR/$DATE"

# Get a list of all databases (excluding system databases)
DATABASES=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW DATABASES;" | grep -Ev "(information_schema|performance_schema|mysql)")

# Loop through each database and create a backup
for DB in $DATABASES; do
    # Create a backup of each database and compress it with gzip
    mysqldump -u $MYSQL_USER -p$MYSQL_PASS --databases $DB | gzip > "$BACKUP_DIR/$DATE/$DB.tar.gz"
done

# Remove backups older than 7 days (optional, customize as needed)
find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;

echo "Backup completed for all databases on $DATE."

Explanation of the Script:

• MYSQL_USER and MYSQL_PASS: Enter your MySQL username and password.
• BACKUP_DIR: The directory where backups will be stored.
• DATE: This retrieves the current date in the format MM_DD_YYYY for organizing backups by date.
• DATABASES: Fetches the list of databases, excluding system databases like information_schema, performance_schema, and mysql.
• mysqldump: Backs up each database and compresses it using gzip.
• find: Cleans up backups older than 7 days (you can adjust this retention period as needed).

Step 2: Grant Execute Permission for the Script

After creating the script, you’ll need to make it executable. Run the following command:

chmod +x /path/to/mysql_backup.sh

Step 3: Set Up a Cron Job to Run the Script Automatically

You can use cron to automate the script so it runs at 11:00 PM every day. To edit your cron jobs, run:

crontab -e

Then, add the following line to the end of the file:

0 23 * * * /path/to/mysql_backup.sh

Explanation:

• 0 23 * * *: This cron schedule means the job will run every day at 11:00 PM (23:00).
• /path/to/mysql_backup.sh: The full path to the backup script you created.

Step 4: Verify Permissions and Ownership

Ensure that the MySQL user has proper permissions and that the backup directory /home/mysql/backup is writable. The script should be able to run without issues if the permissions are correct.

You can check the status of cron to ensure it’s running correctly with:

systemctl status cron

Step 5: Check Cron Job Logs

Cron jobs run in the background, so you can check the cron logs to confirm that the script runs as expected:

grep CRON /var/log/syslog

If the script executes successfully, you’ll see new backup files in /home/mysql/backup.

---

Additional Tips

• Make sure the MySQL user has adequate privileges for accessing and backing up databases.
• You may want to change the password in the script for better security or consider using a secure configuration file to store sensitive information.
• Monitor disk space to ensure there’s enough room to store the backups, especially if you have large databases.

---

Conclusion

By following these steps, you’ll have a fully automated system to back up your MySQL databases every day at 11:00 PM. This ensures you have up-to-date backups available in case of an emergency, without any manual intervention.