Requirement: regularly back up MySQL specific database
Pre:
MySQL socket path;
# netstat -ln | grep mysql
unix 2 [ ACC ] STREAM LISTENING 37194 /tmp/mysql.sock
Backup command, named after dbname + date:
mysqldump --socket=/tmp/mysql.sock -u root -ppassword dbname > dbname_back_`date +%F`.sql
Mount to the public disk, there is no need for SCP;
Prepare the SH file, back up DB to a specific path, package it, and delete the backup for more than 30 days:
#!/bin/bash
backdir=/mnt/backup_db
d=`date +%F`
mysqldump --socket=/tmp/mysql.sock -u root -ppassword dbname > $backdir/dbname_back_$d.sql
cd $backdir
gzip *_$d.sql
find ./ -name "*.gz" -mtime +30 |xargs rm
Set sh file as executable:
chmod +x /mnt/backup_db/mysql_backup.sh
Determine the backup frequency and execute it at 7:59 a.m. every Friday;
# crontab -e
59 07 * * 5 /bin/bash /mnt/backup_db/mysql_backup.sh >/tmp/mysql_backup.log 2>/tmp/mysql_backup.log