How to backup MySQL databases automatically

Use mysqldump and implement backup rotation:

#!/usr/bin/perl
use strict;
use warnings;
use POSIX qw(strftime);

my $db_name = 'myapp';
my $db_user = 'backup_user';
my $db_pass = 'backup_pass';
my $backup_dir = '/backups/mysql';
my $keep_days = 7;

my $timestamp = strftime("%Y%m%d_%H%M%S", localtime);
my $backup_file = "$backup_dir/${db_name}_$timestamp.sql.gz";

my $dump_cmd = "mysqldump -u$db_user -p$db_pass $db_name | gzip > $backup_file";
system($dump_cmd) == 0 or die "Backup failed: $?";

print "Backup created: $backup_file\n";

# Remove old backups
my $cutoff_time = time - ($keep_days * 86400);
opendir my $dh, $backup_dir or die "Cannot open $backup_dir: $!";
while (my $file = readdir $dh) {
    next unless $file =~ /^${db_name}_(\d+_\d+)\.sql\.gz$/;
    my $filepath = "$backup_dir/$file";
    if ((stat($filepath))[9] < $cutoff_time) {
        unlink $filepath;
        print "Removed old backup: $file\n";
    }
}
closedir $dh;

This script creates compressed MySQL backups with timestamps and removes backups older than 7 days.