1. 創(chuàng)建一個包含數(shù)據(jù)庫配置和mysqldump命令的shell腳本，並保存為mysql_backup.sh；2. 通過創(chuàng)建~/.my.cnf文件存儲MySQL憑證並設(shè)置600權(quán)限以提升安全性，修改腳本使用配置文件認(rèn)證；3. 使用chmod x使腳本可執(zhí)行並手動測試備份是否成功；4. 通過crontab -e添加定時任務(wù)，例如0 2 /path/to/mysql_backup.sh >> /path/to/backup/backup.log 2>&1，實現(xiàn)每日凌晨2點自動備份並記錄日誌；5. 在腳本中添加find命令如find $BACKUP_DIR -name "${DB_NAME}backup.sql" -type f -mtime 7 -delete 2>/dev/null，自動刪除7天前的舊備份；最終通過腳本、安全憑證和cron結(jié)合實現(xiàn)可靠自動化MySQL備份，且建議壓縮備份文件、定期監(jiān)控日誌並考慮將備份存儲到離線或云存儲位置以增強(qiáng)數(shù)據(jù)安全性。

Automating MySQL backups with a script is a reliable way to ensure your databases are regularly backed up without manual intervention. Here's how to set it up on a Linux system using a shell script and cron.

1. Create a Backup Script

Start by writing a shell script that uses mysqldump to create a backup of your MySQL database.

 #!/bin/bash

# Configuration
DB_USER="your_mysql_user"
DB_PASS="your_mysql_password"
DB_NAME="your_database_name"
BACKUP_DIR="/path/to/backup/directory"
TIMESTAMP=$(date "%Y%m%d_%H%M%S")
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_backup_$TIMESTAMP.sql"

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Perform the backup
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_FILE

# Check if backup was successful
if [ $? -eq 0 ]; then
    echo "Backup successful: $BACKUP_FILE"
else
    echo "Backup failed!"
    exit 1
fi

Note: There's no space between -p and the password in mysqldump . This is required for the command to work properly.

Save this script as mysql_backup.sh .

2. Secure Your Script and Credentials

Storing passwords in plain text is risky. For better security:

• Use a MySQL configuration file ( ~/.my.cnf ) to store credentials securely.

Create the file:

 nano ~/.my.cnf

Add:

 [client]
user=your_mysql_user
password=your_mysql_password

Set strict permissions:

 chmod 600 ~/.my.cnf

Then, update your script to remove the -u and -p arguments:

 mysqldump $DB_NAME > $BACKUP_FILE

Now mysqldump will automatically use the credentials from .my.cnf .

3. Make the Script Executable

 chmod x mysql_backup.sh

Test it manually:

 ./mysql_backup.sh

Check the backup directory to confirm the .sql file was created.

4. Schedule with Cron

Automate the script using cron .

Edit the crontab:

 crontab -e

Add a line to run the backup daily at 2 AM:

 0 2 * * * /path/to/mysql_backup.sh >> /path/to/backup/backup.log 2>&1

This will:

• Run the script every day at 2:00 AM
• Log output (including errors) to backup.log for monitoring

You can adjust the schedule:

• 0 2 * * 0 — Every Sunday at 2 AM
• 0 */6 * * * — Every 6 hours

5. Optional: Rotate Old Backups

To avoid filling up disk space, delete backups older than X days.

Add this to your script before the exit:

 # Keep only last 7 days of backups
find $BACKUP_DIR -name "${DB_NAME}_backup_*.sql" -type f -mtime 7 -delete 2>/dev/null

Final Notes

• Test the script and cron job thoroughly.
• Store backups off-server when possible (eg, cloud storage).
• Monitor logs regularly to catch failures early.
• Consider compressing backups with gzip to save space:

   gzip $BACKUP_FILE

  Basically, a simple script secure credentials cron = reliable automated MySQL backups.

  以上是如何使用腳本自動化MySQL備份？的詳細(xì)內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！