方案一：官方風格備份腳本（保留 5 天輪轉）

#!/bin/sh
# mysql_backup.sh
# 配置區
db_user="root"
db_passwd="password"
db_host="localhost"
backup_dir="/home/mybackup"
mysql="/usr/local/mysql/bin/mysql"
mysqldump="/usr/local/mysql/bin/mysqldump"
gzip="/bin/gzip"
mkdir="/bin/mkdir"
rm="/bin/rm"
mv="/bin/mv"

time="$(date +%d-%m-%y)"

# 權限檢查
test ! -w $backup_dir && echo "error: $backup_dir un-writeable." && exit 0
test ! -d "$backup_dir/backup.0/" && $mkdir "$backup_dir/backup.0/"

# 取得所有資料庫
all_db="$($mysql -u $db_user -h $db_host -p$db_passwd -Bse 'SHOW DATABASES')"

for db in $all_db; do
    $mysqldump -u $db_user -h $db_host -p$db_passwd $db | $gzip -9 > "$backup_dir/backup.0/$time.$db.gz"
done

# 輪轉：刪除最舊、依序遞增目錄編號
test -d "$backup_dir/backup.5/" && $rm -rf "$backup_dir/backup.5"
for int in 4 3 2 1 0; do
    if [ -d "$backup_dir/backup.$int" ]; then
        next_int=$((int + 1))
        $mv "$backup_dir/backup.$int" "$backup_dir/backup.$next_int"
    fi
done
exit 0

Crontab 排程

# 每天 16:28 執行
28 16 * * * /home/user/mysql_backup.sh

方案二：增強版（打包、掛載備份、FTP 異地）

#!/bin/sh
base_mysql_path=/data/webserver/mysql/
mysql_dump_path=/data/mysqlbackup/
mnt_back_path=/mnt/web/mysqlbackup/
ftp_back_path=mysql_data_backup/

mysql_user="root"
mysql_pass="********"
ftp_host="ip"
ftp_user="********"
ftp_pass="********"

backup_name=$(date +%y%m%d%H%M%S)
cd ${mysql_dump_path}

# 導出全庫
${base_mysql_path}bin/mysqldump -u${mysql_user} -p${mysql_pass} --all-databases > ${backup_name}.sql

# 壓縮
tar zcf ${backup_name}.tar.gz ${backup_name}.sql
rm -f ${backup_name}.sql

# 複製到掛載點
cp ${backup_name}.tar.gz ${mnt_back_path}${backup_name}.tar.gz

# FTP 上傳
ftp -n <

chown mysql:mysql /home/mybackup
# 或
chmod 777 /home/mybackup