Backing up your My SQL databases is crucial for data integrity and disaster recovery. But manually running `mysqldump` every day? That's tedious and error-prone. This tutorial will guide you through automating My SQL database backups using `cron`, the built-in Linux task scheduler. We'll cover everything from the basics to advanced techniques, ensuring your backups are reliable and secure. Whether you're a developer, system administrator, or Dev Ops engineer, this guide will equip you with the knowledge to automate your My SQL backups effortlessly.
Automating database backups offers several advantages. It eliminates the risk of human error, ensures consistent backups, and frees up your time for more important tasks. A reliable backup strategy is the cornerstone of any robust system, providing peace of mind and a safety net against data loss due to hardware failures, software bugs, or accidental deletions.
Here's a quick tip: Before diving into the full tutorial, try scheduling a simple command with `cron` to get familiar with the syntax. For example, `echo "Hello from cron!" >> /tmp/cron_test.txt` will append "Hello from cron!" to `/tmp/cron_test.txt` every minute. Check the file content after a minute to confirm it worked.
Key Takeaway: By the end of this tutorial, you'll be able to create and schedule cron jobs to automatically back up your My SQL databases regularly, ensuring data safety and reducing manual effort.
Prerequisites
Before you start, ensure you have the following: My SQL Server: A running My SQL server instance. You need the hostname, username, and password for a user with `SELECT` privileges on the databases you want to back up. Ideally, create a dedicated user with only `SELECT` and `LOCK TABLES` permissions for backup purposes. `mysqldump`: The `mysqldump` utility must be installed on your system. It's usually included with the My SQL client tools. You can install it using your system's package manager (e.g., `apt-get install mysql-client` on Debian/Ubuntu, `yum install mysql` on Cent OS/RHEL, or `brew install mysql-client` on mac OS). `cron`:The `cron` service must be running on your system. Most Linux distributions have it enabled by default.
To check if cron is running, use: `systemctl status cron` (on systems using systemd) or `/etc/init.d/cron status` (on older systems).
To start cron if it's not running: `sudo systemctl start cron` or `sudo /etc/init.d/cron start`. Basic Linux command-line knowledge: Familiarity with navigating directories, editing files, and running commands in the terminal. Permissions: You will need permissions to edit the crontab for the user you intend to run the backups as. Often, this will require `sudo` or root access.
Overview of the Approach
The basic approach involves creating a shell script that executes `mysqldump` to create a backup of your My SQL database and then scheduling this script to run automatically using `cron`. The script will:
- Use `mysqldump` to export the database to a `.sql` file.
- Optionally, compress the `.sql` file using `gzip` to save disk space.
- Optionally, rotate older backups to prevent excessive disk usage.
Here's a simplified diagram of the workflow:
```text
+-----------------+ +---------------------+ +---------------------+ +-------------------+
Cron Daemon --> Execute Backup --> mysqldump & gzip --> Store Backup File (schedules job) Shell Script (create backup) +-----------------+ +---------------------+ +---------------------+ +-------------------+ ``` Step-by-Step Tutorial
Here are two examples to demonstrate how to run My SQL dumps automatically with cron: a simple, minimal example and a more robust example with locking, logging, and error handling.
Example 1: Simple My SQL Backup with Cron
This example provides a basic, functional script for backing up a single database and scheduling it with `cron`.
1. Create the Backup Script
Create a file named `backup_mysql.sh` (or any name you prefer) and add the following content:
```bash
#!/bin/bash
Simple My SQL backup script
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_NAME="your_db_name"
BACKUP_DIR="/opt/backups/mysql"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$DATE.sql.gz"
Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"
Perform the backup
mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$BACKUP_FILE"
echo "Backup created: $BACKUP_FILE"
```
Explanation
`#!/bin/bash`: Shebang line, specifies the script interpreter. `DB_USER`, `DB_PASS`, `DB_NAME`, `BACKUP_DIR`: Variables to store database credentials, database name, and the backup directory. Replace these with your actual values. `DATE=$(date +%Y-%m-%d_%H-%M-%S)`: Generates a timestamp for the backup filename. `BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$DATE.sql.gz"`: Constructs the full path to the backup file. `mkdir -p "$BACKUP_DIR"`: Creates the backup directory if it doesn't exist. The `-p` option creates parent directories as needed and doesn't error if the directory already exists. `mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$BACKUP_FILE"`: This is the core command. It uses `mysqldump` to export the specified database, pipes the output to `gzip` for compression, and redirects the compressed output to the backup file. WARNING: Storing the password directly in the script is insecure. See the "Best practices & security" section for safer alternatives. `echo "Backup created: $BACKUP_FILE"`: Prints a message indicating the backup file created.
2. Make the Script Executable
```bash
chmod +x backup_mysql.sh
```
3. Schedule the Backup with Cron
Open the crontab for the current user (or use `sudo crontab -e` to edit the root crontab):
```bash
crontab -e
```
Add the following line to the crontab file to run the script daily at 2:00 AM:
```text
0 2 /path/to/backup_mysql.sh
```
Explanation
`0 2`: This is the cron schedule. It means: `0`: Minute 0 of the hour.
`2`: Hour 2 (2 AM).
``:Every day of the month.
``:Every month.
``:Every day of the week. `/path/to/backup_mysql.sh`: The full path to your backup script. Replace this with the actual path.
4. Verify the Installation
To verify that the cron job has been installed, run:
```bash
crontab -l
```
This will list the current user's crontab entries, including the one you just added.
5. Check the backup was successfully created
After the scheduled time (2:00 AM in this example), check if the backup file was created in the specified `BACKUP_DIR`. You can also manually run the script to test it:
```bash
/path/to/backup_mysql.sh
```
Check the output to ensure the backup was created successfully. If it fails, check the script for errors and verify the My SQL credentials.
Example 2: Robust My SQL Backup with Locking, Logging, and Error Handling
This example demonstrates a more advanced script with locking, logging, and error handling. It also includes an example of reading credentials from an environment file, which is a more secure alternative to embedding them directly in the script.
1. Create the Backup Script
Create a file named `backup_mysql_robust.sh` and add the following content:
```bash
#!/bin/bash
Robust My SQL backup script with locking, logging, and error handling
Requires environment file: /opt/backups/mysql/.env
Load environment variables
set -o allexport; source /opt/backups/mysql/.env; set +o allexport
DB_NAME="$MYSQL_DATABASE"
BACKUP_DIR="/opt/backups/mysql"
LOG_FILE="$BACKUP_DIR/backup.log"
LOCK_FILE="/tmp/mysql_backup.lock"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$DATE.sql.gz"
Function to log messages
log() {
echo "$(date +%Y-%m-%d %H:%M:%S) - $1" >> "$LOG_FILE"
}
Function to handle errors
error_exit() {
log "ERROR: $1"
exit 1
}
Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"
error_exit "Failed to create backup directory" Acquire lock
if flock -n 9; then
log "Starting backup..."
# Perform the backup
mysqldump -u "$MYSQL_USER" -h "$MYSQL_HOST" -p"$MYSQL_PASSWORD" "$DB_NAME" | gzip > "$BACKUP_FILE" 2>> "$LOG_FILE"
if [ $? -eq 0 ]; then
log "Backup created successfully: $BACKUP_FILE"
else
error_exit "mysqldump failed"
fi
# Rotate backups (keep last 7)
find "$BACKUP_DIR" -name "$DB_NAME-.sql.gz" -type f -mtime +7 -delete 2>> "$LOG_FILE"
log "Old backups rotated"
log "Backup completed."
flock -u 9 # Release the lock
else
log "Another backup process is already running. Exiting."
exit 1
fi
exit 0
```
Explanation
`#!/bin/bash`:Shebang line. `set -o allexport; source /opt/backups/mysql/.env; set +o allexport`: Loads environment variables from the `/opt/backups/mysql/.env` file. The `set -o allexport` command marks all subsequently defined variables as exportable, meaning they will be available to child processes. `set +o allexport` disables this behavior after sourcing the file. `DB_NAME`, `BACKUP_DIR`, `LOG_FILE`, `LOCK_FILE`, `DATE`, `BACKUP_FILE`: Variables similar to the simple example, but using environment variables where appropriate. `log()`: A function to log messages to a log file, including a timestamp. `error_exit()`: A function to log an error message and exit the script with a non-zero exit code. `mkdir -p "$BACKUP_DIR"
error_exit "Failed to create backup directory"`: Creates the backup directory and exits if it fails. `flock -n 9`: This is the locking mechanism. `flock` attempts to acquire an exclusive lock on file descriptor 9. The `-n` option makes it non-blocking; if the lock is already held, it exits immediately. `mysqldump -u "$MYSQL_USER" -h "$MYSQL_HOST" -p"$MYSQL_PASSWORD" "$DB_NAME" gzip > "$BACKUP_FILE" 2>> "$LOG_FILE"`: The `mysqldump` command, using environment variables for credentials. Standard error (`2>`) is redirected to the log file. `if [ $? -eq 0 ]`: Checks the exit code of the `mysqldump` command. `$?` contains the exit code of the last executed command. An exit code of 0 indicates success. `find "$BACKUP_DIR" -name "$DB_NAME-.sql.gz" -type f -mtime +7 -delete 2>> "$LOG_FILE"`:This command rotates old backups, deleting any files older than 7 days. The standard error is redirected to the log file. `flock -u 9`: Releases the lock. 2. Create the Environment File
Create a file named `/opt/backups/mysql/.env` with the following content:
```text
MYSQL_USER="your_db_user"
MYSQL_PASSWORD="your_db_password"
MYSQL_HOST="localhost"
MYSQL_DATABASE="your_db_name"
```
Replace these with your actual values.
3. Secure the Environment File
Set appropriate permissions on the environment file to prevent unauthorized access:
```bash
chmod 600 /opt/backups/mysql/.env
chown root:root /opt/backups/mysql/.env
```
This restricts access to only the root user.
4. Make the Script Executable
```bash
chmod +x backup_mysql_robust.sh
```
5. Schedule the Backup with Cron
Open the crontab for the current user (or use `sudo crontab -e` to edit the root crontab):
```bash
crontab -e
```
Add the following line to the crontab file to run the script daily at 2:00 AM:
```text
0 2 /path/to/backup_mysql_robust.sh
```
Replace `/path/to/backup_mysql_robust.sh` with the actual path to your script.
6. Verify the Installation
To verify that the cron job has been installed, run:
```bash
crontab -l
```
7. Testing & Monitoring
After the scheduled time, check the log file (`/opt/backups/mysql/backup.log`) for any errors or messages. You can also manually run the script to test it:
```bash
/path/to/backup_mysql_robust.sh
```
Examine the log file after the run to confirm success or diagnose any issues.
How I tested this
I tested both scripts on an Ubuntu 22.04 system with My SQL
8.0 and cron version Vixie Cron
3.0 pl1-138. I installed the My SQL client tools with `sudo apt install mysql-client`. I ran the scripts manually to verify they created the backups and then scheduled them with cron, verifying their execution by checking the created files and log outputs.
Use-case scenario
Imagine a company running an e-commerce website with a My SQL database storing product information, customer data, and order details. To protect this critical data, they implement automated daily backups using the techniques described in this tutorial. The backups are stored on a separate server, ensuring that even if the primary server fails, they can quickly restore the database and minimize downtime.
Real-world mini-story
Sarah, a Dev Ops engineer, was constantly plagued by manual database backups. One night, a server crashed, and the last manual backup was a week old. After a stressful recovery, she implemented automated backups using `cron`, similar to the script above, including backup rotation. A few months later, a rogue script corrupted the database, but thanks to the automated backups, they were back online within an hour.
Best practices & security
File Permissions: Ensure your backup scripts are only readable and executable by the user running the cron job. Use `chmod 700 backup_mysql.sh` and `chown
: Avoid Plaintext Secrets: Never store database passwords directly in the script. Use environment variables, as demonstrated in the robust example, and secure the environment file with appropriate permissions. Consider using a secret management tool like Hashi Corp Vault for even greater security. Limit User Privileges: The My SQL user used for backups should have minimal privileges. Grant only `SELECT` and `LOCK TABLES` permissions. Log Retention: Implement a log rotation policy to prevent the log file from growing indefinitely. Tools like `logrotate` can automate this. Timezone Handling:Be aware of timezone differences between your server and the cron daemon. Consider setting the `TZ` environment variable in your cron file or using UTC for all server clocks. You can set timezone using `timedatectl set-timezonebackup_mysql.sh`. `. Troubleshooting & Common Errors
Cron Job Not Running:
Problem: The cron job is not executing.
Diagnosis: Check the cron logs (`/var/log/syslog` or `/var/log/cron` depending on your system).
Fix: Ensure the script is executable (`chmod +x`), the cron syntax is correct, and the script path is accurate. `mysqldump` Command Not Found:
Problem: The `mysqldump` command is not recognized.
Diagnosis: Verify that `mysqldump` is installed and in the system's PATH.
Fix: Install the My SQL client tools or provide the full path to `mysqldump` in the script (e.g., `/usr/bin/mysqldump`). Permission Denied:
Problem: The script cannot access the database or write to the backup directory.
Diagnosis: Check the permissions of the script, the backup directory, and the My SQL user's privileges.
Fix: Adjust permissions as needed, ensuring the script has write access to the backup directory and the My SQL user has the necessary privileges. Backup File Not Created/Empty:
Problem: The backup file is not created or is empty.
Diagnosis: Check the script's output and the My SQL error log for any errors during the backup process.
Fix: Verify the database credentials are correct, the database name is valid, and the My SQL server is running. Cron Job Overlapping:
Problem: Multiple cron jobs are running simultaneously if the database dump takes longer than the cron interval.
Diagnosis: Check the timestamps in the logs or use `ps` command to see if multiple backup processes are running.
Fix: Implement locking as shown in the robust example using `flock`.
Monitoring & Validation
Check Job Runs: Review the cron logs (`/var/log/syslog` or `/var/log/cron`) for job execution messages. You can use `grep CRON /var/log/syslog` to filter for cron-related entries. Exit Codes: Monitor the exit codes of the backup script. A non-zero exit code indicates an error. You can capture the exit code in your cron job and send an alert if it's not zero. Logging: Regularly review the backup script's log file for any errors or warnings. Implement a system to alert you if any errors are detected. Alerting Patterns: Integrate the backup process with a monitoring system (e.g., Nagios, Zabbix, Prometheus) to receive alerts if backups fail or take longer than expected. Consider sending email notifications upon backup completion.
Alternatives & Scaling
`systemd` Timers: A modern alternative to `cron` that offers more flexibility and control, especially on systems using systemd. Kubernetes Cron Jobs: If you're running your applications in Kubernetes, use Kubernetes Cron Jobs for scheduling backups. CI Schedulers: Use CI/CD tools like Jenkins or Git Lab CI to schedule backups, especially if your database is part of a larger application deployment pipeline. Dedicated Backup Software: For complex backup requirements, consider using dedicated backup software like Percona Xtra Backup or Veeam. Cloud Provider Solutions: Cloud providers like AWS, Azure and GCP offer managed database services with built-in backup and recovery options.
FAQ
How do I back up multiple databases?
You can either create separate cron jobs for each database or modify the script to iterate through a list of database names.
How do I restore a backup?
Use the `mysql` command-line client to import the `.sql` file: `mysql -u your_db_user -p your_db_name < your_backup_file.sql`
If the backup is compressed: `gunzip < your_backup_file.sql.gz | mysql -u your_db_user -p your_db_name`
How often should I run backups?
The frequency depends on your data change rate and recovery time objective (RTO). Daily backups are a good starting point, but you may need more frequent backups for critical data.
Can I back up to a remote server?
Yes, you can use `ssh` to securely copy the backup file to a remote server after it's created: `scp your_backup_file.sql.gz user@remote_server:/path/to/backup_directory`. However, consider the security implications and use SSH keys instead of passwords.
How do I exclude certain tables from the backup?
Use the `--ignore-table` option with `mysqldump`. For example: `mysqldump --ignore-table=your_db_name.your_table your_db_name > backup.sql`.
Conclusion
You've now learned how to automate My SQL backups using `cron`. Remember to test your backup and restore procedures regularly to ensure they work as expected. Automating your backups is a crucial step in ensuring the safety and availability of your data. Regular backups, reliable procedures, and proper configuration can prevent data loss and keep your applications running smoothly.