怎样实现mysql定时备份

#!/bin/bash

# 数据库配置
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_NAME="your_db_name"
BACKUP_DIR="/path/to/backup/directory"
DATE=$(date +%Y%m%d%H%M%S)

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u$DB_USER -p$DB_PASS $DB_NAME > $BACKUP_DIR/$DB_NAME-$DATE.sql

# 压缩备份文件
gzip $BACKUP_DIR/$DB_NAME-$DATE.sql

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

crontab -e

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

SET GLOBAL event_scheduler = ON;

CREATE EVENT backup_event
ON SCHEDULE EVERY 1 DAY
DO
  CALL mysql_backup_procedure();

DELIMITER $$

CREATE PROCEDURE mysql_backup_procedure()
BEGIN
  DECLARE backup_file VARCHAR(255);
  SET backup_file = CONCAT('/path/to/backup/directory/', 'db_name_', CURDATE(), '.sql');
  SET @sql_command = CONCAT('mysqldump -u your_db_user -p your_db_password db_name > ', backup_file);
  PREPARE stmt FROM @sql_command;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;