mysql怎么定时备份数据库

#!/bin/bash

# 数据库连接信息
DB_USER="your_username"
DB_PASS="your_password"
DB_NAME="your_database_name"
BACKUP_DIR="/path/to/backup/directory"

# 生成备份文件名
DATE=$(date +%Y%m%d%H%M%S)
BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$DATE.sql"

# 执行备份
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_FILE

# 压缩备份文件
gzip $BACKUP_FILE

# 删除7天前的备份文件
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +7 -exec rm {} \;

crontab -e

0 2 * * * /path/to/your/backup_script.sh

SET GLOBAL event_scheduler = ON;

CREATE EVENT daily_backup
ON SCHEDULE EVERY 1 DAY
STARTS '2023-04-01 02:00:00'
DO
BEGIN
    DECLARE BACKUP_FILE VARCHAR(255);
    SET BACKUP_FILE = CONCAT('/path/to/backup/directory/', DATABASE(), '-', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '.sql');
    SET @sql = CONCAT('mysqldump -u your_username -pyour_password ', DATABASE(), ' > ', BACKUP_FILE);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;